本文小编为大家详细介绍“Oracle本地UNDO模式怎么实现”,内容详细,步骤清晰,细节处理妥当,希望这篇“Oracle本地UNDO模式怎么实现”文章能帮助大家解决疑惑,下面跟着小编的思路慢慢深入,一起来学习新知识吧。
SQL>COLUMN property_name FORMAT A30 SQL>COLUMN property_value FORMAT A30 SQL>SELECT property_name, property_value FROM database_properties WHERE property_name = 'LOCAL_UNDO_ENABLED'; PROPERTY_NAME PROPERTY_VALUE ------------------------------ ------------------------------ LOCAL_UNDO_ENABLED TRUE
SELECT con_id, tablespace_name FROM cdb_tablespaces WHERE tablespace_name LIKE 'UNDO%' ORDER BY con_id; CON_ID TABLESPACE_NAME ---------- ------------------------------ 1 UNDOTBS1 3 UNDOTBS1
SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP UPGRADE; SQL> ALTER DATABASE LOCAL UNDO OFF; SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP;
SQL>COLUMN property_name FORMAT A30 SQL>COLUMN property_value FORMAT A30 SQL>SELECT property_name, property_value FROM database_properties WHERE property_name = 'LOCAL_UNDO_ENABLED'; PROPERTY_NAME PROPERTY_VALUE ----------------------------- ------------------------------ LOCAL_UNDO_ENABLED FALSE
虽然已经不是本地undo模式了,但是之前存在的undo表空间不会自动删除。如果碍事,要手动删除。
SQL>SELECT con_id, tablespace_name FROM cdb_tablespaces WHERE tablespace_name LIKE 'UNDO%' ORDER BY con_id; CON_ID TABLESPACE_NAME ---------- ------------------------------ 1 UNDOTBS1 3 UNDOTBS1
–删除多余的undo表空间
SQL>ALTER SESSION SET CONTAINER = pdb1; SQL>SELECT file_name FROM dba_data_files WHERE tablespace_name = 'UNDOTBS1'; ---------------------------------------------------------------------------------------------------- /u02/app/oracle/oradata/cdb1/pdb1/undotbs01.dbf SQL>DROP TABLESPACE undotbs1; Tablespace dropped.
随着所有老的undo表空间被移除了,现在该实例就是运行在共享undo模式上了。
利用上面的环境,重新切换回去。
可以看到是运行在共享undo模式上,而且只有一个undo表空间。
SQL>SELECT property_name, property_value FROM database_properties WHERE property_name = 'LOCAL_UNDO_ENABLED'; PROPERTY_NAME PROPERTY_VALUE ------------------------------ ------------------------------ LOCAL_UNDO_ENABLED FALSE SQL>SELECT con_id, tablespace_name FROM cdb_tablespaces WHERE tablespace_name LIKE 'UNDO%' ORDER BY con_id; CON_ID TABLESPACE_NAME ---------- ------------------------------ 1 UNDOTBS1
和切换为共享undo模式步骤相同。
SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP UPGRADE; SQL> ALTER DATABASE LOCAL UNDO ON; SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP;
SQL>COLUMN property_name FORMAT A30 SQL>COLUMN property_value FORMAT A30 SQL>SELECT property_name, property_value FROM database_properties WHERE property_name = 'LOCAL_UNDO_ENABLED'; PROPERTY_NAME PROPERTY_VALUE ------------------------------ ------------------------------ LOCAL_UNDO_ENABLED TRUE
–可以看到Oracle自动为PDB创建了一个undo表空间
SQL>SELECT con_id, tablespace_name FROM cdb_tablespaces WHERE tablespace_name LIKE 'UNDO%' ORDER BY con_id; CON_ID TABLESPACE_NAME ---------- ------------------------------ 1 UNDOTBS1 3 UNDO_1
可以看到新创建的数据库也是运行在本地undo模式上。
SQL>CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1; SQL>ALTER PLUGGABLE DATABASE pdb2 SAVE STATE; SQL>SELECT con_id, tablespace_name FROM cdb_tablespaces WHERE tablespace_name LIKE 'UNDO%' ORDER BY con_id; CON_ID TABLESPACE_NAME ---------- ------------------------------ 1 UNDOTBS1 3 UNDO_1 4 UNDOTBS1
读到这里,这篇“Oracle本地UNDO模式怎么实现”文章已经介绍完毕,想要掌握这篇文章的知识点还需要大家自己动手实践使用过才能领会,如果想了解更多相关内容的文章,欢迎关注亿速云行业资讯频道。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。