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
2. Query how to use temp:
sqlplus system/manager@topprod
2. Clean up the TEMP temporary tablespace: (Operate without user connection, preferably restart the database under 1 before cleaning up)
3. Cleanup the UNDO tablespace: (Operate without user connection, preferably restart the database under 1 before cleaning up)
3. Clean up the TEMPTABS tablespace:
4. Increase the system table space:
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;