About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Tuesday, February 26, 2013

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file

Few days ago when I was trying to start my database , I faced this error. PFB details:-

SQL> startup
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             209718148 bytes
Database Buffers          394264576 bytes
Redo Buffers                7135232 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\AMIT01.DBF'

Cause :-

I checked the following location( C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT) and found out that the file AMIT01.dbf was not present.

Solution :-

There are two solutions to the above problem.
1. If data in this file is not that much important that remove this file information from database and open your database.

2. If data in this file is important and you have backup available for this file and if your database is in archivelog mode than you can recover the data in this file upto the last commit.But if database is in NOARCHIVELOG mode then restoration of data possible only upto the last backup.

Solution 1 :- Make the file Offline and then open your database after that drop that tablespace.


SQL> set lin 400
SQL> col name for a55
SQL> select a.file#,a.name as FILE_NAME,b.name as TABLESPACE_NAME ,status from v$datafile a ,v$tablespace b where a.ts#=b.ts#;

     FILE# FILE_NAME                                                                                             TABLESPACE_NAME                        STATUS
---------- -----------------------------------------------------------------------------------             -----------------------------------------         ------------------
         1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\SYSTEM01.DBF         SYSTEM                                            SYSTEM
         2 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\UNDOTBS01.DBF     UNDOTBS1                                        ONLINE
         3 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\SYSAUX01.DBF        SYSAUX                                             ONLINE
         4 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\USERS01.DBF         USERS                                               ONLINE
         5 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\AMIT01.DBF            AMIT                                                   ONLINE

SQL> drop tablespace AMIT including contents and datafiles;
drop tablespace AMIT including contents and datafiles
*
ERROR at line 1:
ORA-01109: database not open

SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\AMIT01.DBF' offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> select a.file#,a.name as FILE_NAME,b.name as TABLESPACE_NAME ,status from v$datafile a ,v$tablespace b where a.ts#=b.ts#;

     FILE# FILE_NAME                                                                                             TABLESPACE_NAME                        STATUS
---------- -----------------------------------------------------------------------------------             -----------------------------------------         ------------------
         1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\SYSTEM01.DBF         SYSTEM                                            SYSTEM
         2 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\UNDOTBS01.DBF     UNDOTBS1                                        ONLINE
         3 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\SYSAUX01.DBF        SYSAUX                                             ONLINE
         4 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\USERS01.DBF         USERS                                               ONLINE
         5 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\AMIT01.DBF            AMIT                                                   OFFLINE

SQL> drop tablespace AMIT including contents and datafiles;

Tablespace dropped.

SQL>  select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

Solution 2 :- Restore and recover the lost file through RMAN

C:\Documents and Settings\amit.rath>rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Feb 26 12:43:18 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: AMIT (DBID=2171747492, not open)

RMAN> list backup of datafile 5;

using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
19      Full    152.00K    DISK        00:00:23     26-FEB-13
        BP Key: 19   Status: AVAILABLE  Compressed: YES  Tag: TAG20130226T123606
        Piece Name: C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\AMIT\BACKUPSET\2013_02_26\O1_MF_NNNDF_TAG20130226T123606_8LRQPZKC_.BKP
  List of Datafiles in backup set 19
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  5       Full 1028343    26-FEB-13 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\AMIT01.DBF

RMAN> restore datafile 5 preview;

Starting restore at 26-FEB-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
19      Full    152.00K    DISK        00:00:23     26-FEB-13
        BP Key: 19   Status: AVAILABLE  Compressed: YES  Tag: TAG20130226T123606
        Piece Name: C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\AMIT\BACKUPSET\2013_02_26\O1_MF_NNNDF_TAG20130226T123606_8LRQPZKC_.BKP
  List of Datafiles in backup set 19
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  5       Full 1028343    26-FEB-13 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\AMIT01.DBF
using channel ORA_DISK_1

List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
26      2.50K      DISK        00:00:01     26-FEB-13
        BP Key: 26   Status: AVAILABLE  Compressed: YES  Tag: TAG20130226T123711
        Piece Name: C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\AMIT\BACKUPSET\2013_02_26\O1_MF_ANNNN_TAG20130226T123711_8LRQS0JY_.BKP

  List of Archived Logs in backup set 26
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    20      1028338    26-FEB-13 1028364    26-FEB-13
Media recovery start SCN is 1028343
Recovery must be done beyond SCN 1028343 to clear data files fuzziness
Finished restore at 26-FEB-13

