Claim empty space of tablespace to reuse

PSIMAGE has 83% free space and need to reclaim it.

SQL> select df.tablespace_name "Tablespace",
 totalusedspace "Used MB",
 (df.totalspace - tu.totalusedspace) "Free MB",
 df.totalspace "Total MB",
 round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
 "Pct. Free"
 from
 (select tablespace_name,
 round(sum(bytes) / 1048576) TotalSpace
 from dba_data_files
 group by tablespace_name) df,
 (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
 from dba_segments
 group by tablespace_name) tu
 where df.tablespace_name = tu.tablespace_name AND
df.tablespace_name='PSIMAGE';


Tablespace                 Used MB     Free MB    Total MB  Pct. Free
----------------------             -----------         -----------       -----------      ----------
PSIMAGE                   4,978           23,530       28,508      83


SQL>
SQL> SELECT * from dba_directories;

OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS PRD_DIR
/backup1/expimp/hrprd

create directory DUATBK_DIR as '/backup/dbbackup';
grant read, write on directory DUATBK_DIR to system;

TAKE FULL BACKUP:-
expdp system/manager full=y directory=DUATBK_DIR dumpfile='DUAT27MAY2017.dmp' logfile='DUAT27MAY2017.log';

TAKE PSIMAGE TABLESPACE BACKUP:-
expdp system/manager DIRECTORY=DUATBK_DIR DUMPFILE=DUAT27MAY2017TBLPSIMAGE.dmp LOGFILE=DUAT27MAY2017TBLPSIMAGE_LOG.log TABLESPACES= PSIMAGE;


SQL> select file_name from dba_data_files where tablespace_name='PSIMAGE';

FILE_NAME
--------------------------------------------------------------------------------
/backup1/oradata/duat/psimage.dbf


DROP TABLESPACE PSIMAGE INCLUDING CONTENTS and datafile;

SQL> DROP TABLESPACE PSIMAGE INCLUDING CONTENTS;

Tablespace dropped.


Now, PSIMAGE tablespace must not available in database.

SELECT tablespace_name FROM dba_tablespaces where tablespace_name='PSIMAGE';
SELECT file_name FROM dba_data_files where tablespace_name='PSIMAGE';


Create tablespace again:
----------------------------------------
CREATE TABLESPACE PSIMAGE DATAFILE '/backup1/oradata/duat/psimage.dbf' SIZE 1813M EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;


Alter tablespace datafile:
----------------------------------------
ALTER DATABASE DATAFILE '/backup1/oradata/duat/psimage.dbf' AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED;


Import data from backup using impdp:
--------------------------------------------------------------
impdp system/manager DIRECTORY=DUATBK_DIR DUMPFILE=DUAT27MAY2017TBLPSIMAGE.dmp LOGFILE=DUAT27MAY2017TBLPSIMAGE_LOG-IMP.log TABLESPACES= PSIMAGE;

Check now, database tablespace PSIMAGE size should be according to the original data only.