Data Pump

source: http://arjudba.blogspot.com/2009/04/data-pump-process-architecture-master.html 

select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid,
    s.status, s.username, d.job_name, p.spid, s.serial#, p.pid
from v$session s, v$process p, dba_datapump_sessions d
where p.addr=s.paddr and s.saddr=d.saddr;

Kill, cancel and resume or restart datapump expdp and impdp jobs

http://blog.oracle48.nl/killing-and-resuming-datapump-expdp-and-impdp-jobs/

SET serveroutput on
SET lines 100
DECLARE
   h1 NUMBER;
BEGIN
  -- Format: DBMS_DATAPUMP.ATTACH('[job_name]','[owner_name]');
   h1 := DBMS_DATAPUMP.ATTACH('SYS_IMPORT_SCHEMA_01','SCHEMA_USER');
   DBMS_DATAPUMP.STOP_JOB (h1,1,0);
END;
/
 

--- quick data setup (http://www.oracle-developer.net/display.php?id=515)

cREATE TABLE t
    AS
       SELECT ROWNUM                     AS id
       ,      MOD(ROWNUM,2000)           AS grp
       ,      DBMS_RANDOM.STRING('u',5)  AS val
       ,      DBMS_RANDOM.STRING('u',30) AS pad
       FROM   dual
       CONNECT BY ROWNUM <= 100000;
       
exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'T'); 

check tables spaces 

SELECT t.tablespace_name "Tablespace",
       'Datafile' "File Type",
       t.status "Tablespace Status",
       d.status "File Status",
       ROUND((d.max_bytes - NVL(f.sum_bytes, 0))/1024/1024) "Used MB",
       ROUND(NVL(f.sum_bytes, 0)/1024/1024) "Free MB",
       t.initial_extent "Initial Extent",
       t.next_extent "Next Extent",
       t.min_extents "Min Extents",
       t.max_extents "Max Extents",
       t.pct_increase "Pct Increase",
       d.file_name "Datafile name",
       d.file_id
 FROM (SELECT tablespace_name, file_id, SUM(bytes) sum_bytes
       FROM   DBA_FREE_SPACE
       GROUP BY tablespace_name, file_id) f,
      (SELECT tablespace_name, file_name, file_id, MAX(bytes) max_bytes, status
       FROM DBA_DATA_FILES
       GROUP BY tablespace_name, file_name, file_id, status) d,
      DBA_TABLESPACES t
WHERE t.tablespace_name = d.tablespace_name
AND   f.tablespace_name(+) = d.tablespace_name
AND   f.file_id(+) = d.file_id
GROUP BY t.tablespace_name, d.file_name, d.file_id, t.initial_extent,
         t.next_extent, t.min_extents, t.max_extents,
         t.pct_increase, t.status, d.max_bytes, f.sum_bytes, d.status
UNION ALL
SELECT h.tablespace_name,
       'Tempfile',
       ts.status,
       t.status,
       ROUND(SUM(NVL(p.bytes_used, 0))/ 1048576),
       ROUND(SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / 1048576),
       -1, -- initial extent
       -1, -- initial extent
       -1, -- min extents
       -1, -- max extents
       -1, -- pct increase
       t.file_name,
       t.file_id
FROM   sys.V_$TEMP_SPACE_HEADER h, sys.V_$TEMP_EXTENT_POOL p, sys.DBA_TEMP_FILES t, sys.dba_tablespaces ts
WHERE  p.file_id(+) = h.file_id
AND    p.tablespace_name(+) = h.tablespace_name
AND    h.file_id = t.file_id
AND    h.tablespace_name = t.tablespace_name
and    ts.tablespace_name = h.tablespace_name
GROUP BY h.tablespace_name, t.status, t.file_name, t.file_id, ts.status
ORDER BY 1, 5 DESC

Cleanup (http://arjudba.blogspot.com/2009/05/how-to-cleanup-orphaned-datapump-jobs.html) 

SELECT * FROM dba_datapump_jobs;

DROP TABLE xxx.SYS_EXPORT_SCHEMA_01;

PURGE TABLE xxx.SYS_EXPORT_SCHEMA_01; (if table in recycle bin)

need imp/exp Privgs, to show all for user:

SELECT level,LPAD(' ', 2*level) || granted_role "USER PRIVS"
FROM (
  SELECT NULL grantee,  username granted_role
  FROM dba_users
  WHERE username LIKE UPPER('%XXX%')
  UNION
  SELECT grantee, granted_role
  FROM dba_role_privs
  UNION
  SELECT grantee, privilege
  FROM dba_sys_privs)
START WITH grantee IS NULL
CONNECT BY grantee = prior granted_role; 

SELECT  DBMS_METADATA.GET_DEPENDENT_DDL('TRIGGER','table','schema') from dual; 

SELECT  DBMS_METADATA.GET_DEPENDENT_DDL('INDEX','t','s') from dual;

SELECT  DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT','t','s') from dual 

select DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','XXX') from dual;

select DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','XXX') from dual;

select DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','XXX') from dual; 

Objects that can be data pumped with INCLUDE at levels:

DATABASE_EXPORT_OBJECTS,

SCHEMA_EXPORT_OBJECTS,

TABLE_EXPORT_OBJECTS. 

See how much space by a table: 

select owner, segment_name, bytes/(1024*1024) "MB"
from dba_segments
where owner = ''
and segment_type = 'TABLE'
and segment_name = '';