INAINTE DE A NE APUCA DE TBS TREBUIE VERIFICAT DACA AVEM dATAgUARD SAU stANDby

---------------------------------------------------------------------------------------------------------------------------------------------------

SELECT a.TABLESPACE_NAME,

NVL(ROUND((a.BYTES /1024)/1024/1024,2),2) GB_TOTAL,

NVL(ROUND((b.BYTES /1024)/1024/1024,2),2) GB_FREE,

NVL(ROUND((b.largest/1024),2),0) KB_Chunk,

NVL(ROUND(((a.BYTES -NVL(b.BYTES,1))/a.BYTES)*100,4),0) percent_used

FROM

(SELECT TABLESPACE_NAME,

NVL(SUM(BYTES),0) BYTES

FROM dba_data_files

GROUP BY TABLESPACE_NAME

) a,

(SELECT TABLESPACE_NAME,

NVL(SUM(BYTES),1) BYTES ,

NVL(MAX(BYTES),1) largest

FROM dba_free_space

GROUP BY TABLESPACE_NAME

) b

WHERE a.TABLESPACE_NAME=b.TABLESPACE_NAME(+)

ORDER BY ((a.BYTES-b.BYTES)/a.BYTES) DESC;


------------------------------------------------------------------------------------------------------

 
SELECT SUBSTR(DF.TABLESPACE_NAME,1,20) "TABLESPACE NAME",

SUBSTR(DF.FILE_NAME,1,80) "FILE NAME",

ROUND(DF.BYTES/1024/1024/1024,0) "SIZE (G)",

DECODE(E.USED_BYTES,NULL,0,ROUND(E.USED_BYTES/1024/1024/1024,0)) "USED (G)",

DECODE(F.FREE_BYTES,NULL,0,ROUND(F.FREE_BYTES/1024/1024/1024,0)) "FREE (G)",

DECODE(E.USED_BYTES,NULL,0,ROUND((E.USED_BYTES/DF.BYTES)*100,0)) "% USED"

FROM DBA_DATA_FILES DF,

(SELECT FILE_ID, SUM(BYTES) USED_BYTES FROM DBA_EXTENTS GROUP BY FILE_ID) E,

(SELECT SUM(BYTES) FREE_BYTES, FILE_ID FROM DBA_FREE_SPACE GROUP BY FILE_ID) F

WHERE E.FILE_ID (+) = DF.FILE_ID

AND DF.FILE_ID = F.FILE_ID (+)

ORDER BY DF.TABLESPACE_NAME, DF.FILE_NAME;
 
--------------------------------------------------------------------------------------------------------------------------------
 +
 
 
 pentru a mari tabela  daca la SIZE este 10 Gb se poate modifica in 15..pana la 32 mx sau se face dupa add datafile (situatii cand SIZE ul este de 32 si e necesar datafile nou
 
ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\ABS1\ABS_INDX01.DBF' RESIZE 7168M; 

 
--------------------------------------------------------------------------------------------------------------------------------------------
 
 Atentie pt ABS_DATA si pt INDEX   este pentru creare de datafile nou, se face cu 1 gb si treptat sa mareste. atentie daca ai DG sau standby
 si daca e setat automat sau manual pt check: SHOW PARAMETER standby_file_management;  pt set ALTER SYSTEM SET standby_file_management=AUTO SCOPE=BOTH; (ambele db)
 
 PT ABS_DATA
ALTER TABLESPACE ABS_DATA ADD DATAFILE '/u01/app/oracle/oradata/ABS1/abs_indx18.dbf' SIZE 1024M AUTOEXTEND ON NEXT 1024M MAXSIZE 32767M;

PT INDEX
ALTER TABLESPACE INDX ADD DATAFILE '/u01/app/oracle/oradata/ABS1/abs_indx19.dbf' SIZE 1024M AUTOEXTEND ON NEXT 1024M MAXSIZE 32767M;

----------------------------------------------------------------------------------------------------------------------------------------------

REDENUMIRE / MODIFICARE TABELA >>  nu merge la standard edition
ALTER DATABASE MOVE DATAFILE 'E:\ORACLE\ORADATA\CUST15\ABS_DATA137.DBF' TO 'D:\ORACLE\ORADATA\CUST15\ABS_DATA137.DBF';

------------------------------------------------------------------------------------------------------------------------------------------------

TABLESPACE MAXSIZE
select tablespace_name, file_name, autoextensible, round(maxbytes/1024/1024,2)from dba_data_files;

----------------------------------------------------------------------------------------------------------------------------------------------------