Difference between Oracle 9i and 10g

vadivelanvaidyanathan · #1 03-13-2007, 07:34 AM

Hi,

What is the difference between Oracle 9i and 10g and which features are implemented in the 10g.

Thanks
V.Vadivelan

ragavraj · #2 03-13-2007, 07:55 AM

Hi
oralce 10g is higher version of oracle 9i, oracle 10g has added a follwoing features

Transparent Data Encryption
Async commits
CONNECT ROLE can not only connect
Passwords for DB Links are encrypted
New asmcmd utility for managing ASM storage
Grid computing - an extension of the clustering feature (Real Application Clusters)
Manageability improvements (self-tuning features)
Performance and scalability improvements
Automated Storage Management (ASM)
Automatic Workload Repository (AWR)
Automatic Database Diagnostic Monitor (ADDM)
Flashback operations available on row, transaction, table or database level
Ability to UNDROP a table from a recycle bin
Ability to rename tablespaces
Ability to transport tablespaces across machine types (E.g Windows to Unix)
New 'drop database' statement
New database scheduler - DBMS_SCHEDULER
DBMS_FILE_TRANSFER Package
Support for bigfile tablespaces that is up to 8 Exabytes in size
Data Pump - faster data movement with expdp and impdp.

Thanks
Ragav

vadivelanvaidyanathan · #3 03-14-2007, 06:04 AM

Thanks ragav for your information.

Murali · #4 03-15-2007, 05:05 AM

Its simple like how we are all having difference among themselves .....

there are some feature differences available for Oracle 9i and Oracle 10g.

Murali · #5 08-02-2007, 09:54 AM

Here are some additional features available in 10g

Network Management
• Back Up Directory Naming Entries to Local Naming File.
• Dynamic Connection Manager Configuration.
• Easy Connect Naming Method.
• Improved Network Outage Detection.
• Automatic LDAP Discovery for Clients.
• Improved Connection Manager Access Rules.
• Automatic Shared Server Configuration.
• Simplified Shared Server Configuration Parameters.

Manageability Infrastructure
• Database Features Usage Tracking.
• Enhanced Database Time Model.
• End-to-End Application Tracing.
• JDBC: End-to-End Tracing.
• SYSAUX Tablespace.
• Automatic Routine Administration Tasks.
• Server-Generated Alerts.
• Automatic Workload Respository.
• Enhanced Wait Model.
• Threshold-Based Alerts.

Storage Management
• Multiple Default Temporary Tablespace Support for SQL Operations.
• Automatic Storage Management.
• Rename Tablespace.

Backup and Recovery Manageability
• Enhanced RMAN Reporting.
• Backup Compression.
• Bounded Backup Window.
• RMAN Scripts Manageability.
• Recovery Area Full Alert.

Performance and Scalability
• Index-Organized Table (IOT) Partitioning Enhancements.
• Eliminate Duplicated Columns in Index on an Index-Organized Table.
• Configurable TCP/IP Send and Receive Buffer Size.
• Single-Set Aggregates in DML Returning Clause.
• High-Speed Infiniband Network Support.
• Sorted Hash Clusters.
• Windows Fiber Support.
• Optimized 64-bit Database.
• SQL Tuning Advisor.
• SQLAccess Advisor.
• Materialized View Tuning API.
• Automatic Optimizer Statistics Collection.

Backup and Recovery
• Flash Backup and Recovery.
• Drop Database.
• RMAN Database Deregistration.
• Backup and Restore of Standby Control File.
• Automatic TSPITR.
• Simplified Recovery Manager Cataloging of Backup Files.
• Automatic Channel Failover for Backup and Restore.
• Automatic File Creation During Recovery.
• Simplified Backups to Disk.
• Proxy Copy Backup of Archivelogs.
• Incrementally Updated Backups.
• Simplified Recovery Through Resetlogs.
• Restore Tolerates Corrupt or Missing Backups.
• Full Database Begin Backup Command.
• Change-Aware Incremental Backups.

Improved Administration
• DML Support in FGA.
• Communication over SASL.
• Unified User Model.
• Easy Database Registration.
• Extended and Uniform Audit Trail.
• Oracle Label Security Directory Integration.

Data Mining Support for Analytic Applications
• Frequent Itemsets.
• DBMS_DATA_MINING PL/SQL Interface for Oracle Data Mining.
• Features Extraction using Non-Negative Matrix Factorization Algorithm.
• Enhanced Data Preprocessing.
• Enhanced Adaptive Bayes Network.
• Multi-User Access Control.
• Oracle Data Mining Client (DM4J).
• Support Vector Machines (SVM).

And still many more......

Murali · #6 08-03-2007, 11:17 AM

Hi,

Here are some,

