Using dbms_metadata.get_ddl to get DDL but not all source provided

Today I tried to get structure of aud$ table using dbms_metadata.get_ddl. But result shows only a few lines not entire table.

SQL> select dbms_metadata.get_ddl('TABLE', 'AUD$') from dual;  
 DBMS_METADATA.GET_DDL('TABLE','AUD$')  
 -------------------------------------------------------------  
  CREATE TABLE "SYS"."AUD$"  
   (  "SESSIONID" NUMBER NOT NULL ENABLE,  
     "ENTRYI  
 SQL>  

But to get all source I used to_char function and it provide me all source.

 SQL> select   
 to_char(dbms_metadata.get_ddl('TABLE', 'AUD$'))   
 from dual;  
  CREATE TABLE "SYS"."AUD$"  
   (  "SESSIONID" NUMBER NOT NULL ENABLE,  
     "ENTRYID" NUMBER NOT NULL ENABLE,  
     "STATEMENT" NUMBER NOT NULL ENABLE,  
     "TIMESTAMP#" DATE,  
     "USERID" VARCHAR2(30),  
     "USERHOST" VARCHAR2(128),  
     "TERMINAL" VARCHAR2(255),  
     "ACTION#" NUMBER NOT NULL ENABLE,  
     "RETURNCODE" NUMBER NOT NULL ENABLE,  
     "OBJ$CREATOR" VARCHAR2(30),  
     "OBJ$NAME" VARCHAR2(128),  
     "AUTH$PRIVILEGES" VARCHAR2(16),  
     "AUTH$GRANTEE" VARCHAR2(30),  
     "NEW$OWNER" VARCHAR2(30),  
     "NEW$NAME" VARCHAR2(128),  
     "SES$ACTIONS" VARCHAR2(19),  
     "SES$TID" NUMBER,  
     "LOGOFF$LREAD" NUMBER,  
     "LOGOFF$PREAD" NUMBER,  
     "LOGOFF$LWRITE" NUMBER,  
     "LOGOFF$DEAD" NUMBER,  
     "LOGOFF$TIME" DATE,  
     "COMMENT$TEXT" VARCHAR2(4000),  
     "CLIENTID" VARCHAR2(64),  
     "SPARE1" VARCHAR2(255),  
     "SPARE2" NUMBER,  
     "OBJ$LABEL" RAW(255),  
     "SES$LABEL" RAW(255),  
     "PRIV$USED" NUMBER,  
     "SESSIONCPU" NUMBER,  
     "NTIMESTAMP#" TIMESTAMP (6),  
     "PROXY$SID" NUMBER,  
     "USER$GUID" VARCHAR2(32),  
     "INSTANCE#" NUMBER,  
     "PROCESS#" VARCHAR2(16),  
     "XID" RAW(8),  
     "AUDITID" VARCHAR2(64),  
     "SCN" NUMBER,  
     "DBID" NUMBER,  
     "SQLBIND" CLOB,  
     "SQLTEXT" CLOB,  
     "OBJ$EDITION" VARCHAR2(30)  
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255   
 NOCOMPRESS LOGGING   
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1   
 MAXEXTENTS 2147483645  
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1   
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT   
 CELL_FLASH_CACHE DEFAULT) TABLESPACE "SYSTEM"  
 LOB ("SQLBIND") STORE AS   
 BASICFILE (TABLESPACE "SYSTEM"   
 ENABLE STORAGE IN ROW CHUNK 8192 RETENTION  
 NOCACHE LOGGING  
 STORAGE(INITIAL 65536 NEXT 1048576   
 MINEXTENTS 1 MAXEXTENTS 2147483645  
  0 FREELISTS 1 FREELIST GROUPS 1   
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT   
 CELL_FLASH_CACHE DEFAULT))  
 LOB ("SQLTEXT") STORE AS BASICFILE (TABLESPACE "SYSTEM"   
 ENABLE STORAGE IN ROW CHUNK 8192 RETENTION  
 NOCACHE LOGGING STORAGE(INITIAL 65536   
 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1   
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT   
 CELL_FLASH_CACHE DEFAULT)  
 )  
 SQL>  

ORACLE DBA - Get segment size, compare table and index size

Creating simple function we can get table`s size.

CREATE OR REPLACE FUNCTION ULFET.CHECK_TABLE_SIZE(TAB_NAME VARCHAR) RETURN varchar
IS
   tab_size number;
   str varchar2(50);
BEGIN  
     SELECT  SUM (bytes) / (1024 * 1024)  INTO tab_size
     FROM sys.dba_extents WHERE segment_type = 'TABLE' AND segment_name = TAB_NAME GROUP BY segment_name;
str := tab_name||' size is '||tab_size||' MB';
RETURN str;
END;
/

P.S: If compilation will error grant select on sys.dba_extents to desired user and repeat creation.

SQL> conn /as sysdba

SQL> grant select on sys.dba_extents to ulfet;

Grant succeeded.

SQL>



--call
select ULFET.CHECK_TABLE_SIZE('TEST_TAB') from dual;

or

set serveroutput on
declare
l_answvarchar2(100);
begin
l_answ:=ULFET.CHECK_TABLE_SIZE('TEST_TAB');
dbms_output.put_line(l_answ);
end;
select ULFET.CHECK_TABLE_SIZE('TEST_TAB') from dual;

P.S: Please note that table_name should be entered with upper case.

Above function will return for tables which gathered statistic.
To get statistics you can use several methods : ANALYZE or DBMS_STATS.GATHER_TABLE_STATS

Example:


exec ANALYZE TABLE employees COMPUTE STATISTICS; --it computes actual statistics
exec ANALYZE TABLE employees ESTIMATE STATISTICS; --estimates the stats
exec DBMS_STATS.GATHER_TABLE_STATS ('HR','EMPLOYEES');


DBMS_STATS package available on Oracle 10g and later versions.

Sometimes we getting request something like that, is it possible index size bigger than table`s size?
Yes, possible, let`s simulate.

SQL> create table test_tbl (id number, col1 varchar2(2000));

Table created.

SQL> create index idx_test_tbl on test_tbl(col1);

Index created.

SQL> begin
        for i in 1..10000 loop
                insert into test_tbl values (i, lpad(i,2000, 'z'));
        end loop;
        commit;
end;
/  2    3    4    5    6    7

PL/SQL procedure successfully completed.


SQL> delete from test_tbl;

10000 rows deleted.

SQL> commit;

Check table and index`s size


SQL> col segment_name format a20
SQL> SELECT segment_name, SUM (bytes) / (1024 * 1024) size_mb
FROM sys.dba_extents
WHERE segment_type in('TABLE','INDEX')
AND segment_name in('TEST_TBL', 'IDX_TEST_TBL') and owner='ULFET'
group by segment_name;

SEGMENT_NAME            SIZE_MB
--------------------              ----------
TEST_TBL                          27
IDX_TEST_TBL                 46

SQL>

SQL> with tabs as (select segment_name tablename,bytes tablesize from user_segments where segment_type='TABLE'),

inds as (select i.index_name indexname,i.table_name tablename,s.bytes indexsize from user_indexes i join user_segments s on (i.index_name=s.segment_name))

select * from tabs natural join inds where indexsize > tablesize;  2    3  

 TABLENAME    TABLESIZE  INDEXNAME     INDEXSIZE  
 ---------    ---------  ------------  ---------  
 TEST_TBL     28311552   IDX_TEST_TBL  48234496  
 SQL> 

List of indexes which size bigger than tables


SQL> col tablename format a23
SQL> WITH tabs AS (SELECT segment_name tablename, bytes tablesize FROM dba_segments

  WHERE segment_type = 'TABLE' AND owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'PUBLIC')),
        inds AS (SELECT i.index_name indexname, i.table_name tablename, s.bytes indexsize FROM dba_indexes i  JOIN dba_segments s ON (i.index_name = s.segment_name)
AND s.owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'PUBLIC')
)
SELECT * FROM tabs NATURAL JOIN inds WHERE indexsize > tablesize;

