Find datafiles' High Watermark per tablespace

This is an easy way to automatically create the commands to resize datafiles to their HWM and reclaim space.
Just, declare your tablespace name in V_TBLSPC variable:

SET SERVEROUTPUT ON

DECLARE
   V_STMT     VARCHAR2 (500);
   V_TBLSPC   VARCHAR2 (30) := 'CZD';

   CURSOR C1
   IS
      SELECT FILE_ID
        FROM DBA_DATA_FILES
       WHERE TABLESPACE_NAME = V_TBLSPC;
BEGIN
   FOR LINE IN C1
   LOOP
      SELECT    'ALTER DATABASE DATAFILE '
             || ''''
             || D.FILE_NAME
             || ''''
             || ' RESIZE '
             || NVL (CEIL (D.BYTES / 1024 / 1024 - TAKE_BACK.TAKE_BACK_MB),
                     D.BYTES / 1024 / 1024)
             || 'M;'
                SQL
        INTO V_STMT
        FROM DBA_DATA_FILES D,
             (SELECT SUM (BYTES) / 1024 / 1024 TAKE_BACK_MB
                FROM DBA_FREE_SPACE
               WHERE TABLESPACE_NAME = V_TBLSPC AND FILE_ID = LINE.FILE_ID
                     AND BLOCK_ID >=
                            NVL (
                               (SELECT (A.BLOCK_ID + (A.BYTES / B.BLOCK_SIZE))
                                  FROM DBA_EXTENTS A, DBA_TABLESPACES B
                                 WHERE A.BLOCK_ID =
                                          (SELECT MAX (BLOCK_ID)
                                             FROM DBA_EXTENTS
                                            WHERE FILE_ID = LINE.FILE_ID
                                                  AND TABLESPACE_NAME =
                                                         V_TBLSPC)
                                       AND A.FILE_ID = LINE.FILE_ID
                                       AND A.TABLESPACE_NAME = V_TBLSPC
                                       AND B.TABLESPACE_NAME = V_TBLSPC),
                               0)) TAKE_BACK
       WHERE D.FILE_ID = LINE.FILE_ID;

      DBMS_OUTPUT.PUT_LINE (V_STMT);
   END LOOP;
END;
/

A sample output of the above script is:

