Oracle 12c New Features.

The Oracle 12C means different things to different people.It all depends on which areas you are looking at, as there are improvements in many areas. Summarized below is the list of Top 12 Features of Oracle 12C as I see it. I have summarized below, the top 12 which I found interesting.

01. Pluggable Databases Through Database Consolidation:

Oracle is doing every thing to jump into the cloud bandwagon. With 12C, Oracle is trying to address the problem of Multitenancy through this feature. There is a radical change and a major change in the core database architecture through the introduction of Container Databases also called CBD and Pluggable Databases (PDB). The memory and process is owned by the Container Database. The container holds the metadata where the PDBs hold the user data. You can create upto 253 PDBs including the seed PDB.

In a large setup, it is common to see 20 or 30 different instances running in production environment. With these many instances, it is a maintenance nightmare as all these instances have to be separately

•Upgraded
•Patched
•Monitored
•Tuned
•RAC Enabled
•Adjusted
•Backed up and
•Data Guarded.

With Pluggable Databases feature, you just have to do all this for ONE single instance. Without this feature, prior to 12C, you would have to create separate schemas and there is always a thread of security how much ever the isolation we build into it. There are problems with namespace conflicts, there is always going to be one public synonym that you can create. With PDBs you can have a separate HR or Scott schema for each PDB, separate Emp, Dept Tables and separate public synonyms. Additionally, 2 PDBs can talk to each other through the regular DB Link feature. There is no high startup cost of creating a database any more. Instead of one instance per day, the shift is into one instance per many databases. For the developer community, you can be oblivious of all this and still continue to use the PDBs as if it were a traditional database, but for the DBAs the world would look like it has changed a lot.

Another cool feature is, you can allocate a CPU percentage for each PDB.

Another initiative being, it has announced a strategic tieup with salesforce.com during the first week of July 2013.

 

02. Redaction Policy:

This is one of the top features in Oracle 12C. Data Redaction in simple terms means, masking of data. You can setup a Data Redaction policy, for example SSN field in a Employee table can be masked. This is called redaction. From Sql Develop you can do this by going to the table: Employee->Right click on Security Policy->click on New->click on Redaction Policy->Enter SSN.
When you do a select * from employee, it will show that the SSN is masked.
The new data masking will use a package called DBMS_REDACT. It is the extension to the FGAC and VPD present in earlier versions.
By doing this, whoever needs to view the data will be able to see it where as the other users will not be able to view it.

 

03. Top N Query and Fetch and offset Replacement to Rownum:

With the release of Oracle Database 12c, Oracle has introduced this new SQL syntax to simplify fetching the first few rows. The new sql syntax “Fetch First X Rows only” can be used.

 

04. Adaptive Query Optimization and Online Stats Gathering:

With this feature, it helps the optimizer to make runtime adjustments to execution plan which leads to better stats. For statements like CTAS (Create Table As Select) and IAS (Insert As Select), the stats is gathered online so that it is available immediately.

 

05. Restore a Table easily through RMAN:

Earlier if you had to restore a particular table, you had to do all sorts of things like restoring a tablespace and or do Export and Import. The new restore command in RMAN simplifies this task.

 

06. Size Limit on Varchar2, NVarchar2, Raw Data Types increased:

The previous limit on these data types was 4K. In 12C, it has been increased to 32,767 bytes. Upto 4K, the data is stored inline. I am sure everyone will be happy with this small and cute enhancement.

 

07. Inline PL/SQL Functions and Procedures:

The in line feature is extended in Oracle 12C. In addition to Views, we can now have PL/SQL Procedures and Functions as in line constructs. The query can be written as if it is calling a real stored procedure, but however the functions do not actually exist in the database. You will not be able to find them in ALL_OBJECTS. I think this will be a very good feature for the developers to explore as there is no code that needs to be compiled.

 

08. Generated as Identity/Sequence Replacement:

You can now create a col with ‘generated as identity’ clause. Thats it. Doing this is equivalent to creating a separate sequence and doing a sequence.nextval for each row. This is another handy and a neat feature which will help developer community. This is also called No Sequence Auto Increment Primary Key.

 

