-- ============================================================
-- EXPORT COMPLET PDB XEPDB1 - Oracle XE 21c
-- Scop: backup inainte de recreare PDB pentru recuperare spatiu
-- Rulat ca SYS AS SYSDBA conectat la PDB (XEPDB1)
-- ============================================================
-- NOTA: Inainte de rulare, asigura-te ca directorul C:\DMPDIR exista
-- si ca DMPDIR e definit: CREATE OR REPLACE DIRECTORY DMPDIR AS 'C:\DMPDIR';
-- ============================================================

SET ECHO ON
SET FEEDBACK ON
SET LONG 100000
SET LONGCHUNKSIZE 100000
SET PAGESIZE 0
SET LINESIZE 300
SET TRIMSPOOL ON
SET HEADING OFF
SET VERIFY OFF
SET SERVEROUTPUT ON SIZE UNLIMITED

-- ============================================================
-- PASUL 1: Export scheme cu Data Pump (ruleaza separat din CMD)
-- ============================================================
-- expdp \"sys/parola@XEPDB1 AS SYSDBA\" schemas=CONTAFIN_ORACLE,FIRMA directory=DMPDIR dumpfile=schemas_export.dmp logfile=schemas_export.log
-- expdp \"sys/parola@XEPDB1 AS SYSDBA\" tables=SYS.AUTH_SERII,SYS.AUTH_DETALII,SYS.INFO directory=DMPDIR dumpfile=sys_tables.dmp logfile=sys_tables_export.log

-- ============================================================
-- PASUL 2: Export sinonime publice
-- ============================================================
SPOOL C:\DMPDIR\02_sinonime_publice.sql

SELECT 'CREATE PUBLIC SYNONYM ' || synonym_name || ' FOR ' ||
       table_owner || '.' || table_name || ';'
FROM dba_synonyms
WHERE owner = 'PUBLIC'
AND table_owner IN ('CONTAFIN_ORACLE', 'FIRMA')
ORDER BY synonym_name;

SPOOL OFF

-- ============================================================
-- PASUL 3: Export grant-uri
-- ============================================================
SPOOL C:\DMPDIR\03_granturi.sql

-- 3a. Grant-uri pe obiecte SYS catre schemele aplicatiei
SELECT 'GRANT ' || privilege || ' ON ' || owner || '.' || table_name ||
       ' TO ' || grantee || ';'
FROM dba_tab_privs
WHERE (grantee IN ('CONTAFIN_ORACLE', 'FIRMA')
       AND owner = 'SYS')
ORDER BY grantee, table_name;

-- 3b. Grant-uri de la schemele aplicatiei catre PUBLIC
SELECT 'GRANT ' || privilege || ' ON ' || owner || '.' || table_name ||
       ' TO ' || grantee || ';'
FROM dba_tab_privs
WHERE grantor IN ('CONTAFIN_ORACLE', 'FIRMA')
ORDER BY grantee, owner, table_name;

-- 3c. System privileges
SELECT 'GRANT ' || privilege || ' TO ' || grantee || ';'
FROM dba_sys_privs
WHERE grantee IN ('CONTAFIN_ORACLE', 'FIRMA')
ORDER BY grantee, privilege;

-- 3d. Role privileges
SELECT 'GRANT ' || granted_role || ' TO ' || grantee || ';'
FROM dba_role_privs
WHERE grantee IN ('CONTAFIN_ORACLE', 'FIRMA')
ORDER BY grantee, granted_role;

SPOOL OFF

-- ============================================================
-- PASUL 4: Export DIRECTORY-uri custom
-- ============================================================
SPOOL C:\DMPDIR\04_directories.sql

