Feature of DDL With the WAIT Option OR Ora-00054 Resource busy error solution in Oracle Database 11g

This is usually common that some time when we try to modify some table and 

we got error of "ORA-00054: resource busy" because when we try to modify table 

that table is in use.
For overcoming this error in oracle 11g database introduce a parameter 

DDL_LOCK_TIMEOUT in which you set the time for which your modify command wait 

for showing the error of Ora-00054 . And you can set DDL_LOCK_TIMEOUT parameter 

at session as well as on instance level what ever you want.
Example:-
1. In your First session
CREATE TABLE new (
  no  NUMBER
);

INSERT INTO new VALUES (10);
2. In your second session
open another session and set  DDL_LOCK_TIMEOUT to value say 60 sec
ALTER SESSION SET ddl_lock_timeout=60;
and then issue alter command on created table


ALTER TABLE new ADD (
  name  VARCHAR2(50)
);
Note: Here  DDL_LOCK_TIMEOUT value is 60 if in between these 60 seconds you 

issue commit command in your first session then your alter command run 

successfully in second session and if you didn't issue commit command in 

first session then your second session wait for 60 seconds and on 61st seconds 

it will give the error of  

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired.
 

First Case: if issue commit in first session 

 1. In your First session 

 CREATE TABLE new (
  no  NUMBER
);

INSERT INTO new VALUES (10);
commit;
2. In your second session 

 ALTER TABLE new ADD (
  name  VARCHAR2(50)
);

Table altered.

SQL>

 Second Case: if commit is not issue in first session 

 1. In your First session
CREATE TABLE new (
  no  NUMBER
);

INSERT INTO new VALUES (10);
2. In your second session
ALTER TABLE new ADD (
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

How To Create Restore Point In Oracle 11g With Illustration

HOW TO CREATE RESTORE POINT IN ORACLE 11G WITH EXAMPLE

SQL> CREATE RESTORE POINT before_update GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL> !rman

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 20 16:20:27 2012

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

RMAN> connect target

connected to target database: DB (DBID=2351874955)

RMAN> list restore point all

2> ;

using target database control file instead of recovery catalog

SCN RSP Time Type Time Name

---------------- --------- ---------- --------- ----

3280671 GUARANTEED 12-OCT-12 BEFORE_UPDATE

RMAN> exit

Recovery Manager complete.

SQL> create table before_restore_point(ename varchar2(40));

Table created.

SQL> !rman

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 20 16:21:35 2012

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

RMAN> shutdown immediate

using target database control file instead of recovery catalog

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of shutdown command at 19/20/2012 16:21:40

RMAN-06171: not connected to target database

RMAN> connect target

connected to target database: DB (DBID=2351874955)

using target database control file instead of recovery catalog

RMAN> FLASHBACK DATABASE TO RESTORE POINT 'BEFORE_UPDATE';

Starting flashback at 20-OCT-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=241 device type=DISK

starting media recovery

media recovery failed

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of flashback command at 10/20/2012 16:22:21

ORA-38757: Database must be mounted and not open to FLASHBACK.

RMAN> exit

Recovery Manager complete.

SQL> !rman

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 20 16:22:35 2012

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

RMAN> connect target

connected to target database: DB (DBID=2351874955)

RMAN> shutdown immediate

using target database control file instead of recovery catalog

RMAN> exit

Recovery Manager complete.

SQL> shutdown abort

ORA-03135: connection lost contact

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 32bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing

[oracle@oracleoel test]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 20 16:25:21 2012

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 32bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select status from v$instance;

STATUS

------------

OPEN

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !rman

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 20 16:25:46 2012

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

RMAN> connect target

connected to target database (not started)

RMAN> startup mount

Oracle instance started

database mounted

Total System Global Area 1837244416 bytes

Fixed Size 2214336 bytes

Variable Size 1207961152 bytes

Database Buffers 603979776 bytes

Redo Buffers 23089152 bytes

RMAN> FLASHBACK DATABASE TO RESTORE POINT 'BEFORE_UPDATE';

Starting flashback at 20-OCT-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=201 device type=DISK

starting media recovery

media recovery complete, elapsed time: 00:00:03

Finished flashback at 20-OCT-12

RMAN> alter database open resetlogs;

database opened

RMAN> exit

Recovery Manager complete.

SQL> select instance_name,status from v$instance;

select instance_name,status from v$instance

*

ERROR at line 1:

ORA-01034: ORACLE not available

Process ID: 22495

Session ID: 153 Serial number: 3

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 32bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@oracleoel test]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 20 16:27:10 2012

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 32bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

---------------- ------------

DB OPEN

SQL> select * from before_restore;

select * from before_restore

*

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> select * from before_restore_point;

select * from before_restore_point

*

ERROR at line 1:

ORA-00942: table or view does not exist

How To Make Tables Read Only in oracle 11g Database with illustration

How To Make Tables Read Only in oracle 11g Database

there is new feature of making tables read only in oracle 11g database .prior to oracle 11g there ,you can make tables read only to other users by only give SELECT grant to the user.

For making tables read only in oracle 11g

alter table table_name read only;

example:-

let us we have to make a table named TEST (no number(10),name varchar2(30)) as read only

1. SQL> select * from test;
   SQL> insert into test values (2,'kumar');
   SQL> commit;
2. SQL> alter table test read only;
   SQL>insert into test values (3,'john');
   ORA-12081: update operation not allowed on table "TEST"
   SQL> update test set no=1 where no=2
   ORA-12081: update operation not allowed on table "TEST"

Now TEST table is read only no one can alter ,update or delete operation on TEST.

For making tables read only to read write oracle 11g

3. SQL> alter table test read write;

SQL> insert into test values (3,'john');
SQL> commit;

How To Enable Archivlelog Mode In Oracle Datbase 11g

How To Enable Archivlelog Mode In Oracle Datbase 11g

for enabling archive log mode in oracle 11g

steps are:-

1. SQL> ARCHIVE LOG LIST;
   Database log mode NoArchive Mode
   Automatic archival Disabled
   Archive destination /Database/archivelog/
   Oldest online log sequence 1129
   Next log sequence to archive 1131
   Current log sequence 1131
2. Database is in noarchive log mode to change it to archivelog mode the first requirement is that database must be mounted not opened for mounting the database shutdown the database and then mount it for this issue the following command
   SQL> SHUTDOWN IMMEDIATE
   SQL> STARTUP MOUNT

after putting the database to mount stage issue the following command

ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
now issue the command to check whether database is in archivelog mode or not
2. SQL> ARCHIVE LOG LIST;
   Database log mode NoArchive Mode
   Automatic archival Disabled
   Archive destination /Database/archivelog/
   Oldest online log sequence 1132
   Next log sequence to archive 1133
   Current log sequence 1134