09. Multiple Indexes on a Single Column:

Prior to 12C, a column cant be in more than one index. In 12C, you can include a column in B-tree index as well as a Bit Map index. But, please note that only one index is usable at a given time.

 

10. Online Migration of Table Partition or Sub Partition:

You can very easily migrate a partition or sub partition from one tablespace to another. Similar to how the online migration was achieved for a non-partitioned table in prior releases, a table partition or sub partition can be moved to another tablespace online or offline. When an ONLINE clause is specified, all DML operations can be performed without any interruption on the partition|sub-partition which is involved in the procedure. In contrast, no DML operations are allowed if the partition|sub-partition is moved offline.

 

11. Temporary UNDO:

Prior to 12C, undo records generated by TEMP Tablespace is stored in the undo tablespace. With Temp undo feature in 12C, temp undo records can be stored in temporary table instead of UNDO TS. The benefit is – reduced undo tablespace and reduced redo log space used.

SQL> ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=2G;
SQL> ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=0; –disables the hard limit

 

12. In Database Archiving:

This feature enables archiving rows within a table by marking them as inactive. These inactive rows are in the database and can be optimized using compression but are not visible to the application. These records are skipped during FTS (Full Table Scan).

Advertisements

Oracle new features & enhancements Release wise

Oracle new features & enhancements

New Features of Oracle10g release 2:

  • Web server load balancing – The web cache component includes Apache extension to load-balance transactions to the least-highly-loaded Oracle HTTP server (OHS).
  • RAC instance load balancing – Staring in Oracle 10g release 2, Oracle JDBC and ODP.NET provide connection pool load balancing facilities through integration with the new “load balancing advisory” tool.  This replaces the more-cumbersome listener-based load balancing technique.
  • Automated Storage Load balancing – Oracle’s Automatic Storage Management (SAM) now enables a single storage pool to be shared by multiple databases for optimal load balancing.  Shared disk storage resources can alternatively be assigned to individual databases and easily moved from one database to another as processing requirements change.
  • Data Guard Load Balancing – Oracle Dataguard allows for load balancing between standby databases.
  • Listener Load Balancing – If advanced features such as load balancing and automatic failover are desired, there are optional sections of the listener.ora file that must be present.  Automatic Storage Management (ASM) includes multiple disk operations and a non-ASM database migration utility
  • Enhancements to data provisioning and Oracle Streams designed to make it easier to archive, move, and copy large data sets
  • New Fast-Start Failover for automatic fail over to standby databases
  • Integrated data encryption and key management in the database
  • Automated statistics collection directly from memory designed to eliminate the need to execute SQL queries
  • New administrative reports include automatic database workload repository comparison
  • Extended use of Standard Chunk sizes – In 10gR2, the server has been enhanced to further leverage standard chunk allocation sizes. This additional improvement reduces the number of problems arising from memory fragmentation.
  • Mutexes – To improve cursor execution and also hard parsing, a new memory serialization mechanism has been created in 10gR2. For certain shared-cursor related operations, mutexes are used as a replacement for library cache latches and librarycache pins. Using mutexes is faster, uses less CPU and also allows significantly improved concurrency over the existing latch mechanism. The use of mutexes for cursor pins can be enabled by setting the init.ora parameter _use_kks_mutex to TRUE.
  • V$SGASTAT – V$SGASTAT has been enhanced to display a finer granularity of memory to component allocation within the shared pool. This allows faster diagnosis of memory usage (in prior releases many smaller allocations were grouped under the ‘miscellaneous’ heading).
  • V$SQLSTAT – A new view, V$SQLSTAT has been introduced which contains SQL related statistics (such as CPU time, elapsed time, sharable memory). This view is very cheap to query even on high-concurrency systems, as it does not require librarycache latch use. It contains the most frequently used SQL statistics in the V$SQL family of views.
  • V$OPEN_CURSOR – This implementation of this view has also been enhanced to be latchless, making it inexpensive to query.
  • V$SQLAREA – The V$SQLAREA view has been improved in 10gR2; the view optimizes the aggregation of the SQL statements while generating the view data.

