Anuj Singh Oracle DBA: 6 Sept 2011

Tuesday, 6 September 2011

How does one enable the SQL*Plus HELP facility?

Oracle SQL*Plus help

cd $ORACLE_HOME/sqlplus/admin/help

oracle@apt-amd-02:/opt/app/oracle/product/11.2/sqlplus/admin/help> ls -ltr
total 80
-rw-r--r-- 1 oracle oinstall 337 2000-06-28 01:30 helpdrop.sql
-rw-r--r-- 1 oracle oinstall 265 2003-02-16 20:47 helpbld.sql
-rw-r--r-- 1 oracle oinstall 2086 2009-01-05 20:07 hlpbld.sql
-rw-r--r-- 1 oracle oinstall 65975 2009-06-28 21:54 helpus.sql

oracle@apt-amd-02:/opt/app/oracle/product/11.2/sqlplus/admin/help> sqlplus system/sys @hlpbld.sql helpus.sql

select info
from system.help
where upper(topic)=upper('&1')
/

Enter value for 1: COLUMN
old 1: select info from system.help where upper(topic)=upper('&1')
new 1: select info from system.help where upper(topic)=upper('COLUMN')

INFO
--------------------------------------------------------------------------------

COLUMN
------

Specifies display attributes for a given column, such as:
- text for the column heading
- alignment for the column heading
- format for NUMBER data
- wrapping of column data
Also lists the current display attributes for a single column
or all columns.

COL[UMN] [{column | expr} [option ...] ]

where option represents one of the following clauses:
ALI[AS] alias
CLE[AR]
ENTMAP {ON|OFF}
FOLD_A[FTER]
FOLD_B[EFORE]
FOR[MAT] format
HEA[DING] text
JUS[TIFY] {L[EFT] | C[ENTER] | R[IGHT]}
LIKE {expr | alias}
NEWL[INE]
NEW_V[ALUE] variable
NOPRI[NT] | PRI[NT]
NUL[L] text
OLD_V[ALUE] variable
ON|OFF
WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]

32 rows selected.

Oracle Table size

Table Size !!!!



DEFINE schema_name = 'ANUJ'
set pagesize 500 linesize 300

col OBJECT_NAME for a30
col TABLE_NAME for a30 
col gb for 999999.99


