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;

SQL Query for Oracle Redo Log Files

Handy SQL when manipulating logfile groups set pagesize 30

set linesize 100

column member format a60

column status format a10

column size_MB format '999,999'

select group#,thread#,members,status,bytes/1024/1024 size_MB from v$log;

select group#,member from v$logfile order by group#; select group#, thread#, status, bytes/1024/1024 size_MB from v$standby_log; --show parameter db_create_online_log_dest --alter system switch all logfile; --alter database add logfile thread &thread group &group size &size; --alter database drop logfile group &group; --alter database drop logfile member '&member'; Sample Output:     GROUP#    THREAD#    MEMBERS STATUS      SIZE_MB ---------- ---------- ---------- ---------- --------          1          1          2 INACTIVE      4,096          2          1          2 INACTIVE      4,096          3          1          2 CURRENT       4,096          6          2          2 INACTIVE      4,096          7          2          2 CURRENT       4,096          8          2          2 INACTIVE      4,096     GROUP# MEMBER ---------- ------------------------------------------------------------          1 +MY_DATA/mydb/onlinelog/group_1.324.807662009          1 +MY_FRA/mydb/onlinelog/group_1.2611.807662031          2 +MY_DATA/mydb/onlinelog/group_2.336.807662061          2 +MY_FRA/mydb/onlinelog/group_2.2601.807662085          3 +MY_DATA/mydb/onlinelog/group_3.321.807662115          3 +MY_FRA/mydb/onlinelog/group_3.2607.807662137 ...snip...