ALTER DATABASE DATAFILE '/oracle67/oradata/czd20.dbf' RESIZE 4994M;
ALTER DATABASE DATAFILE '/oracle7/oradata/czd05.dbf' RESIZE 3091M;
ALTER DATABASE DATAFILE '/oracle6/oradata/czd06.dbf' RESIZE 3092M;
ALTER DATABASE DATAFILE '/oracle6/oradata/czd07.dbf' RESIZE 3094M;
ALTER DATABASE DATAFILE '/oracle6/oradata/czd08.dbf' RESIZE 3093M;
ALTER DATABASE DATAFILE '/oracle6/oradata/czd09.dbf' RESIZE 3090M;
ALTER DATABASE DATAFILE '/oracle6/oradata/czd01.dbf' RESIZE 3095M;
ALTER DATABASE DATAFILE '/oracle7/oradata/czd10.dbf' RESIZE 2048M;
ALTER DATABASE DATAFILE '/oracle6/oradata/czd02.dbf' RESIZE 3096M;
ALTER DATABASE DATAFILE '/oracle7/oradata/czd03.dbf' RESIZE 3090M;
ALTER DATABASE DATAFILE '/oracle7/oradata/czd04.dbf' RESIZE 3096M;
ALTER DATABASE DATAFILE '/oracle40/oradata/czd11.dbf' RESIZE 4995M;
ALTER DATABASE DATAFILE '/oracle43/oradata/czd12.dbf' RESIZE 9998M;
ALTER DATABASE DATAFILE '/oracle48/oradata/czd13.dbf' RESIZE 8093M;
ALTER DATABASE DATAFILE '/oracle52/oradata/czd14.dbf' RESIZE 3996M;
ALTER DATABASE DATAFILE '/oracle54/oradata/czd15.dbf' RESIZE 14996M;
ALTER DATABASE DATAFILE '/oracle58/oradata/czd16.dbf' RESIZE 4000M;
ALTER DATABASE DATAFILE '/oracle59/oradata/czd17.dbf' RESIZE 9994M;
ALTER DATABASE DATAFILE '/oracle62/oradata/czd18.dbf' RESIZE 13497M;
ALTER DATABASE DATAFILE '/oracle66/oradata/czd19.dbf' RESIZE 15497M;
ALTER DATABASE DATAFILE '/oracle69/oradata/czd21.dbf' RESIZE 11993M;
ALTER DATABASE DATAFILE '/oracle71/oradata/czd22.dbf' RESIZE 9994M;
ALTER DATABASE DATAFILE '/oracle75/oradata/czd23.dbf' RESIZE 6996M;
ALTER DATABASE DATAFILE '/oracle78/oradata/czd24.dbf' RESIZE 24993M;
ALTER DATABASE DATAFILE '/oracle76/oradata/czd25.dbf' RESIZE 9994M;
ALTER DATABASE DATAFILE '/oracle78/oradata/czd26.dbf' RESIZE 3995M;
ALTER DATABASE DATAFILE '/oracle81/oradata/czd27.dbf' RESIZE 24993M;
ALTER DATABASE DATAFILE '/oracle81/oradata/czd28.dbf' RESIZE 24993M;
ALTER DATABASE DATAFILE '/oracle87/oradata/czd29.dbf' RESIZE 24985M;
ALTER DATABASE DATAFILE '/oracle88/oradata/czd30.dbf' RESIZE 24641M;
ALTER DATABASE DATAFILE '/oracle89/oradata/czd31.dbf' RESIZE 13641M;
ALTER DATABASE DATAFILE '/oracle90/oradata/czd32.dbf' RESIZE 8961M;

Find a schema's dependencies

Create the following, under SYSTEM or any other schema you may use for logging purposes:

CREATE TABLE HELPDESK.DEPSCHEMA
(
   OBJECT_ID              NUMBER,
   REFERENCED_OBJECT_ID   NUMBER,
   NEST_LEVEL             NUMBER,
   SEQ#                   NUMBER
)
TABLESPACE HELPDESK
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;

GRANT ALL ON HELPDESK.DEPSCHEMA TO SYSTEM;

CREATE SEQUENCE HELPDESK.DEPSCHEMASEQ
   START WITH 0
   INCREMENT BY 1
   MINVALUE 0
   MAXVALUE 100000000
   NOCACHE
   CYCLE
   NOORDER;

GRANT ALL ON HELPDESK.DEPSCHEMASEQ TO SYSTEM;

CREATE OR REPLACE FORCE VIEW HELPDESK.DEPSCHEMA_V
(
   SEQ#,
   NEST_LEVEL,
   SCHEMA,
   OBJECT_TYPE,
   OBJECT_NAME,
   STATUS;
)
AS
     SELECT D.SEQ#,
            D.NEST_LEVEL,
            O.OWNER,
            O.OBJECT_TYPE,
            O.OBJECT_NAME,
            O.STATUS
       FROM HELPDESK.DEPSCHEMA D, ALL_OBJECTS O
      WHERE D.OBJECT_ID = O.OBJECT_ID(+)
   ORDER BY SEQ#;

CREATE OR REPLACE PROCEDURE SYSTEM.DEPSCHEMA_FILL (SCHEMA CHAR)
IS
   TYPE T_CURSOR IS REF CURSOR;
   V_CURSOR   T_CURSOR;
   OBJ_ID     NUMBER;