Automatic Database Diagnostic Monitor (ADDM).
a. To Produce the Automatic Database reports and Monitoring.
Automatic SQL Tuning.
a. New features to find the automated SQL Tuning Scripts.
DBMS_FILE_TRANSFER Package
a. Its an API to make binary copies of files on the local server or to transfer files between the local server and remote servers.
PL/SQL Enhancements
a. The compiled shared libraries are now stored in the database and extracted as necessary. This means they form part of the
normal backup and recovery process, require no manual maintenance.
b. Nested Tables comes with more flexibility with the enhancement of using collections comparison and types.
c. FOR ALL Syntax can be supportive for Non-Consecutive Index in collections.

And still many more......

Murali · #7 08-07-2007, 06:13 AM

And some more...

An additional Feature in ORACLE 10g

Enchancement in PL/SQL

IEEE Floating-Point Types
1. BINARY_FLOAT
2. BINARY_DOUBLE.

These can be used for computations as the work is passed directly to the operating system.
Literal assignments can be perfomed using the "f" and "d" suffixes or conversion functions TO_BINARY_FLOAT and TO_BINARY_DOUBLE

Code:

DECLARE
  vFloat   BINARY_FLOAT;
  vDouble  BINARY_DOUBLE;
BEGIN
  vFloat  := 1.1f;
  vDouble := 1.00001d;
  vFloat  := TO_BINARY_FLOAT(1.1);  
  vDouble := TO_BINARY_DOUBLE(1.00001);END;

Improvement in Overloading With Numeric Types

Code:

CREATE OR REPLACE PACKAGE SP_NUMBERIC AS
  PROCEDURE NUMBERIC(vNumber  NUMBER);
  PROCEDURE NUMBERIC(vNumber  BINARY_FLOAT);
  PROCEDURE NUMBERIC(vNumber  BINARY_DOUBLE);
END;
/

CREATE OR REPLACE PACKAGE BODY SP_NUMBERIC AS
  PROCEDURE NUMBERIC (vNumber  NUMBER) AS
  BEGIN
    DBMS_OUTPUT.put_line('PRINTING NUMBER');
  END;

  PROCEDURE NUMBERIC(vNumber  BINARY_FLOAT) AS
  BEGIN
    DBMS_OUTPUT.put_line('PRINTING BINARY_FLOAT');
  END;

  PROCEDURE NUMBERIC(vNumber  BINARY_DOUBLE) AS
  BEGIN
    DBMS_OUTPUT.put_line('PRINTING BINARY_DOUBLE');
  END;
END;

Code:

Output
SET SERVEROUTPUT ON
BEGIN
  SP_NUMBERIC.NUMBERIC(2);
  SP_NUMBERIC.NUMBERIC(0.1f);
  SP_NUMBERIC.NUMBERIC(0.1d);
END;

PRINTING NUMBER
PRINTING BINARY_FLOAT
PRINTING BINARY_DOUBLE
PL/SQL procedure successfully completed.

Regular Expressions

• REGEXP_INSTR - Similar to INSTR except it uses a regular expression rather than a literal as the search string.
• REGEXP_LIKE - Similar to LIKE except it uses a regular expression as the search string.
• REGEXP_REPLACE - Similar to REPLACE except it uses a regular expression as the search string.
• REGEXP_SUBSTR - Returns the string matching the regular expression. Not really similar to SUBSTR.

And still many more......

Murali · #8 08-09-2007, 08:16 AM

Hi All,

Here are some more.....

Enhancements in SQL plus

SPOOL file comes with the Extension of CREATE, REPLACE and APPEND.

Code:

SQL> spool <File Path> create

This creates a new file in the specified path.

Code:

SQL> spool <File Path> Replace

This Replaces the data in the Existing file in the specified path.

Code:

SQL> spool <File Path> append

This adds/include/appends the data in the Existing file in the specified path.

And still many more......

Murali · #9 08-22-2007, 08:50 AM

Hi All,

Here are some more.....

Database Security Enhancements in Oracle Database 10g

Virtual Private Database (VPD) Enhancements

Column-Level VPD Policy

Code:

CREATE TABLE EMPLOYEE
(
  EMPNO     NUMBER PRIMARY KEY,
  ENAME     VARCHAR2(30),
  EJOB      VARCHAR2(30),
  ESALARY 	NUMBER,
  ECOMMENTS VARCHAR2(30),
  EDEPTNO   NUMBER
);

Table Created.

Code:

INSERT INTO EMPLOYEE VALUES(1,'kuamr','DBD',10000,'',10);
INSERT INTO EMPLOYEE VALUES(2,'ram','PHP',20000,'',20);
INSERT INTO EMPLOYEE VALUES(3,'raja','DBD',30000,'',10);
INSERT INTO EMPLOYEE VALUES(4,'kanna','PHP',40000,'',20);
INSERT INTO EMPLOYEE VALUES(5,'karna','DBD',50000,'',30);
INSERT INTO EMPLOYEE VALUES(6,'arun','DBD',60000,'',3);
INSERT INTO EMPLOYEE VALUES(7,'mani','DBD',70000,'',10);

