SR 4 DB

--- Format set heading off; set echo off; Set pages 999 ; set long 90000 ; BEGIN dbms_metadata.set_transform_param ( DBMS_METADATA.SESSION_TRANSFORM , 'PRETTY' , TRUE ); dbms_metadata.set_transform_param ( DBMS_METADATA.SESSION_TRANSFORM , 'SQLTERMINATOR' , TRUE ); END; / --- Example   select dbms_metadata.get_ddl ( 'TRIGGER' , '&TRIGGER_NAME , '&OWNER' ) from dual ; select dbms_metadata.get_ddl ( 'TYPE' , '&TYPE_NAME' , '&OWNER' ) from dual ; select dbms_metadata.get_ddl ( 'INDEX' , '&INDEX_NAME' , '&OWNER' ) from dual ; select dbms_metadata.get_ddl ( 'TABLE' , '&TABLE_NAME' , '&OWNER' ) from dual ; select dbms_metadata.get_ddl ( 'TABLESPACE' , '&TABLESPACE_NAME' ) from dual ; select dbms_metadata.get_ddl ( 'PROCEDURE' , '&PROCE

In this example we lost one extent (128M) because oracle uses in  locally managed tablespaces with a UNIFORM extent size  at least 64K per datafile  for Metadata Blocks . create   tablespace  TBS_TEST_BAD datafile   '/oradata/TEST/lm02.dbf'   size   524288 K   -- <-- 512M extent   management   local   uniform   size   128 M; Tablespace                          Size  MB    Free MB    Used MB Used Pct ------------------------------ ---------- ---------- ---------- -------- TBS_TEST_BAD                           512          384          128   25  % Then datafile has to be  a multiple of their extent size + 64K. create tablespace TBS_TEST datafile ' /oradata/TEST/lm01.dbf ' size 524352 K   -- <-- 512M  + 64K extent management local uniform size 128 M; Tablespace                         Size MB    Free MB    Used MB Used Pct ------------------------------ ---------- ---------- ---------- -------- TBS_TEST_GOOD

The following script allows us: Used percent, free space Details and properties Segments inside tablespaces Statistics of segments Statistics of IO per datafiles Datafiles of tablespace Quotas Script: view        : tbsinfo.sql download : tbsinfo.sql Example: Tablespace Name : DATACCSL02 --- Tablespaces Tablespace                         Size MB    Free MB    Used MB Used Pct ------------------------------ ---------- ---------- ---------- -------- DATACCSL02                          49158       48256         902 1.83 % Status    Logging    Extent Man Allocation ASSM    Next Extent KB Contents   Block Size --------- --------- ---------- ---------- ------ -------------- --------- ---------- ONLINE     LOGGING    LOCAL       UNIFORM      AUTO            131072 PERMANENT        8192 --- Quotas User                           Used  Quota ------------------------ ---------- --------------- UCS                             896 UNLI