Connecting with sys user with Or without pwd

As we know "SYS" & "SYSDBA" is superuser privilege in oracle database.

There is two method to connect SYSDBA user.

1. WITH PASSWORD

2. WITHOUT PASSWORD

For example:

SQL> CONN / AS SYSDBA ---without password



For without password need follow thing to be done.

1. Oracle Database User must have SYSDBA privileges.

2. Operating System User must add in DBA group.

On windows: ORA_DBA group

On linux: DBA group

3. In sqlnet.ora file must content "NTS" in below line

SQLNET.AUTHENTICATION_SERVICES=(NTS)

For example:
SQL> CONN SYS/PWD AS SYSDBA --- with password or prevent to connect without password.

1. Remove OS user from DBA group

2. Edit sqlnet.ora file and change "NONE" to "NTS" in below line

SQLNET.AUTHENTICATION_SERVICES=(NONE)

Default Value is NTS when sqlnet.ora file created.

Database & Patch set Upgrade

Database & patch upgrade is one of the most important work for Database Administrator.

Before going further first understand what are “upgrade” & “migration” & “patch set” & “CPU patch” word.

Upgrade:
Usually use for oracle database upgrade.
Eg: upgrade from oracle 9i to oracle 10g.

Migration:
Usually use for migrate non oracle database to oracle database.
Eg: From SQL server database to oracle database

Patch set:
There are two types of patch upgrade
1. Patch set update
From one oracle version to another oracle version
Eg: oracle 9i to oracle 10g

NOTE: Patch set based on oracle base release
Eg: oracle 10gr1 (10.1.0.2.0) and for 10gr2 (10.2.0.1.0)

2. CPU patch (Critical Patch update)
Patches is apply for fix database bug suppose after apply latest patch set for current release if there is any bug occur then oracle release cpu patches in regular interval to fix those bug.
Eg: oracle 10gr1 base rel: 10.1.0.2.0, Latest Patch set: 10.1.0.5.0, Latest CPU patch: Jan-08 CPU patch for 10.1.0.5.0

NOTE: CPU patch based on latest patch set.
For more info: http://www.oracle.com/technology/deploy/security/alerts.htm
How to upgrade database to oracle 10gr2?

Determine the Upgrade Path to the New Oracle Database 10g Release
http://download.oracle.com/docs/cd/B19306_01/server.102/b14238/preup.htm#i1007814

Before upgrading process we need to define upgrade method
There are four methods to upgrade our database
1. DBUA (Database upgrade assistant)
2. Manually upgrade
3. Export/Import
4. Data copying
http://download.oracle.com/docs/cd/B19306_01/server.102/b14238/preup.htm#i694345
Patch set upgrade example:
1. From oracle 9ir2 (9.2.0.2 to 9.2.0.8) on Windows http://babudba.blogspot.com/2007/11/patch-installation.html
2. From oracle 9ir2 (9.2.0.1 to 9.2.0.7) on Solaris
http://sabdarsyed.blogspot.com/2007/02/upgrade-oracle-database-9201-to-9207-on.html
3. From oracle 10gr1 (10.1.0.2 to 10.1.0.5) on windows
http://dbataj.blogspot.com/2007/06/upgrading-database-from-101020-to.html

4. From oracle 10gr2 (10.2.0.2 to 10.2.0.3) on windows
http://babudba.blogspot.com/2007/11/upgrade-oracle-10202-to-10203.html

5. From oracle 10gr2 (10.2.0.1 to 10.2.0.3) on Linux
http://sabdarsyed.blogspot.com/2007/03/upgrading-oracle-database-server-10g.html

Critical Patch update example:
On Oracle 10gr1 (10.1.0.5.0) patch set
http://dbataj.blogspot.com/2007/06/critical-patch-update.html

During import table created in wrong tablespace.

During import table is created in other than default tablespace is happen what is reason.
Suppose we have two databases.
1. Orcl
2. Hgc

Now i created one table in ORCL database in SYSTEM tablespace.

SQL> create table test_orcl ( no number) tablespace SYSTEM;
Table created.

Now in HGC database i import above created table in user default tablespace.

SQL> conn system/oracle@hgc
Connected.
SQL> alter user scott default tablespace USERS;
User altered.

NOTE:
1. I assign default tablespace "USERS" to scott user which import table.
2. Scott user having "connect" & "resource" role privileges

Question: NOW TELL me during IMPORT in which tablespace "USERS" or "SYSTEM" tbs table "test_orcl" created ?

NOW guess your answer..we will check later ...

SQL> conn scott/tiger@Orcl
Connected.
SQL> host exp scott/tiger@Orcl file=c:\test_orcl.dmp tables=test_orcl
Export: Release 10.1.0.5.0 - Production on Wed Jan 2 11:59:18 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
. . exporting table TEST_ORCL 0 rows exported
Export terminated successfully without warnings.

NOW import table in HGC database.

SQL> conn scott/tiger@HGC
Connected.
SQL> host imp scott/tiger@HGC file=c:\test_orcl.dmp fromuser=scott touser=scott
Import: Release 10.1.0.5.0 - Production on Wed Jan 2 12:02:26 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
. . importing table "TEST_ORCL" 0 rows imported
Import terminated successfully without warnings.

now we check in which tablespace table is created ?

SQL> select table_name,tablespace_name
2 from user_tables
3 where table_name='TEST_ORCL';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEST_ORCL SYSTEM

ofh table is created in SYSTEM tablespace instead of user default tablespace means "USERS' why it so ?

Again import table but now make one changes ...
1. Revoke "resource" role and grant explicit quota on tablespace to user.

SQL> conn system/oracle@hgc
Connected.
SQL> revoke resource from scott;
Revoke succeeded.
SQL> alter user scott quota 100m on users;
User altered.

NOW import again...

SQL> conn scott/tiger@hgc
Connected.
SQL> drop table test_orcl purge;
Table dropped.
SQL> host imp scott/tiger@HGC file=c:\test_orcl.dmp fromuser=scott touser=scott
Import: Release 10.1.0.5.0 - Production on Wed Jan 2 12:06:51 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
. . importing table "TEST_ORCL" 0 rows imported
Import terminated successfully without warnings.

SQL> select table_name,tablespace_name
2 from user_tables
3 where table_name='TEST_ORCL';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEST_ORCL USERS

But what is actual reason...?
1. Resource role having "unlimited tablespace" privileges means database user have quota on all tablespace in database if user having "unlimited tablespace" privileges.

2. table is created in "system" tablespace becuase tables is actually created in SYSTEM tablespace on "ORCL" database during first time creation.

Export file created by EXPORT:V10.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
"CREATE TABLE "TEST_ORCL" ("NO" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MA"
"XTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL "
"DEFAULT) TABLESPACE "SYSTEM" LOGGING NOCOMPRESS"
. . skipping table "TEST_ORCL"

redolog file status

Redo log file is very crucial for database recovery...
and most of time we confuse about "status" of v$log file for redo log files.

