This exam focuses on three primary topics: basic Oracle Net configuration (in other words, configuring Oracle for network access), the skills that you use to protect and recover an Oracle database from the inevitable problems of data loss, and the Export/Import and SQL*Loader utilities. Expect to answer approximately 63 multiple choice questions in 90 minutes. To pass the exam, you need to answer at least 41 questions correctly (65%). Specifically, the exam tests you about the following categories of information:

• the general need for Oracle Net, Oracle Corp.’s products that support applications in a networked environment
• the benefits of Oracle Net
• Oracle Net software architecture
• Oracle Net name resolution methods (host naming, local naming, etc.)
• shared server configuration and tuning
• Oracle Net connection models (shared server, dedicated server, bequeathed, etc.)
• Oracle Net disconnections
• Oracle Net configuration files (sqlnet.ora, tnsnames.ora, listener.ora, etc.)
• the TNS_ADMIN environment variable
• network design
• the Oracle Net Manager and Oracle Net Configuration Assistant
• the purpose and configuration of a listener process
• automatic service registration with the listener
• static service registration with the listener
• the Listener Control utility
• transparent connection load balancing and failover
• troubleshooting network connectivity problems
• the TNSPING utility
• failures that can damage an Oracle database system such as SQL statement failure, foreground and background process failure, system and instance crashes, disk (media) failures, and user errors
• the roll forward (redo) and roll back (undo) recovery stages of crash, instance, and media recovery
• the function of the database’s online log members, archived log files, and undo (rollback) segments in database recovery
• configuration of the database’s online log groups and members, log archiving, checkpoints, and the control file
• user-managed physical database backup and recovery using SQL commands and operating system commands
• database backup and recovery using the Recovery Manager (RMAN) utility
• logical database backup and recovery using the Import and Export utilities
• detection and repair of data block corruption
• bulk data loads of external data using the SQL*Loader utility
• bulk data loads of internal data using direct-load INSERT statements

Be prepared to identify the correct usage the optional ESCAPE parameter when you want Oracle to interpret literally a wildcard character within the expression. In the expression, specify the escape character (for example, \) followed by the wildcard character that you want Oracle to interpret literally. Then specify the escape character in the ESCAPE parameter itself. For example, the following query returns the job IDs and job titles for jobs that have a job ID that begins with the characters “FI_”.

SELECT job_id, job_title
FROM jobs
WHERE job_id LIKE ‘FI\_%’ ESCAPE ‘\’;

The following example explains how to use the ROUND and TRUNC functions to round or truncate DATE values to the nearest day. Notice that the ROUND and TRUNC functions accept a format model element (for example, DAY) as their second argument; if you omit this argument, these functions round and truncate dates to the nearest day, respectively. Note that you can use the SQL functions ROUND and TRUNC with DATE values only; you cannot use these functions with TIMESTAMP, TSTZ, or TSLTZ values.

SELECT
ROUND(TO_DATE(’23-AUG-99:12:12′,’DD-MON-RR:HH24:MI’), ‘DAY’) AS result,
TRUNC(TO_DATE(’23-AUG-99:12:12′,’DD-MON-RR:HH24:MI’), ‘DAY’) AS result
FROM dual;

The only valid logical operators in an outer join condition are = and AND.

Continue Reading

In the architecture section of the exam, expect questions that test your knowledge of very specific details about the Oracle software architecture. Specifically, you should be intimately familiar with what functions each Oracle background process performs; make sure that you know details such as what SMON does, what events cause DBWn to write dirty buffers to data files, what events cause LGWR to write the log buffer to the current online log group, which background processes perform I/O, and so on.

Understand that when a user establishes a dedicated server connection, Oracle allocates all portions of the corresponding PGA outside the shared pool. However, when a user establishes a multithreaded server (MTS) connection,Oracle allocates memory for the session’s cursor state and sort area, collectively known as the User Global Area (UGA) from the shared pool. Therefore, when you configure an Oracle instance with MTS, it is important to consider how much memory MTS sessions can allocate from the shared pool.

Continue Reading

Locks are mechanisms that prevent transactions from accessing the same resource concurrently with an intent of modification to the data.

Objects Affected By Locks

• Tables and rows (data in tables).
• data dictionary rows , shared memory structures which are not designated as physical objects.

When do Locks occur:

Any transaction resulting in modifying the data in a table acquires a lock. These are termed as DML locks. Other types of locks also occur when a ddl statement is used.

Oracle Lock duration

All the locks acquired by Statements with in a transaction are held until completion of transaction.

How Oracle Locks are released

