meta données pour cette page
  •  

Ceci est une ancienne révision du document !

Export & Import

Pour afficher les répertoires virtuel existants

SET lines 300
col directory_name format A30
col directory_path format A100


Afficher les Grants sur les Repertoires

SET lines 110
col privilege format a12
col grantee   format  a25
col owner format  a25
 SELECT    p.grantee, p.privilege, p.owner, d.directory_name
FROM    dba_tab_privs p,  dba_directories d
WHERE    p.table_name=d.directory_name;


Ajouter un répertoire virtuel

CREATE OR REPLACE DIRECTORY NAS_DIR AS '/export/nas/SRVCHRONOSP';
 
GRANT READ,WRITE ON DIRECTORY NAS_DIR  TO SYSTEM;
 
 
CREATE OR REPLACE DIRECTORY DPUMP_DIR1 AS '/oracle/exploit/RMANAWS/rman';
GRANT READ,WRITE ON DIRECTORY DPUMP_DIR1  TO SYSTEM;


Pour afficher les schémas

SELECT DISTINCT    owner  FROM   dba_segments
WHERE    owner IN    (SELECT username     FROM dba_users     WHERE default_tablespace NOT IN ('SYSTEM','SYSAUX')   );


taille des schemas

sur un schema en particulier 

SELECT SUM(bytes)/1024/1024/1024 AS "Size in GB"
FROM dba_segments
WHERE owner = UPPER('&schema_name');

tous les schémas 

SET linesize 300
SET pagesize 5000
col owner FOR a25
col segment_name FOR a30
col segment_type FOR a20
col TABLESPACE_NAME FOR a30
clear breaks
clear computes
compute SUM OF SIZE_IN_GB ON report
break ON report
SELECT OWNER,SUM(bytes)/1024/1024/1000 "SIZE_IN_GB" FROM dba_segments GROUP BY owner ORDER BY owner;


Afficher taille de la base

SELECT
"Reserved_Space(GB)", "Reserved_Space(GB)"- "Free_Space(GB)" "Used_Space(GB)","Free_Space(GB)"
FROM(
SELECT
(SELECT SUM(bytes/(1014*1024*1024)) FROM dba_data_files) "Reserved_Space(GB)",
(SELECT SUM(bytes/(1024*1024*1024)) FROM dba_free_space) "Free_Space(GB)"
FROM dual);


Export

sur un schema en particulier 

expdp \'/ as sysdba\' DIRECTORY=DATAPUMP  DUMPFILE=DataPump_DUVF6409_SCTASK0707960.dmp    LOGFILE=DataPump_DUVF6409_SCTASK0707960.log  schemas=TBS_1N67_DATA  JOB_NAME=Mikael_DUVF6409;

Full 

expdp \'/ as sysdba\' DIRECTORY=DATAPUMP  DUMPFILE=DataPump_DUVF6409_SCTASK0707960.dmp    LOGFILE=DataPump_DUVF6409_SCTASK0707960.log FULL=YES JOB_NAME=Mikael_DUVF6409;


Import on a specific shema

Vérifier les MDP des users/schema de destination sont identiques sur la source et la DEST, car il seront ecrasés lors de l'import 

sqlplus xxxxx/xxxx

Penser à dropper le schéma de dest (fonctionne si aucune session en cours) 

DROP USER sidney CASCADE;

Éventuellement désactiver ARCH LOG temporairement ou ajouter option 12C 'TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y' dans IMPDP – afin d'éviter de saturer le FS des ARCHLOG 

shutdown immediate
startup mount
alter database noarchivelog;
alter database open;
impdp \'/ as sysdba\'     directory=DATAPUMP    schemas=TBS_1N67_DATA dumpfile=DataPump_DUVF6409_SCTASK0707960.dmp logfile=DataPump_DUVF6409_SCTASK0707960.log   reuse_dumpfiles=y exclude=statistics

Faire un backup full pour remettre au carré le jeu de backup


Import FULL

Penser à dropper le schéma de dest 

impdp system FULL=y directory=NAS_DIR dumpfile=expdp_CHRONOS_GTADM_%U.dmp logfile=Import_CHRONOS_PROD_TO_TEST.log TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y PARALLEL=2

%U ⇒ utile si plus d'un .DMP

Mount NAS share

AS ROOT 

mkdir /media/res_dba
mount -t cifs -o username=TA-SRV-ZIOHC-MDE //fr0-svm09/abc-automation-tls /media/res_dba

Then create the destination directory