New Features in Oracle10g release 10.1.0

  • Oracle10g Grid – RAC enhanced for Oracle10g dynamic scalability with server blades
  • Completely reworked 10g Enterprise Manager (OEM)
  • AWR and ASH tables incorporated into OEM Performance Pack and Diagnostic Pack options
  • Automated Session History (ASH) materializes the Oracle Wait Interface over time
  • Data Pump replaces imp utility with impdp
  • Automatic Database Diagnostic Monitor (ADDM)
  • Automatic Storage Management (ASM) introduced Stripe And Mirror Everywhere (SAME) standard
  • Automatic Workload Repository (AWR) replaces STATSPACK
  • SQLTuning Advisor
  • SQLAccess Advisor
  • Rolling database upgrades (using Oracle10g RAC)
  • dbms_scheduler package replaces dbms_job for scheduling
  • OEM Partition Manager introduced

Miscellaneous Oracle10g enhancements:

  • Set Database Default Tablespace syntax
  • Rename Tablespace command
  • Introduced RECYCLEBIN command for storing objects before they are dropped. Required new PURGE command for maintenance.
  • sqlplus / as sysdba accessibility without quote marks
  • SYSAUX tablespace
  • Multiple Temporary Tablespaces supported to reduce stress on sorting in TEMP
  • RMAN introduces compression for backups
  • New drop database syntax
  • New alter database begin backup syntax
  • Oracle10g Data Guard Broker introduced
  • Oracle10g RAC supports secure Redo Log transport
  • Flashback enhancements for flashback database and flashback table syntax
  • SQL Apply feature
  • VPD (FGAC, RLS) supports both row-level and column-level VPD
  • Cross Platform Transportable Tablespaces
  • External Table unload utility
  • SQL Regular Expression Support with the evaluate syntax
  • New ROW TIMESTAMP column
  • Improvement to SSL handshake speed
  • Automatic Database Tuning of Checkpoints, Undo Segments and shared memory
  • Automated invoking of dbms_stats for CBO statistics collection
  • RAC introduces Integrated Cluster ware
  • Oracle Application Builder supports HTML DB
  • Browser Based Data Workshop and SQL Workshop
  • PL/SQL Compiler enhanced for compile-time Warnings in utl_mail and utl_compress
  • VPD (FGAC, RLS) supports both row-level and column-level VPD
  • External Table unload utility
  • SQL Regular Expression Support with the evaluate syntax
  • New ROW TIMESTAMP column

Feature Enhancements in Oracle9i 9.2:

  • Oracle Streams
  • RAC Cluster Guard II introduced
  • XML DB enhanced for XML repository
  • Oracle JVM made compliant with JDK 1.3
  • SQL Apply added to Oracle DataGuard
  • Locally Managed tablespaces (LMT) for the SYSTEM tablespaces
  • Default Install Account locking
  • VPD adds support for table and column synonyms

Major new utilities in Oracle9i – (9.0.1):

  • Oracle Data Guard introduced
  • Oracle9i Real Application Clusters introduced
  • Oracle9i Flashback Utility (dbms_flashback)
  • Online table reorganization with dbms_redefinition
  • Database Configuration Assistant (DBCA) – OEM Wizard
  • Multiple blocksize support
  • New blocksize parameter for create tablespace syntax
  • Easy extraction of DDL with dbms_metadata package
  • System-level triggers – startup trigger DDL trigger
  • Oracle Internet File System (IFS) becomes functional
  • Oracle upsert statement introduced
  • SQL case statement replaces decode syntax
  • Oracle external tables

Feature Enhancements on Oracle9i 9.0.1:

  • New v$sql_plan allows execution plan analysis
  • New set autotrace traceonly explain syntax for SQL*Plus replace explain plan syntax
  • System-managed rollback segments
  • Updateable Scrollable Cursors
  • analyze command and dbms_utility.analyze_schema replaced by dbms_stats package
  • connect as sysdba replaces as sysdba syntax in SQL*Plus
  • Crummy svrmgrl utility disappears
  • List Partitioning the multi-level list-hash partitioning
  • RMAN allows re-startable backups
  • RMAN supports block-level recovery
  • Oracle ultra-search

