Processing When the Oracle tablespace is too large

Processing When the Oracle tablespace is too large

First, view the data usage of all Oracle tablespaces.

SELECT Upper (F. TABLESPACE_NAME) "tablespace name", D. TOT_GROOTTE_MB "tablespace size (M)", D. TOT_GROOTTE_MB-F. TOTAL_BYTES "used space (M)", To_char (Round (D. TOT_GROOTTE_MB-F. TOTAL_BYTES)/D. TOT_GROOTTE_MB * 100, 2), '2017. 99 ') |' % '"usage ratio", F. TOTAL_BYTES "Free Space (M)", F. MAX_BYTES "maximum block (M)" FROM (SELECT TABLESPACE_NAME, Round (Sum (BYTES)/(1024*1024), 2) TOTAL_BYTES, Round (Max (BYTES) // (1024*1024), 2) MAX_BYTES from sys. DBA_FREE_SPACE group by TABLESPACE_NAME) F, (select dd. TABLESPACE_NAME, Round (Sum (DD. BYTES)/(1024*1024), 2) TOT_GROOTTE_MB from sys. DBA_DATA_FILES dd group by dd. TABLESPACE_NAME) dwhere d. TABLESPACE_NAME = F. TABLESPACE_NAMEORDER BY 1

-Query the first 15 tables with relatively large space

select a.*   from(    select owner,segment_name,SEGMENT_TYPE, round(bytes/1024/1024/1024,2) all_size      from dba_segments      where SEGMENT_TYPE='TABLE'     order by  all_size desc   ) a where rownum <15;

-The data file size corresponding to the table space

Select t1.name, t2.name, Round (t2.BYTES/(1024*1024), 2) "size (M)" from v $ tablespace t1, v $ datafile t2 where t1.ts # = t2.ts # order by 1, 3

**

Start processing

**

-- Clear the recycle bin

Purge recyclebin; Purge recyclebin; -- clear the current user's recycle bin Purge dba_recyclebin; -- clear the recycle bin of all users

-- Method for deleting a large table from the thought of truncate sys. aud $

-View database Audit information (NONE indicates that audit is not enabled)

SQL> show parameter  audit_trail NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------audit_trail                          string      DB

-- Final direct processing

(1) truncate table aud $; release the size of the SYSTEM tablespace

SQL> alter table xx_xlog enable row movement;Table alteredSQL> alter table xx_xlog shrink space compact;Table alteredSQL> alter table xx_xlog shrink space;Table altered

Space used to release a table

Make sure that the following table list does not need to be retained

truncate table JXNCPC.XX_XLOG reuse storage; truncate table JXPC141224.XX_XLOG  reuse storage;truncate table TEST001.XX_XLOG  reuse storage;truncate table NCJXPC2013.XX_XLOG  reuse storage;truncate table NCJXPC2014.XX_XLOG  reuse storage;truncate table NCJXPCTEMP.XX_XLOG  reuse storage;truncate table NCJXPCERP.XX_XLOG  reuse storage;alter table JXNCPC.XX_XLOG enable row movement;alter table JXPC141224.XX_XLOG enable row movement;alter table TEST001.XX_XLOG enable row movement;alter table NCJXPC2013.XX_XLOG  enable row movement;alter table NCJXPC2014.XX_XLOG  enable row movement;alter table NCJXPCTEMP.XX_XLOG  enable row movement;alter table NCJXPCERP.XX_XLOG enable row movement;alter table JXNCPC.XX_XLOG shrink space compact; alter table JXPC141224.XX_XLOG  shrink space compact;alter table TEST001.XX_XLOG  shrink space compact;alter table NCJXPC2013.XX_XLOG  shrink space compact;alter table NCJXPC2014.XX_XLOG  shrink space compact;alter table NCJXPCTEMP.XX_XLOG  shrink space compact;alter table NCJXPCERP.XX_XLOG  shrink space compact;alter table JXNCPC.XX_XLOG shrink space; alter table JXPC141224.XX_XLOG  shrink space;alter table TEST001.XX_XLOG  shrink space;alter table NCJXPC2013.XX_XLOG  shrink space;alter table NCJXPC2014.XX_XLOG  shrink space;alter table NCJXPCTEMP.XX_XLOG  shrink space;alter table NCJXPCERP.XX_XLOG  shrink space;----truncate table JXNCPC.SM_OPERATELOG;truncate table JXPC141224.SM_OPERATELOG; truncate table TEST001.SM_OPERATELOG;    truncate table NCJXPCTEMP.SM_OPERATELOG;     truncate table NCJXPCERP.SM_OPERATELOG;  truncate table NCJXPC2013.SM_OPERATELOG;     truncate table NCJXPC2014.SM_OPERATELOG;    

Release table buckets