Rebuild the application tablespace

ORACLE_Guide_Rebuild the applicationtablespace

1. Please exp the tablespace, like this:

$ exp"'sys/sys as sysdba'" file=/opt/oracle/oradata/tablespace_test.dmp log=/opt/oracle/oradata/tablespace_test.log direct=y recordlength=65535 tablespaces=(tbs_test)

2. Find out the related indexes

SELECT DBMS_METADATA.GET_DDL('INDEX', F.INDEX_NAME, F.OWNER)  || ';'   FROM (SELECTDISTINCT T.OWNER, T.INDEX_NAME           FROM (SELECT  A.OWNER, A.INDEX_NAME                   FROM DBA_INDEXES A, DBA_SEGMENTS B                  WHERE B.OWNER = A.OWNER                    AND B.SEGMENT_NAME = A.TABLE_NAME                    AND B.SEGMENT_TYPE IN ('TABLE',  'TABLE  PARTITION')                    AND B.TABLESPACE_NAME IN ('TBS_TEST')                 UNIONALL                 SELECT C.OWNER, C.INDEX_NAME                   FROM DBA_INDEXES C                  WHERE C.TABLESPACE_NAME IN ('TBS_TEST'))  T) F;

3. Drop and Rebuild the tablespace

$ sqlplus / as sysdba

set long 999999999

set linesize 400

set pagesize 20000

set feedback ON

set heading off

spool /tmp/tablespace_script.txt

SELECT DBMS_METADATA.GET_DDL('TABLESPACE', 'TBS_TEST')||';' FROMdual;

Spool off;

SQL>

SQL> drop tablespace TBS_TEST including contents and datafiles;

And rebuild the new tablespace using the create script.

4. imp the tablespace backup, like this:

$ imp"'sys/sys as sysdba'" file=/opt/oracle/oradata/tablespace_test.dmplog=/opt/oracle/oradata/tablespace_test_imp.log feedback=10000 buffer=10240000IGNORE=Y COMMIT=Y tablespaces=(tbs_test)

5. Rebuild the related indexes;

6. Recompile the invalid objects:

$ cd$ORACLE_HOME/rdbms/admin

$ sqlplus / assysdba

SQL>@utlrp.sql

SQL> select'alter ' || object_type || ' ' || owner || '.' || object_name || ' compile;'from dba_objects where status = 'INVALID';

7.Gather statistics

SQL>exec dbms_stats.gather_schema_stats(ownname=> 'TEST',cascade=>TRUE,estimate_percent=>30);