Oracle Enhancements 8i (8.1.7)

  • Oracle HTTP server gets Apache extensions
  • Oracle Integration Server (OIS) introduced
  • PL/SQL Gateway introduced for deploying PL/SQL based solutions on the Web
  • Oracle JVM Accelerator
  • Java Server Pages (JSP) engine
  • New memstat utility for analyzing Java Memory footprints
  • OEM auto-discover for new services
  • New UNDO tablespace
  • Oracle Character Set Migration utility

New features in Oracle 8i (8.1.6)

  • PL/SQL Server Pages (PSP’s)
  • Oracle DBA Studio Introduced
  • New SQL analytic Functions (rank, moving average)
  • Alter table xxx storage (freelists) command supported
  • Java XML parser
  • PL/SQL dbms_crypto_toolkit encryption package

New Features in Oracle8i 8.1.5:

  • Oracle Log Miner
  • Oracle iFS
  • JAVA stored procedures introduced (Oracle Java VM)
  • Virtual private database (VPD, FGAC) using dbms_rls package
  • Locally managed tablespace (LMT) reduces dictionary contention

New Feature Enhancements in Oracle8i 8.1.5:

  • Functional based indexes (FBI)
  • ESTAT/BSTAT is made obsolete by STATSPACK
  • MTTR Fast Start recovery – Checkpoint rate auto-adjusted to match roll forward criteria
  • Online index rebuilding
  • Support for alter table xxx drop column syntax
  • Oracle Parallel Server (OPS) adds Cache Fusion
  • Advanced Queuing improved
  • New execute immediate syntax
  • New dbms_debug package
  • Oracle users and roles can be accessed across dblinks for multiple databases
  • Resource Management introduces priorities and resource classes
  • Partitioned tables enhanced for Hash and Composite partitioning
  • SQL*Loader introduces a direct load API
  • dbms_stats enhanced to allow saving and migration of CBO statistics
    analyze table in parallel
  • Net8 support for SSL, HTTP, HOP protocols

Major Enhancements in Oracle8 – Release 8.0

  • Nested tables
  • OID addressing
  • Pointers allowed in table columns
  • VARRAY support within tables (non first normal form tables)
  • New create type syntax
  • Table Partitioning and Index partitioning enhancements
  • Oracle Universal Server introduced
  • Oracle Data Cartridges introduced for Oracle Spatial
  • Oracle ConText cartridge introduced
  • RMAN introduced to replace Enterprise Backup Utility (EBU)
  • RMAN has incremental backups and parallel syntax support
  • Oracle security server introduced (precursor to Oracle SSO)
  • Oracle WebServer enhanced

Other Oracle8 8.0 enhancements:

  • OPS introduces $gv views
  • OPS allows transparent failover to a new node
  • OPS has Oracle-created Integrated Distributed Lock Manager (IDLM)
  • Ability to call external procedures
  • exp enhanced to export specific table partitions
  • online/offline, backup/recover individual partitions
  • reverse key index supported
  • updatable views
  • syntax to merge and balance partitions
  • Advanced Queuing allows message handling
  • parallel syntax supported for insert, update and delete DML
  • MTS connection pooling
  • Improved “STAR” query joins in CBO
  • Tablespace point in time recovery (TSPITR)
  • Oracle password expiration feature
  • Database links are privileged (no embedded passwords)
  • Oracle Replication manager introduced into OEM
  • Deferred Oracle constraints aids data loading
  • SQL*Net v 2 is renamed to Net8

Major enhancements in Oracle 7.3:

  • Bitmap Indexes
  • Partitioned Views
  • Oracle Standby Database packages
  • Full table scans support prefetch (asynchronous read ahead)
  • alter index xx rebuild syntax
  • Oracle db_verify package for corruption detection
  • Oracle block editor (BBED) created
  • Oracle Spatial
  • Oracle ConText
  • Oracle Trace

