oracle Table Empty Single Cleaning Common Code Snippet Arrangement

  • 2021-09-05 01:14:34
  • OfStack

1. Query table space usage:
sqlplus system/manager@topprod
 
SQL>@q_tbsFREE 

2. Query how to use temp:
sqlplus system/manager@topprod
 
SQL>SELECT 
d.tablespace_name tablespace_name 
, d.status tablespace_status 
, NVL(a.bytes, 0) tablespace_size 
, NVL(t.bytes, 0) used 
, TRUNC(NVL(t.bytes / a.bytes * 100, 0)) used_pct 
, NVL(s.current_users, 0) current_users 
FROM 
sys.dba_tablespaces d 
, ( select tablespace_name, sum(bytes) bytes 
from dba_temp_files 
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 s 
WHERE 
d.tablespace_name = a.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 up the TEMP temporary tablespace: (Operate without user connection, preferably restart the database under 1 before cleaning up)
 
# Restart the database  
sqlplus '/as sysdba' 
SQL>shutdown immediate 
SQL>startup 
# Create 1 Temporary tablespace temp02 For temporary replacement  
SQL>create temporary tablespace temp02 tempfile '/u2/oradb/oradata/topprod/temp02.dbf' size 10M autoextend on next 10M; 
# Point the system temporary tablespace to temp02 
SQL>alter database default temporary tablespace temp02; 
# Delete the original temporary tablespace temp 
SQL>drop tablespace temp including contents and datafiles; 
# Create a new temporary tablespace temp 
SQL>create temporary tablespace temp tempfile '/u2/oradb/oradata/topprod/temp01.dbf' size 4096M autoextend on next 100M; 
# Refers the system temporary tablespace back to temp 
SQL>alter database default temporary tablespace temp; 
# Delete temporary tablespace temp02 
SQL>drop tablespace temp02 including contents and datafiles; 

3. Cleanup the UNDO tablespace: (Operate without user connection, preferably restart the database under 1 before cleaning up)
 
# Restart the database  
sqlplus '/as sysdba' 
SQL>shutdown immediate 
SQL>startup 
# Create 1 A UNDO Tablespace undotbs2 For temporary replacement  
SQL>create undo tablespace undotbs2 datafile '/u2/oradb/oradata/topprod/undotbs02.dbf' size 10M autoextend on next 10M; 
# Will the system UNDO Tablespace points to undotbs2 
SQL>alter system set undo_tablespace=undotbs2 scope=both; 
# Make sure that all UNDOTBS1 Adj. undo segment Have all been offline 
SQL> select SEGMENT_NAME ,STATUS ,TABLESPACE_NAME from dba_rollback_segs; 
# Delete the original UNDO Tablespace undotbs1 
SQL>drop tablespace undotbs1 including contents and datafiles; 
# Create a new temporary tablespace undotbs1 
SQL>create undo tablespace undotbs1 datafile '/u2/oradb/oradata/topprod/undotbs01.dbf' size 4096M; 
# Will the system UNDO Tablespace refers back to undotbs1 
SQL>alter system set undo_tablespace=undotbs1 scope=both; 
# Delete UNDO Tablespace undotbs2 
SQL>drop tablespace undotbs2 including contents and datafiles; 

3. Clean up the 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 4096M autoextend on next 100M; 
 Or delete the table  
[code] 
select 'drop table '||segment_name ||';' from dba_segments where tablespace_name='TEMPTABS' and segment_name like 'TT%' and segment_name not like '%_FILE'; 

4. Increase the system table space:
 
alter tablespace SYSTEM add datafile '/u2/oradb/oradata/topprod/system02.dbf' size 2000M autoextend on next 10M; 
alter tablespace SYSAUX add datafile '/u2/oradb/oradata/topprod/sysaux02.dbf' size 2000M autoextend on next 10M; 

Related articles: