Top 10 - Biggest Tables and Indexes

Top 10 - Biggest Tables and Indexes

set lines 170
set pages 20
col owner      format a20
col object     format a30
col type       format a6
col tablespace format a20
col size_in_gb format 999,999.9
select * 
  from (select owner           owner,
               segment_name    object,
               segment_type    type,
               tablespace_name tablespace,
               round(bytes/1024/1024/1024,1) size_in_gb
          from dba_segments 
         where owner not in ('SYS','SYSTEM')
         order by bytes desc
       ) 
 where rownum < 11
 order by size_in_gb desc;

Generate Tablespace DDL

GENERATE TABLESPACE DDL

select dbms_metadata.get_ddl('TABLESPACE',tablespace_name) from dba_tablespaces;

or

select 'select dbms_metadata.get_ddl(''TABLESPACE'','''||tablespace_name||''') from dual;' from dba_tablespaces;