Other New Features in Oracle 7.3:

  • Updatable Join Views
  • SQL*DBA obseleted
  • Alter tablespace coalesce syntax
  • temporary parameter added to create tablespace syntax for TEMP tablespace
  • extents unlimited syntax added to table syntax
  • CBO introduced histograms for skewed columns and n-way joins
  • CBO introduces hash joins and hash_area_size parameter
  • CBO supports antijoins for NOT subqueries
  • New dba_histograms view
  • New utl_file package allows interface to flat files

New features in Oracle 7.2

  • Manual Shrink Rollback Segment syntax
  • Resizing of datafiles introduced with resize syntax
  • New dbms_job package
  • Autoextend option added for data files

 

Materialized View

MATERIALIZED VIEW:

A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table’s data. Materialized views, which store data based on remote tables are also, know as snapshots.

A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term). This reference uses “master tables” for consistency. The databases containing the master tables are called the master databases.

A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term).

For replication purposes, materialized views allow you to maintain copies of remote data on your local node. These copies are read-only. If you want to update the local copies, you have to use the Advanced Replication feature. You can select data from a materialized view as you would from a table or view.

For replication purposes, materialized views allow you to maintain copies of remote data on your local node. The copies can be updatable with the Advanced Replication feature and are read-only without this feature. You can select data from a materialized view as you would from a table or view. In replication environments, the materialized views commonly created are primary key, rowid, object, and subquery materialized views.

For data warehousing purposes, the materialized views commonly created are aggregate views, single-table aggregate views, and join views.

For data warehousing purposes, the materialized views commonly created are materialized aggregate views, single-table materialized aggregate views, and materialized join views. All three types of materialized views can be used by query rewrite, an optimization technique that transforms a user request written in terms of master tables into a semantically equivalent request that includes one or more materialized views.

Prerequisites:

The privileges required to create a materialized view should be granted directly rather than through a role.

To create a materialized view in your own schema:

  • You must have been granted the CREATE MATERIALIZED VIEW system privilege and either the CREATE TABLE or CREATE ANY TABLE system privilege.
  • You must also have access to any master tables of the materialized view that you do not own, either through a SELECT object privilege on each of the tables or through the SELECT ANY TABLE system privilege.

To create a materialized view in another user’s schema:

  • You must have the CREATE ANY MATERIALIZED VIEW system privilege.
  • The owner of the materialized view must have the CREATE TABLE system privilege. The owner must also have access to any master tables of the materialized view that the schema owner does not own (for example, if the master tables are on a remote database) and to any materialized view logs defined on those master tables, either through a SELECT object privilege on each of the tables or through the SELECT ANY TABLE system privilege.

To create a refresh-on-commit materialized view (ON COMMIT REFRESH clause), in addition to the preceding privileges, you must have the ON COMMIT REFRESH object privilege on any master tables that you do not own or you must have the ON COMMIT REFRESH system privilege.

To create the materialized view with query rewrite enabled, in addition to the preceding privileges:

  • If the schema owner does not own the master tables, then the schema owner must have the GLOBAL QUERY REWRITE privilege or the QUERY REWRITE object privilege on each table outside the schema.
  • If you are defining the materialized view on a prebuilt container (ON PREBUILT TABLE clause), then you must have the SELECT privilege WITH GRANT OPTION on the container table.

The user whose schema contains the materialized view must have sufficient quota in the target tablespace to store the master table and index of the materialized view or must have the UNLIMITED TABLESPACE system privilege.

When you create a materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view data. You must have the privileges necessary to create these objects.

In this article, we shall see how to create a Materialized View and discuss Refresh Option of the view.

In replication environments, the materialized views commonly created are primary key, rowid, and subquery materialized views.

Primary Key Materialized Views

The following statement creates the primary-key materialized view on the table emp located on a remote database.

SQL>    CREATE MATERIALIZED VIEW mv_emp_pk
        REFRESH FAST START WITH SYSDATE 
        NEXT  SYSDATE + 1/48
        WITH PRIMARY KEY 
        AS SELECT * FROM emp@remote_db;