RMAN> restore datafile 5;

Starting restore at 26-FEB-13
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\AMIT01.DBF
channel ORA_DISK_1: reading from backup piece C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\AMIT\BACKUPSET\2013_02_26\O1_MF_NNNDF_TAG20130226T123606_8LRQPZKC_.BKP
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\AMIT\BACKUPSET\2013_02_26\O1_MF_NNNDF_TAG20130226T123606_8LRQPZKC_.BKP tag=TAG20130226T123606
channel ORA_DISK_1: restore complete, elapsed time: 00:00:27
Finished restore at 26-FEB-13

RMAN> recover datafile 5 ;

Starting recover at 26-FEB-13
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:01:02

Finished recover at 26-FEB-13

RMAN> exit

Recovery Manager complete.

C:\Documents and Settings\amit.rath>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 26 12:46:12 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> alter database open;

Database altered.

SQL> set lin 400
SQL> col name for a55

SQL> select a.file#,a.name as FILE_NAME,b.name as TABLESPACE_NAME ,status from v$datafile a ,v$tablespace b where a.ts#=b.ts#;

     FILE# FILE_NAME                                                                                             TABLESPACE_NAME                        STATUS
---------- -----------------------------------------------------------------------------------             -----------------------------------------         ------------------
         1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\SYSTEM01.DBF         SYSTEM                                            SYSTEM
         2 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\UNDOTBS01.DBF     UNDOTBS1                                        ONLINE
         3 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\SYSAUX01.DBF        SYSAUX                                             ONLINE
         4 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\USERS01.DBF         USERS                                               ONLINE
         5 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\AMIT01.DBF            AMIT                                                   ONLINE

NOTE :- If database is in NOARCHIVELOG mode and you want to restore and recover your lost file, then after restoration of file from backup if recovery is possible through the redo logs currently available then you can open your database but if while in the process of recovery it asks for archive logs then you cannot open your database, you have to restore your full database from the backup to open your database. For Recovery upto last commit keep your database in ARCHIVELOG mode.

I hope this article helped you.

Regards,
Amit Rath

Monday, February 25, 2013

ORA-09817: Write to audit file failed

Few Days ago I faced this error when I am trying to connect to my Oracle Database :-

bash-3.2$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 25 14:58:31 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: /as sysdba
ERROR:
ORA-09817: Write to audit file failed.
IBM AIX RISC System/6000 Error: 28: No space left on device
Additional information: 12
ORA-01075: you are currently logged on

After this I checked space available in the FS. I got this :-


bash-3.2$ df -g
Filesystem    GB blocks      Free %Used    Iused %Iused Mounted on
/dev/hd4          10.38      3.88   63%    12155     2% /
/dev/hd2          12.25      6.76   45%    97364     6% /usr
/dev/hd9var        5.50      5.03    9%    11606     1% /var
/dev/hd3           5.12      2.52   51%      438     1% /tmp
/dev/hd1           8.12      0.00  100%    76570    29% /home
/dev/hd11admin      1.12      1.12    1%        5     1% /admin
/proc                 -         -    -         -     -  /proc
/dev/hd10opt       5.50      1.69   70%    32636     8% /opt
/dev/livedump      0.25      0.25    1%        4     1% /var/adm/ras/livedump
/dev/fs1v0disk1    200.00    183.22    9%    10805     1% /disk1
/dev/fs1v0disk2    200.00    183.39    9%     3655     1% /disk2
/dev/fs1v0disk3    100.00     39.36   61%     5251     1% /disk3
/dev/fs1v0archive    200.00    199.90    1%       12     1% /archive_log
/dev/fs1v0redo     15.00     15.00    1%        4     1% /log_redo_group_1
/dev/fs1v0oracle     15.00      0.00  100%    42003    41% /oracle
/dev/fs1v0pacs    100.00     96.45    4%    17900     1% /amit
/dev/fs1v0other    100.00     98.98    2%        5     1% /other
/dev/fs1v0backup    100.00     99.86    1%        5     1% /backup

I found out that /oracle mount point is 100% full where oracle writes its audit logs.

SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------      -----------    ------------------------------
audit_file_dest                    string        /oracle/oracle/admin/amit/adump
audit_sys_operations        boolean    FALSE
audit_syslog_level             string
audit_trail                           string        DB

