Scripts to get a list of objects to be moved for a data file resize for space released

Scripts to Get list of objects to be moved from a data file for space released

When you specify the size and file id during execution of the script then script will provide the list of objects for moving into new tablespace to reduce the current Size to size you mentioned in Script.
After you move you can resize the datafile with alter datafile resize command.

Follow the following link for more detail:
http://nikolayivankin.wordpress.com

So, the solution is to relocate used blocks to free gaps between for reduce the highwater marks of datafiles.

We can achieve this by several ways:

ALTER TABLE …. MOVE or INDEX REDUILD;
use DBMS_REDIFINITION package;
Export table, purge it and import again with the help of DataPump.

Following Script give the list of objects, which need to be relocated/moved to make datafile reduce possible size as needed.
The following query show such objects:

Parameters:
You can mention the file id and size upto which you want to reduce:

Script:

DECLARE
V_FILE_ID NUMBER;
V_BLOCK_SIZE NUMBER;
V_RESIZE_SIZE NUMBER;
BEGIN
V_FILE_ID := &FILE_ID;
V_RESIZE_SIZE := &RESIZE_FILE_TO;
SELECT BLOCK_SIZE
INTO V_BLOCK_SIZE
FROM V$DATAFILE
WHERE FILE# = V_FILE_ID;

DBMS_OUTPUT.PUT_LINE ('.');
DBMS_OUTPUT.PUT_LINE ('.');
DBMS_OUTPUT.PUT_LINE ('.');
DBMS_OUTPUT.PUT_LINE (
'OBJECTS IN FILE '
|| V_FILE_ID
|| ' THAT MUST MOVE IN ORDER TO RESIZE THE FILE TO '
|| V_RESIZE_SIZE
|| ' BYTES');
DBMS_OUTPUT.PUT_LINE (
'=======================================================================');
FOR my_record
IN ( SELECT DISTINCT
( OWNER
|| '.'
|| SEGMENT_NAME
|| ' - OBJECT TYPE = '
|| SEGMENT_TYPE)
ONAME
FROM DBA_EXTENTS
WHERE (block_id + blocks - 1) * V_BLOCK_SIZE > V_RESIZE_SIZE
AND FILE_ID = V_FILE_ID
ORDER BY 1)
LOOP
DBMS_OUTPUT.PUT_LINE (my_record.ONAME);
END LOOP;
END;
/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.