Materialized view created.

Note: When you create a materialized view using the FAST option you will need to create a view log on the master tables(s) as shown below:

SQL> CREATE MATERIALIZED VIEW LOG ON emp;
Materialized view log created.

Rowid Materialized Views

The following statement creates the rowid materialized view on table emp located on a remote database:

 

SQL>    CREATE MATERIALIZED VIEW mv_emp_rowid 
        REFRESH WITH ROWID 
        AS SELECT * FROM emp@remote_db; 

Materialized view log created.

Subquery Materialized Views

The following statement creates a subquery materialized view based on the emp and dept tables located on the remote database:

SQL> CREATE MATERIALIZED VIEW  mv_empdept
AS SELECT * FROM emp@remote_db e
WHERE EXISTS
     (SELECT * FROM dept@remote_db d
     WHERE e.dept_no = d.dept_no)

REFRESH CLAUSE

[refresh [fast|complete|force]
            [on demand | commit]
            [start with date] [next date]
            [with {primary key|rowid}]]

The refresh option specifies:

  1. The refresh method used by Oracle to refresh data in materialized view
  2. Whether the view is primary key based or row-id based
  3. The time and interval at which the view is to be refreshed

Refresh Method – FAST Clause

The FAST refreshes use the materialized view logs (as seen above) to send the rows that have changed from master tables to the materialized view.

You should create a materialized view log for the master tables if you specify the REFRESH FAST clause.

SQL> CREATE MATERIALIZED VIEW LOG ON emp;

Materialized view log created.

Materialized views are not eligible for fast refresh if the defined subquery contains an analytic function.

Refresh Method – COMPLETE Clause

The complete refresh re-creates the entire materialized view. If you request a complete refresh, Oracle performs a complete refresh even if a fast refresh is possible.

Refresh Method –FORCE Clause

When you specify a FORCE clause, Oracle will perform a fast refresh if one is possible or a complete refresh otherwise. If you do not specify a refresh method (FAST, COMPLETE, or FORCE), FORCE is the default.

PRIMARY KEY and ROWID Clause

WITH PRIMARY KEY is used to create a primary key materialized view i.e. the materialized view is based on the primary key of the master table instead of ROWID (for ROWID clause). PRIMARY KEY is the default option. To use the PRIMARY KEY clause you should have defined PRIMARY KEY on the master table or else you should use ROWID based materialized views.

Primary key materialized views allow materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh.

Rowid materialized views should have a single master table and cannot contain any of the following:

  • Distinct or aggregate functions
  • GROUP BY Subqueries , Joins & Set operations

Timing the refresh

The START WITH clause tells the database when to perform the first replication from the master table to the local base table. It should evaluate to a future point in time. The NEXT clause specifies the interval between refreshes

SQL>     CREATE MATERIALIZED VIEW mv_emp_pk
            REFRESH FAST 
            START WITH SYSDATE 
            NEXT  SYSDATE + 2
            WITH PRIMARY KEY 
            AS SELECT * FROM emp@remote_db;

Materialized view created.

In the above example, the first copy of the materialized view is made at SYSDATE and the interval at which the refresh has to be performed is every two days.

Summary

Materialized Views thus offer us flexibility of basing a view on Primary key or ROWID, specifying refresh methods and specifying time of automatic refreshes.


 

Differences between Oracle 9i and 10g

Differences between Oracle 9i and 10g

Each release of Oracle has many differences, and Oracle 10g is a major re-write of the Oracle kernel from Oracle 9i.  I keep a list of the differences and make sure to see these important differences when migrating from 9i to Oracle 10g.  While there are several hundred new features and other differences between 9i and 10g, here are the major differences between Oracle9i and Oracle10g:

  • Major changes to SQL optimizer internals
  • Oracle Grid computing
  • AWR and ASH tables incorporated into Oracle Performance Pack and Diagnostic Pack options
  • Automated Session History (ASH) materializes the Oracle Wait Interface over time
  • Data Pump replaces imp utility with impdp
  • Automatic Database Diagnostic Monitor (ADDM)
  • SQLTuning Advisor
  • SQLAccess Advisor
  • Rolling database upgrades (using Oracle10g RAC)
  • dbms_scheduler package replaces dbms_job for scheduling