SELECT distinct owner,table_name,total_table_meg/1000 GB FROM (
--SELECT distinct owner,table_name,total_table_meg MB FROM (
  SELECT
    owner, object_name, object_type, table_name, ROUND(bytes)/1024/1024 AS meg, tablespace_name, extents, initial_extent,  ROUND(Sum(bytes/1024/1024) OVER (PARTITION BY table_name)) AS total_table_meg
  FROM (
    -- Tables
    SELECT owner, segment_name AS object_name, 'TABLE' AS object_type,segment_name AS table_name, bytes, tablespace_name, extents, initial_extent
    FROM   dba_segments
    WHERE  segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
    UNION ALL
    -- Indexes
    SELECT i.owner, i.index_name AS object_name, 'INDEX' AS object_type, i.table_name, s.bytes, s.tablespace_name, s.extents, s.initial_extent
    FROM   dba_indexes i, dba_segments s
    WHERE  s.segment_name = i.index_name
    AND    s.owner = i.owner
    AND    s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
    -- LOB Segments
    UNION ALL
    SELECT l.owner, l.column_name AS object_name, 'LOB_COLUMN' AS object_type, l.table_name, s.bytes, s.tablespace_name, s.extents, s.initial_extent
    FROM   dba_lobs l, dba_segments s
    WHERE  s.segment_name = l.segment_name
    AND    s.owner = l.owner
    AND    s.segment_type = 'LOBSEGMENT'
    -- LOB Indexes
    UNION ALL
    SELECT l.owner, l.column_name AS object_name, 'LOB_INDEX' AS object_type, l.table_name, s.bytes,s.tablespace_name, s.extents, s.initial_extent
    FROM   dba_lobs l, dba_segments s
    WHERE  s.segment_name = l.index_name
    AND    s.owner = l.owner
    AND    s.segment_type = 'LOBINDEX'
  )
  WHERE owner in UPPER('&schema_name')
)
WHERE 0=0  
-- and total_table_meg > 10
--ORDER BY total_table_meg DESC, meg DESC
/




 --- with database name 
 DEFINE schema_name = 'ANUJ'  -----!!!!!!
set pagesize 500 linesize 300

col OBJECT_NAME for a30
col TABLE_NAME for a25 
col gb for 999999.99
col owner for a20
col INSTANCE_NAME for a14
col DB_NAME 	for a12
col SERVER_HOST for a15
SELECT distinct  sys_context('USERENV','INSTANCE_NAME') INSTANCE_NAME ,sys_context('USERENV','DB_NAME') DB_NAME, sys_context('USERENV','SERVER_HOST') SERVER_HOST ,owner,table_name,total_table_meg/1000 GB FROM (
--SELECT distinct owner,table_name,total_table_meg MB FROM (
  SELECT
    owner, object_name, object_type, table_name, ROUND(bytes)/1024/1024 AS meg, tablespace_name, extents, initial_extent,  ROUND(Sum(bytes/1024/1024) OVER (PARTITION BY table_name)) AS total_table_meg
  FROM (
    -- Tables
    SELECT owner, segment_name AS object_name, 'TABLE' AS object_type,segment_name AS table_name, bytes, tablespace_name, extents, initial_extent
    FROM   dba_segments
    WHERE  segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
    UNION ALL
    -- Indexes
    SELECT i.owner, i.index_name AS object_name, 'INDEX' AS object_type, i.table_name, s.bytes, s.tablespace_name, s.extents, s.initial_extent
    FROM   dba_indexes i, dba_segments s
    WHERE  s.segment_name = i.index_name
    AND    s.owner = i.owner
    AND    s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
    -- LOB Segments
    UNION ALL
    SELECT l.owner, l.column_name AS object_name, 'LOB_COLUMN' AS object_type, l.table_name, s.bytes, s.tablespace_name, s.extents, s.initial_extent
    FROM   dba_lobs l, dba_segments s
    WHERE  s.segment_name = l.segment_name
    AND    s.owner = l.owner
    AND    s.segment_type = 'LOBSEGMENT'
    -- LOB Indexes
    UNION ALL
    SELECT l.owner, l.column_name AS object_name, 'LOB_INDEX' AS object_type, l.table_name, s.bytes,s.tablespace_name, s.extents, s.initial_extent
    FROM   dba_lobs l, dba_segments s
    WHERE  s.segment_name = l.index_name
    AND    s.owner = l.owner
    AND    s.segment_type = 'LOBINDEX'
  )
  WHERE owner in UPPER('&schema_name')
)
WHERE 0=0  
-- and total_table_meg > 10
--ORDER BY total_table_meg DESC, meg DESC
/







with dbms_xplan.format_size  ******

DEFINE schema_name ='ARC'
col size1 for a10
col OWNER for a20
col TABLE_NAME for a20
SELECT distinct owner,table_name,dbms_xplan.format_size(total_table) size1 FROM (
--SELECT distinct owner,table_name,total_table_meg MB FROM (
  SELECT
    owner, object_name, object_type, table_name, ROUND(bytes) AS bytes1, tablespace_name, extents, initial_extent,  Sum(bytes) OVER (PARTITION BY table_name) AS total_table
  FROM (
    -- Tables
    SELECT owner, segment_name AS object_name, 'TABLE' AS object_type,segment_name AS table_name, bytes, tablespace_name, extents, initial_extent
    FROM   dba_segments
    WHERE  segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
    UNION ALL
    -- Indexes
    SELECT i.owner, i.index_name AS object_name, 'INDEX' AS object_type, i.table_name, s.bytes, s.tablespace_name, s.extents, s.initial_extent
    FROM   dba_indexes i, dba_segments s
    WHERE  s.segment_name = i.index_name
    AND    s.owner = i.owner
    AND    s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
    -- LOB Segments
    UNION ALL
    SELECT l.owner, l.column_name AS object_name, 'LOB_COLUMN' AS object_type, l.table_name, s.bytes, s.tablespace_name, s.extents, s.initial_extent
    FROM   dba_lobs l, dba_segments s
    WHERE  s.segment_name = l.segment_name
    AND    s.owner = l.owner
    AND    s.segment_type = 'LOBSEGMENT'
    -- LOB Indexes
    UNION ALL
    SELECT l.owner, l.column_name AS object_name, 'LOB_INDEX' AS object_type, l.table_name, s.bytes,s.tablespace_name, s.extents, s.initial_extent
    FROM   dba_lobs l, dba_segments s
    WHERE  s.segment_name = l.index_name
    AND    s.owner = l.owner
    AND    s.segment_type = 'LOBINDEX'
  )
  WHERE owner in UPPER('&schema_name')
)
WHERE 0=0  
-- and total_table_meg > 10
--ORDER BY total_table_meg DESC, meg DESC
/

col topseg_seg_owner HEAD OWNER FOR A15
col topseg_segment_name head SEGMENT_NAME for a30
col topseg_segment_type head SEGMENT_TYPE for a30
col OWNER for a15
col Size1 for a10


define 1='USER'  ---- tablespace NAME

select * from (
    select
        tablespace_name,
        owner,
        segment_name topseg_segment_name,
        --partition_name,
        REPLACE(segment_type, ' PARTITION', ' - PARTITIONED') topseg_segment_type,
        count(*),
        dbms_xplan.format_size(sum(bytes)) Size1
    from dba_segments
    where upper(tablespace_name) like upper('%&1%')
   and owner= 'SYS'     -----<<<<<
    group by tablespace_name, owner, segment_name, segment_type
    order by Size1 desc
)
where rownum <= 30;


set linesize 300
column table_name   format a25
column object_name  format a32
column owner        format a15
col ByteH for a12
compute sum of Size_GB on report
break on report
SELECT
owner, table_name,segment_type,TABLESPACE_NAME, TRUNC(sum(bytes)/1024/1024/1024) Size_GB,dbms_xplan.format_size(sum(bytes)) ByteH
FROM
(SELECT segment_name table_name, owner, TABLESPACE_NAME,bytes ,segment_type FROM dba_segments  
WHERE segment_type = 'TABLE'
UNION ALL
SELECT i.table_name, i.owner,s.TABLESPACE_NAME, s.bytes ,segment_type FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name 
AND   s.owner = i.owner
AND   s.segment_type = 'INDEX'
UNION ALL
SELECT l.table_name, l.owner,s.TABLESPACE_NAME, s.bytes ,segment_type FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND   s.owner = l.owner
AND   s.segment_type = 'LOBSEGMENT'
UNION ALL
SELECT l.table_name, l.owner,s.TABLESPACE_NAME, s.bytes ,segment_type FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND   s.owner = l.owner
AND   s.segment_type = 'LOBINDEX'
)
WHERE 1=1
and owner ='OOOOOO'
and table_name='OOOOOOOO'
GROUP BY owner,table_name,segment_type,TABLESPACE_NAME
ORDER BY SUM(bytes) desc
/

---- Partition included  !!!!!


set linesize 300
column table_name   format a25
column object_name  format a32
column owner        format a15
col ByteH for a12
compute sum of Size_GB on report
break on report
SELECT
owner, table_name,segment_type,TABLESPACE_NAME, TRUNC(sum(bytes)/1024/1024/1024) Size_GB,dbms_xplan.format_size(sum(bytes)) ByteH
FROM
(SELECT segment_name table_name, owner, TABLESPACE_NAME,bytes ,segment_type FROM dba_segments  
WHERE segment_type = 'TABLE'
union all
SELECT segment_name table_name, owner, TABLESPACE_NAME,bytes ,segment_type FROM dba_segments  
WHERE segment_type = 'TABLE PARTITION'
UNION ALL
SELECT i.table_name, i.owner,s.TABLESPACE_NAME, s.bytes ,segment_type FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name 
AND   s.owner = i.owner
AND   s.segment_type = 'INDEX'
UNION ALL
SELECT l.table_name, l.owner,s.TABLESPACE_NAME, s.bytes ,segment_type FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND   s.owner = l.owner
AND   s.segment_type = 'LOBSEGMENT'
UNION ALL
SELECT l.table_name, l.owner,s.TABLESPACE_NAME, s.bytes ,segment_type FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND   s.owner = l.owner
AND   s.segment_type = 'LOBINDEX'
)
WHERE 1=1
and owner ='OOO'
and table_name='OOO'
GROUP BY owner,table_name,segment_type,TABLESPACE_NAME
ORDER BY SUM(bytes) desc
/

set linesize 300
column table_name   format a25
column object_name  format a32
column owner        format a15

compute sum of Size_GB on report
break on report
SELECT
owner, table_name,segment_type,TABLESPACE_NAME, TRUNC(sum(bytes)/1024/1024/1024) Size_GB
FROM
(SELECT segment_name table_name, owner, TABLESPACE_NAME,bytes ,segment_type FROM dba_segments  
WHERE segment_type = 'TABLE'
UNION ALL
SELECT i.table_name, i.owner,s.TABLESPACE_NAME, s.bytes ,segment_type FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name 
AND   s.owner = i.owner
AND   s.segment_type = 'INDEX'
UNION ALL
SELECT l.table_name, l.owner,s.TABLESPACE_NAME, s.bytes ,segment_type FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND   s.owner = l.owner
AND   s.segment_type = 'LOBSEGMENT'
UNION ALL
SELECT l.table_name, l.owner,s.TABLESPACE_NAME, s.bytes ,segment_type FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND   s.owner = l.owner
AND   s.segment_type = 'LOBINDEX'
)
WHERE 1=1
and owner ='XX'
and table_name='XX'
GROUP BY owner,table_name,segment_type,TABLESPACE_NAME
ORDER BY SUM(bytes) desc
/

***************************

--with regexp_like

set linesize 300
column table_name   format a30
column object_name  format a32
column owner        format a15

compute sum of Size_GB on report
break on report
SELECT
owner, table_name,segment_type,TABLESPACE_NAME, TRUNC(sum(bytes)/1024/1024/1024) Size_GB
FROM
(SELECT segment_name table_name, owner, TABLESPACE_NAME,bytes ,segment_type FROM dba_segments  
WHERE segment_type = 'TABLE'
UNION ALL
SELECT i.table_name, i.owner,s.TABLESPACE_NAME, s.bytes ,segment_type FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name 
AND   s.owner = i.owner
AND   s.segment_type = 'INDEX'
UNION ALL
SELECT l.table_name, l.owner,s.TABLESPACE_NAME, s.bytes ,segment_type FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND   s.owner = l.owner
AND   s.segment_type = 'LOBSEGMENT'
UNION ALL
SELECT l.table_name, l.owner,s.TABLESPACE_NAME, s.bytes ,segment_type FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND   s.owner = l.owner
AND   s.segment_type = 'LOBINDEX'
)
WHERE 1=1
and owner ='XXXX'
and regexp_like (table_name, '[0-9]') --- tablename with digit 
GROUP BY owner,table_name,segment_type,TABLESPACE_NAME
ORDER BY SUM(bytes) desc
/

***********************************************

set linesize 300
col SEGMENT_NAME for a20
define table_owner='USER'
WITH
t AS (
SELECT owner, table_name
  FROM dba_tables
 WHERE owner = '&&table_owner.'
   --AND table_name = '&&table_name.'
),
s AS (
SELECT 1 AS oby, s.segment_type, s.owner, s.segment_name, s.partition_name, NULL AS column_name, s.bytes, s.tablespace_name
  FROM t, dba_segments s
 WHERE s.owner = t.owner
   AND s.segment_name = t.table_name
   AND s.segment_type LIKE 'TABLE%'
 UNION ALL
SELECT 2 AS oby, s.segment_type, s.owner, s.segment_name, s.partition_name, NULL AS column_name, s.bytes, s.tablespace_name
  FROM t, dba_indexes i, dba_segments s
 WHERE i.table_owner = t.owner
   AND i.table_name = t.table_name
   AND s.owner = i.owner
   AND s.segment_name = i.index_name
   AND s.segment_type LIKE 'INDEX%'
 UNION ALL
SELECT 3 AS oby, s.segment_type, s.owner, s.segment_name, s.partition_name, l.column_name, s.bytes, s.tablespace_name
  FROM t, dba_lobs l, dba_segments s
 WHERE l.owner = t.owner
   AND l.table_name = t.table_name
   AND s.owner = l.owner
   AND s.segment_name = l.segment_name
   AND s.segment_type LIKE 'LOB%'
)
--SELECT segment_type, COUNT(*) AS segments, ROUND(SUM(bytes)/POWER(2,20),3) AS mebibytes, tablespace_name
SELECT segment_type, COUNT(*) AS segments, ROUND(SUM(bytes)/1024/1024/1024) AS GB, tablespace_name,s.segment_name
  FROM s
 GROUP BY oby, segment_type, tablespace_name,s.segment_name
 ORDER BY oby, segment_type, tablespace_name,s.segment_name
/





---- with dbms_xplan.format_size

set linesize 300
col SEGMENT_NAME for a27
col size1 for a10

define table_owner='ESS'
define table_name='LOG'


WITH
t AS (
SELECT owner, table_name
  FROM dba_tables
 WHERE owner = '&&table_owner.'
   AND table_name = '&&table_name.'
),
s AS (
SELECT 1 AS oby, s.segment_type, s.owner, s.segment_name, s.partition_name, NULL AS column_name, s.bytes, s.tablespace_name
  FROM t, dba_segments s
 WHERE s.owner = t.owner
   AND s.segment_name = t.table_name
   AND s.segment_type LIKE 'TABLE%'
 UNION ALL
SELECT 2 AS oby, s.segment_type, s.owner, s.segment_name, s.partition_name, NULL AS column_name, s.bytes, s.tablespace_name
  FROM t, dba_indexes i, dba_segments s
 WHERE i.table_owner = t.owner
   AND i.table_name = t.table_name
   AND s.owner = i.owner
   AND s.segment_name = i.index_name
   AND s.segment_type LIKE 'INDEX%'
 UNION ALL
SELECT 3 AS oby, s.segment_type, s.owner, s.segment_name, s.partition_name, l.column_name, s.bytes, s.tablespace_name
  FROM t, dba_lobs l, dba_segments s
 WHERE l.owner = t.owner
   AND l.table_name = t.table_name
   AND s.owner = l.owner
   AND s.segment_name = l.segment_name
   AND s.segment_type LIKE 'LOB%'
)
--SELECT segment_type, COUNT(*) AS segments, ROUND(SUM(bytes)/POWER(2,20),3) AS mebibytes, tablespace_name
SELECT segment_type, COUNT(*) AS segments, dbms_xplan.format_size(SUM(bytes)) size1, tablespace_name,s.segment_name
  FROM s
 GROUP BY oby, segment_type, tablespace_name,s.segment_name
 ORDER BY oby, segment_type, tablespace_name,s.segment_name
/

all Table size in a particular  schema  
oracle table size 



undefine ownr
accept ownr prompt  'Enter schemaname or press  for all schemas: '
SET LINES 132
SET VERIFY off
COLUMN OWNER FORMAT A30
COLUMN TABLE FORMAT A30
COLUMN Taille FORMAT A15
COLUMN TABLESPACE FORMAT A20
SELECT "OWNER"
,      "TABLE"
,      "DB Blocks"
,      ROUND(DECODE(SIGN("Size"/1048576 -1 )
              , -1 , DECODE(SIGN("Size"/1024 -1)
                     , -1, "Size"
                     ,  "Size"/1024)
             , "Size"/1048576) ,2) "SIZE"
,       DECODE(SIGN("Size"/1048576 -1 )
              , -1, DECODE(SIGN("Size"/1024 -1)
                    ,-1 ,' Byte'
                    , ' Kb')
              , ' Mb') " "
,      "TABLESPACE"
FROM
(SELECT owner "OWNER"
 , segment_name "TABLE"
 , SUM(BYTES)   "Size"
 , blocks "DB Blocks"
 , tablespace_name "TABLESPACE"
 FROM DBA_SEGMENTS
 WHERE segment_type = 'TABLE'
  AND DECODE('&&ownr', null,'X', OWNER) = DECODE('&&ownr',null,'X',UPPER('&&ownr'))
  AND OWNER NOT IN ('SYS' , 'SYSTEM')
 GROUP BY owner, segment_name, tablespace_name, blocks
 ORDER BY owner, segment_name) ;




SQL> @tablesize
Enter schemaname or press  for all schemas: SCOTT


OWNER                          TABLE                           DB Blocks       SIZE       TABLESPACE
------------------------------ ------------------------------ ---------- ---------- ----- --------------------
SCOTT                          DEPT                                    8         64  Kb   USERS
SCOTT                          EMP                                     8         64  Kb   USERS
SCOTT                          SALGRADE                                8         64  Kb   USERS

3 rows selected.







Table size .....


undefine ownr
undefine seg_name
accept ownr prompt  'Enter schemaname or press  for all schemas: '
SET LINES 132
SET VERIFY off
COLUMN OWNER FORMAT A30
COLUMN TABLE FORMAT A30
COLUMN Taille FORMAT A15
COLUMN TABLESPACE FORMAT A20
SELECT "OWNER"
,      "TABLE"
,      "DB Blocks"
,      ROUND(DECODE(SIGN("Size"/1048576 -1 )
              , -1 , DECODE(SIGN("Size"/1024 -1)
                     , -1, "Size"
                     ,  "Size"/1024)
             , "Size"/1048576) ,2) "SIZE"
,       DECODE(SIGN("Size"/1048576 -1 )
              , -1, DECODE(SIGN("Size"/1024 -1)
                    ,-1 ,' Byte'
                    , ' Kb')
              , ' Mb') " "
,      "TABLESPACE"
FROM
(SELECT owner "OWNER"
 , segment_name "TABLE"
 , SUM(BYTES)   "Size"
 , blocks "DB Blocks"
 , tablespace_name "TABLESPACE"
FROM DBA_SEGMENTS
WHERE segment_type = 'TABLE'
AND DECODE('&&ownr', null,'X', OWNER) = DECODE('&&ownr',null,'X',UPPER('&&ownr'))
and segment_name =upper('&Tab_Name') 
AND OWNER NOT IN ('SYS' , 'SYSTEM')
GROUP BY owner, segment_name, tablespace_name, blocks
ORDER BY owner, segment_name) ;


SQL> @tablesize1
Enter schemaname or press  for all schemas: SCOTT
Enter value for tab_name: EMP


OWNER                          TABLE                           DB Blocks       SIZE       TABLESPACE
------------------------------ ------------------------------ ---------- ---------- ----- --------------------
SCOTT                          EMP                                     8         64  Kb   USERS

====================

-- from Web

 
 object size !!!!!!!!!!!!!!!!!!!!!!!
 
 

set linesize 500 pagesize 300

COLUMN name NEW_VALUE _instname NOPRINT
select lower(instance_name) name from v$instance;

COLUMN conname NEW_VALUE _conname NOPRINT
select case 
            when a.conname = 'CDB$ROOT'   then 'ROOT'
            when a.conname = 'PDB$SEED'   then 'SEED'
            else a.conname
            end as conname
from (select SYS_CONTEXT('USERENV', 'CON_NAME') conname from dual) a;

COLUMN conid NEW_VALUE _conid NOPRINT
select SYS_CONTEXT('USERENV', 'CON_ID') conid from dual;



set numf 99999999999999.99
col OWNER for a20
col SEGMENT_NAME for a30
col TABLE_NAME  for a25

WITH schema_object AS (
SELECT /*+  MATERIALIZE NO_MERGE  */ /* 2b.207 */
       segment_type,
       owner,
       segment_name,
       tablespace_name,
       COUNT(*) segments,
       SUM(extents) extents,
       SUM(blocks) blocks,
       SUM(bytes) bytes
  FROM dba_segments
 WHERE 'Y' = 'Y'
 GROUP BY
       segment_type,
       owner,
       segment_name,
       tablespace_name
), totals AS (
SELECT /*+  MATERIALIZE NO_MERGE  */ /* 2b.207 */
       SUM(segments) segments,
       SUM(extents) extents,
       SUM(blocks) blocks,
       SUM(bytes) bytes
  FROM schema_object
), top_200_pre AS (
SELECT /*+  MATERIALIZE NO_MERGE  */ /* 2b.207 */
       ROWNUM rank, v1.*
       FROM (
SELECT so.segment_type,
       so.owner,
       so.segment_name,
       so.tablespace_name,
       so.segments,
       so.extents,
       so.blocks,
       so.bytes,
       ROUND((so.segments / t.segments) * 100, 3) segments_perc,
       ROUND((so.extents / t.extents) * 100, 3) extents_perc,
       ROUND((so.blocks / t.blocks) * 100, 3) blocks_perc,
       ROUND((so.bytes / t.bytes) * 100, 3) bytes_perc
  FROM schema_object so,
       totals t
 ORDER BY
       bytes_perc DESC NULLS LAST
) v1
 WHERE ROWNUM < 201
), top_200 AS (
SELECT p.*,
       (SELECT object_id
          FROM dba_objects o
         WHERE o.object_type = p.segment_type
           AND o.owner = p.owner
           AND o.object_name = p.segment_name
           AND o.object_type NOT LIKE '%PARTITION%') object_id,
       (SELECT data_object_id
          FROM dba_objects o
         WHERE o.object_type = p.segment_type
           AND o.owner = p.owner
           AND o.object_name = p.segment_name
           AND o.object_type NOT LIKE '%PARTITION%') data_object_id,
       (SELECT SUM(p2.bytes_perc) FROM top_200_pre p2 WHERE p2.rank <= p.rank) bytes_perc_cum
  FROM top_200_pre p
), top_200_totals AS (
SELECT /*+  MATERIALIZE NO_MERGE  */ /* 2b.207 */
       SUM(segments) segments,
       SUM(extents) extents,
       SUM(blocks) blocks,
       SUM(bytes) bytes,
       SUM(segments_perc) segments_perc,
       SUM(extents_perc) extents_perc,
       SUM(blocks_perc) blocks_perc,
       SUM(bytes_perc) bytes_perc
  FROM top_200
), top_100_totals AS (
SELECT /*+  MATERIALIZE NO_MERGE  */ /* 2b.207 */
       SUM(segments) segments,
       SUM(extents) extents,
       SUM(blocks) blocks,
       SUM(bytes) bytes,
       SUM(segments_perc) segments_perc,
       SUM(extents_perc) extents_perc,
       SUM(blocks_perc) blocks_perc,
       SUM(bytes_perc) bytes_perc
  FROM top_200
 WHERE rank < 101
), top_20_totals AS (
SELECT /*+  MATERIALIZE NO_MERGE  */ /* 2b.207 */
       SUM(segments) segments,
       SUM(extents) extents,
       SUM(blocks) blocks,
       SUM(bytes) bytes,
       SUM(segments_perc) segments_perc,
       SUM(extents_perc) extents_perc,
       SUM(blocks_perc) blocks_perc,
       SUM(bytes_perc) bytes_perc
  FROM top_200
 WHERE rank < 21
)
SELECT v.rank,
       v.segment_type,
       v.owner,
       v.segment_name,
       v.object_id,
       v.data_object_id,
       v.tablespace_name,
       CASE
       WHEN v.segment_type LIKE 'INDEX%' THEN
         (SELECT i.table_name
            FROM dba_indexes i
           WHERE i.owner = v.owner AND i.index_name = v.segment_name)
       WHEN v.segment_type LIKE 'LOB%' THEN
         (SELECT l.table_name
            FROM dba_lobs l
           WHERE l.owner = v.owner AND l.segment_name = v.segment_name)
       ELSE v.segment_name
       END table_name,
       v.segments,
       v.extents,
       v.blocks,
       v.bytes,
       ROUND(v.bytes / POWER(10,9), 3) gb,
       LPAD(TO_CHAR(v.segments_perc, '990.000'), 7) segments_perc,
       LPAD(TO_CHAR(v.extents_perc, '990.000'), 7) extents_perc,
       LPAD(TO_CHAR(v.blocks_perc, '990.000'), 7) blocks_perc,
       LPAD(TO_CHAR(v.bytes_perc, '990.000'), 7) bytes_perc,
       LPAD(TO_CHAR(v.bytes_perc_cum, '990.000'), 7) perc_cum
  FROM (
SELECT d.rank,
       d.segment_type,
       d.owner,
       d.segment_name,
       d.object_id,
       d.data_object_id,
       d.tablespace_name,
       d.segments,
       d.extents,
       d.blocks,
       d.bytes,
       d.segments_perc,
       d.extents_perc,
       d.blocks_perc,
       d.bytes_perc,
       d.bytes_perc_cum
  FROM top_200 d
 UNION ALL
SELECT TO_NUMBER(NULL) rank,
       NULL segment_type,
       NULL owner,
       NULL segment_name,
       TO_NUMBER(NULL),
       TO_NUMBER(NULL),
       'TOP  20' tablespace_name,
       st.segments,
       st.extents,
       st.blocks,
       st.bytes,
       st.segments_perc,
       st.extents_perc,
       st.blocks_perc,
       st.bytes_perc,
       TO_NUMBER(NULL) bytes_perc_cum
  FROM top_20_totals st
 UNION ALL
SELECT TO_NUMBER(NULL) rank,
       NULL segment_type,
       NULL owner,
       NULL segment_name,
       TO_NUMBER(NULL),
       TO_NUMBER(NULL),
       'TOP 100' tablespace_name,
       st.segments,
       st.extents,
       st.blocks,
       st.bytes,
       st.segments_perc,
       st.extents_perc,
       st.blocks_perc,
       st.bytes_perc,
       TO_NUMBER(NULL) bytes_perc_cum
  FROM top_100_totals st
 UNION ALL
SELECT TO_NUMBER(NULL) rank,
       NULL segment_type,
       NULL owner,
       NULL segment_name,
       TO_NUMBER(NULL),
       TO_NUMBER(NULL),
       'TOP 200' tablespace_name,
       st.segments,
       st.extents,
       st.blocks,
       st.bytes,
       st.segments_perc,
       st.extents_perc,
       st.blocks_perc,
       st.bytes_perc,
       TO_NUMBER(NULL) bytes_perc_cum
  FROM top_200_totals st
 UNION ALL
SELECT TO_NUMBER(NULL) rank,
       NULL segment_type,
       NULL owner,
       NULL segment_name,
       TO_NUMBER(NULL),
       TO_NUMBER(NULL),
       'TOTAL' tablespace_name,
       t.segments,
       t.extents,
       t.blocks,
       t.bytes,
       100 segemnts_perc,
       100 extents_perc,
       100 blocks_perc,
       100 bytes_perc,
       TO_NUMBER(NULL) bytes_perc_cum
  FROM totals t) v;
  
  
  
               RANK SEGMENT_TYPE       OWNER                SEGMENT_NAME                            OBJECT_ID     DATA_OBJECT_ID TABLESPACE_NAME                TABLE_NAME                      SEGMENTS             EXTENTS             BLOCKS              BYTES                 GB SEGMENTS_PERC                EXTENTS_PERC                 BLOCKS_PERC                  BYTES_PERC       PERC_CUM
------------------ ------------------ -------------------- ------------------------------ ------------------ ------------------ ------------------------------ ------------------------- ------------------ ------------------ ------------------ ------------------ ------------------ ---------------------------- ---------------------------- ---------------------------- ---------------------------- ----------------------------
              1.00 TABLE              OT                   T                                       833695.00          833695.00 USERS                          T                                   1.00              412.00         1476480.00     12095324160.00              12.10    0.01                         1.62                        33.52                        33.52            33.52
              2.00 TABLE              TEST2                T                                       582806.00          582814.00 USERS                          T                                   1.00              241.00          475136.00      3892314112.00               3.89    0.01                         0.94                        10.78                        10.78            44.31
              3.00 TABLE              ANUJ                 TEST9                                   206819.00          206819.00 USERS                          TEST9                               1.00              232.00          401408.00      3288334336.00               3.29    0.01                         0.91                         9.11                         9.11            53.42
              4.00 INDEX              SYS                  I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST           14364.00           14364.00 SYSAUX                         WRI$_OPTSTAT_HISTGRM_HIST           1.00              244.00          172672.00      1414529024.00               1.42    0.01                         0.96                         3.92                         3.92            57.34
                                                                                                                                                               ORY

==========

----schema size

https://anuj-singh.blogspot.com/2023/

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews

Tuesday, 6 September 2011

How does one enable the SQL*Plus HELP facility?

Oracle Table size

Oracle DBA

anuj blog Archive