After further findings I found out that listener.log file is around 4 gb in size and Alert log folder is 3.5 GB in size. PFB steps to reduce size of both:-

1. How to empty/remove Listener.log file :-

bash-3.2$ cd /oracle/oracle/diag/tnslsnr/amit3/listener/trace
bash-3.2$ ls -ltr

total 379432
-rw-r-----    1 oracle   oinstall  4193857778 Feb 25 15:48 listener.log


bash-3.2$ lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production on 25-FEB-2013 15:50:25

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.


LSNRCTL> set log_status OFF
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "log_status" set to OFF
The command completed successfully
LSNRCTL>exit

bash-3.2$ pwd
/oracle/oracle/diag/tnslsnr/amit3/listener/trace
bash-3.2$ mv listener.log listener_bak.log
bash-3.2$ touch listener.log
bash-3.2$ls -ltr

total 379432
-rw-r-----    1 oracle   oinstall  4193857778 Feb 25 15:48 listener_bak.log

-rw-r-----    1 oracle   oinstall                    0 Feb 25 15:48 listener.log

bash-3.2$ cp listener_bak.log /disk1
bash-3.2$ rm listener_bak.log

bash-3.2$ lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production on 25-FEB-2013 15:50:25

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.


LSNRCTL> set log_status ON

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "log_status" set to ON
The command completed successfully
LSNRCTL> exit
bash-3.2$

2. Remove old alert log files from Alert log folder :-

bash-3.2$ cd /oracle/oracle/diag/rdbms/amit/amit/trace
bash-3.2$ pwd
/oracle/oracle/diag/rdbms/pacs/pacs/trace
bash-3.2$ ls -ltr|wc -l
    1018
bash-3.2$ ls -ltr |tail
-rw-r-----    1 oracle   oinstall       1207 Feb 25 12:26 pacs_ora_45547758.trc
-rw-r-----    1 oracle   oinstall         63 Feb 25 12:29 pacs_ora_23920872.trm
-rw-r-----    1 oracle   oinstall       1231 Feb 25 12:29 pacs_ora_23920872.trc
-rw-r-----    1 oracle   oinstall         63 Feb 25 12:33 pacs_ora_45547548.trm
-rw-r-----    1 oracle   oinstall       1207 Feb 25 12:33 pacs_ora_45547548.trc
-rw-r-----    1 oracle   oinstall         63 Feb 25 12:40 pacs_ora_40435750.trm
-rw-r-----    1 oracle   oinstall       1232 Feb 25 12:40 pacs_ora_40435750.trc
-rw-r-----    1 oracle   oinstall        104 Feb 25 15:01 pacs_ckpt_7602350.trm
-rw-r-----    1 oracle   oinstall       1762 Feb 25 15:01 pacs_ckpt_7602350.trc
-rw-r-----    1 oracle   oinstall    1381033 Feb 25 16:00 alert_amit.log

Except alert_amit.log remove all alert log files to a dfferent mount point where you have space available.

After alert log files have been removed again check FS space status :-

bash-3.2$ df -g
Filesystem    GB blocks      Free %Used    Iused %Iused Mounted on
/dev/hd4          10.38      3.90   63%    12154     2% /
/dev/hd2          12.25      6.76   45%    97364     6% /usr
/dev/hd9var        5.50      5.03    9%    11606     1% /var
/dev/hd3           5.12      2.52   51%      439     1% /tmp
/dev/hd1           8.12      0.00  100%    76570    29% /home
/dev/hd11admin      1.12      1.12    1%        5     1% /admin
/proc                 -         -    -         -     -  /proc
/dev/hd10opt       5.50      1.69   70%    32636     8% /opt
/dev/livedump      0.25      0.25    1%        4     1% /var/adm/ras/livedump
/dev/fs1v0disk1    200.00    183.22    9%    10805     1% /disk1
/dev/fs1v0disk2    200.00    183.39    9%     3655     1% /disk2
/dev/fs1v0disk3    100.00     39.36   61%     5251     1% /disk3
/dev/fs1v0archive    200.00    199.90    1%       12     1% /archive_log
/dev/fs1v0redo     15.00     15.00    1%        4     1% /log_redo_group_1
/dev/fs1v0oracle     15.00      7.87   48%    42012     3% /oracle
/dev/fs1v0pacs    100.00     96.45    4%    17900     1% /amit
/dev/fs1v0other    100.00     98.98    2%        5     1% /other
/dev/fs1v0backup    100.00     99.86    1%        5     1% /backup
bash-3.2$
bash-3.2$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 25 16:02:00 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