7 rows inserted.

Now creating a Policy to Restrict to SALARY and COMMENTS for Employee belongs to the Department 20

Code:

DECLARE
 vRestrictDept VARCHAR2(200);	   
 BEGIN
  vRestrictDept := 'deptno = 20';
  DBMS_RLS.ADD_POLICY (object_schema     => <Schema_Name>,
                       object_name       => <OBject_Name>,
                       policy_name       => <Policy_Name>,
                       function_schema   => <Policy Schema_Name>,
                       policy_function   => <Policy_Function>,
                       sec_relevant_cols => <Fields to be Restricted>);
 EXCEPTION WHEN OTHERS THEN
  NULL;					   
END;

PL/SQL procedure successfully completed.

When we select the fields which not come unders the restriction, then

SELECT empno, ename, job FROM employee;

Code:

EMPNO	ENAME	JOB

1	kuamr	DBD
2	ram	PHP
3	raja	DBD
4	kanna	PHP
5	karna	DBD
6	arun	DBD
7	mani	DBD

While selecting the Field which is issued in the Policy for Restriction then we get,

SELECT empno, ename, job, salary, comm FROM employee;

Code:

EMPNO	ENAME	JOB	SALARY	COMM
1	ram	 DBD	20000	
4	kanna	 PHP	40000

Now we get the results based on the Department 20 which is used in the Policy.

Removing the Creating Policy

Code:

BEGIN
  DBMS_RLS.DROP_POLICY (
                        object_schema     => <Schema_Name>,
                        object_name       => <Object_Name>,
                        policy_name       => <Policy_Name>);
END;

And still many more......

Murali · #10 08-23-2007, 03:42 AM

Hi All,

Here are some more.....

Enhancement in Collection Comparison in ORACLE-10g

Code:

CREATE OR REPLACE TYPE Typ_TableElements IS TABLE OF VARCHAR2(4000); SET SERVEROUTPUT ON;
DECLARE
  Array_1 Typ_TableElements := Typ_TableElements('D','F','S');
  Array_2 Typ_TableElements := Typ_TableElements('R','J','K');
  Array_3 Typ_TableElements := Typ_TableElements('R','J','K');
  PROCEDURE SP_COMPARE_ARRAYS(pmInput IN VARCHAR2,pmArray  IN Typ_TableElements,pmArray1 IN Typ_TableElements) IS
  BEGIN
     DBMS_OUTPUT.PUT(RPAD('{'||pmInput||'}',40) || '-----> ');
     IF(pmArray = pmArray1)THEN
        DBMS_OUTPUT.PUT_LINE( 'EQUAL' );
     ELSE
        DBMS_OUTPUT.PUT_LINE( 'NOT EQUAL' );
     END IF;
  END SP_COMPARE_ARRAYS;
  BEGIN
  	-- Comparing the First values from the Array    
  	SP_COMPARE_ARRAYS( '1: Array_1 -> Array_2, Compared Array',Array_1,Array_2);
  	SP_COMPARE_ARRAYS( '2: Array_2 -> Array_3, Compared Array',Array_2,Array_3);
	-- Deleting the Compared Array Values
	Array_1.DELETE;
	Array_2.DELETE;
  	SP_COMPARE_ARRAYS( '3: Array_1 -> Array_2, Deleted Array',Array_1,Array_2);
	Array_1 := Typ_TableElements();
	Array_2 := Typ_TableElements();
	-- Reintializing the Array value for comparison
	SP_COMPARE_ARRAYS( '4: Array_1 -> Array_2, ReInitialized',Array_1,Array_2);
END;

Output:

Code:


Type created.
{1: Array_1 -> Array_2, Compared Array} -----> NOT EQUAL
{2: Array_2 -> Array_3, Compared Array} -----> EQUAL
{3: Array_1 -> Array_2, Deleted Array}  -----> EQUAL
{4: Array_1 -> Array_2, ReInitialized}  -----> EQUAL
PL/SQL procedure successfully completed.

And still many more......

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to get sequence number from oracle database using biztalk oracle db adapter	arjkhanna	Server Management	3	06-11-2009 03:30 AM
difference between oracle 9i and oracle 10g	mayu.vini	Database Support	0	10-13-2008 12:22 AM
difference between oracle and sqlserver?	smani	Database Support	1	08-23-2007 03:08 AM
oracle VS mysql	jegan	Database Support	0	07-20-2007 06:17 AM
SQL query in Oracle 8	vigneshgets	Software Testing	0	05-17-2007 05:27 AM