1. UNUSED
Whenever you create any redo log file ...that time v$log view showing status "UNUSED"

2. CURRENT
After Creation when redo log "lgwr" process use that file ....that time v$log view showing status "CURRENT"

3. ACTIVE
If database running in archivelog mode then after log switch "arch" process use log file and create archivelog file that time v$Log file view showing status "ACTIVE"

4. CLEARING
Whenever any log file is corrupted or missing ( which is not "active" or not "current") then we use "alter database clear logfile" statement to recreate corrupted log file ... that time v$log view showing status "CLEARING" after clearing it is showing "UNUSED"

5. INACTIVE
During archivelog file creation v$log view showing status "ACTIVE" and when archivelog file is created then it is showing "INACTIVE" for particular one redo log file at a time.


6. CLEARING_CURRENT
If any error occured during "alter database clear logfile" then v$log file view showing "CLEARING_CURRENT".

Recovery Catalog

Recovery Catalog

What is recovery catalog?
Recovery catalog is external database which use to store RMAN repository

What is RMAN repository?
RMAN repository is collection of target database which store information about RMAN backup, recovery and maintenance.

When recovery catalog is not in used then where RMAN repository stored?
When recovery catalog is not in used then RMAN repository store in CONTROLFILE.

Where we create recovery catalog?
We have two option for it.
1. We can create recovery catalog on target database through create separate tablespace for recovery catalog.
Or
2. We can create separate database for recovery catalog.

NOTE: if we have more than one database then separate database for recovery catalog is recommended instead of separate tablespace.

How recovery catalog store information about RMAN repository?
After creation of recovery catalog we have to manually register each target database to recovery catalog.

Should we need to take backup of recovery catalog?
Yes, We must take backup of recovery catalog because recovery catalog store most important information about RMAN backup, recovery and RMAN configuration if we lost recovery catalog then we can’t get those information so it is always recommended to take recovery catalog backup.

How take recovery catalog backup?
1. If recovery catalog created as separate tablespace on target database
Then just export recovery catalog tablespace and store in backup drive.

2. If recovery catalog created as separate database.
Then we can use any backup method which we use for target database for instance we can use user managed backup or we can also use RMAN method.

Is there any view for query information about recovery catalog?
Yes, actually for recovery catalog we need to create separate user. And after user and recovery catalog creation there is some view created in recovery catalog users with RC_ prefix. For instance: RC_DATABASE

How to configure recovery catalog for target database?
There are three steps for recovery catalog configuration.
1. Configure the database that will content the recovery catalog,
2. Create owner for recovery catalog
3. Create recovery catalog

SQL> conn sys@dev as sysdba
Enter password:
Connected.
SQL> create tablespace CATDB
2 datafile 'c:\oracle\catdb01.dbf' size 10m;

Tablespace created.

NOTE: Here I am create separate tablespace for recovery catalog on target database.

SQL>conn sys@dev as sysdba
Connected.

SQL> create user CATDB
2 identified by CATDB
3 default tablespace CATDB;

NOTE: Owner for recovery catalog

SQL> grant connect,resource to catdb;

Grant succeeded.

SQL> grant recovery_catalog_owner to CATDB;

Grant succeeded.

NOTE: Grant the RECOVERY_CATALOG_OWNER role to the schema owner. This role provides the user with all privileges required to maintain and query the recovery catalog.


C:\>rman

Recovery Manager: Release 10.1.0.5.0 - Production

Copyright (c) 1995, 2004, Oracle. All rights reserved.

RMAN> connect catalog catdb/catdb@dev

connected to recovery catalog database
recovery catalog is not installed

RMAN> create catalog tablespace CATDB;

recovery catalog created

NOTE: now recovery catalog is created.

SQL> conn catdb/catdb@dev
Connected.
SQL> select table_name from user_tables where rownum =1;

TABLE_NAME
------------------------------
RCVER

NOTE: after recovery catalog creation we can check all recovery catalog views is created in recovery catalog owner.


What we do after create recovery catalog?
Now just register target database to recovery catalog and store RMAN repository in recovery catalog.

C:\>rman target sys@dev catalog catdb/catdb@dev

Recovery Manager: Release 10.1.0.5.0 - Production

Copyright (c) 1995, 2004, Oracle. All rights reserved.

target database Password:
connected to target database: DEV (DBID=3718891706)
connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

NOTE: “target sys@dev” is use for target database which we want to register to recovery catalog through recovery catalog owner “catdb/catdb@dev”.

How to Clear Oracle Certification ... ?

Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips

How to Clear Oracle Certification?

There are couple of questions come in mind when anyone think about oracle certification.

1. Is Oracle Certification is beneficial for Job Seekers?
You know what is certification…?
It is just some couple of questions which based 80% on concept and theory.
And any Job opening wants Practical DBA means Practical Experience with Theory.

So as per my advice any oracle newbie first get some practical experience then go for certification.


2. How to prepare for Oracle Certification?
Oracle Certification is examination, so some rules apply which we follow in our college examination.
1. First define which Oracle Paper you want to write.
Go through below link and choose your paper
http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=42&p_org_id=1080544&lang=US
2. Second check Exam Topic, Objective.
Suppose we choose “izo-007” introduction to SQL paper. So now check what is exam topic is come in “introduction to SQL paper”. Because SQL is very big topic and exam question based on some most important chapters.
Check Exam topic for “introduction to SQL” paper
http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=41&p_org_id=1080544&lang=US&p_exam_id=1Z0_001#3

3. What is Oracle Exam format?
Whenever anybody going to write first time oracle paper then always worry about exam format. Now don’t worry Oracle provide demo for exam format.
1. Oracle Exam is objective types
2. For demo version please go through below link (paper:iz0-007)
http://www.selftestsoftware.com/demo/default.aspx

3. What material we used for preparation?
1. Best material is “student guide” simple, easy and short.
Student Guide books comes with Oracle Course Kits.
2. Oracle Documentation
http://tahiti.oracle.com
3. Practice Questions
On internet there are so many site which provide us practice question for oracle certification.

http://www.google.ae/search?hl=en&q=practice+question+oracle&meta=

4. Looking for Dumps?
You know what is Dumps?
Dumps are real question which comes in Oracle Certification.
Some centre sold exam question like dumps for money and it is illegal please check it below link
http://www.oracle.com/global/us/education/certification/canagreemt.html
And suppose you will get dumps from anywhere …so please don’t depend on it.
It is not sure all question comes from dumps.
Because some prometric centre keeps real question in their centre and refresh periodically. But some prometric centre like oracle prometric centre use main server through internet.

Related Links

1. http://dbataj.blogspot.com/2007/10/why-i-choose-oracle-as-my-career.html

2. http://dbataj.blogspot.com/2007/09/faqs-about-oracle-certification.html

3. http://dbataj.blogspot.com/2007/08/oracle-plsql-developer-certified.html

4. http://dbataj.blogspot.com/2007/08/oracle-11i-apps-dba.html

