ROMFASTSQL/clienti/oracle-xe-21c/depanare-ora-12954-spatiu.md

ORA-12954: Database size exceeds 12 GB limit (Oracle XE)

Oracle XE are limita hard de 12 GB. Limita se aplica pe suma datafile-urilor (dba_data_files), NU pe segmente (dba_segments). Un datafile poate fi 90% gol dar tot conteaza la limita.

Diagnostic rapid

-- IMPORTANT: Verifica AMBELE - segmente vs datafiles
-- Segmente (spatiu efectiv folosit)
SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024) AS used_mb
FROM dba_segments GROUP BY tablespace_name ORDER BY used_mb DESC;

-- Datafiles (spatiu ALOCAT - asta conteaza la limita de 12 GB!)
SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024) AS allocated_mb
FROM dba_data_files GROUP BY tablespace_name ORDER BY allocated_mb DESC;

SELECT ROUND(SUM(bytes)/1024/1024) AS total_allocated_mb FROM dba_data_files;

-- Ce ocupa SYSAUX (de obicei vinovatul)
SELECT occupant_name, ROUND(space_usage_kbytes/1024) AS size_mb
FROM v$sysaux_occupants ORDER BY space_usage_kbytes DESC;

-- Top 20 segmente mari in SYSAUX
SELECT segment_name, segment_type, ROUND(bytes/1024/1024) AS size_mb
FROM dba_segments WHERE tablespace_name = 'SYSAUX'
ORDER BY bytes DESC FETCH FIRST 20 ROWS ONLY;

-- Identifica LOB-uri mari
SELECT owner, table_name, column_name
FROM dba_lobs
WHERE segment_name = '<segment_name_from_above>';

-- Audit policies active (trebuie dezactivate!)
SELECT policy_name, enabled_option FROM audit_unified_enabled_policies;

-- Auto tasks (sql tuning advisor = vinovatul principal)
SELECT client_name, status FROM dba_autotask_client;

Pas 1: Cleanup segmente (elibereaza spatiu in datafile)

SQL Tuning Sets (principalul vinovat, poate ocupa 5+ GB)

-- Incearca mai intai DROP curat
BEGIN
  FOR r IN (SELECT name, owner FROM dba_sqlset) LOOP
    BEGIN
      DBMS_SQLTUNE.DROP_SQLSET(r.name, r.owner);
    EXCEPTION WHEN OTHERS THEN NULL;
    END;
  END LOOP;
END;
/

-- Daca DROP nu elibereaza spatiul, TRUNCATE direct:
TRUNCATE TABLE sys.wri$_sqlset_plan_lines;
TRUNCATE TABLE sys.wri$_sqlset_plans;
TRUNCATE TABLE sys.wri$_sqlset_statistics;
TRUNCATE TABLE sys.wri$_sqlset_statements;
TRUNCATE TABLE sys.wri$_sqlset_references;
TRUNCATE TABLE sys.wri$_sqlset_definitions;

Advisor + AWR + Statistici

EXEC DBMS_ADVISOR.DELETE_EXPIRED_TASKS;
-- NOTA: retention trebuie >= moving window baseline (default 8 zile)
-- Daca da ORA-13541, creste retention la 8*24*60
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 8*24*60, interval => 60);
EXEC DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(7);
EXEC DBMS_STATS.PURGE_STATS(SYSDATE - 7);
PURGE DBA_RECYCLEBIN;

Pas 2: Resize datafiles (recupereaza spatiul alocat)

TRUNCATE elibereaza segmentele dar datafile-ul ramane la aceeasi dimensiune. Trebuie RESIZE.

-- Verifica path-urile fisierelor
SELECT file_id, tablespace_name, ROUND(bytes/1024/1024) AS size_mb, file_name
FROM dba_data_files ORDER BY bytes DESC;

