===== Export & Import =====
=== Pour afficher les répertoires virtuel existants ===
<code PLSQL>
set lines 300
col OWNER format A30
col directory_name format A30
col directory_path format A100
select OWNER,directory_name,directory_path  from dba_directories;
</code>
\\  
=== Afficher les Grants sur les Répertoires ===
<code PLSQL>
set lines 500
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;
</code>
\\  
=== Ajouter un répertoire virtuel ===
<code PLSQL>
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;
</code>
\\  
=== Pour afficher les schémas ===
<code PLSQL>
select distinct    owner  from   dba_segments
Where    owner in    (select username     from dba_users     where default_tablespace not in ('SYSTEM','SYSAUX')   );
</code>

\\  
=== taille des schémas ===
**sur un schema en particulier**
<code PLSQL>
SELECT sum(bytes)/1024/1024/1024 as "Size in GB"
from dba_segments
WHERE owner = UPPER('&schema_name'); 
</code>
**tous les schémas**
<code PLSQL>
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;
</code>
\\  
=== Afficher taille de la base ===
<code PLSQL>
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);
</code>

 \\  
=== Export ===
**sur un schéma en particulier**
<code>
expdp \'/ as sysdba\' DIRECTORY=DATAPUMP  DUMPFILE=DataPump_DUVF6409_SCTASK0707960.dmp    LOGFILE=DataPump_DUVF6409_SCTASK0707960.log  schemas=TBS_1N67_DATA  JOB_NAME=Mikael_DUVF6409;
</code>

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

\\  
=== Import  ===
**sur un schéma en particulier** \\  
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
<code>
sqlplus xxxxx/xxxx
</code>

Penser à dropper le schéma de dest (fonctionne si aucune session en cours)
<code PLSQL>
DROP USER sidney CASCADE;
</code>

É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
<code>
shutdown immediate
startup mount
alter database noarchivelog;
alter database open;
</code>
  
<code>
impdp \'/ as sysdba\'     directory=DATAPUMP    schemas=TBS_1N67_DATA dumpfile=DataPump_DUVF6409_SCTASK0707960.dmp logfile=DataPump_DUVF6409_SCTASK0707960.log   reuse_dumpfiles=y exclude=statistics
</code>
Faire un backup full pour remettre au carré le jeu de backup
\\  

**FULL** \\  
Penser à dropper le schéma de dest
<code>
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
</code>
%U => utile si plus d'un .DMP \\  
\\  

=== Monter partageNAS ===
En temps que ROOT
<code>
mkdir /media/res_dba
mount -t cifs -o username=TA-SRV-ZIOHC-MDE //fr0-svm09/abc-automation-tls /media/res_dba
</code>
Then create the destination directory