Empty oracle table ticket clearing common code segments

1. query table space usage:
Sqlplus system/manager @ topprod
Copy codeThe Code is as follows:
SQL> @ q_tbsFREE

2. How to query temp:
Sqlplus system/manager @ topprod
Copy codeThe Code is as follows:
SQL> SELECT
D. 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
)
, (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. Clear TEMP temporary tablespace: (if there is no user connection, it is best to restart the database before cleaning)
Copy codeThe Code is as follows:
# Restarting a database
Sqlplus '/as sysdba'
SQL> shutdown immediate
SQL> startup
# Create a temporary tablespace temp02 for temporary replacement
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 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 4096 M autoextend on next 100 M;
# Returning the system temporary tablespace to temp
SQL> alter database default temporary tablespace temp;
# Delete temporary tablespace temp02
SQL> drop tablespace temp02 including contents and datafiles;

3. Clear the UNDO tablespace: (if there is no user connection, it is best to restart the database before cleaning)
Copy codeThe Code is as follows:
# Restarting a database
Sqlplus '/as sysdba'
SQL> shutdown immediate
SQL> startup
# Create an UNDO tablespace undotbs2 for temporary replacement
SQL> create undo tablespace undotbs2 datafile '/u2/oradb/oradata/topprod/undotbs02.dbf' size 10 M autoextend on next 10 M;
# Point the system UNDO tablespace to undotbs2
SQL> alter system set undo_tablespace = undotbs2 scope = both;
# Ensure that all undo segments in UNDOTBS1 have 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 4096 M;
# Returning the system UNDO tablespace to undotbs1
SQL> alter system set undo_tablespace = undotbs1 scope = both;
# Delete UNDO tablespace undotbs2
SQL> drop tablespace undotbs2 including contents and datafiles;

3. Clear the TEMPTABS tablespace:
Copy codeThe Code is as follows:
# Delete the TEMPTABS tablespace
SQL> drop tablespace temptabs including contents and datafiles;
# Create a TEMPTABS tablespace
SQL> create tablespace temptabs datafile '/u2/oradb/oradata/topprod/temptabs. dbf' size 4096 M autoextend on next 100 M;
Or delete a 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. Add the system tablespace:
Copy codeThe Code is as follows:
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;