-- Gaseste minimul posibil de resize
SELECT file_id, CEIL((MAX(block_id + blocks - 1) * 8192) / 1048576) + 10 AS min_size_mb
FROM dba_extents WHERE tablespace_name = 'SYSAUX' GROUP BY file_id;

-- Resize
ALTER DATABASE DATAFILE '<path>' RESIZE <size>M;

Daca resize esueaza cu ORA-03297 (used data beyond resize value)

Segmentele sunt imprastiate in datafile. Pe Oracle XE 21c nu exista SHRINK_TABLESPACE (doar 23ai+).

Solutie UNDOTBS1 (testat, functioneaza):

-- Seteaza undo pe MANUAL temporar
ALTER SYSTEM SET undo_management = 'MANUAL' SCOPE = SPFILE;

-- Restart PDB
ALTER SESSION SET CONTAINER = CDB$ROOT;
ALTER PLUGGABLE DATABASE XEPDB1 CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE XEPDB1 OPEN RESTRICTED;
ALTER SESSION SET CONTAINER = XEPDB1;

-- Drop si recreare
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '<path>\UNDOTBS01.DBF' SIZE 100M AUTOEXTEND ON MAXSIZE 500M;
ALTER SYSTEM SET undo_tablespace = 'UNDOTBS1';
ALTER SYSTEM SET undo_management = 'AUTO' SCOPE = SPFILE;

-- Restart PDB normal
ALTER SESSION SET CONTAINER = CDB$ROOT;
ALTER PLUGGABLE DATABASE XEPDB1 CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE XEPDB1 OPEN;

Solutie SYSAUX (daca resize cu ORA-03297): SYSAUX nu se poate face drop/recreare. Singura solutie este recrearea PDB-ului:

1. Export complet (scheme + obiecte SYS custom)
2. Drop PDB
3. Recreare PDB din PDB$SEED
4. Import

Scripturi de export/import disponibile in directorul import/. Scriptul master: import/recreare_pdb.sql

Pas 3: Preventie (IMPORTANT - ruleaza pe orice server nou!)

-- Dezactiveaza auto tasks care umfla SYSAUX
EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);
EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto space advisor', operation => NULL, window_name => NULL);
SELECT client_name, status FROM dba_autotask_client;

-- AWR retention (min 8 zile din cauza moving window baseline)
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 8*24*60, interval => 60);
EXEC DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(7);

-- Dezactiveaza audit policies (umfla AUDSYS)
NOAUDIT POLICY ORA_SECURECONFIG;
NOAUDIT POLICY ORA_LOGON_FAILURES;
SELECT policy_name, enabled_option FROM audit_unified_enabled_policies;

AUDSYS (audit trail)

Cleanup-ul via DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL esueaza cu ORA-12954 cand baza e plina (chiar si in restricted mode). Dezactiveaza audit policies preventiv pe orice server nou:

NOAUDIT POLICY ORA_SECURECONFIG;
NOAUDIT POLICY ORA_LOGON_FAILURES;
SELECT policy_name, enabled_option FROM audit_unified_enabled_policies;
-- Ar trebui sa returneze: no rows selected

Tablespace USERS

PDB-ul nou creat din PDB$SEED NU are tablespace USERS. Obiectele care erau pe USERS pe serverul vechi trebuie remapate la import:

impdp "sys/PAROLA_SYS@XEPDB1 AS SYSDBA" directory=DMPDIR dumpfile=schema.dmp logfile=schema.log remap_tablespace=USERS:ROA

Grant-uri pe DIRECTORY-uri

Sintaxa corecta pentru grant-uri pe Oracle DIRECTORY objects:

-- CORECT:
GRANT READ, WRITE ON DIRECTORY DMPDIR TO CONTAFIN_ORACLE;

-- GRESIT (ORA-00942):
GRANT READ ON SYS.DMPDIR TO CONTAFIN_ORACLE;
GRANT EXECUTE ON SYS.DMPDIR TO CONTAFIN_ORACLE;  -- EXECUTE nu e valid pe DIRECTORY