BEGIN
   DELETE FROM HELPDESK.DEPSCHEMA;
   COMMIT;

   OPEN V_CURSOR FOR
      SELECT OBJECT_ID
        FROM ALL_OBJECTS
       WHERE OWNER = UPPER (DEPSCHEMA_FILL.SCHEMA) AND OBJECT_TYPE != 'INDEX';

   LOOP
      FETCH V_CURSOR INTO OBJ_ID;
      EXIT WHEN V_CURSOR%NOTFOUND;

      INSERT INTO HELPDESK.DEPSCHEMA
           VALUES (OBJ_ID,
                   0,
                   0,
                   HELPDESK.DEPSCHEMASEQ.NEXTVAL);

      INSERT INTO HELPDESK.DEPSCHEMA
             SELECT OBJECT_ID,
                    REFERENCED_OBJECT_ID,
                    LEVEL,
                    HELPDESK.DEPSCHEMASEQ.NEXTVAL
               FROM PUBLIC_DEPENDENCY A
         CONNECT BY PRIOR A.OBJECT_ID = A.REFERENCED_OBJECT_ID
         START WITH REFERENCED_OBJECT_ID = DEPSCHEMA_FILL.OBJ_ID;
   END LOOP;
   COMMIT;
END;
/

Run the procedure:

SQL> EXEC SYSTEM.DEPSCHEMA_FILL('xxe');
PL/SQL procedure successfully completed.

And finally querying the HELPDESK.DEPSCHEMA_V, you will get:

SEQ#	NEST_LEVEL	OWNER	OBJECT_TYPE	OBJECT_NAME	STATUS
132	0	XXE	TABLE	XXLL_ERROR_DEBUG_LOG	VALID
133	1	APPS	SYNONYM	XXLL_ERROR_DEBUG_LOG	VALID
134	2	APPS	PACKAGE BODY	XXLL_UTILS	VALID
135	0	XXE	TRIGGER	XXLL_PARAGRAPH_ID_T	VALID
136	0	XXE	TABLE	XXLL_ADJUSTMENT_TYPE_CONF	VALID
137	0	XXE	TABLE	XXE_IPTV_SYMPTOMS	VALID
138	1	APPS	SYNONYM	XXE_IPTV_SYMPTOMS	VALID
139	1	APPS	PACKAGE BODY	XXNTT_SERVICEREQUEST_PVT	VALID
140	1	APPS	VIEW	XXSLAM_TICKETS_V	VALID
141	0	XXE	TABLE	XXE_CS_CALC_INACTIVE	VALID
142	1	APPS	PACKAGE BODY	XXE_ISUPPORT_EXTRA_DATA	VALID

So, for instance, table XXE.XXLL_ERROR_DEBUG_LOG is used by synonym APPS.XXLL_ERROR_DEBUG_LOG, which is used by package body APPS.XXLL_UTILS.

Here, I describe a method to find common dependencies between objects in the same schema or not.

Online table move - reorganisation using DBMS_REDEFINITION

Let's say you have little or no downtime available to move segments to a new tablespace.
Or, perhaps, you have very big segments in a data dictionary tablespace with very small extents.
In a tablespace, I have some segments a few GB each, with thousands KB extents.
If you execute a DDL statement, such as ALTER TABLE MOVE [ATM], TRUNCATE, DROP at those segments,
you will notice your session to take A LOT of time to complete, with "Row Cache Lock" and "DFS Lock Handle" waits.
If you trace your session, you will discover a lot of activity to fet$ and uet$ tables.
Since the tablespace is dictionary managed, those thousand of extents must be deleted from uet$ [used extents dictionary table]
and inserted into fet$ [free extents dictionary table] and then released.


A quick way for these situations is to use the DBMS_REDEFINITION package.
Let's say you want to move a table [ORIG TABLE], under schema [USER] from one tablespace [TBS1] to another [TBS2].


Do not forget to give the necessary quota to [USER] in [TBS2].


First of all, check if [ORIG TABLE] can be used by the redefinition process, by executing:

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('[USER]','[ORIG TABLE]');

