Export/Import - EXPDP/IMPDP

EXPDP

expdp sys/pass@"ggate-scan.srv.bmogc.net:1521/ggatedb as sysdba" attach=SYS_EXPORT_FULL_01

expdp  \"/ as sysdba\"@"ggate-scan.srv.bmogc.net:1521/ggatedb" attach=SYS_EXPORT_FULL_01

Attach at the server to the job

expdp  \"/ as sysdba\" attach=SYS_EXPORT_FULL_01

IMPDP

nohup impdp \"/ as sysdba\" parfile=test.par &

impdp system/*****  attach=SYS_IMPORT_FULL_01

impdp \"/ as sysdba\" attach=SYS_IMPORT_SCHEMA_01

How to validate A DataPump Export (EXPDP) Dump File?

impdp \"/ as sysdba\" DIRECTORY=alex_temp DUMPFILE=GGATE.expdp_ggtab_54tables_20231006_%U.dmp SQLFILE=test.sql 

Monitoring Scripts

::::::::::::::
ASM_check.sql
::::::::::::::

set line 220 pages 1000 heading off feedback off termout off
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
col db_unique_name for a10
spool 1_asm_data.log appendSELECT d.db_unique_name,v.name
diskgroup,round((total_mb/1024),1) "Total_GB",round((free_mb/1024),1) "FREE_GB",round(((total_mb-free_mb)/total_mb*100),1) as Percentage FROM V$asm_diskgroup v, v$database d;
spool off

exit

::::::::::::::
DB_check_PDB.sql
::::::::::::::

set line 220 pages 100 heading off feedback off termout off
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
col OPEN_MODE for a10
col OPEN_TIME for a40
col name for a10
col RESTRICTED for a12

spool pdb_1_highlevel.log append
select inst_id,name, OPEN_MODE,RESTRICTED,OPEN_TIME,(select round(sum(bytes)/1024/1024/1024,0) from dba_segments) size_gb from gv$PDBS;

spool off

spool 4_outstandingalerts.log append
col reason for a70
col creation_time for a35
col suggested_action for a70
select name Database,creation_time,message_type,reason,suggested_action from dba_outstanding_alerts,v$database;
spool off

spool 5_tablespace.log append
select name Database,tablespace_name,round(((used_space*8192)/1024/1024/1024),1) "USED_GB",round(((tablespace_size*8192)/1024/1024/1024),1) "TOTAL_GB",round(USED_PERCENT,1) Percentage_USED FROM dba_tablespace_usage_metrics,v$database where USED_PERCENT>80;

spool off

exit

::::::::::::::
DB_check_RO.sql
::::::::::::::

set line 220 pages 1000 heading off feedback off termout off
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

col name for a10
col instance_name for a15
col host_name for a35
col open_mode for a21
col database_role for a17
col SWITCHOVER_STATUS for a20
col startup_time for a20
col flashback_on for a12
col processes for a15
col sessions for a15

spool 1_highlevel.log append

select a.name,b.instance_name,b.host_name,a.open_mode,database_role,startup_time,

(select CURRENT_UTILIZATION || '/' || trim(limit_value) ||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='processes') processes,(select CURRENT_UTILIZATION || '/' || trim(limit_value)||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='sessions') sessions,(select round(sum(bytes)/1024/1024/1024,1) from dba_segments) "Size(GB)" from v$database a,v$instance b;

set heading off

select a.name,b.instance_name,b.host_name,a.open_mode,database_role,startup_time,(select CURRENT_UTILIZATION || '/' || trim(limit_value) ||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='processes') processes,(select CURRENT_UTILIZATION || '/' || trim(limit_value) ||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='sessions') sessions 
from gv$database a,gv$instance b where a.inst_id=b.inst_id and a.inst_id=2;

spool off

spool 2_standbylag.log append

col source for a10
col target for a10
col primary_time for a20
col standby_TIME for a20

SELECT source, target, primary_log, standby_log,primary_log-standby_log LOG_GAP, sysdate primary_time,standby_TIME,round((sysdate-standby_TIME)*1440,0) Time_gap_min
FROM
(
SELECT db_unique_name source
FROM v$database
),
(
SELECT MAX(SEQUENCE#) primary_log
FROM v$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'
),
(
SELECT name target,MAX(SEQUENCE#) standby_log,MAX(COMPLETION_TIME) standby_TIME
FROM v$ARCHIVED_LOG WHERE name='DSNASB' AND APPLIED='YES' group by name
)
where standby_TIME>sysdate-30
/

spool off

spool 3_otherinfo.log append

col spfile for a65
col last_DB_bkp for a20
col last_arc_bkp for a20
col MMON for a44

select
(select instance_name from v$instance) name,
(select max(END_TIME) from v$RMAN_STATUS WHERE OPERATION='BACKUP' and STATUS='COMPLETED' and OBJECT_TYPE='DB FULL' or OBJECT_TYPE='DB INCR') last_DB_bkp,(select max(END_TIME) from v$RMAN_STATUS WHERE OPERATION='BACKUP' and OBJECT_TYPE='ARCHIVELOG' and STATUS='COMPLETED') last_arc_bkp,(select DISPLAY_VALUE from v$parameter where name='spfile') spfile
from dual;

spool off

spool 4_outstandingalerts.log append

col reason for a70
col creation_time for a35
col suggested_action for a70

select name Database,creation_time,message_type,reason,suggested_action from dba_outstanding_alerts,v$database;

spool off

spool 5_tablespace.log append

select name Database,tablespace_name,round(((used_space*8192)/1024/1024/1024),1) "USED_GB",round(((tablespace_size*8192)/1024/1024/1024),1) "TOTAL_GB",round(USED_PERCENT,1) Percentage_USED FROM dba_tablespace_usage_metrics,v$database where USED_PERCENT>80;

spool off

spool 6_recoveryarea.log append

col RECOVERY_AREA_USAGE for a30

select i.instance_name as DB_NAME,flashback_on, (select count(*) from v$restore_point) RESTORE_POINTS, round((f.space_used/1024/1024/1024),0)||'/'||(f.space_limit/1024/1024/1024)||' ('||round((f.space_used/space_limit*100),1)||'%)' "RECOVERY_AREA_USAGE" from v$database d, v$instance i,v$recovery_file_dest f;

spool off

exit

::::::::::::::

DB_check.sql

::::::::::::::

set line 220 pages 1000 heading off feedback off termout off
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
col name for a10
col instance_name for a15
col host_name for a35
col open_mode for a21
col database_role for a17
col SWITCHOVER_STATUS for a20
col startup_time for a20
col flashback_on for a12
col processes for a15
col sessions for a15

spool 1_highlevel.log append

select a.name,b.instance_name,b.host_name,a.open_mode,database_role,startup_time,(select CURRENT_UTILIZATION || '/' || trim(limit_value) ||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='processes') processes,(select CURRENT_UTILIZATION || '/' || trim(limit_value) ||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='sessions') sessions,(select round(sum(bytes)/1024/1024/1024,1) from dba_segments) "Size(GB)" from v$database a,v$instance b;

set heading off

select a.name,b.instance_name,b.host_name,a.open_mode,database_role,startup_time,(select CURRENT_UTILIZATION || '/' || trim(limit_value) ||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='processes') processes, (select CURRENT_UTILIZATION || '/' || trim(limit_value) ||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='sessions') sessions from gv$database a,gv$instance b where a.inst_id=b.inst_id and a.inst_id=2;

spool off

spool 2_standbylag.log append

col source for a10
col target for a10
col primary_time for a20
col standby_TIME for a20

SELECT source, target, primary_log, standby_log,primary_log-standby_log LOG_GAP, sysdate primary_time,standby_TIME,round((sysdate-standby_TIME)*1440,0) Time_gap_min
FROM
(
SELECT db_unique_name source
FROM v$database
),
(
SELECT MAX(SEQUENCE#) primary_log
FROM v$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'
),
(
SELECT name target,MAX(SEQUENCE#) standby_log,MAX(COMPLETION_TIME) standby_TIME
FROM v$ARCHIVED_LOG WHERE DEST_ID in (select dest_id from v$archive_dest where destination is not null and dest_id!=1) AND APPLIED='YES' group by name
)
where standby_TIME>sysdate-30
/

spool off

spool 3_otherinfo.log append

col spfile for a65
col last_DB_bkp for a20
col last_arc_bkp for a20
col MMON for a44

select
(select instance_name from v$instance) name,(select max(END_TIME) from v$RMAN_STATUS WHERE OPERATION='BACKUP' and STATUS='COMPLETED' and OBJECT_TYPE='DB FULL' or OBJECT_TYPE='DB INCR') last_DB_bkp, (select max(END_TIME) from v$RMAN_STATUS WHERE OPERATION='BACKUP' and OBJECT_TYPE='ARCHIVELOG' and STATUS='COMPLETED') last_arc_bkp,(select DISPLAY_VALUE from v$parameter where name='spfile') spfile 
from dual;

spool off

spool 4_outstandingalerts.log append

col reason for a70
col creation_time for a35
col suggested_action for a70
select name Database,creation_time,message_type,reason,suggested_action from dba_outstanding_alerts,v$database;

spool off

spool 5_tablespace.log append

select name Database, tablespace_name,round(((used_space*8192)/1024/1024/1024),1) "USED_GB",round(((tablespace_size*8192)/1024/1024/1024),1) "TOTAL_GB",round(USED_PERCENT,1) Percentage_USED FROM dba_tablespace_usage_metrics,v$database where USED_PERCENT>80;

spool off

spool 6_recoveryarea.log append

col RECOVERY_AREA_USAGE for a30

select i.instance_name as DB_NAME,flashback_on, (select count(*) from v$restore_point) RESTORE_POINTS, round((f.space_used/1024/1024/1024),0)||'/'||(f.space_limit/1024/1024/1024)||' ('||round((f.space_used/space_limit*100),1)||'%)' "RECOVERY_AREA_USAGE" from v$database d, v$instance i,v$recovery_file_dest f;

spool off

exit 

Statistics - DBMS_STATS

Table statistics:

DBMS_STATS.GATHER_TABLE_STATS ( ownname => 'SCOTT', tabname => 'DEPT', estimate_percent => NULL, method_opt =>'FOR ALL INDEXED COLUMNS SIZE AUTO', granularity =>'ALL', NO_INVALIDATE => TRUE, Cascade => TRUE);

Dynamic query

SELECT 'EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname => '||CHR(39)||owner||CHR(39)||', tabname =>'||CHR(39)||object_name||CHR(39)||', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => '||CHR(39)||'FOR ALL COLUMNS SIZE AUTO'||CHR(39)||', cascade => TRUE);'
from dba_objects 
where object_name IN ('DEPT', 'EMP') and object_type = 'TABLE';

Database Statistics:

EXEC DBMS_STATS.gather_dictionary_stats;
EXEC DBMS_STATS.gather_system_stats;
EXEC DBMS_STATS.gather_fixed_objects_stats;
EXEC DBMS_STATS.gather_database_stats;

Schema statistics:

dbms_stats.gather_schema_stats( ownname => 'SCOTT', cascade => TRUE, method_opt => 'FOR ALL COLUMNS SIZE 1',degree => DBMS_STATS.AUTO_DEGREE);

dbms_stats.gather_schema_stats( ownname => 'SCOTT_ADM', cascade => TRUE, method_opt => 'FOR ALL COLUMNS SIZE 1',degree => DBMS_STATS.AUTO_DEGREE);

BEGIN 
DBMS_STATS.gather_schema_stats ( 
ownname => 'SCOTT', 
cascade => TRUE, 
options => 'GATHER STALE');
END; 
/ 

Recompile objects:

@?/rdbms/admin/utlrp.sql

Move non-OMF datafiles to OMF

SYS@poctest_1> sho parameter db_create_file_dest

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------
db_create_file_dest                  string

SYS@poctest_1> alter system set db_create_file_dest='+DATA01' scope=both;

System altered.

SYS@poctest_1> sho parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +DATA01

col file_name for a70
set linesize 150 pages 150
select file_id, file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA01/POCTEST/system01.dbf
+DATA01/POCTEST/sysaux01.dbf
+DATA01/POCTEST/undotbs02.dbf
+DATA01/POCTEST/users01.dbf
+DATA01/POCTEST/test_data01
+DATA01/POCTEST/undotbs01.dbf

select 'PROMPT Moving file '||file_id||CHR(10)||'alter database move datafile '||file_id||';' from dba_data_files where file_name like '%.db%';

'ALTERDATABASEMOVEDATAFILE'||FILE_ID||';
---------------------------------------------------------------------alter database move datafile 1;
alter database move datafile 3;
alter database move datafile 5;
alter database move datafile 7;
alter database move datafile 2;
alter database move datafile 4;
alter database move datafile 8;

7 rows selected.

Elapsed: 00:00:00.03

SYS@poctest_1> alter database move datafile 1;

Database altered.

Elapsed: 00:00:15.43

SYS@poctest_1> alter database move datafile 3;

alter database move datafile 5;
alter database move datafile 7;
alter database move datafile 2;
alter database move datafile 4;
alter database move datafile 8;

Database altered.

SYS@poctest_1> select file_name from dba_data_files;

FILE_NAME
----------------------------------------------------------
+DATA01/POCTEST/DATAFILE/system.2558.1152873171
+DATA01/POCTEST/DATAFILE/sysaux.1127.1152873193
+DATA01/POCTEST/DATAFILE/undotbs2.1169.1152873221
+DATA01/POCTEST/DATAFILE/users.1181.1152873225
+DATA01/POCTEST/DATAFILE/test.1157.1152873227
+DATA01/POCTEST/DATAFILE/undotbs1.2126.1152873379
+DATA01/POCTEST/DATAFILE/test1.1171.1152873393

7 rows selected. 

Find table last modification date

select X.*  , Y.timestamp Last_updated_date from (
select     t.owner , table_name,     num_rows, TABLE_SIZE_IN_GB 
 from     dba_tables t
Inner join (select OWNER, segment_name,segment_type, sum(bytes/1024/1024/1024) TABLE_SIZE_IN_GB
from dba_segments
where OWNER in ('SCOTT', 'SCOTT_ADM')
group by OWNER,segment_name,segment_type ) s
  On table_name =segment_name and t.owner =s.owner ) X
  join dba_tab_modifications Y on X.owner = Y.TABLE_OWNER  and X.table_name = Y.table_name
  order by 4 desc , 3 desc ;

 

Find Oracle Maintained (internal) users

 SYS @ GGATE1 >select username from dba_users where ORACLE_MAINTAINED ='Y' order by username;

USERNAME
--------------------------------------------------
ANONYMOUS
APPQOSSYS
AUDSYS
CTXSYS
DBSFWUSER
DBSNMP
DIP
GGSYS
GSMADMIN_INTERNAL
GSMCATUSER
GSMUSER
MDSYS
OJVMSYS
ORACLE_OCM
ORDDATA
ORDPLUGINS
ORDSYS
OUTLN
REMOTE_SCHEDULER_AGENT
SI_INFORMTN_SCHEMA
SYS
SYS$UMF
SYSBACKUP
SYSDG
SYSKM
SYSRAC
SYSTEM
MSYS
XDB
XS$NULL

30 rows selected.

Enable/Disable Restricted Session

 SYS @ GGATE1>select inst_id, instance_name, logins from gv$instance;

   INST_ID INSTANCE_NAME    LOGINS
---------- ---------------- ----------
         1 GGATE11          ALLOWED
         2 GGATE12          ALLOWED

-- Run the following command on each RAC node if applicable

SYS @ GGATE1>Alter system enable restricted session;

System altered.

SYS @ GGATE1>select inst_id, instance_name, logins from gv$instance;

   INST_ID INSTANCE_NAME    LOGINS
---------- ---------------- ----------
         1 GGATE11          RESTRICTED
         2 GGATE12          ALLOWED

SYS @ GGATE1>Alter system disable restricted session;

System altered.

SYS @ GGATE1>select inst_id, instance_name, logins from gv$instance;

   INST_ID INSTANCE_NAME    LOGINS
---------- ---------------- ----------
         1 GGATE11          ALLOWED
         2 GGATE12          ALLOWED

Define Unified Audit in Oracle 19c

Step 1: Run the following query. Value for below query should be TRUE.

select value from v$option where parameter = 'Unified Auditing';

Step 2 :  As sysdba  run the below

CREATE AUDIT POLICY SCOTT_DEPT_POL
  ACTIONS DELETE on SCOTT.DEPT,
          INSERT on SCOTT.DEPT,
          UPDATE on SCOTT.DEPT;

AUDIT POLICY SCOTT_DEPT_POL;

Create and Delete Oracle 19c database in Silent mode using DBCA

The example of rsp file you will find under Create and Delete commands!!

Create Database:

dbca -silent -createDatabase -responseFile /home/oracle/dbca_alex.rsp

Delete Database:

dbca -silent -deleteDatabase -sourceDB alex -sysDBAUserName sys -sysDBAPassword oracle

----------------------------------------------------------------

After DML on the Master Table(s) of Local Materialized View, USER_MVIEWS.COMPILE_STATE becomes 'NEEDS_COMPILE' and USER_OBJECTS.STATUS becomes 'INVALID' (Doc ID 264036.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.0.1.0 and later

Information in this document applies to any platform.

SYMPTOMS

After executing a DML statement on base table(s) of MV that is on the same database as its master table(s) (i.e. a local MV), USER_OBJECTS.STATUS becomes INVALID for the MV.  Also, USER_MVIEWS.COMPILE_STATE shows status "NEEDS_COMPILE'.

Example :

SQL> select object_name, object_type, status from user_objects

2 where object_type = 'MATERIALIZED VIEW';

Output :

OBJECT_NAME                    OBJECT_TYPE        STATUS

------------------------------ ------------------ -------

EMP_MV_PK                      MATERIALIZED VIEW  VALID

SQL> select mview_name, compile_state from user_mviews;

Output :

MVIEW_NAME                     COMPILE_STATE

------------------------------ -------------------

EMP_MV_PK                      VALID

SQL> update emp set empno = empno where empno = 605;

SQL> commit;

SQL> select object_name, object_type, status from user_objects

2 where object_type = 'MATERIALIZED VIEW';

Output :

OBJECT_NAME                    OBJECT_TYPE        STATUS

------------------------------ ------------------ -------

EMP_MV_PK                      MATERIALIZED VIEW  INVALID

SQL> select mview_name, compile_state from user_mviews;

Output :

MVIEW_NAME                      COMPILE_STATE

------------------------------ -------------------

EMP_MV_PK                       NEEDS_COMPILE

CHANGES

DML was executed and committed on master table of the MV since the MV was last refreshed.

CAUSE

This is expected behavior. Dependencies related to MVs are automatically maintained to ensure correct operation. When an MV is created, the materialized view depends on the master tables referenced in its definition. Any DML operation, such as an INSERT, or DELETE, UPDATE, or DDL operation on any dependency in the materialized view will cause it to become invalid.

SOLUTION

Though the status is INVALID, the MV can still be queried.  However, the query on MV will not return the latest data in master table unless the MV is refreshed.

Example :

SQL> select object_name, object_type, status from user_objects

2 where object_type = 'MATERIALIZED VIEW';

Output :

OBJECT_NAME                    OBJECT_TYPE        STATUS

------------------------------ ------------------ -------

EMP_MV_PK                      MATERIALIZED VIEW  INVALID

SQL> select mview_name, compile_state from user_mviews;

Output :

MVIEW_NAME                     COMPILE_STATE

------------------------------ -------------------

EMP_MV_PK                      NEEDS_COMPILE

SQL> select count(*) from emp_mv_pk;

Output :

COUNT(*)

----------

2686976

- On next refresh, the status becomes VALID.

Example :

SQL> execute dbms_mview.refresh('emp_mv_pk');

SQL> select object_name, object_type, status from user_objects

2 where object_type = 'MATERIALIZED VIEW';

Output :

OBJECT_NAME                    OBJECT_TYPE        STATUS

------------------------------ ------------------ -------

EMP_MV_PK                      MATERIALIZED VIEW  VALID

SQL> select mview_name, compile_state from user_mviews;

Output :

MVIEW_NAME                     COMPILE_STATE

------------------------------ -------------------

EMP_MV_PK                      VALID

"REFRESH ON COMMIT" MVs are the exception to this behavior because they are refreshed at the same time of DML.

Toad hangs up during login

There is a setting you'll have to change once you have 10.6.

Go to Options -> Oracle -> Optimizer Hints. You'll have to add a RULE hint for
DBA_SYNONYMS on Oracle 11Gr2.

How To Understand AWR Report / Statspack Report ?

How To Understand AWR Report / Statspack Report
==============================================
(From  http://halimdba.blogspot.ca/2011/07/how-to-understand-awr-report-statspack.html )

script is here $ORACLE_HOME\RDBMS\ADMIN\
awrrpt.sql
awrrpti.sql

execute like below

SQL*Plus: Release 11.1.0.6.0 - Production on Sun Jul 10 14:37:04 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> CONN sys@stlbas105 as sysdba
Enter password:
Connected.
SQL>
SQL>
SQL>
SQL> @G:\app\Administrator\product\11.1.0\db_1\RDBMS\ADMIN\awrrpt.sql

How to check Time Zone of Database

select DBTIMEZONE from dual;

select systimestamp from dual

How to check the capacity of your instance - 10g and higher

SELECT 'Max Sessions - '||highwater "Resource"

  FROM DBA_HIGH_WATER_MARK_STATISTICS

WHERE name = 'SESSIONS'

UNION

SELECT 'Processes - '||value

  FROM v$parameter

WHERE name = 'processes'

UNION

SELECT 'Sessions - '||value

  FROM v$parameter

WHERE name = 'sessions';

Resource
----------------------------
Max Sessions - 127
Processes - 300
Sessions - 335

How to find "Maximum Number of Concurrent Sessions seen in the database"

SELECT highwater 
  FROM DBA_HIGH_WATER_MARK_STATISTICS 
WHERE name = 'SESSIONS'; 

Statspack Performance

Tip: Collecting statistics (e.g. with DBMS_STATS.GATHER_SCHEMA_STATS) on perfstat schema before purging, it may save you a lot of waiting time.

SQL>  CONNECT perfstat/my_perfstat_password

SQL>  DEFINE losnapid=1

SQL>  DEFINE hisnapid=2

SQL>  @?/rdbms/admin/sppurge

When SPPURGE.SQL is run, it does not prompt for the information provided by the variables.

Delete database with DBCA in silent mode

dbca -deleteDatabase -silent -sourceDB MYDB -sysDBAUserName SYS -sysDBAPassword mypass

How to move tempfile to another location online!

alter tablespace temp add tempfile '/u01/app/oracle/oradata/KNK/temp02.dbf' size 1024m autoextend on;

alter tablespace temp drop tempfile '/u01/app/oracle/oradata/KNK/temp01.dbf';


Check tempfiles from dba_temp_files.


If you still see the dropped tempfile, check whether any sessions running, which are probably use TEMP tablespace.


After you kill the relevant sessions (if you need to), check dba_temp_files again.


Check if file still exists in OS. If yes then drop it!

Check the total possible shrinkage for tablespace

SELECT SUM(shrinkage_possible_mb) FROM 
(SELECT NVL ( ROUND ( ( blocks-hwm + 1 ) * bytes / blocks / 1024 / 1024 ) , 
              ROUND ( ( blocks ) * bytes / blocks / 1024 / 1024 ) ) shrinkage_possible_mb
   FROM dba_data_files a , 
       ( SELECT file_id , max ( block_id + blocks ) hwm 
         FROM dba_extents GROUP BY file_id ) b 
  WHERE a.file_id =b.file_id ( + ) AND
        a.tablespace_name = 'DATA01'
);

Check which segment resides in the last block of each datafile

SELECT de.file_id, owner, segment_name, partition_name, segment_type
  FROM dba_extents de,
      (SELECT file_id, MAX(block_id) mblock_id
         FROM dba_extents
       WHERE tablespace_name = 'DATA01'
       GROUP BY file_id
      ) fmax
WHERE de.file_id = fmax.file_id AND de.block_id = fmax.mblock_id 
ORDER BY file_id;