Search This Blog

Total Pageviews

Wednesday, 7 September 2011

Oracle GATHER_STATS_JOB in Oracle 11g or Automatic Optimizer Statistics Collection

gather_stats_job in 10g

BEGIN
DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;



in oracle 11g

dbms gather_stats_job or optimizer stats


SQL> BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

1 BEGIN
2 DBMS_AUTO_TASK_ADMIN.enable(
3 client_name => 'auto optimizer stats collection',
4 operation => NULL,
5 window_name => NULL);
6* END;
SQL> /

PL/SQL procedure successfully completed.


EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('OE',DBMS_STATS.AUTO_SAMPLE_SIZE);

AUTO_SAMPLE_SIZE lets Oracle determine the best sample size necessary for good statistics
Posted by at No comments:

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.
Posted by at No comments:

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/


Posted by at 1 comment:

Monday, 5 September 2011

Oracle Count alphabet

count in plsql



DECLARE
l_count_a NUMBER := 0;
l_string VARCHAR2(200);
l_string_length INTEGER;
BEGIN
l_string := 'eyryoeryaaasdflhaaaasdfhfhhhjaaasdfaffdsfaadsfdooo83u4384asdfaaaassssaaddAAAAA';

l_string_length := LENGTH( l_string );

FOR i IN 1..l_string_length LOOP
IF SUBSTR( l_string, i, 1 ) IN ( 'A', 'a' ) THEN
l_count_a := l_count_a + 1;
END IF;
END LOOP;

dbms_output.put_line( 'aA Count: ' || l_count_a );
END;
/

aA Count: 25

Posted by at No comments:

Saturday, 3 September 2011

encrypted data in Oracle


How to store data DES encrypted in Oracle ?

This tip is from the Oracle Magazine, it shows the usage of the DBMS_OBFUSCATION_TOOLKIT.
The DBMS_OBFUSCATION_TOOLKIT is the DES encryption package. This package shipped with Oracle8i Release 2 and later. It provides first-time field-level encryption in the database. The trick to using this package is to make sure everything is a multiple of eight. Both the key and the input data must have a length divisible by eight (the key must be exactly 8 bytes long).

Example

CREATE OR REPLACE PROCEDURE obfuscation_demo AS
l_data varchar2(255);
l_string VARCHAR2(25) := 'hello world';
BEGIN
--
-- Both the key and the input data must have a length
-- divisible by eight (the key must be exactly 8 bytes long).
--
l_data := RPAD(l_string,(TRUNC(LENGTH(l_string)/8)+1)*8,CHR(0));
--
DBMS_OUTPUT.PUT_LINE('l_string before encrypt: ' || l_string);
--
-- Encrypt the input string
--
DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT
(input_string => l_data,
key_string => 'magickey',
encrypted_string => l_string);
--
DBMS_OUTPUT.PUT_LINE('l_string ENCRYPTED: ' || l_string);
--
--
-- Decrypt the input string
--
DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT
(input_string => l_string,
key_string => 'magickey',
decrypted_string => l_data);
--
DBMS_OUTPUT.PUT_LINE('l_string DECRYPT: ' || L_DATA);
--
END;
/

SQL> exec obfuscation_demo

l_string before encrypt: hello world
l_string ENCRYPTED: ¿¿¿H?¿¿¿
l_string DECRYPT: hello world

PL/SQL procedure successfully completed.

You must protect and preserve your "magickey"—8 bytes of data that is used to encrypt/decrypt the data. If it becomes compromised, your data is vulnerable.


Posted by at No comments:

Oracle foreign key constraints procedure



CREATE OR REPLACE
PROCEDURE show_fkeys(
p_table_name IN user_constraints.table_name%TYPE)
IS
-- constants
-- identify fkeys on pkey for given table
CURSOR id_fkeys (
c_table_name user_constraints.table_name%TYPE)
IS
SELECT table_name, constraint_name fkey, r_constraint_name pkey,
status
FROM user_constraints
WHERE constraint_type='R'
AND r_constraint_name IN (
SELECT constraint_name
FROM user_constraints
WHERE table_name=c_table_name
AND constraint_type='P')
ORDER BY table_name, constraint_name;
-- record variables
rec_id_fkeys id_fkeys%ROWTYPE;
-- variables
l_table_name user_constraints.table_name%TYPE;
l_pkey_name user_constraints.constraint_name%TYPE;
l_status NUMERIC;
BEGIN
l_table_name := UPPER(p_table_name);
-- a primary key for the given table must exist
SELECT constraint_name
INTO l_pkey_name
FROM user_constraints
WHERE table_name=l_table_name
AND constraint_type='P';
DBMS_OUTPUT.put_line(
'show_fkeys: foreign key constraints on table [' || l_table_name || ']');
DBMS_OUTPUT.put_line(
' whose primary key is [' || l_pkey_name || ']');
OPEN id_fkeys(l_table_name);
LOOP -- display foreign keys
FETCH id_fkeys INTO rec_id_fkeys;
EXIT WHEN id_fkeys%NOTFOUND;
DBMS_OUTPUT.put_line(RPAD('Table: [' || rec_id_fkeys.table_name || ']',40) ||
RPAD('FK Name: [' || rec_id_fkeys.fkey || ']',42) ||
'Status: [' || rec_id_fkeys.status || ']');
END LOOP; -- display foreign keys
IF (id_fkeys%ROWCOUNT = 0) THEN -- no fkeys found
DBMS_OUTPUT.put_line(
'show_fkeys: No foreign keys found against table ' || l_table_name);

END IF; -- no rows found
CLOSE id_fkeys;
EXCEPTION
WHEN NO_DATA_FOUND THEN -- primary key lookup failed
DBMS_OUTPUT.put_line(
'show_fkeys: no primary key exists for table ' || l_table_name);
WHEN OTHERS THEN
l_status := SQLCODE;
DBMS_OUTPUT.put_line('show_fkeys: ' || SQLERRM(l_status));
IF (id_fkeys%ISOPEN) THEN
CLOSE id_fkeys;
END IF;
END show_fkeys;
/


SQL>set serveroutput on

SQL> exec show_fkeys('DEPT');
show_fkeys: foreign key constraints on table [DEPT]
whose primary key is [PK_DEPT]
Table: [EMP] FK Name: [FK_DEPTNO]
Status: [ENABLED]

PL/SQL procedure successfully completed.
Posted by at No comments:

Oracle Box ip address on SQLPLUS


Find a ip address of Oracle Box

select sys_context('userenv','ip_address') from dual;
Posted by at No comments:
Subscribe to: Posts (Atom)

Oracle DBA

My photo
Essex, United Kingdom

anuj blog Archive