If you get an error, is probably because [ORIG TABLE] has no primary key.
You may use ROWID, so the following execution should return no error:

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('[USER]','[ORIG TABLE]',2);

Now, create an interim table [INT TABLE], an empty, exact copy of [ORIG TABLE].
You may use a "Create As Select" [CTAS] statement with "where 1=2" clause, but know that if a column has a default value set, it will not be transferred.
The best way is to get the creation script of [ORIG TABLE] and change it accordingly to create the [INT TABLE].
You can get it from a tool like TOAD, or by executing the following command:

SELECT DBMS_METADATA.GET_DDL('TABLE','[ORIG TABLE]','[USER]') FROM DUAL;

You should not transfer the "Big size, Small extent" problem to [INT TABLE].
A rule of thumb could be to use the 1/10 of current table size for INITIAL EXTENT and the 1/100 for NEXT EXTENT:

SELECT SEGMENT_NAME, BYTES/1024 SIZE_K,CEIL(BYTES/1024/10) INITIAL_K, CEIL(BYTES/1024/100) NEXT_K
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = '[ORIG TABLE]'
AND OWNER = '[USER]';

At this point, you have [ORIG TABLE] in tablespace [TBS1] and an empty [INT TABLE] in tablespace [TBS2].


Start the redefinition process, by issuing the following:
If your table is huge, then parallelize first:

ALTER SESSION FORCE PARALLEL DML PARALLEL [#];
ALTER SESSION FORCE PARALLEL QUERY PARALLEL [#];

Then for PK method:

EXEC DBMS_REDEFINITION.START_REDEF_TABLE(uname=>'[USER]',orig_table=>'[ORIG TABLE]',int_table=>'[INT TABLE]');

Or for ROWID method:

EXEC DBMS_REDEFINITION.START_REDEF_TABLE(uname=>'[USER]',orig_table=>'[ORIG TABLE]',int_table=>'[INT TABLE]',options_flag=>2);

If your database is 9i, you should create the appropriate triggers, constraints, privileges and indexes manually to [INT TABLE].
If your database is 10g or higher, you may use COPY_TABLE_DEPENDENTS procedure to transfer constraints, triggers and privileges to [INT TABLE]:

DECLARE
   ERR   PLS_INTEGER;
BEGIN
   DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS (UNAME           => '[USER]',
                                            ORIG_TABLE      => '[ORIG TABLE]',
                                            INT_TABLE       => '[INT TABLE]',
                                            COPY_INDEXES    => 0,
                                            NUM_ERRORS      => ERR,
                                            IGNORE_ERRORS   => TRUE);
END;
/

Better use creation scripts for indexes, and not this procedure.
Set TRUE for ignore_errors, because the NOT NULL constraints will be already transfered and setting it to FALSE will result to an error.
Just after the execution, query the DBA_REDEFINITION_ERRORS view for errors not related to NOT NULL constraints and investigate them.


Now, you may finish the redefinition process:

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname=>'[USER]',orig_table=>'[ORIG TABLE]',int_table=>'[INT TABLE]');

Querying the DBA_REDEFINITION_OBJECTS view should return no rows.


Now you should have [ORIG TABLE] in tablespace [TBS2] and [INT TABLE] under tablespace [TBS1], which you can safely DROP.


Know this, DML statements [INSERT, UPDATE, DELETE] are permitted during the redefinition process.
The committed changes will be written to [ORIG TABLE] only, until you execute the FINISH_REDEF_TABLE and synchronize both tables.
The goal, which is actually achieved, is that the committed changes will always be written to [ORIG TABLE], during and after the redefinition process.
BUT any uncommitted transactions will result to a "Wait for Table Lock" wait to the session running the FINISH_REDEF_TABLE procedure.
ONLY when these transactions are committed the FINISH_REDEF_TABLE will be successfully completed and, conclusively, the whole redefinition process.