I hope this article helped you.

Regards,
Amit Rath

How to generate script to kill multiple Oracle sessions

Killing Oracle session :-

TO kill a oracle session you have to be very sure which session you want o kill otherwise you may kill any other session which is useful to you.

To Find your session and machine from which you are connected

SQL> select sid,serial#,status,machine,osuser,to_char(LOGON_TIME, 'DD-MON-YYYY hh24:mi:ss') as LOGON_TIME from v$session where username='USERNAME' order by logon_time;

Alter system kill session 'sid,serial#' immediate;

Disconnecting Oracle sessions :-

Disconnecting a session is similar to kill a session . Unlike Kill session asks session to kill itself, disconnect session kill the dedicated server process equivalent to killing from OS level.
Syntax wise disconnect has a additional clause called POST_TRANSACTION, it waits for ongoing transactions to complete before disconnecting the session while IMMEDIATE clause disconnects the session and ongoing transactions are rolled back immeiately.

Alter system disconnect session 'sid,serial#' POST_TRANSACTION;
Alter system disconnect session 'sid,serial#' IMMEDIATE;

When in our database we have multiple inactive sessions and we want to kill all of them, then we can generate a script to kill all of them.

Small Script to kill multiple oracle sessions where status is INVALID:-

SQL> select 'alter system kill session ''' ||sid|| ',' || serial#|| ''' immediate;' from v$session where status='INACTIVE'

Small Script to kill multiple oracle sessions of a particular user :-

SQL> select 'alter system kill session ''' ||sid|| ',' || serial#|| ''' immediate;' from v$session where username='USERNAME' ;

Finding how much a session is executed

SQL>col OPNAME for a20
SQL>col USERNAME for a18
SQL> col START_TIME for a25
SQL> select sid,serial#,opname,sofar,totalwork,username,to_char(start_time,'dd-mon-yyyy hh24:mi:ss') as "START_TIME",time_remaining from v$session_longops where username='SYSTEM' and time_remaining!=0;


I hope this article helped you.

Regards,
Amit Rath

dbms_metadata.get_ddl package, How to get ddl's of object's in the database

DDL 's of Objects in a Schema :-

select dbms_metadata.get_ddl('TABLE','TABLE_NAME') from dual;
select dbms_metadata.get_ddl('INDEX','INDEX_NAME') from dual;
select dbms_metadata.get_ddl('PROCEDURE','PROCEDURE_NAME') from dual;


SQL> set lin 1000
SQL> set pagesize 9999
SQL> set long 9999
SQL> select dbms_metadata.get_ddl('TABLE','AMIT') from dual;


  CREATE TABLE "OWNER"."AMIT"
   (    "A" VARCHAR2(26),
        "B" VARCHAR2(10),
        "C" VARCHAR2(10),
        "D" VARCHAR2(20),
        "E" VARCHAR2(10),
        "F" DATE,
        "G" NUMBER,
        "H" VARCHAR2(20),
        "I" VARCHAR2(20),
        "J" VARCHAR2(5),
        "K" NUMBER,
        "L" NUMBER,
        "M" NUMBER,
        "N" NUMBER,
        "O" DATE,
        "P" DATE,
        "Q" VARCHAR2(255),
        "R" VARCHAR2(255),
        "S" NUMBER,
        "T" NUMBER,
        "U" VARCHAR2(10),
        "V" DATE
   ) 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)
  TABLESPACE "USERS"
;

DDL 's of Objects in a Any Schema :-

select dbms_metadata.get_ddl('TABLE','TABLE_NAME','USERNAME') from dual;
select dbms_metadata.get_ddl('INDEX','INDEX_NAME','USERNAME') from dual;
select dbms_metadata.get_ddl('PROCEDURE','PROCEDURE_NAME','USERNAME') from dual;