SELECT 'CREATE OR REPLACE DIRECTORY ' || directory_name ||
       ' AS ''' || directory_path || ''';'
FROM dba_directories
WHERE origin_con_id != 1
ORDER BY directory_name;

SPOOL OFF

-- ============================================================
-- PASUL 5: Export proceduri, functii, pachete SYS custom
-- ============================================================
SPOOL C:\DMPDIR\05_sys_code_objects.sql

-- Packages (include si body automat)
PROMPT -- PACKAGES
SELECT DBMS_METADATA.GET_DDL('PACKAGE', object_name, 'SYS')
FROM dba_objects
WHERE owner = 'SYS' AND oracle_maintained = 'N'
AND object_type = 'PACKAGE'
AND object_name NOT LIKE 'KUPC%'
AND object_name NOT LIKE 'AQ$%';

SELECT '/' FROM dual;

-- Proceduri
PROMPT -- PROCEDURES
SELECT DBMS_METADATA.GET_DDL('PROCEDURE', object_name, 'SYS')
FROM dba_objects
WHERE owner = 'SYS' AND oracle_maintained = 'N'
AND object_type = 'PROCEDURE'
AND object_name NOT LIKE 'KUPC%'
AND object_name NOT LIKE 'AQ$%';

SELECT '/' FROM dual;

-- Functii
PROMPT -- FUNCTIONS
SELECT DBMS_METADATA.GET_DDL('FUNCTION', object_name, 'SYS')
FROM dba_objects
WHERE owner = 'SYS' AND oracle_maintained = 'N'
AND object_type = 'FUNCTION'
AND object_name NOT LIKE 'KUPC%'
AND object_name NOT LIKE 'AQ$%';

SELECT '/' FROM dual;

SPOOL OFF

-- ============================================================
-- PASUL 6: Export tabele custom SYS (structura)
-- ============================================================
SPOOL C:\DMPDIR\06_sys_tables.sql

SELECT DBMS_METADATA.GET_DDL('TABLE', object_name, 'SYS')
FROM dba_objects
WHERE owner = 'SYS' AND oracle_maintained = 'N'
AND object_type = 'TABLE'
AND object_name IN ('AUTH_SERII', 'AUTH_DETALII', 'INFO');

SELECT '/' FROM dual;

-- Sequence
SELECT DBMS_METADATA.GET_DDL('SEQUENCE', 'SEQ_AUTH_SERII', 'SYS') FROM dual;

SELECT '/' FROM dual;

-- Index custom
SELECT DBMS_METADATA.GET_DDL('INDEX', 'PK_AUTH_SERII', 'SYS') FROM dual;

SELECT '/' FROM dual;

SPOOL OFF

-- ============================================================
-- PASUL 7: Export VIEW-uri custom SYS
-- ============================================================
SPOOL C:\DMPDIR\07_sys_views.sql

SELECT DBMS_METADATA.GET_DDL('VIEW', 'VAUTH_SERII', 'SYS') FROM dual;

SELECT '/' FROM dual;

SPOOL OFF

-- ============================================================
-- PASUL 8: Export TRIGGER-uri custom SYS
-- ============================================================
SPOOL C:\DMPDIR\08_sys_triggers.sql

SELECT DBMS_METADATA.GET_DDL('TRIGGER', 'LOGON_AUDIT_TRIGGER', 'SYS') FROM dual;

SELECT '/' FROM dual;

SPOOL OFF

-- ============================================================
-- PASUL 9: Export CONTEXT
-- ============================================================
SPOOL C:\DMPDIR\09_context.sql

SELECT DBMS_METADATA.GET_DDL('CONTEXT', 'SESIUNE') FROM dual;

SELECT '/' FROM dual;

SPOOL OFF

-- ============================================================
-- PASUL 10: Export useri si profile
-- ============================================================
SPOOL C:\DMPDIR\10_useri.sql

SELECT DBMS_METADATA.GET_DDL('USER', username)
FROM dba_users
WHERE oracle_maintained = 'N'
AND username NOT IN ('PDBADMIN')
ORDER BY username;

SELECT '/' FROM dual;

SPOOL OFF

-- ============================================================
-- PASUL 11: Export tablespace-uri custom
-- ============================================================
SPOOL C:\DMPDIR\11_tablespaces.sql

SELECT 'CREATE TABLESPACE ' || tablespace_name ||
       ' DATAFILE SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 4G;'
FROM dba_tablespaces
WHERE tablespace_name NOT IN ('SYSTEM', 'SYSAUX', 'UNDOTBS1', 'TEMP', 'USERS')
AND contents = 'PERMANENT';

SPOOL OFF

-- ============================================================
-- REZUMAT FISIERE GENERATE
-- ============================================================
PROMPT
PROMPT =============================================
PROMPT  EXPORT COMPLET - FISIERE GENERATE
PROMPT =============================================
PROMPT  C:\DMPDIR\02_sinonime_publice.sql
PROMPT  C:\DMPDIR\03_granturi.sql
PROMPT  C:\DMPDIR\04_directories.sql
PROMPT  C:\DMPDIR\05_sys_code_objects.sql
PROMPT  C:\DMPDIR\06_sys_tables.sql
PROMPT  C:\DMPDIR\07_sys_views.sql
PROMPT  C:\DMPDIR\08_sys_triggers.sql
PROMPT  C:\DMPDIR\09_context.sql
PROMPT  C:\DMPDIR\10_useri.sql
PROMPT  C:\DMPDIR\11_tablespaces.sql
PROMPT =============================================
PROMPT
PROMPT  NU UITA sa rulezi si Data Pump SEPARAT:
PROMPT  expdp \"sys/parola@XEPDB1 AS SYSDBA\" schemas=CONTAFIN_ORACLE,FIRMA directory=DMPDIR dumpfile=schemas_export.dmp logfile=schemas_export.log
PROMPT  expdp \"sys/parola@XEPDB1 AS SYSDBA\" tables=SYS.AUTH_SERII,SYS.AUTH_DETALII,SYS.INFO directory=DMPDIR dumpfile=sys_tables.dmp logfile=sys_tables_export.log
PROMPT =============================================