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=>'%');