For performance it is good to check table`s which size more than 2 GB and not partitioned.

Get list of tables which size more than 2 GB and not partitioned.

SELECT A.OWNER,
       A.SEGMENT_NAME,
       BYTES,
       ROUND (BYTES / 1024 / 1024 / 1024, 2) SIZE_GB,
       B.OBJECT_TYPE,
       PARTITIONED
  FROM DBA_SEGMENTS A, DBA_OBJECTS B, DBA_TABLES C
 WHERE     A.SEGMENT_NAME = B.OBJECT_NAME
       AND A.OWNER NOT IN ('SYS', 'SYSTEM')
       AND BYTES > 2147483648
       AND OBJECT_TYPE = 'TABLE'
       AND C.TABLE_NAME = A.SEGMENT_NAME
       --AND PARTITIONED = 'YES'
       ORDER by size_gb,  segment_name

Drop database using RMAN

As you know using DBCA (Database Configuration Assistant) we can easily delete database. Alternatively we can drop database using RMAN utility. Before to do that, database should be opened as restricted mode.

[oracle@localhost bin]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jan 5 22:10:40 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area     146472960 bytes

Fixed Size                     1335080 bytes
Variable Size                 92274904 bytes
Database Buffers              50331648 bytes
Redo Buffers                   2531328 bytes

RMAN> alter database mount;

using target database control file instead of recovery catalog
database mounted

RMAN> SQL 'ALTER SYSTEM ENABLE RESTRICTED SESSION';

sql statement: ALTER SYSTEM ENABLE RESTRICTED SESSION

RMAN> drop database noprompt;

database name is "MYDB" and DBID is 2718904503
database dropped

RMAN>

Also we can database with backup option

RMAN> drop database including backups noprompt;

Source: http://docs.oracle.com/cd/B28359_01/backup.111/b28273/rcmsynta019.htm