tempfile文件过大问题处理 for logical standby

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;

[@more@]

在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/