Ulfat`s DataBase Blog

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...

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 s...

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 data...