5. http://dbataj.blogspot.com/2007/03/oracle-certified-professional.html

How to Enable ARCHIVELOG mode

My Signature Article

Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips

Benefits of ARCHIVELOG mode.

1. We can able to take database backup ONLINE.
2. No Need to Down Oracle DB Server
3. We can better Manage our Backup Policy through RMAN
4. Limited DBA task
5. We can able to RECOVER our database point-in-time

How can enable ARCHIVELOG MODE in 9iR1.

When we create new database then database is created NOARCHIVELOG mode by default.

shutdown database


SQL>shutdown immediate;


Edit below parameters in pfile(init.ora) file.


1. LOG_ARCHIVE_START=TRUE


Create New SPFILE from Modified pfile(init.ora)


SQL> create SPFILE from PFILE;


Startup Database in Mount Mode


SQL> startup mount;


Change Database log mode from NOARCHIVELOG to ARCHIVELOG


SQL> alter database archivelog;


Open DB for normal use


SQL> alter database open;


check archive log status


Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\Oracle\product\RDBMS
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1



Default Archivelog destination is :$ORACLE_HOME/rdbms.

How can enable ARCHIVELOG MODE in 10gr1

In 10g Archivelog mode enabling is easy becuase we need to just turn DB log mode and archive log is automatic start.


shutdown database


SQL> shutdown immediate;


startup database in mount mode


SQL> startup mount;


Change DB log mode


SQL> alter database archivelog;


Open DB for normal use


SQL> alter database open;


check archivelog status


SQL> archive log list


Default Archive log destination.


1. 10g introduce new feature called FRA (flashback recovery area) so all archivelog files created in FRA.

2. If your not using FRA then all files created in $ORACLE_HOME/database


We can check database archivelog mode through below query


SQL> select log_mode from v$database;
or
SQL> archive log list

Table Fragmentation

What is Table fragmentation


When rows are not stored contiguously, or if rows are split onto more than one page, performance decreases because these rows require additional page accesses. Table fragmentation is distinct from file fragmentation.

When lots of DML operation apply on tables then tables is fragmented.
because DML is not release free space from table below HWM.

Hint: HWM is indicator for USED BLOCKS in database. Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted.
Since Oracle knows that blocks beyond the high water mark don't have data, it only reads blocks up to the high water mark in a full table scan.

DDL statement always reset HWM.


How to find table fragmentation

SQL> select count(*) from big1;

1000000 rows selected.

SQL> delete from big1 where rownum <= 300000;

300000 rows deleted.

SQL> commit;

Commit complete.

SQL> update big1 set object_id = 0 where rownum <=350000;

342226 rows updated.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');

PL/SQL procedure successfully completed.

Table Size ( with fragmented)

SQL> select table_name,round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 72952kb

Actual data in table

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 30604.2kb

Note= 72952 – 30604 = 42348 Kb is wasted space in table

The difference between two values is 60% and Pctfree 10% (default) so table is 50% extra space which is wasted because there is no data.

How to reset HWM / remove fragemenation


For that we need to reorganize fragmented table

We have four options to reorganize fragmented tables

1. alter table … move + rebuild indexes
2. export / truncate / import
3. create table as select ( CTAS)
4. dbms_redefinition



Option: 1 “alter table … move + rebuild indexes”

SQL> alter table BIG1 move;

Table altered.

SQL> select status,index_name from user_indexes
2 where table_name = 'BIG1';

STATUS INDEX_NAME
-------- ------------------------------
UNUSABLE BIGIDX

SQL> alter index bigidx rebuild;

Index altered.

SQL> select status,index_name from user_indexes
2 where table_name = 'BIG1';

STATUS INDEX_NAME
-------- ------------------------------
VALID BIGIDX


SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');

PL/SQL procedure successfully completed.

SQL> select table_name,round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 38224kb

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 30727.37kb

Option: 2 “Create table as select”

SQL> create table big2 as select * from big1;

Table created.

SQL> drop table big1 purge;

Table dropped.

SQL> rename big2 to big1;

Table renamed.

SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');

PL/SQL procedure successfully completed.

SQL> select table_name,round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 85536kb

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 68986.97kb

SQL> select status from user_indexes
2 where table_name = 'BIG1';

no rows selected

SQL> --Note we need to create all indexes.

Option: 3 “export / truncate / import”

SQL> select table_name, round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 85536kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 42535.54kb

SQL> select status from user_indexes where table_name = 'BIG1';

STATUS
--------
VALID

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

C:\>exp scott/tiger@Orcl file=c:\big1.dmp tables=big1

Export: Release 10.1.0.5.0 - Production on Sat Jul 28 16:30:44 2007

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table BIG1 468904 rows exported
Export terminated successfully without warnings.

C:\>sqlplus scott/tiger@orcl

SQL*Plus: Release 10.1.0.5.0 - Production on Sat Jul 28 16:31:12 2007

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

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

SQL> truncate table big1;

Table truncated.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

C:\>imp scott/tiger@Orcl file=c:\big1.dmp ignore=y

Import: Release 10.1.0.5.0 - Production on Sat Jul 28 16:31:54 2007

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

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

Export file created by EXPORT:V10.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. . importing table "BIG1" 468904 rows imported
Import terminated successfully without warnings.

C:\>sqlplus scott/tiger@orcl

SQL*Plus: Release 10.1.0.5.0 - Production on Sat Jul 28 16:32:21 2007

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

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

SQL> select table_name, round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 85536kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 42535.54kb

SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 51840kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 42542.27kb

SQL> select status from user_indexes where table_name = 'BIG1';

STATUS
--------
VALID