When a transaction explicitly commits with a commit statement or the transaction is rolledback with a rollback statement, all the locks held by the transaction are released.

Oracle also releases locks acquired after a savepoint when rolling back to the savepoint.

Oracle Lock Modes

Exclusive Lock

The lock is obtained to modify the data in a table.Any transaction acquiring an exclusive lock would be given permission to modify the data. The first transaction that acquires an exlusive lock is the only eligible transaction to modify the resource until the exclusive lock is released.

Continue Reading

Oracle database Certification is a recognition to you, to work as qualified professional recognized around the world to work on Oracle database at various levels.With Oracle certification you will get more Opportunities and higher Pay packages.

 Being an Oracle certified Professional you would be having the right skills, efficient hands-on work experience to work on Oracle datbase.

Oracle provides three certification tracks.  You can choose any track that best suits  your individual job profile and your interests.

Oracle certification tracks :

• Oracle database Administrator
• Web Application developer
• Oracle Application server administrator.

Continue Reading

Shared Pool tuning is the most important aspect of Oracle shared global area optimization. And by far it is the most neglected area.

Shared Pool is a memory area in Oracle which is in turn a part of Oracle SGA(System Global Area). Shared Pool comprises of  different meory Strctures. If shared pool size is not configured Properly , the performance of the database suffers drastically.

Shared Pool consists of

1. Library Cache

2. Dictionary Cache

3. Control Structures.

Library cache inturn comprises of Shared Sql Area, Pl/sql Procedures, Functions, Packages (loaded) and control Structures (Latches and locks).

Continue Reading

I used to  kick a test job from sql * plus or Pl/sql developer during the late evening. And i will check the result next day.  But next day when i see the Sql * plus window or any editor window i usually see the error “ORA-03113: END- OF -FILE ON communication channel“.

So what this error is all about and how to resolve this ?

 It is not that critical error as a programmer  you should be concerned with ? Basically this error may rise in Four situations.

1.  Your DBA might have  set a maximum connection time limit on your Oracle session. if your session reaches maximum connection time to the server, your session will automatically disconnected from the server ( your session will be automatically timed out explicitly by the Oracle server). And you will see the above error.

Continue Reading

Have you ever faced this error ora-01652 ?

The above error is also similar to

ora-01652 : Unable to extend temp segment by 256 in tablespace

Every Oracle professional might have faced this error serveral times. In thi post i will discuss about the error , Why the error “Ora-01652 ” occurs and how to solve this problem .

Most propably if you faced the this error while inserting , updating the data , then this might be a space issue.
Oracle allocates the space required for a table expansion in the form of extents. if there is insufficient space in the data files that contribute your tablespace,then oracle throws this error.

If you faced this problem while inserting or updating the data , try to look at the free space available in each tablespace.

 If the tot_pct_used is nearing 98%, then you need to add some space to your tablespace in the form of data files.

You can achieve this by the following statement

ALTER TABLESPACE users
ADD DATAFILE 'c:\oraexp\oradata\users23.dbf' SIZE 100M;

If you face the Ora-01652 error while running a query then there is a different twist to the story.If your query uses heavy sorting, huge joins then this error can also occur.

Continue Reading

Renaming a table is not possible until Oracle 8i. From Oracle 8i you can rename a table , all the correspoding dependencies would be automatically updated by Oracle.

Rename table in Oracle

  can be done by two ways.
Either using the Rename statement or Alter Table statement.

RENAME statement syntax

Rename old_table_name to New_table_name.

Ex:

Rename emp to emp_temp

Rename table by using ALTER TABLE:

syntax :

Alter table (tab_name_old) rename to (tab_name_new);

Ex:

Alter table emp rename to emp_temp;

The first difference between

delete, drop and truncate

statements is the statement nature itself. DELETE statement is a DML (DATA MANIPULATION LANGUAGE) statement. DROP AND truncate statements are DDL ( DATA definition language) statements.

A DML action can be rolledback if the data is not committed. Whereas all the DDL actions cannot be rolled back until Oracle 9i. But with Oracle 9i FLASH BACK enabled even some of the DDL statements can be rolledback.

Delete : A delete statement deletes the data from a table. A delete statement can have  a where clause, on which it deletes the records that satisfy only the “where” condition. If the
“where” condition is omitted the delete statement deletes all the records in a table.

Actions of Delete statement :

• deletes the data from the current table space
• Modifies the undo tablespace with the delete records.
• Executes all the before / after statement and row level triggers.
• Updates the indexes (makes the index empty if the where clause is omitted).
• Constraint checks are performed before deleting the rows

. Continue Reading