Search This Blog

Total Pageviews

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 !!!!
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
/









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/


Oracle DBA

anuj blog Archive