Other notes on differences between 9i and 10g:

New Features of Oracle10g release 2:

  • Web server load balancing – The web cache component includes Apache extension to load-balance transactions to the least-highly-loaded Oracle HTTP server (OHS).
  • RAC instance load balancing – Staring in Oracle 10g release 2, Oracle JDBC and ODP.NET provide connection pool load balancing facilities through integration with the new “load balancing advisory” tool.  This replaces the more-cumbersome listener-based load balancing technique.
  • Automated Storage Load balancing – Oracle’s Automatic Storage Management (SAM) now enables a single storage pool to be shared by multiple databases for optimal load balancing.  Shared disk storage resources can alternatively be assigned to individual databases and easily moved from one database to another as processing requirements change.
  • Data Guard Load Balancing – Oracle Dataguard allows for load balancing between standby databases.
  • Listener Load Balancing – If advanced features such as load balancing and automatic failover are desired, there are optional sections of the listener.ora file that must be present.  Automatic Storage Management (ASM) includes multiple disk operations and a non-ASM database migration utility
  • Enhancements to data provisioning and Oracle Streams designed to make it easier to archive, move, and copy large data sets
  • New Fast-Start Failover for automatic fail over to standby databases
  • Integrated data encryption and key management in the database
  • Automated statistics collection directly from memory designed to eliminate the need to execute SQL queries
  • New administrative reports include automatic database workload repository comparison
  • Extended use of Standard Chunk sizes – In 10gR2, the server has been enhanced to further leverage standard chunk allocation sizes. This additional improvement reduces the number of problems arising from memory fragmentation.
  • Mutexes – To improve cursor execution and also hard parsing, a new memory serialization mechanism has been created in 10gR2. For certain shared-cursor related operations, mutexes are used as a replacement for library cache latches and librarycache pins. Using mutexes is faster, uses less CPU and also allows significantly improved concurrency over the existing latch mechanism. The use of mutexes for cursor pins can be enabled by setting the init.ora parameter _use_kks_mutex to TRUE.
  • V$SGASTAT – V$SGASTAT has been enhanced to display a finer granularity of memory to component allocation within the shared pool. This allows faster diagnosis of memory usage (in prior releases many smaller allocations were grouped under the ‘miscellaneous’ heading).
  • V$SQLSTAT – A new view, V$SQLSTAT has been introduced which contains SQL related statistics (such as CPU time, elapsed time, sharable memory). This view is very cheap to query even on high-concurrency systems, as it does not require librarycache latch use. It contains the most frequently used SQL statistics in the V$SQL family of views.
  • V$OPEN_CURSOR – This implementation of this view has also been enhanced to be latchless, making it inexpensive to query.
  • V$SQLAREA – The V$SQLAREA view has been improved in 10gR2; the view optimizes the aggregation of the SQL statements while generating the view data.

New Features in Oracle10g release 10.1.0

  • Oracle10g Grid – RAC enhanced for Oracle10g dynamic scalability with server blades
  • Completely reworked 10g Enterprise Manager (OEM)
  • AWR and ASH tables incorporated into OEM Performance Pack and Diagnostic Pack options
  • Automated Session History (ASH) materializes the Oracle Wait Interface over time
  • Data Pump replaces imp utility with impdp
  • Automatic Database Diagnostic Monitor (ADDM)
  • Automatic Storage Management (ASM) introduced Stripe And Mirror Everywhere (SAME) standard
  • Automatic Workload Repository (AWR) replaces STATSPACK
  • SQLTuning Advisor
  • SQLAccess Advisor
  • Rolling database upgrades (using Oracle10g RAC)
  • dbms_scheduler package replaces dbms_job for scheduling
  • OEM Partition Manager introduced

