Empty oracle table ticket cleanup

Empty oracle table ticket cleanup 1. query table space usage: sqlplus system/manager @ topprodSQL> @ q_tbsFREE2. how to query temp: sqlplus system/manager @ topprodSQL> SELECT d. tablespace_name, d. status tablespace_status, NVL (. bytes, 0) tablespace_size, NVL (t. bytes, 0) used, TRUNC (NVL (t. bytes/. bytes * 100, 0) used_pct, NVL (s. current_users, 0) current_usersFROM sys. dba_tablespaces d, (select tablespace_name, sum (bytes) bytes from nation group by tablespace_name) a, (select tablespace_name, sum (bytes_cached) bytes from v $ temp_extent_pool group by tablespace_name) t, v $ sort_segment sWHERE d. tablespace_name =. tablespace_name (+) AND d. tablespace_name = t. tablespace_name (+) AND d. tablespace_name = s. tablespace_name (+) AND d. extent_management like 'local' AND d. contents like 'temporary '; 2. clean TEMP temporary tablespace: (if there is no user connection, it is best to restart the database before cleaning) # restart the database sqlplus '/as sysdba' SQL> shutdown immediateSQL> startup # create a temporary tablespace temp02, SQL> create temporary tablespace temp02 tempfile '/u2/oradb/oradata/topprod/temp02.dbf' size 10 M autoextend on next 10 M; # point the system temporary tablespace to temp02SQL> alter database default temporary tablespace temp02; # Delete the original temporary tablespace tempSQL> drop tablespace temp including contents and datafiles; # create a temporary tablespace tempSQL> create temporary tablespace temp tempfile '/u2/oradb/oradata/topprod/temp01.dbf' size 4096 M autoextend on next 100 M; # returning the system temporary tablespace to tempSQL> alter database default temporary tablespace temp; # deleting the temporary tablespace temp02SQL> drop tablespace temp02 including contents and datafiles; 3. clear UNDO tablespace: (if there is no user connection, it is best to restart the database before cleaning) # restart the database sqlplus '/as sysdba' SQL> shutdown immediateSQL> startup # create an UNDO tablespace undotbs2, SQL> create undo tablespace undotbs2 datafile '/u2/oradb/oradata/topprod/undotbs02.dbf' size 10 M autoextend on next 10 M; # direct the system UNDO tablespace to undotbs2SQL> alter system set undo_tablespace = undotbs2 scope = both; # ensure that all undo segment in UNDOTBS1 has offlineSQL> select SEGMENT_NAME, STATUS, TABLESPACE_NAME from Region; # Delete the original UNDO tablespace undotbs1SQL> drop tablespace undotbs1 including contents and datafiles; # create a new temporary tablespace undotbs1SQL> create undo tablespace undotbs1 datafile '/u2/oradb/oradata/topprod/undotbs01.dbf' size 4096 M; # returning system UNDO tablespace refers to undotbs1SQL> alter system set undo_tablespace = undotbs1 scope = both; # deleting UNDO tablespace undotbs2SQL> drop tablespace undotbs2 including contents and datafiles; 3. clear TEMPTABS tablespace: # Delete TEMPTABS tablespace SQL> drop tablespace temptabs including contents and datafiles; # create TEMPTABS tablespace SQL> create tablespace temptabs datafile '/u2/oradb/oradata/topprod/temptabs. dbf 'size 4096 M autoextend on next 100 M; or delete the table select 'drop table' | segment_name | '; 'From dba_segments where tablespace_name = 'temptabs 'and segment_name like 'tt %' and segment_name not like '% _ file'; 4. add SYSTEM tablespace: alter tablespace SYSTEM add datafile '/u2/oradb/oradata/topprod/system02.dbf' size 2000 M autoextend on next 10 M; alter tablespace SYSAUX add datafile '/u2/oradb/oradata/topprod/sysaux02.dbf' size 2000 M autoextend on next 10 M;