SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
SMPERP_MV
SMPERP_MV_IDX
USERS
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/ora103/oradata/STCSAPP/temp01.dbf
/ora103/oradata/STCSAPP/temp02.dbf
/ora103/oradata/STCSAPP/temp03.dbf
/ora103/oradata/STCSAPP/temp04.dbf
/ora103/oradata/STCSAPP/temp05.dbf
/ora103/oradata/STCSAPP/temp06.dbf
SQL> create temporary tablespace temp2 tempfile '/ora103/oradata/STCSAPP/temp07.dbf' size 5120m reuse autoextend on next 100m;
Tablespace created.
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
SMPERP_MV
SMPERP_MV_IDX
USERS
TEMP2
SQL> alter database default temporary tablespace temp2;
Database altered.
SQL> drop tablespace temp including contents and datafiles;
Tablespace dropped.
---------------------
create temporary tablespace temp tempfile '/ora103/oradata/STCSAPP/temp01.dbf' size 5120m reuse autoextend on next 100m;
alter database default temporary tablespace temp;
drop tablespace temp2 including contents and datafiles;
select name from v$tempfile;
alter tablespace temp add tempfile '/ora103/oradata/STCSAPP/temp02.dbf' size 5120m reuse autoextend on next 100m;
alter tablespace temp add tempfile '/ora103/oradata/STCSAPP/temp03.dbf' size 5120m reuse autoextend on next 100m;
alter tablespace temp add tempfile '/ora103/oradata/STCSAPP/temp04.dbf' size 5120m reuse autoextend on next 100m;
alter tablespace temp add tempfile '/ora103/oradata/STCSAPP/temp05.dbf' size 5120m reuse autoextend on next 100m;
alter tablespace temp add tempfile '/ora103/oradata/STCSAPP/temp06.dbf' size 5120m reuse autoextend on next 100m;
在logical standby:
SQL> EXECUTE DBMS_LOGSTDBY.SKIP(stmt=>'CREATE TABLESPACE', schema_name=>'SYS', object_name=>'%');
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGSTDBY.SKIP(stmt=>'DROP TABLESPACE', schema_name=>'SYS', object_name=>'%');
PL/SQL procedure successfully completed.
EXECUTE DBMS_LOGSTDBY.SKIP(stmt=>'ALTER TABLESPACE', schema_name=>'SYS', object_name=>'%');
如果主库增删表空间或数据文件,对于逻辑备库,db_file_name_convert参数是无用的,应该如下操作:
SQL> alter database stop logical standby apply;
SQL> alter session disable guard;
SQL> create/drop tablespace or add datafile
SQL> alter session enable guard;
SQL> alter database start logical standby apply immediate;
-------
EXECUTE DBMS_LOGSTDBY.UNSKIP(stmt=>'CREATE TABLESPACE', schema_name=>'SYS', object_name=>'%');
EXECUTE DBMS_LOGSTDBY.UNSKIP(stmt=>'DROP TABLESPACE', schema_name=>'SYS', object_name=>'%');
EXECUTE DBMS_LOGSTDBY.UNSKIP(stmt=>'ALTER TABLESPACE', schema_name=>'SYS', object_name=>'%');
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/789833/viewspace-1041125/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/789833/viewspace-1041125/