SQL> set lin 1000
SQL> set pagesize 9999
SQL> set long 9999
SQL> select dbms_metadata.get_ddl('TABLE','AMIT','OWNER') from dual;

  CREATE TABLE "OWNER"."AMIT"
   (    "A" VARCHAR2(26),
        "B" VARCHAR2(10),
        "C" VARCHAR2(10),
        "D" VARCHAR2(20),
        "E" VARCHAR2(10),
        "F" DATE,
        "G" NUMBER,
        "H" VARCHAR2(20),
        "I" VARCHAR2(20),
        "J" VARCHAR2(5),
        "K" NUMBER,
        "L" NUMBER,
        "M" NUMBER,
        "N" NUMBER,
        "O" DATE,
        "P" DATE,
        "Q" VARCHAR2(255),
        "R" VARCHAR2(255),
        "S" NUMBER,
        "T" NUMBER,
        "U" VARCHAR2(10),
        "V" DATE
   ) 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)
  TABLESPACE "USERS"
;

Script to Generate DDL 's of Various Objects of database :-

Script for DDL 's of All Indexes of database:-

SQL> select 'select dbms_metadata.get_ddl(''INDEX'',''' || index_name|| ''' ) from dual;'  from user_indexes;


SQL> select 'select dbms_metadata.get_ddl(''INDEX'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='INDEX';

SQL> select 'select dbms_metadata.get_ddl(''INDEX'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='INDEX' and owner not in ('SYS','SYSTEM');

Script for DDL's of all Tables of database:-

SQL> select 'select dbms_metadata.get_ddl(''TABLE'',''' || table_name|| ''' ) from dual;'  from user_tables;

SQL> select 'select dbms_metadata.get_ddl(''TABLE'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='TABLE';

SQL> select 'select dbms_metadata.get_ddl(''TABLE'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='TABLE' and owner not in ('SYS','SYSTEM');

Script for DDL's of All Procedures of database:-

SQL> select 'select dbms_metadata.get_ddl(''PROCEDURE'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='PROCEDURE';

SQL> select 'select dbms_metadata.get_ddl(''PROCEDURE'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='PROCEDURE' and owner not in ('SYS','SYSTEM');

SQL> select 'select dbms_metadata.get_ddl(''PROCEDURE'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='PROCEDURE' and owner='OWNER_NAME';

Script for DDL's of All Functions of database :-

SQL> select 'select dbms_metadata.get_ddl(''FUNCTION'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='FUNCTION';

SQL> select 'select dbms_metadata.get_ddl(''FUNCTION'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='FUNCTION' and owner not in ('SYS','SYSTEM');

SQL> select 'select dbms_metadata.get_ddl(''FUNCTION'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='FUNCTION' and owner='OWNER_NAME';

Script for DDL's of All Triggers of database:-

SQL> select 'select dbms_metadata.get_ddl(''TRIGGER'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='TRIGGER';

SQL> select 'select dbms_metadata.get_ddl(''TRIGGER'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='TRIGGER' and owner not in ('SYS','SYSTEM');

SQL> select 'select dbms_metadata.get_ddl(''TRIGGER'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='TRIGGER' and owner='OWNER_NAME';

Script for DDL's of All Views of database:-

SQL> select 'select dbms_metadata.get_ddl(''VIEW'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='VIEW';

SQL> select 'select dbms_metadata.get_ddl(''VIEW'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='VIEW' and owner not in ('SYS','SYSTEM');

SQL> select 'select dbms_metadata.get_ddl(''VIEW'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='VIEW' and owner='OWNER_NAME';

I hope this article helped you.

Regards,
Amit Rath


Saturday, February 9, 2013

How to determine size of Schema or Index or Table in Oracle Database.

Size of a User or Schema

select owner,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' group by owner;

Size of INDEX

select segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from user_segments where segment_name='INDEX_NAME' group by segment_name;
OR
select owner,segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_name='INDEX_NAME' group by owner,segment_name;

List of Size of all INDEXES of a USER

select segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from user_segments where segment_type='INDEX' group by segment_name order by "SIZE in GB" desc;
 OR
select owner,segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_type='INDEX' group by owner,segment_name order by "SIZE in GB" desc;

Sum of sizes of all indexes

select owner,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_type='INDEX' group by owner;

Size of table

select segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from user_segments where segment_name='TABLE_NAME' group by segment_name;
OR
select owner,segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_name='TABLE_NAME' group by owner,segment_name;

 List of Size of all tables of a USER

select segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from user_segments where segment_type='TABLE' group by segment_name order by "SIZE in GB" desc
 OR
select owner,segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_type='TABLE' group by owner,segment_name order by "SIZE in GB" desc;

Sum of sizes of all tables

select owner,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_type='TABLE' group by owner;

I hope this article helped you.

Regards,
Amit Rath