SQL> exec dbms_redefinition.can_redef_table('SCOTT','BIG1',-
> dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

Option: 4 “dbms_redefinition”

SQL> create table TABLE1 (
2 no number,
3 name varchar2(20) default 'NONE',
4 ddate date default SYSDATE);

Table created.

SQL> alter table table1 add constraint pk_no primary key(no);

Table altered.

SQL> begin
2 for x in 1..100000 loop
3 insert into table1 ( no , name, ddate)
4 values ( x , default, default);
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> create or replace trigger tri_table1
2 after insert on table1
3 begin
4 null;
5 end;
6 /

Trigger created.

SQL> select count(*) from table1;

COUNT(*)
----------
100000

SQL> delete table1 where rownum <= 50000;

50000 rows deleted.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('SCOTT','TABLE1');

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'TABLE1';

TABLE_NAME size
------------------------------ ------------------------------------------
TABLE1 2960kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'TABLE1';

TABLE_NAME size
------------------------------ ------------------------------------------
TABLE1 822.69kb


SQL> --Minimum Privs required "DBA" role or "SELECT" on dbms_redefinition pkg
SQL> --First check table is condidate for redefinition.
SQL>
SQL> exec sys.dbms_redefinition.can_redef_table('SCOTT',-
> 'TABLE1',-
> sys.dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

SQL> --After verifying that the table can be redefined online, you manually crea
te an empty interim table (in the same schema as the table to be redefined)
SQL>
SQL> create table TABLE2 as select * from table1 WHERE 1 = 2;

Table created.

SQL> exec sys.dbms_redefinition.start_redef_table ( 'SCOTT',-
> 'TABLE1',-
> 'TABLE2');

PL/SQL procedure successfully completed.

SQL> --This procedure keeps the interim table synchronized with the original tab
le.
SQL>
SQL> exec sys.dbms_redefinition.sync_interim_table ('SCOTT',-
> 'TABLE1',-
> 'TABLE2');

PL/SQL procedure successfully completed.

SQL> --Create PRIMARY KEY on interim table(TABLE2)
SQL> alter table TABLE2
2 add constraint pk_no1 primary key (no);

Table altered.

SQL> create trigger tri_table2
2 after insert on table2
3 begin
4 null;
5 end;
6 /

Trigger created.

SQL> --Disable foreign key on original table if exists before finish this proces
s.
SQL>
SQL> exec sys.dbms_redefinition.finish_redef_table ( 'SCOTT',-
> 'TABLE1',-
> 'TABLE2');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('SCOTT','TABLE1');

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'TABLE1';

TABLE_NAME size
------------------------------ ------------------------------------------
TABLE1 1376kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'TABLE1';

TABLE_NAME size
------------------------------ ------------------------------------------
TABLE1 841.4kb

SQL> select status,constraint_name
2 from user_constraints
3 where table_name = 'TABLE1';

STATUS CONSTRAINT_NAME
-------- ------------------------------
ENABLED PK_NO1

SQL> select status ,trigger_name
2 from user_triggers
3 where table_name = 'TABLE1';

STATUS TRIGGER_NAME
-------- ------------------------------
ENABLED TRI_TABLE2

SQL> drop table TABLE2 PURGE;

Table dropped.

BFILE datatype

BFILE Datatype
The BFILE datatype stores unstructured binary data in operating-system files outside the database. A BFILE column or attribute stores a file locator that points to an external file containing the data. BFILEs can store up to 8 terabytes of data.

BFILEs are read only; you cannot modify them. They support only random (not sequential) reads, and they do not participate in transactions. The underlying operating system must maintain the file integrity, security, and durability for BFILEs. The database administrator must ensure that the file exists and that Oracle processes have operating-system read permissions on the file.

SQL> --Create logical directory


SQL> create directory DATA as 'c:\lob';

Directory created.


SQL> --Grant READ,WRITE privs to PUBLIC

SQL> grant read,write on directory data to public;

Grant succeeded.


SQL> --Create Table to store pointer for large binary objects


SQL> create table bfileobj ( id number, type varchar2(20), image bfile);

Table created.


SQL> --Insert locator/populate table


SQL> insert into bfileobj values (&n,'&a',bfilename('DATA','&A'));
Enter value for n: 1
Enter value for a: winRAR
Enter value for a: 1.rar
old 1: insert into bfileobj values (&n,'&a',bfilename('DATA','&A'))
new 1: insert into bfileobj values (1,'winRAR',bfilename('DATA','1.rar'))

1 row created.

SQL> commit;

Commit complete.


SQL> --Select Table ( First Create function)


SQL> create or replace function get_filename( p_bfile in bfile ) return
2 varchar2
3 as
4 l_dir varchar2(4000);
5 l_fname varchar2(4000);
6 begin
7 dbms_lob.FILEGETNAME( p_bfile, l_dir, l_fname );
8 return l_dir ':' l_fname;
9 end;
10 /

Function created.

SQL> column fname format a30
SQL> select id,type, get_filename( image ) fname from bfileobj;

ID TYPE FNAME
---------- -------------------- ------------------------------
1 winRAR DATA:1.rar
2 Windows Media DATA:2.wmv
3 PowerPoint DATA:3.pps
4 Adobe Acrobat DATA:4.pdf
5 jpeg image DATA:5.jpg




SQL> -- Check BFILE is exists or not on physical location



SQL> set serveroutput on
SQL> ed
Wrote file afiedt.buf

1 create or replace procedure fileexists ( nn number) is
2 file_loc bfile;
3 begin
4 dbms_output.put_line ( '----------bfile fileexists example --------');
5 select image into file_loc from bfileobj
6 where id = nn;
7 if (dbms_lob.fileexists(file_loc) != 0)
8 then
9 dbms_output.put_line('Processing given that the bfile exists');
10 else
11 dbms_output.put_line('Processing given that the bfile does not exists');
12 end if;
13 exception
14 when others then
15 dbms_output.put_line('operation failed');
16* end;
SQL> /

Procedure created.

SQL> exec fileexists(1);
----------bfile fileexists example --------
Processing given that the bfile exists

PL/SQL procedure successfully completed.

SQL> exec fileexists(5);
----------bfile fileexists example --------
Processing given that the bfile does not exists

PL/SQL procedure successfully completed.




SQL> --Display data from bfile

SQL> insert into bfileobj values (7,'WORD DOC',bfilename('DATA','7.DOC'));

1 row created.

SQL> commit;

Commit complete.


SQL> ed
Wrote file afiedt.buf

1 CREATE OR REPLACE PROCEDURE displayBFILE ( nn varchar2)IS
2 file_loc BFILE := BFILENAME('DATA', nn);
3 Buffer RAW(1024);
4 Amount BINARY_INTEGER := 200;
5 Position INTEGER := 1;
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE('------------ BFILE DISPLAY EXAMPLE ------------');

8 /* Opening the BFILE: */
9 DBMS_LOB.OPEN (file_loc, DBMS_LOB.LOB_READONLY);
10 LOOP
11 DBMS_LOB.READ (file_loc, Amount, Position, Buffer);
12 /* Display the buffer contents: */
13 DBMS_OUTPUT.PUT_LINE(substr(utl_raw.cast_to_varchar2(Buffer), 1, 250)
);
14 Position := Position + Amount;
15 END LOOP;
16 /* Closing the BFILE: */
17 DBMS_LOB.CLOSE (file_loc);
18 EXCEPTION
19 WHEN NO_DATA_FOUND THEN
20 DBMS_OUTPUT.PUT_LINE('End of data');
21* END;
SQL> /

Procedure created.

SQL> exec displaybfile('6.log');

PL/SQL procedure successfully completed.

SQL> set serveroutput on size 1000000
SQL> exec displaybfile('6.log');

------------ BFILE DISPLAY EXAMPLE ------------

Starting up ORACLE RDBMS Version: 10.1.0.5.0.
System parameters with
non-default values:
processes = 150
sga_target
= 285212672
control_files = C:\O
RACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTL,
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTL,
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTL
db_block_size
= 8192
compatib
le = 10.1.0.5.0
db_file_multiblock_read_count= 16

db_recovery_file_dest = C:\oracle\product\10.1.0\flash_recovery_area

db_recovery_file_dest_size= 2147483648
undo_manageme
nt = AUTO
undo_tablespace = UNDOTBS1

remote_login_passwordfile= EXCLUSIVE
db_domain =

dispatchers = (PROTOCOL=TCP) (SERVICE=orclXDB)
job_q
ueue_processes = 10
background_dump_dest =
C:\ORACLE\PRODUCT\10.1.0\ADMIN\ORCL\BDUMP
user_dump_dest =
C:\ORACLE\PRODUCT\10.1.0\ADMIN\ORCL\UDUMP
core_dump_dest = C
:\ORACLE\PRODUCT\10.1.0\ADMIN\ORCL\CDUMP
db_name = orcl

open_cursors = 300
pga_aggregate_target = 94371840

End of data


PL/SQL procedure successfully completed.


SQL> --Getting the length of a bfile


SQL> ed
Wrote file afiedt.buf

1 CREATE OR REPLACE PROCEDURE getLengthBFILE ( nn varchar2) IS
2 file_loc BFILE := BFILENAME('DATA',nn);
3 Length INTEGER;
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE('------------ BFILE LENGTH EXAMPLE ------------');
6 /* Open the BFILE: */
7 DBMS_LOB.OPEN(file_loc, DBMS_LOB.LOB_READONLY);
8 /* Get the length of the LOB: */
9 Length := DBMS_LOB.GETLENGTH(file_loc);
10 IF Length IS NULL THEN
11 DBMS_OUTPUT.PUT_LINE('BFILE is null.');
12 ELSE
13 DBMS_OUTPUT.PUT_LINE('The length is ' length);
14 END IF;
15 /* Close the BFILE: */
16 DBMS_LOB.CLOSE(file_loc);
17* END;
SQL> /

Procedure created.

SQL> exec getlengthbfile('6.log');
------------ BFILE LENGTH EXAMPLE ------------
The length is 1150

PL/SQL procedure successfully completed.

SQL> exec getlengthbfile('1.rar');
------------ BFILE LENGTH EXAMPLE ------------
The length is 6953658

PL/SQL procedure successfully completed.


Note: you can display only text file.

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

BLOB datatype

In above we see how to store pdf,txt file through BFILE datatype now in this section we use BLOB datatype to store those files.

1. Create directory for store pdf,txt files which we want to store in database

create or replace directory MY_FILES as 'e:\';

2. Create table in database which store target files

create table demo (id number primary key, theBlob BLOB);

3. Write Small plsql for upload pdf,txt etc files from OS to Database

declare

l_blob blob;

l_bfile bfile;

begin

insert into demo values(1,empty_blob() )

returning theBlob into l_blob;

l_bfile := bfilename ('MY_FILES','test1.pdf');

dbms_lob.fileopen(l_bfile);

dbms_lob.loadfromfile(l_blob,l_bfile,

dbms_lob.getlength(l_bfile));

dbms_lob.fileclose(l_bfile);

end;

4. Check file is upload successfully?

SQL> column theblob format a30

SQL> select * from demo;
ID THEBLOB

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

1 255044462D312E330D332030206F62 6A0D3C3C2F54797065202F50616765 0D2F506172656E742031203020520D 2F5265736F75726365732032203020 520D2F436F6E74656E747320342030

declare
l_blob blob;
l_bfile bfile;
begin
insert into demo values(2,empty_blob() )
returning theBlob into l_blob;
l_bfile := bfilename ('MY_FILES','11gDBA.pdf');
dbms_lob.fileopen(l_bfile);
dbms_lob.loadfromfile(l_blob,l_bfile,
dbms_lob.getlength(l_bfile));
dbms_lob.fileclose(l_bfile);
end;

Oracle Database 10g Release 2 (10.2) New Features

Oracle Database 10g Release 2 (10.2) New Features

Transparent data encryption

With the CREATE TABLE or ALTER TABLE commands, you can specify table columns for which data is encrypted before being stored in the datafile. If users attempt to circumvent the database access control mechanisms by looking inside datafiles directly with operating system tools, encryption prevents such users from viewing sensitive data.

DML error logging

A new error logging clause for all DML statements enables certain types of errors (for example, constraint violations or data conversion errors) to be logged to an error logging table, allowing the statement to continue instead of terminating and rolling back.

Enhancements to Automatic Shared Memory Management

The Streams Pool is now also automatically tuned when automatic shared memory management is enabled. A new view, V$SGA_TARGET_ADVICE, provides information to help with tuning SGA_TARGET.


Improved automatic tuning of undo retention results in fewer "ORA-01555: snapshot too old" messages.

Automatic tuning of undo retention now always tunes for the maximum possible retention for the undo tablespace based on tablespace size and current system activity. This improves the success rate of Oracle Flashback operations and long-running queries.

The Segment Advisor now reports tables with excessive row chaining.

The Segment Advisor now runs automatically during the maintenance window.

Upon installation, a Scheduler job is automatically created to run the Segment Advisor during the maintenance window. You can also continue to run the Segment Advisor manually. The automatically run Segment Advisor (the "Automatic Segment Advisor") examines statistics on the entire database and selects segments and tablespaces to analyze. You can view recommendations with Enterprise Manager, with the DBA_ADVISOR_* views, or with the new DBMS_SPACE.ASA_RECOMMENDATIONS procedure.

Enhancements to the online segment shrink capability

Online segment shrink now supports:

LOB segments

IOT overflow segments (both standalone and as a dependent object of an IOT index segment)

Enhancements to online table redefinition

Online redefinition of tables now supports:

Redefining a single partition of a partitioned table

Clustered tables, Advanced Queuing queue tables, and materialized view logs

Object data types (objects, VARRAYs, and nested tables, including nested table dependent objects)

Check and NOT NULL constraints

Preservation of table statistics

Parallel execution for Long-to-LOB migration

In addition, dependent PL/SQL package recompilation is no longer required when the redefined table has the same number, types, and order of columns as the original table.


Support for XMLTypes in the transportable tablespace facility

Enhancements to space management

The DBMS_SPACE_ADMIN package provides new tools to troubleshoot space management problems


Control files no longer need to be recreated when changing certain configuration parameters

Control files can now dynamically grow in size when you increase the values of the following parameters: MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, and MAXINSTANCES.

Tablespace low-space alert thresholds by free space remaining

Low-space alert thresholds for locally managed tablespaces can now be by percent full or by free space remaining (in KB). Free-space-remaining thresholds are more useful for very large tablespaces.

Automatic Storage Management (ASM) files are now accessible through an XML DB virtual folder

ASM files can now be accessed through the XML DB repository, either programmatically or with protocols like FTP and HTTP/WebDAV.

Automatic Storage Management now has a command-line utility (ASMCMD)

With ASMCMD you can easily view and manipulate files and directories within disk groups. ASMCMD can list the contents of disk groups, perform searches, create and remove directories and aliases, display space utilization, and more.

Automatic Storage Management (ASM) supports multiple database versions

ASM maintains forward and backward compatibility between most 10.x versions of Oracle Database and 10.x versions of ASM. That is, any combination of versions 10.1.x.y and 10.2.x.y for either the ASM instance or the database instance works correctly, with this caveat: For a 10.1.x.y database instance to connect to a 10.2.x.y ASM instance, the database must be version 10.1.0.3 or later.

The DBMS_FILE_TRANSFER package can now copy files between a local file system and an Automatic Storage Management (ASM) disk group.

The DBMS_FILE_TRANSFER package can use a local file system or an ASM disk group as the source or destination for a file transfer. You can now copy files from ASM to the file system or from the file system to ASM.

The ALTER DISKGROUP command has a new REBALANCE WAIT clause.

ALTER DISKGROUP commands that cause a rebalance of an ASM disk group—commands that add, drop, or resize disks, or the command that starts a manual rebalance operation—can now wait until the rebalance operation completes before returning. This is especially useful in scripts.

Oracle Database 10g(10.1) New Features

Oracle 10g(10.1) New Features

Bigfile tablespaces

Oracle Database lets you create single-file tablespaces, called bigfile tablespaces, which can contain up to 232 or 4G blocks. The benefits of bigfile tablespaces are the following:

They significantly enhance the storage capacity of an Oracle Database.

They reduce the number of datafiles needed for an ultra large database.

They simplify database management by providing datafile transparency.

Multiple default temporary tablespace support for SQL operations

You can create a temporary tablespace group that can be specifically assigned to users in the same way that a single temporary tablespace is assigned. A tablespace group can also be specified as the default temporary tablespace for the database.

Rename tablespace

The RENAME TO clause of the ALTER TABLESPACE statement enables you to rename tablespaces


Cross-platform transportable tablespaces

Tablespaces can be transported from one platform to another. The RMAN CONVERT command is used to do the conversion


SYSAUX tablespace

Oracle Database creates an auxiliary system tablespace called SYSAUX at database creation. This tablespace can be used by various Oracle Database features and products, rather than saving their data in separate tablespaces or in the SYSTEM tablespace.

Automatic Storage Management

Automatic Storage Management provides a logical volume manager integrated with Oracle Database, eliminating the need for you to purchase a third-party product. Oracle Database creates Oracle-managed files within user-defined disk groups that provide redundancy and striping.



Drop database

The new DROP DATABASE statement lets you delete a database and all of its files that are listed in the control file.


Oracle Flashback Transaction Query

This feature introduces the FLASHBACK_TRANSACTION_QUERY view, which lets you examine changes to the database at the transaction level. As a result, you can diagnose problems, perform analysis, and audit transactions.



Oracle Flashback Version Query

Using undo data stored in the database, you can now view multiple changes to one or more rows, along with the metadata for the changes.


Oracle Flashback Table

A new FLASHBACK TABLE statement lets you quickly recover a table to a point in time in the past without restoring a backup


Oracle Flashback Drop

Oracle Database now provides a way to restore accidentally dropped tables. When tables are dropped, they are placed into a recycle bin from which they can later be recovered.

Enhanced online redefinition

New procedures have been added to the DBMS_REDEFINITION package that automate the cloning of dependent objects such as indexes, triggers, privileges, and constraints. Some restrictions have been lifted, allowing more types of tables to be redefined.

Automatic statistics collection

You no longer need to specify the MONITORING keyword in the CREATE TABLE or ALTER TABLE statement to enable the automatic collecting of statistics for a table. Statistics are now collected automatically as controlled by the STATISTICS_LEVEL initialization parameter. Automatic statistics collection is the default.

Scheduler

Oracle Database provides advanced scheduling capabilities through the database Scheduler.

Simplified recovery through resetlogs

The format for archive log file naming, as specified by the ARCHIVE_LOG_FORMAT initialization parameter, now includes the resetlogs ID, which allows for easier recovery of a database from a previous backup.

Automated shared server configuration and simplified shared server configuration parameters.

You no longer need to preconfigure initialization parameters for shared server. Parameters can be configured dynamically, and most parameters are now limiting parameters to control resources. The recommended method for enabling shared server now is by setting SHARED_SERVERS initialization parameter, rather than the DISPATCHERS initialization parameter.

Consolidation of session-specific trace output

For shared server sessions, the trcsess command-line utility consolidates in one place the trace pertaining to a user session.

Block remote access to restricted instances

Remote access to a restricted instance through an Oracle Net listener is blocked.

Dynamic SGA enhancements

The JAVA_POOL_SIZE initialization parameter is now dynamic. There is a new STREAMS_POOL_SIZE initialization parameter, which is also dynamic. A new view, V$SGAINFO, provides a consolidated and concise summary of SGA information.

Irreversible database compatibility

In previous releases you were allowed to lower the compatibility setting for your database. Now, when you advance the compatibility of the database with the COMPATIBLE initialization parameter, you can no longer start the database using a lower compatibility setting, except by doing a point-in-time recovery to a time before the compatibility was advanced.

Flash recovery area

You can create a flash recovery area in your database where Oracle Database can store and manage files related to backup and recovery


Copying Files Using the Database Server

You do not have to use the operating system to copy database files. You can use the DBMS_FILE_TRANSFER package to copy files.

Deprecation of MAXTRANS physical attribute parameter

The MAXTRANS physical attribute parameter for database objects has been deprecated. Oracle Database now automatically allows up to 255 concurrent update transactions for any data block, depending on the available space in the block.

Deprecation of use of rollback segments (manual undo management mode)

Manual undo management mode has been deprecated and is no longer documented in this book. Use an undo tablespace and automatic undo management instead.

Deprecation of the UNDO_SUPPRESS_ERRORS initialization parameter

When operating in automatic undo management mode, the database now ignores any manual undo management mode SQL statements instead of returning error messages.

Deprecation of the PARALLEL_AUTOMATIC_TUNING initialization parameter

Oracle Database provides defaults for the parallel execution initialization parameters that are adequate and tuned for most situations. The PARALLEL_AUTOMATIC_TUNING initialization parameter is now redundant and has been deprecated.

Oracle Database 9i(9.2) New Features

Oracle 9i(9.2) New Features

Automatic Server Parameter File Backup and Configuration

Recovery Manager can now automatically back up and restore the server parameter File (SPFILE) configuration file, simplifying the backup and restoration processes of the system.

Performance Tuning Intelligent Advisories

The current release of Oracle9i provides a number of built-in intelligent advisories for performance tuning. These advisories are set to go "out-of-the-box", and allow the administrator to simulate a variety of hypothetical scenarios. These advisories use minimal resources and are available though the standard SQL interface.

These advisories show Shared Pool usage to improve parse time and to minimize CPU usage. They also show SQL execution memory to shorten SQL execution time and to minimize unnecessary CPU and I/O usage.
An advisory for the Mean-Time-To-Recover setting makes it possible for the administrator to set time requirements to recover from a system failure without jeopardizing run-time performance.
An advisory for the PGA Aggregate Target makes it possible for the server to control dynamically the amount of PGA memory allotted to SQL work areas according to the PGA_AGGREGATE_TARGET limit that is set by the DBA


Actual Operation-Level Query Statistics

Performance tuning in the current release of Oracle9i is simplified with intelligent tuning features that provide actual operation-level query execution statistics rather than estimates. This new information helps database administrators identify what tables, partitions, and indexes are most heavily accessed.

Dynamic Sampling of Optimizer Statistics

At compile time, this feature, if enabled, will dynamically gather statistics if the existing statistics are incomplete or known to be inaccurate. With default settings, this feature addresses missing optimizer statistics, providing for a possible source of poor optimization. With optional settings, this feature addresses additional sources of inaccurate statistics. Dynamic sampling of optimizer statistics may improve performance by improving the quality of the statistics used by the query optimizer.

Locally Managed SYSTEM Tablespace

The SYSTEM tablespace can be locally managed to simplify the database administrator's tasks.

Specifying FORCE LOGGING Mode

The FORCE LOGGING clause of the CREATE DATABASE, CREATE CONTROLFILE, and CREATE TABLESPACE statement enables you to force redo log records to be written even when NOLOGGING has been specified in a DDL statement.

LogMiner Enhancements

LONG and LOB Datatype Support for Redo Logs
LONG and LOB datatypes are supported for redo logs generated on a release 9.2 or later Oracle database.

Supplemental Logging Turned Off by Default

Supplemental logging is turned off by default. This is a change from release 1 (9.0.1), in which minimal supplemental logging was turned on by default. In release 9.2, the level of supplemental logging desired must be specified.

New Formatting Options

Two new options have been added that affect the formatting of returned data. The DBMS_LOGMNR.NO_SQL_DELIMITER option suppresses the semi-colon at the end of SQL_REDO and SQL_UNDO statements. The DBMS_LOGMNR.PRINT_PRETTY_SQL option formats the reconstructed SQL statements so that they are easier to read.

New Option, DBMS_LOGMNR.CONTINUOUS_MINE, in LogMiner

A new option, DBMS_LOGMNR.CONTINUOUS_MINE, directs LogMiner to automatically add and mine redo log files that are archived after the LogMiner session has started.

DBMS_LOGMNR.NO_DICT_RESET_ONSELECT Option Unnecessary

Use of the DBMS_LOGMNR.NO_DICT_RESET_ONSELECT option is no longer necessary. When data definition language (DDL) tracking is enabled, LogMiner stores old metadata definitions so that a second select operation has all the needed metadata versions.


New Procedure, DBMS_LOGMNR_D.SET_TABLESPACE, in LogMiner

A new procedure, DBMS_LOGMNR_D.SET_TABLESPACE,recreates all LogMiner tables in a tablespace other than the default tablespace, SYSTEM.


Minimizing the Production Impact of Upgrading

The Oracle9i Database release 2 introduces new features to lessen the time spent upgrading applications and database systems. These new features include the ability to rename CONSTRAINTS and COLUMNS as well as a significant reduction in the recompilation of PL/SQL packages and procedures. The database system also provides for fast loading of wrapped source code, automatically determines to do nothing upon loading an unchanged package, view or synonym, and removes double invalidation and parallel compilation in dependency code.

Domain Indexes Enhancement

It is now possible to create and rebuild domain indexes and local domain index partitions in parallel.

Segment-level Statistics Collection

With the capacity to collect of segment-level statistics in Oracle9i release 2, users can specify that relevant statistics be collected on the segment level to pinpoint hot spots and performance bottlenecks in the system. You can query these statistics by using the V$SEGMENT_STATISTICS to do a SELECT * FROM V$SEGMENT_STATISTICS. You can also query V$SEGSTAT_NAME for statistics properties and V$SETSTAT for additional efficient access to statistics.

Oracle9i Database New Features

Oracle9i Database (9.0.1) New Features

Online redefinition of tables

The new DBMS_REDEFINITION PL/SQL package provides a mechanism to redefine tables online. When a table is redefined online, it is accessible to DML during much of the redefinition process. This provides a significant increase in availability compared to traditional methods of redefining tables that require tables to be taken offline.

ONLINE option for ANALYZE VALIDATE STRUCTURE statement

The ANALYZE statement can now perform validation while DML is ongoing within the object being analyzed.

Controlling Archive Lag


Oracle now provides a time-based means of switching the current online redo log group. In a primary/standby configuration, where all noncurrent logs of the primary site are archived and shipped to the standby database, this effectively limits the number of redo records, as measured in time, that will not be applied in the standby database.

Suspending a database

Oracle9i includes a database suspend/resume feature. The ALTER SYSTEM SUSPEND statement suspends a database by halting all input and output (I/O) to datafiles and control files. When the database is suspended all preexisting I/O operations are allowed to complete and any new database accesses are placed in a queued state. The ALTER SYSTEM RESUME statement resumes normal database operation.

Quiescing a database

Oracle9i allows you to place the database into a quiesced state, where only DBA transactions, queries, or PL/SQL statements are allowed. This quiesced state allows you to perform administrative actions that cannot safely be done otherwise. The ALTER SYSTEM QUIESCE RESTRICTED statement places a database into a quiesced state.

More archiving destinations

The maximum number of destinations to which you can archive the online redo log, has been increased from 5 to 10.

Automatic segment- space management

Locally managed tablespaces allow extents to be managed automatically by Oracle. Oracle9i allows free and used space within segments stored in locally managed tablespaces to also be managed automatically. Using the SEGMENT SPACE MANAGEMENT clause of CREATE TABLESPACE you specify AUTO or MANUAL to specify the type of segment space management Oracle will use.

Update of global indexes when partition maintenance is performed

By default, many table maintenance operations on partitioned tables invalidate (mark UNUSABLE) global indexes. You must then rebuild the entire global index or, if partitioned, all of its partitions. Oracle9i allows you to override this default behavior. When you specify the UPDATE GLOBAL INDEX clause in your ALTER TABLE statement for the maintenance operation, the global index is updated in conjunction with the base table operation.

Multiple block sizes

Oracle now supports multiple block sizes. It has a standard block size, as set by the DB_BLOCK_SIZE initialization parameter, and additionally up to 4 nonstandard block sizes. Nonstandard block sizes are specified when creating tablespaces. The standard block size is used for the SYSTEM tablespace and most other tablespaces. Multiple block size support allows for the transporting of tablespaces with unlike block sizes between databases.

Dynamic buffer cache

The size of the buffer cache subcomponent of the System Global Area is now dynamic. The DB_BLOCK_BUFFERS initialization parameter has been replaced by a new dynamic parameter, DB_CACHE_SIZE, where the user specifies the size of the buffer subcache for the standard database block size. The buffer cache now consists of subcaches when multiple block sizes are specified for the database. Up to four DB_nK_CACHE_SIZE initialization parameters allow you to specify the sizes of buffer subcaches for the additional block sizes.

Dynamic SGA

The initialization parameters affecting the size of SGA have been made dynamic. It is possible to alter the size of SGA dynamically through an ALTER SYSTEM SET statement.

Automatic undo management

Historically, Oracle has used rollback segments to store undo. Undo is defined as information that can be used to roll back, or undo, changes to the database when necessary. Oracle now enables you to create an undo tablespace to store undo. Using an undo tablespace eliminates the complexities of managing rollback segment space, and enables you to exert control over how long undo is retained before being overwritten.

Oracle managed files

The Oracle managed files feature of Oracle9i eliminates the need for you to directly manage the files comprising an Oracle database. Through the DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n initialization parameters, you specify the file system directory to be used for a particular type of file comprising a tablespace, online redo log file, or control file. Oracle then ensures that a unique file, an Oracle-managed file, is created and deleted when no longer needed.

Automatic deletion of datafiles

Oracle9i provides an option to automatically remove a tablespaces's operating system files (datafiles) when the tablespace is dropped using the DROP TABLESPACE statement. A similar option for the ALTER DATABASE TEMPFILE statement, causes deletion the operating system files associated with a temporary file.

Metadata API

A new PL/SQL package, DBMS_METADATA.GET_DDL, allows you to obtain metadata (in the form of DDL used to create the object) about a schema object.

External tables

Oracle9i allows you read-only access to data in external tables. External tables are defined as tables that do not reside in the database, and can be in any format for which an access driver is provided. The CREATE TABLE ... ORGANIZATION EXTERNAL statement specifies metadata describing the external table. Oracle currently provides the ORACLE_LOADER access driver which provides data mapping capabilities that are a subset of the SQL*Loader control file syntax.

Server parameter file

Oracle has traditionally stored initialization parameters in a text initialization parameter file, often on a client machine. Starting with Oracle9i, you can elect to maintain initialization parameters in a server parameter file, which is a binary parameter file stored on the database server. Initialization parameters stored in a server parameter file are persistent, in that any changes made to the parameters while an instance is running persist across instance shutdown and startup.

Default temporary tablespace

The new DEFAULT TEMPORARY TABLESPACE clause of the CREATE DATABASE statement allows you to create a default temporary tablespace at database creation time. This tablespace is used as the default temporary tablespace for users who are not otherwise assigned a temporary tablespace.

Database Configuration Assistant changes

The Database Configuration Assistant has been redesigned. It now provides templates, which are saved definitions of databases, from which you can generate your database. Oracle provides templates, or you can create your own templates by modifying existing ones, defining new ones, or by capturing the definition of an existing database.

When creating a database with the Database Configuration Assistant, you can either initially include, or later add as an option, Oracle's new Sample Schemas. These schemas are the basis for many of the examples used in Oracle documentation.

Monitoring index usage

A MONITORING USAGE clause has been added for the ALTER INDEX statement. It allows you to monitor an index to determine if it is actively being used.

Fine-grained auditing

In Oracle's traditional auditing methods, a fixed set of facts is recorded in the audit trail. Audit options can only be set to monitor access of objects or privileges. A new PL/SQL package, DBMS_FGA, allows applications to implement fine-grained auditing of data access based on content.

Oracle 8i Database New Features

Oracle 8i New Features

Drop Column

A new drop column capability allows the DBA to easily remove unused columns in the database. Previously, it was necessary to use the export/import utilities.

Locally Managed Tablespaces

Prior to Oracle8i, management of free and used extents within a tablespace relied heavily on data dictionary tables. Now, Oracle introduces a new mechanism for managing space within a tablespace: locally managed tablespaces. All extent information is tracked in the tablespace itself, using bitmaps.


Online Index Creation, Rebuild, and Defragmentation

Creating or rebuilding an index on a table improves query performance when completed, but can be a very time-consuming operation. For large tables, it can require several hours of downtime because it forces a lock on the table and prevents concurrent DML. This is called offline index build.

Oracle8i supports the online creation or rebuilding of an index that works for partitioned or nonpartitioned B*-tree indexes, including index-organized tables. Since the table is not locked, DML operations and queries can execute on the base table while the index is being built.

Alternatively, if you do not want to rebuild the index because of space or other considerations, then the new COALESCE keyword may be specified to defragment the index. In either case, performance is improved and space recovered.


Nonpartitioned Table Reorganization

A new MOVE clause used in an ALTER TABLE statement provides a means to easily reorganize a nonpartitioned table by allowing the user to move data into a new segment while preserving all views, privileges, etc. defined on the table. The operation is performed offline.

Online Read-Only Tablespaces

Oracle8i improves the performance of the operation that places a tablespace in read-only mode. A tablespace in Oracle8i can be placed in read-only mode when there are no outstanding transactions in that tablespace alone, unlike previous versions of Oracle where the operation completed only when there were no outstanding transactions in the entire database.


Temporary Tables

A temporary table is a table with session-specific or transaction-specific data. It is empty when the session or transaction begins, and discarded at the end of the session or transaction. Its definition is visible to all sessions but the data is visible to, and can be queried by, only the session that inserts the data into the table

Note: Above is not complete list of new features for more details please refer documentation.

Retrieve only the Nth row from a table

SQL> select * from taj order by sal desc;

NAME SAL
-------------------- ----------
CEO 10000
MANAGER 8000
IT MANAGER 7000
PROGRAMMER 5000
SALESMAN 3000
SALESMAN 3000
OFFICEBOY 1000
OFFICEBOY 1000

8 rows selected.

Note: Select 6th hightest salary

SQL> select * from (
2 select name,sal,rownum rn from taj where rownum < 7)
3 where rn = 6;

NAME SAL RN
-------------------- ---------- ----------
OFFICEBOY 1000 6

Delete Duplicate Rows

SQL> select * from taj;

CO CO CO
-- -- --
a a a
a a b
a b c
b b b
b b c
c c c
c c d
c d e
b c d
b c d

10 rows selected.

SQL> ed
Wrote file afiedt.buf

1 delete from taj t1
2 where t1.rowid >
3 ( select min(t2.rowid) from taj t2
4* where t1.col1 = t2.col1 and t1.col2 = t2.col2 and t1.col3
= t2.col3)
SQL> /

1 row deleted.

SQL> select * from taj;

CO CO CO
-- -- --
a a a
a a b
a b c
b b b
b b c
c c c
c c d
c d e
b c d

9 rows selected.

Duplicate Rows Selections

SQL> create table taj ( col1 char(2),col2 char(2), col3 char(2));

Table created.


SQL> select * from taj;

CO CO CO
-- -- --
a a a
a a b
a b c
b b b
b b c
c c c
c c d
c d e

8 rows selected.

SQL> select * from taj t1
2 where t1.rowid >
3 ( select min(t2.rowid) from taj t2
4 where t1.col1 = t2.col2);

CO CO CO
-- -- --
a a b
a b c
b b b
b b c
c c d
c d e

6 rows selected.


Note: According your need and conditions add column name in subquery where clause