Following several alerts from a particular database about high usage of a few tablespaces, i set out to look into the issue and found that a few of the tablespaces had been dragging their feet over 95% usage but i also noticed that a couple of the tablespaces were not being used at all with their used percentage as close to zero.
The tablespaces that had the used percentage as zero had been hogging up a lot of disk space in their datafiles while the ones that really require more space to be added had very little space available from the disk since the mount points were already used in the high 90s. This led to reducing the datafiles that are under utilized.
Heres is how the utilitzation of tablespaces look like
TABLESPACE EXT_MGMT TOTAL(MBs) USED(MBs) FREE(MBs) MAX_FREE(MBs) %Full
------------------------- ---------- ---------- ---------- ---------- ------------- -----
TOOLS L 1900 1574.13 325.88 174.94 83
UNDO L 2400 1976.13 423.88 400.94 82
USER_BASE L 1000 .13 999.88 51.94 0
CUSTOMER L 1900 .13 1899.88 1799.94 0
INDX L 175 .13 174.88 99.94 0
MISC L 150 80.13 69.88 59.94 53
SQL> select sum(bytes)/1024/1024,file_name from dba_data_files
2 where tablespace_name='&tablespace_name' group by file_name
3 order by file_name;
Enter value for tablespace_name: CUSTOMER
old 2: where tablespace_name='&tablespace_name' group by file_name
new 2: where tablespace_name='CUSTOMER' group by file_name
SUM(BYTES)/1024/1024 FILE_NAME
-------------------- -------------------------
100 /prd/u01/CUSTOMER_02.DBF
1800 /prd/u02/CUSTOMER_01.DBF
SQL> select sum(bytes)/1024/1024,file_name from dba_data_files
2 where tablespace_name='&tablespace_name' group by file_name
3 order by file_name;
Enter value for tablespace_name: USER_BASE
old 2: where tablespace_name='&tablespace_name' group by file_name
new 2: where tablespace_name='USER_BASE' group by file_name
SUM(BYTES)/1024/1024 FILE_NAME
-------------------- ---------------------------
100 /prd/u01/USER_BASE_02.DBF
900 /prd/u02/USER_BASE_01.DBF
SQL> alter database datafile '/prd/u02/CUSTOMER_01.DBF' resize 100M;
Database altered.
SQL> select sum(bytes)/1024/1024,file_name from dba_data_files
2 where tablespace_name='&tablespace_name' group by file_name
3 order by file_name;
Enter value for tablespace_name: CUSTOMER
old 2: where tablespace_name='&tablespace_name' group by file_name
new 2: where tablespace_name='CUSTOMER' group by file_name
SUM(BYTES)/1024/1024 FILE_NAME
-------------------- --------------------------
100 /prd/u01/CUSTOMER_02.DBF
100 /prd/u02/CUSTOMER_01.DBF
SQL> alter database datafile '/prd/u02/USER_BASE_01.DBF' resize 100M;
alter database datafile '/prd/u02/USER_BASE_01.DBF' resize 100M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
Then I tried to locate the tiny objects that MAY be filling up the datafile in question above to know how far back can i reduce the datafile and found only one object in it and it happend to be a deleted table from the recyclebin.
SQL> select sum(bytes)/1024/1024,segment_name,segment_type,owner,tablespace_name,header_file 2 from dba_segments wheretablespace_name in ('USER_BASE') group by 3 owner,tablespace_name,header_file,segment_name,segment_type; SUM(BYTES)/1024/1024 SEGMENT_NAME SEGMENT_TYPE OWNER TABLESPACE_NAME HEADER_FILE -------------------- ------------ ------------ ----- --------------- ----------- 944 BIN$lLi0+QToAHbgRAADurJuKQ==$0 TABLE SCOTT USER_BASE 20
Wow....That was an interesting discovery, there is an object that is as big as the size of the datafile that it resides on !!!
SQL> select file_id,sum(bytes)/1024/1024,file_name from dba_data_files
2 where tablespace_name='USER_BASE' group by file_id,relative_fno,file_name;
FILE_ID SUM(BYTES)/1024/1024 FILE_NAME
---------- -------------------- ------------------------------
20 900 /prd/u02/USER_BASE_01.DBF
21 100 /prd/u01/USER_BASE_02.DBF
SQL> purge recyclebin;
Recyclebin purged.
SQL> select sum(bytes)/1024/1024,segment_name,segment_type,owner,tablespace_name,
2 header_file from dba_segments wheretablespace_name in ('USER_BASE') group by
3 owner,tablespace_name,header_file,segment_name,segment_type;
SUM(BYTES)/1024/1024 SEGMENT_NAME SEGMENT_TYPE OWNER TABLESPACE_NAME HEADER_FILE
-------------------- ------------ ------------ ----- --------------- -----------
944 BIN$lLi0+QToAHbgRAADurJuKQ==$0 TABLE SCOTT USER_BASE 20
SQL> select owner,OBJECT_NAME,ORIGINAL_NAME,TS_NAME from dba_recyclebin;
OWNER OBJECT_NAME ORIGINAL_NAME TS_NAME
----- ------------------------------ ------------- ----------
SCOTT BIN$lLi0+QToAHbgRAADurJuKQ==$0 DEMO_TABLE USER_BASE
so i tried a "PURGE TABLE" on the recyclebin named table and it worked, It no longer exists in the dba_segments neither does it show up in the dba_recyclebin and i was able to successfully reduce the datafile.
SQL> purge table scott."BIN$lLi0+QToAHbgRAADurJuKQ==$0";
Table dropped.
SQL> alter database datafile '/prd/u02/USER_BASE_01.DBF' resize 100M;
Database altered.
SQL> select sum(bytes)/1024/1024,file_name from dba_data_files
2 where tablespace_name='&tablespace_name' group by file_name
3 order by file_name;
Enter value for tablespace_name: USER_BASE
old 2: where tablespace_name='&tablespace_name' group by file_name
new 2: where tablespace_name='USER_BASE' group by file_name
SUM(BYTES)/1024/1024 FILE_NAME
-------------------- --------------------------
100 /prd/u01/USER_BASE_02.DBF
100 /prd/u02/USER_BASE_01.DBF
1) Keep purging the recycle bin from time to time, or may be set up a job to do that.
or
2) Advice the users to use the 'PURGE' keyword of the 'DROP TABLE' statement.
or
3) Turn off RECYCLEBIN - but this does not give you the option of flashing back your database to recover a lost table.
Posts
No comments:
Post a Comment