Miscellaneous Oracle10g enhancements:

  • Set Database Default Tablespace syntax
  • Rename Tablespace command
  • Introduced RECYCLEBIN command for storing objects before they are dropped. Required new PURGE command for maintenance.
  • sqlplus / as sysdba accessibility without quote marks
  • SYSAUX tablespace
  • Multiple Temporary Tablespaces supported to reduce stress on sorting in TEMP
  • RMAN introduces compression for backups
  • New drop database syntax
  • New alter database begin backup syntax
  • Oracle10g Data Guard Broker introduced
  • Oracle10g RAC supports secure Redo Log transport
  • Flashback enhancements for flashback database and flashback table syntax
  • SQL Apply feature
  • VPD (FGAC, RLS) supports both row-level and column-level VPD
  • Cross Platform Transportable Tablespaces
  • External Table unload utility
  • SQL Regular Expression Support with the evaluate syntax
  • New ROW TIMESTAMP column
  • Improvement to SSL handshake speed
  • Automatic Database Tuning of Checkpoints, Undo Segments and shared memory
  • Automated invoking of dbms_stats for CBO statistics collection
  • RAC introduces Integrated Cluster ware
  • Oracle Application Builder supports HTML DB
  • Browser Based Data Workshop and SQL Workshop
  • PL/SQL Compiler enhanced for compile-time Warnings in utl_mail and utl_compress
  • VPD (FGAC, RLS) supports both row-level and column-level VPD
  • External Table unload utility
  • SQL Regular Expression Support with the evaluate syntax
  • New ROW TIMESTAMP column

 

Data Manipulation Language Statements Description

Data Manipulation Language Statements Description

Data manipulation language (DML) statements query or manipulate data in existing schema objects. They enable you to:

  • Retrieve data from one or more tables or views (SELECT); in Oracle9i, Release 1 (9.0.1), fetches can be scrollable (see “Scrollable Cursors”)
  • Add new rows of data into a table or view (INSERT)
  • Change column values in existing rows of a table or view (UPDATE)
  • Update or insert rows conditionally into a table or view (MERGE)
  • Remove rows from tables or views (DELETE)
  • See the execution plan for a SQL statement (EXPLAIN PLAN)
  • Lock a table or view, temporarily limiting other users’ access (LOCK TABLE)

DML statements are the most frequently used SQL statements. Some examples of DML statements are:

SELECT ename, mgr, comm + sal FROM emp; 

INSERT INTO emp VALUES 
    (1234, 'DAVIS', 'SALESMAN', 7698, '14-FEB-1988', 1600, 500, 30); 

DELETE FROM emp WHERE ename IN ('WARD','JONES');

SQL Statements Overview

SQL Statements Overview

All operations performed on the information in an Oracle database are executed using SQL statements. A statement consists partially of SQL reserved words, which have special meaning in SQL and cannot be used for any other purpose. For example, SELECT and UPDATE are reserved words and cannot be used as table names.

A SQL statement is a computer program or instruction. The statement must be the equivalent of a complete SQL sentence, as in:

SELECT ename, deptno FROM emp;
 

Only a complete SQL statement can be executed, whereas a fragment such as the following generates an error indicating that more text is required before a SQL statement can execute:

SELECT ename 
 

Oracle SQL statements are divided into the following categories:

  • Data manipulation language (DML) statements
  • Data definition language statements (DDL)
  • Transaction control statements
  • Session control statements
  • System control statements

Embedded SQL statements

How to perform a character wise replacement of a string in Oracle

In Oracle SQL/PL-SQL the TRANSLATE function performs a character wise replacement of a string. The syntax is: TRANSLATE( input_string , string1 , string2 )

– String1 is the string that will be searched for in input_string

– All characters in the String1 will be replaced with the corresponding character in the String2.Example


SELECT TRANSLATE(‘PASSPORT’, ‘AR’ ,’OO’) FROM DUAL;Result
————
POSSPOOT

What is the DUAL table in Oracle Database

The DUAL table is a table that Oracle creates with it is data dictionary consisting in exactly one row. DUAL belongs to the SYS user but all users can query it , and use it’s unique property of returning only one row, to select pseudo-columns .