DBMS_LOCK pe Oracle XE 21c

Pe Oracle XE 21c, SYS.DBMS_LOCK necesita grant explicit:

GRANT EXECUTE ON SYS.DBMS_LOCK TO CONTAFIN_ORACLE;

Fara acest grant, pachetele care folosesc sys.dbms_lock.sleep() dau PLS-00201.

impdp/expdp AS SYSDBA pe Windows

Pe Windows CMD, AS SYSDBA trebuie inclus in ghilimele escaped:

-- CORECT:
impdp "sys/PAROLA@XEPDB1 AS SYSDBA" directory=DMPDIR dumpfile=schema.dmp logfile=schema.log

-- GRESIT (interpretat ca argument separat):
impdp sys/PAROLA@XEPDB1 AS SYSDBA directory=DMPDIR dumpfile=schema.dmp logfile=schema.log

User SYSTEM pe PDB nou

SYSTEM e common user — nu poate fi unlock/modificat doar pe un PDB. Trebuie din CDB$ROOT:

ALTER SESSION SET CONTAINER = CDB$ROOT;
ALTER USER SYSTEM IDENTIFIED BY "ParolaNoua" ACCOUNT UNLOCK;

Alternativ, foloseste SYS AS SYSDBA pentru impdp (recomandat).

Datafile path explicit

La CREATE TABLESPACE pe Oracle XE 21c, trebuie specificat path-ul complet:

-- CORECT:
CREATE TABLESPACE ROA DATAFILE 'C:\APP\...\ORADATA\XE\XEPDB1\ROA01.DBF' SIZE 2048M AUTOEXTEND ON NEXT 100M MAXSIZE 6G;

-- GRESIT (ORA-02236: invalid file name):
CREATE TABLESPACE ROA DATAFILE SIZE 2048M AUTOEXTEND ON NEXT 100M MAXSIZE 6G;

Gaseste path-ul corect:

SELECT file_name FROM dba_data_files WHERE ROWNUM = 1;

Verificare mod undo (local vs shared)

-- Din CDB$ROOT
SELECT property_value FROM database_properties WHERE property_name = 'LOCAL_UNDO_ENABLED';

Rezultate tipice

Dupa cleanup (fara recreare PDB)

Componenta	Inainte	Dupa cleanup
SYSAUX segmente	7.5 GB	1.9 GB
SYSAUX datafile	7.8 GB	7.8 GB (fara resize) / 2.5 GB (cu resize)
UNDOTBS1	2.1 GB	0.1 GB (dupa recreare)
Total alocat	13.5 GB	4.5 GB (cu recreare UNDO)

Dupa recreare PDB (solutia definitiva)

Componenta	Inainte	Dupa recreare PDB
SYSAUX	7.8 GB	390 MB
SYSTEM	-	370 MB
ROA	-	2 GB
UNDOTBS1	2.1 GB	165 MB
Total alocat	13.5 GB	~3 GB

Lectii invatate

1. TRUNCATE vs DROP pe SQL Tuning Sets: DROP nu elibereaza spatiul LOB-ului. TRUNCATE functioneaza.
2. Limita 12 GB = dba_data_files, nu dba_segments: Chiar daca segmentele ocupa 4.5 GB, datafile-urile alocate conteaza.
3. SYSAUX nu se poate shrink: ORA-03297 la orice valoare cand segmentele sunt la high water mark. Singura solutie = recreare PDB.
4. AWR retention >= moving window baseline: Default baseline = 8 zile. Nu poti seta retention < 8 zile fara a reduce baseline-ul (care nu exista ca procedura pe XE 21c).
5. Audit policies active pe PDB nou: PDB-ul creat din PDB$SEED vine cu ORA_SECURECONFIG si ORA_LOGON_FAILURES active. Dezactiveaza-le imediat.