• With Program, With Schedule 
• With Program, Without Schedule
• Without Program, With Schedule
• Without Program, Without Schedule

select job, log_user, next_date, next_sec, interval, what
from dba_jobs
where job=152152;

       JOB LOG_USER   NEXT_DATE  NEXT_SEC INTERVAL                  WHAT
---------- ---------- ---------- -------- ------------------------- --------------------
    152152 SCOTT      05/01/2006 01:00:00 trunc(sysdate+1) + 1/24   SCOTTS_PROC;

DBMS_SCHEDULER.CREATE_JOB is an overloaded procedure; in this example, we'll look at just one of many ways to call it. Here's the call:

begin
dbms_scheduler.create_job
 (job_name => 'SCOTT.RUN_SCOTTS_PROC',
  job_type => 'STORED_PROCEDURE',
  job_action=> 'SCOTT.SCOTTS_PROC',
  start_date=> trunc(sysdate+1)+1/24,
  repeat_interval=> 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT; BYHOUR=1;',
  enabled=>true,
  auto_drop=>false,
  comments=>'Converted from job 152152');
end;

There are a lot of things to notice here; let's go through the call line by line.

1. Instead of a number, the job's identified by the name you pass as job_name. Job names follow the standard Oracle naming conventions. SYS can create a job for anyone. So, job_name => 'SCOTT.RUN_SCOTTS_PROC' creates a job in SCOTT's schema named RUN_SCOTTS_PROC .
2. You need to specify the job type. As in the old job scheduling mechanism, you can schedule anonymous blocks (job_type: 'PLSQL_BLOCK') or stored procedures (job_type: 'STORED_PROCEDURE'). You can also schedule programs that run outside the database (job_type: 'EXECUTABLE') or schedule "job chains" (job_type: 'CHAIN'). A job chain is a sequence of jobs.
3. job_action is the equivalent of "what" in dbms_job.submit. Note that if you're scheduling a stored procedure, you don't put a semicolon after the procedure name, as you did in dbms_job.submit . Note also that if you want to pass arguments to a stored procedure, like UPDATE_INDEXES('SCOTT'), you have to either wrap the stored procedure in an anonymous pl/sql block, or set the arguments with a separate call to SET_JOB_ARGUMENT_VALUE - a topic we won't cover here.
4. start_date is the first date the job will execute. Note that if you specify the repeat_interval using calendaring syntax, which we'll cover shortly, start-date is used as a reference only - the job won't actually execute until the next date that matches your repeat_interval. For example, if you set the repeat interval to every Thursday, and pass a start date that's on a Tuesday, then the job will execute on the first Thursday following the start date.
5. This call passes repeat_interval in calendaring syntax, which we'll discuss below. You could also pass it just as you had it in DBA_JOBS, as 'trunc(sysdate+1) + 1/24'.
6. In DBMS_SCHEDULER, unlike DBMS_JOB, the default is that new jobs are disabled. If you want your job to be enabled as soon as you create it, as it would have been with DBMS_JOB, then pass enabled=>true.
7. You can control whether or not a one-time job is dropped after it's run by setting auto_drop to FALSE.
8. You can pass in comments, which are displayed alongside the job in the static data dictionary view that shows Scheduler jobs.

After you run this call to create_job, there's one more thing to notice: you don't have to COMMIT. The results are immediately visible to all users in the DBA_SCHEDULER_JOBS table:

OWNER JOB_NAME        JOB_ACTION        START_DATE REPEAT_INTERVAL STATE     COMMENTS
----- --------------- ----------------- ---------- --------------- --------- -------------------------
SCOTT RUN_SCOTTS_PROC SCOTT.SCOTTS_PROC 05/08/2006 FREQ=DAILY; BYD SCHEDULED Converted from job 152152

What if you want to run this job right away? Use dbms_scheduler.run_job:

connect scott/tiger
begin
DBMS_SCHEDULER.RUN_JOB (
   job_name => 'RUN_SCOTTS_PROC',
   use_current_session => false);
end;
/

Note that the default for use_current_session is TRUE -- that is, if you don't pass this parameter, RUN_SCOTTS_PROC will run synchronously, ie. in your current session, instead of running asynchronously, ie. being picked up and run by a job slave.

• External - no mirroring
• Normal - data is mirrored on two separate disks. This is the default setting.
• High Redundancy - data is mirrored on there separate disks providing three-way mirroring capabilities.

• Simplified daily administration
• The performance of raw disk I/O for all ASM files
• Compatibility with any type of disk configuration, be it JBOD or complex SAN
• Use of a specific file-naming convention to name files, enforcing an enterprise-wide file-naming convention
• Prevention of the accidental deletion of files, since there is no file system interface and ASM is solely responsible for file management
• Load balancing of data across all ASM managed disk drives, which helps improve performance by removing disk hot spots
• Dynamic load balancing of disks as usage patterns change and when additional disks are added or removed
• Ability to mirror data on different disks to provide fault tolerance
• Support of vendor-supplied storage offerings and features
• Enhanced scalability over other disk-management techniques

• When creating disk groups and allocating storage, most customers will be best served by using just two disk groups – one for a recovery area and one for their work area. However, customers with intensive OLTP applications but no disk cache may also need a separate disk group for online log files.
• When creating disk groups, you will need to tell ASM whether the storage is already mirrored by the hardware, or whether ASM should mirror the data

select ....
from ....
model [main] 
   [ reference models ] 
   [ partition by (<cols>)] 
   dimension by (<cols>) 
   measures (<cols>)
    [ ignore nav ] | [ keep nav ]
  [ rules
    [ upsert | update]
    [ automatic order | sequential order ]
    [ iterate (n) [ until <condition>] 
  ]
    (   <cell_assignment> = <expression> ...)

• The "RETURN UPDATED ROWS" clause following the keyword MODEL limits the results returned  to just those rows that were created or updated in this query.  Using this clause is a convenient way to limit result sets to just the newly calculated values.  We will use the RETURN UPDATED ROWS clause throughout our examples.
• The keyword  "RULES," shown in all our examples at the start of the formulas, is optional, but we include it for easier reading. 
• Likewise, many of our examples do not require ORDER BY on the Country column, but we include the specification for convenience in case readers want to modify the examples and use multiple countries.

Table 1: Anchoring Metacharacters Metacharacter Description ^ Anchor the expression to the start of a line $ Anchor the expression to the end of a line Table 2: Quantifiers, or Repetition Operators Quantifier Description * Match 0 or more times ? Match 0 or 1 time + Match 1 or more times {m} Match exactly m times {m,} Match at least m times {m, n} Match at least m times but no more than n times Table 3: Predefined POSIX Character Classes Character Class Description [:alpha:] Alphabetic characters [:lower:] Lowercase alphabetic characters [:upper:] Uppercase alphabetic characters [:digit:] Numeric digits [:alnum:] Alphanumeric characters [:space:] Space characters (nonprinting), such as carriage return, newline, vertical tab, and form feed [:punct:] Punctuation characters [:cntrl:] Control characters (nonprinting) [:print:] Printable characters Table 4: Alternate Matching and Grouping of Expressions Metacharacter Description | Alternation Separates alternates, often used with grouping operator () ( ) Group Groups subexpression into a unit for alternations, for quantifiers, or for backreferencing [char] Character list Indicates a character list; most metacharacters inside a character list are understood as literals, with the exception of character classes, and the ^ and - metacharacters Table 5: The REGEXP_LIKE Operator Syntax Description REGEXP_LIKE(source_string, pattern [, match_parameter]) source_string supports character datatypes (CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2, and NCLOB but not LONG). The pattern parameter is another name for the regular expression. match_parameter allows optional parameters such as handling the newline character, retaining multiline formatting, and providing control over case-sensitivity. Example: SELECT ename FROM emp WHERE REGEXP_LIKE (ename, '^J.(N|M),S$'); ENAME ----- JONES JAMES In this example, we tell Oracle to retrieve any values that start with J, followed by any letter, then N or M, then any letter, then S Another one, the following regular expression would match fly, flying, flew, flown, and flies: SELECT c1 FROM t1 WHERE REGEXP_LIKE(c1, ‘fl(y(ing)?|(ew)|(own)|(ies))'); Table 6: The REGEXP_INSTR Function Syntax Description REGEXP_INSTR(source_string, pattern [, start_position [, occurrence [, return_option [, match_parameter]]]]) This function looks for a pattern and returns the first position of the pattern. Optionally, you can indicate the start_position you want to begin the search. The occurrence parameter defaults to 1 unless you indicate that you are looking for a subsequent occurrence. The default value of the return_option is 0, which returns the starting position of the pattern; a value of 1 returns the starting position of the next character following the match. Example: SELECT REGEXP_INSTR('5035 Forest Run Trace, Alpharetta, GA', '[^ ]+', 1, 6] "Test" FROM dual;       TEST         36 In this example, we are telling Oracle to examine the string, looking for occurrences of one or more non-blank characters and to return the sixth occurrence of one or more non-blank character. Table 7: Explanation of 5-digit + 4 Zip-Code Expression Syntax Description   Empty space that must be matched [:digit:] POSIX numeric digit class ] End of character list {5} Repeat exactly five occurrences of the character list ( Start of subexpression - A literal hyphen, because it is not a range metacharacter inside a character list [ Start of character list [:digit:] POSIX [:digit:] class [ Start of character list ] End of character list {4} Repeat exactly four occurrences of the character list ) Closing parenthesis, to end the subexpression ? The ? quantifier matches the grouped subexpression 0 or 1 time thus making the 4-digit code optional $ Anchoring metacharacter, to indicate the end of the line Table 8: The REGEXP_SUBSTR Function Syntax Description REGEXP_SUBSTR(source_string, pattern [, position [, occurrence [, match_parameter]]]) The REGEXP_SUBSTR function returns the substring that matches the pattern. Example: SELECT REGEXP_SUPSTR('5035 Forest Run Trace, Alpharetta, GA',',[^,]+,') "Test"    FROM dual; Test ------------------ , Alpharetta, In this example we search for a comma, followed by one or more characters immediately followed by a comma. Table 9: The REGEXP_REPLACE Function Syntax Description REGEXP_REPLACE(source_string, pattern [, replace_string [, position [,occurrence, [match_parameter]]]]) This function replaces the matching pattern with a specified replace_string, allowing complex search-and-replace operations. Example: SELECT  REGEXP_REPLACE(emp_phone,     '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',     '(\1) \2-\3') "Test"     FROM emp; Test ---------------- (404) 444-4321 (404) 555-5432 (404) 666-6543 In this eample we search for a pattern of numbers that looks like a European phone number listing such as 111.222.3333 and convert it to a normal USA format listing of (111) 222-3333. Table 10: Backreference Metacharacter Metacharacter Description \digit Backslash Followed by a digit between 1 and 9, the backslash matches the preceding digit-th parenthesized subexpression. (Note: The backslash has another meaning in regular expressions; depending on the context it can also mean the Escape character Table 11: Explanation of Pattern-Swap Regular Expression Regular-Expression Item Description ( Start of first subexpression . Match any single character except a newline * Repetition operator, matches previous . metacharacter 0 to n times ) End of first subexpression; result of the match is captured in \1 (In this example, it's Ellen.)   Empty space that needs to be present ( Start of the second subexpression . Match any single character except a newline * Repetition operator matches the previous . metacharacter 0 to n times ) End of second subexpression; result of this match is captured in \2 (In this example, it stores Hildi.)   Empty space ( Start of third subexpression . Match any single character except a newline * Repetition operator matches . metacharacter 0 to n times ) End of third subexpression; result of this match is captured in \3 (In this example, it holds Smith.) Table 12: Explanation of the Social Security Number Regular Expression Regular-Expression Item Description ^ Start of line character (Regular expression cannot have any leading characters before the match.) ( Start subexpression and list alternates separated by the | metacharacter [ Start of character list [:digit:] POSIX numeric digit class ] End of character list {3} Repeat exactly three occurrences of character list - A hyphen [ Start of character list [:digit:] POSIX numeric digit class ] End of character list {2} Repeat exactly two occurrences of character list - Another hyphen [ Start of character list [:digit:] POSIX numeric digit class ] End of character list {4} Repeat exactly four occurrences of character list | Alternation metacharacter; ends the first choice and starts the next alternate expression [ Start of character list [:digit:] POSIX numeric digit class. ] End of character list {9} Repeat exactly nine occurrences of character list ) Ending parenthesis, to close the subexpression group used for alternation $ Anchoring metacharacter, to indicate the end of the line; no extra characters can follow the pattern

CREATE DIRECTORY datapump AS 'C:\user\datafile\datapump';

Some Parameters

We are all familiar with the FULL database export. Data Pump easily performs this with the following command line:
expdp ananda/abc123 tables=CASES directory=DPDATA1 dumpfile=expCASES.dmp job_name=CASES_EXPORT

Notice there are just a few name changes and instead of specifying the directory path in the file locations the additional parameter for your DIRECTORY is supplied. Note the parameter job_name above, a special one not found in the original export. All Data Pump work is done though jobs. Data Pump jobs, unlike DBMS jobs, are merely server processes that process the data on behalf of the main process. The main process, known as a master control process, coordinates this effort via Advanced Queuing; it does so through a special table created at runtime known as a master table.


Exclude Objects
The exclude parameter allows any database object type to be excluded from an export or import operation. The optional name qualifier allows you finer selectivity within each object type specified. For example, the following three lines in a parameter file:
Exclude=function
Exclude=procedure
Exclude=package:”like ‘PAYROLL%' “
Would exclude all functions, procedures and packages with names starting with ‘PAYROLL' from the job.

Include
The include parameter includes only the specified object types and objects in an operation. For example, if the above three specifications were INCLUDE parameters in a full database export, only functions, procedures and packages with names starting with ‘PAYROLL' would be written to the dumpfile set. You can use the clause INCLUDE=TABLE:"LIKE 'TAB%'" to export only those tables whose name start with TAB. Similarly, you could use the construct INCLUDE=TABLE:"NOT LIKE 'TAB%'" to exclude all tables starting with TAB. Alternatively you can use the EXCLUDE parameter to exclude specific objects.

Content
The content parameter allows one to request for the current operation just metadata, just data or both. The exp ‘ROWS=N' parameter is equivalent to content=metadata_only, but there is no equivalent for content=data_only.

Query
The query parameter operates much as it did in original export, but with two significant enhancements:
1.    It can be qualified with a table name such that it only applies to that table
2.    It can be used during import as well as export.

table_exists_action
The TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing tables. Other uses:
    * skip
    * append
    * truncate
    * replace

Data Pump provides the following export modes:

• Full Export - Activated by specifying "FULL=Y". Notifies Export to export the entire database. Everything you need to recreate all of the application data objects will be included in the output file including tablespaces, tables, indexes, views, synonyms, PL/SQL programs, users, grants - the works.
  
  
• Schema Export - Activated by specifying "SCHEMAS=schema, schema, schema….). Exports the entire schema and all dependent objects (tables, indexes, grants, synonyms, PL/SQL programs, views). The problem with the older version of Import was that because it didn't recreate the user (just the user's objects), the administrator was forced to save the user creation and grant statements to recreate the user with the same privileges it had before the drop was performed. A Data Pump schema import will recreate the user and execute all of the associated grants, user password history, etc..
  
  
• Table Export - Activated by specifying "TABLES=[schemas].tablename, [schemas].tablename,…. Exports a table and all dependent objects.
  
  
• Tablespace Export - Activated by specifying "TABLESPACES=tablespacename, tablespacename, tablespacename… All of the objects contained in the specified tablespace are exported. If a table is exported, its dependent objects are also exported regardless of the tablespace they are stored in.
  
  
• Transportable Tablespace Export - Activated by specifying "TRANSPORT TABLESPACES=tablespacename… Only the metadata is exported for objects contained in the specified tablespaces. The administrator then uses an operating system command to copy the tablespace datafile(s) to the target database. The transportable tablespace Import feature is then used to attach the datafile (and its contents) to the database.

I provided a listing of parameters that I thought readers might find interesting in a previous blog titled "10G Data Pump Part 1". It may be beneficial for those of you that haven't read that blog to do so before continuing.

Here are a few other parameters I thought you may find to be beneficial during your day-to-day usage of Data Pump Export:

• JOB_NAME=jobname_string - You are able to assign a job name to a Data Pump operation. This allows the job to be more easily identified when querying the DBA_DATAPUMP_JOBS and DBA_DATAPUMP_SESSIONS data dictionary views. If you do not specify a job name, Oracle will create a default job name for you. The form will be SYS_EXPORT_mode_nn, where mode is the type of export (FULL, TABLE, etc.) and "nn" is a incrementing number starting at 01. The job name is used in the ATTACH command below to attach, and reattach, to running jobs.
  
  
• ATTACH [=[schema_name.]job_name] - Used to attach a client session to an existing Data Pump operation. An example of the ATTACH command is provided below:
  
  > expdp foot/foot ATTACH=foot.export_full
  
  
• DIRECTORY=directory_object - All of the dumpfile set I/O is generated by Data Pump processes that run within the constructs of the database engine. As a result, the O/S account performing the I/O is Oracle, which usually has a higher level of privileges than most other accounts. Oracle attempts to prevent unwarranted operations from occurring by using Oracle directory objects that require read and write privileges granted by the DBA. Users running Data Pump operations are only able to read and write files in the directories that they have been granted access to.
  
  Before a user is able to run a Data Pump operation, the administrator must pre-create a directory and grant privileges to the user on that directory. This means that users are unable to fully qualify the output file and log file as they were able to do in the non Data Pump versions of Export and Import. If you don't pre-create the directory, Oracle provides a default directory called DATA_PUMP_DIR.
  
  
• DUMPFILE=[directory_object:]file_name [, ...] - Contains the metadata and/or data output from a Data Pump Export operation. The directory object specification is not required to be specified if a directory is specified in the DIRECTORY parameter. If one is specified in the DIRECTORY parameter and the DUMPFILE= parameter, the DUMPFILE=directory: specification will take precedence.
  
  
• ESTIMATE={BLOCKS | STATISTICS} - Used in conjunction with the ESTIMATE_ONLY parameter to estimate the amount of space a Data Pump Export dumpfile will consume.
  
  
• Fine Grained Selectivity Parameters (Exclude, Include, Query, Flashback, Content) - Allows the administrator to include or exclude objects.
  
  
• HELP = {y | n} - Displays a listing of all the parameters that can be specified for the Data Pump operation. For example:
  
  > expdp help=y
  
  Would provide a listing of parameters for a Data Pump Export operation.
  
  
• LOGFILE=[directory_object:]file_name - Contains work-in-progress, work completed and errors generated during a Data Pump Operation. For a real time update on a Data Pump operation's activities, you are able to attach to a running Data Pump operation using the ATTACH command and then execute the STATUS command. Like its dumpfile counterpart, The directory object specification is not required to be specified if a directory is specified in the DIRECTORY parameter. If one is specified in the DIRECTORY parameter and the LOGFILE= parameter, the LOGFILE=directory: specification will take precedence.
  
  
• PARALLEL=integer - Multiple worker processes are able to perform inter-table and inter-partition work in parallel. This allows administrators to load and unload database tables using parallel processing.
  
  The most beneficial aspect of Data Pump's parallel processing feature is that it is dynamic. Administrators are able to attach (connect) to a Data Pump job that is currently executing and dynamically alter the number of parallel processes it uses.
  
  
• SAMPLE=[[schema_name.]table_name:]sample_percent - Allows an administrator to sample a percentage of the object specified. If you don't specify a table name, the percentage will be applied to the entire Data Pump operation.
  
  
• STATUS=[integer] - Specifies the number of seconds between each status information display.

Data Pump provides the following import modes:

• Full Export - Activated by specifying "FULL=Y". Notifies Import to import the entire content of the input file. The amount of data actually imported is dependent upon the data contained in the input file. If the input file contains all of the objects and data from a full database export, all of those objects will be imported. If the input file contains a schema export, all of the objects in that schema will be imported.
  
  
• Schema Import - Activated by specifying "SCHEMAS=schema, schema, schema….). Imports the entire schema and all dependent objects (tables, indexes, grants, synonyms, PL/SQL programs, views). The problem with the older version of Import was that because it didn't recreate the user (just the user's objects), the administrator was forced to save the user creation and grant statements to recreate the user with the same privileges it had before the drop was performed. A Data Pump schema import will recreate the user and execute all of the associated grants, user password history, etc..
  
  
• Table Import - Activated by specifying "TABLES=[schemas].tablename, [schemas].tablename,…. Imports a table and all dependent objects.
  
  
• Tablespace Import - Activated by specifying "TABLESPACES=tablespacename, tablespacename, tablespacename… All of the objects contained in the specified tablespace are imported. If a table is imported, its dependent objects are also imported regardless of the tablespace they were stored in.
  
  
• Transportable Tablespace Import - Activated by specifying "TRANSPORT TABLESPACES=tablespacename… Here's how this feature works. The administrator first runs Data Pump Export with the Transportable Tablespace feature activated. Data Pump exports only the metadata for the objects contained in the specified tablespaces. The objects in the tablespace set being exported can not have any references to objects contained in tablespaces outside of the tablespace set. In addition there can't be any objects in the tablespace set that are referenced by objects outside of the tablespace set being exported. Having a index in a tablespace that is not contained in the same tablespace set that contains the table it is built upon would be an example of a dependency reference problem. Administrators use the TRANSPORT_FULL_CHECK parameter to perform the dependency verification.

I/O BANDWIDTH IS MOST IMPORTANT FACTOR
It is important to make sure there is sufficient I/O bandwidth to handle the number of parallel threads specified; otherwise performance can actually degrade with additional parallel threads. Care should be taken to make sure the dumpfile set is located on spindles other than those holding the instance's data files. Wildcard file support makes it easy to spread the I/O load over multiple spindles. For example, a specification such as:
Dumpfile=dmpdir1:full1%u.dmp,dmpdir2:full2%u.dmp
Dumpfile=dmpdir3:full3%u.dmp,dmpdir4:full4%u.dmp
will create files named full101.dmp,  full201.dmp, full301.dmp, full401.dmp, full102.dmp, full202.dmp, full302.dmp, etc. in a round-robin fashion across the four directories pointed to by the four directory objects.
In parallel mode, the status screen will show four worker processes. (In default mode, only one process will be visible.) All worker processes extract data simultaneously and show their progress on the status screen

INITIALIZATION PARAMETERS
Essentially no tuning is required to achieve maximum Data Pump performance. Initialization parameters should be sufficient out of the box.

• Make sure disk_asynch_io remains TRUE: It has no effect on those platforms whose file systems already support asynchronous I/O, but those that don't are significantly impacted by a value of FALSE.
• db_block_checksum's default value is FALSE, but if an integrity issue is being investigated requiring this to be set to TRUE, its impact on data loading and unloading is minimal; less than 5%.

Export monitoring:
While Data Pump Export (DPE) is running, press Control-C; it will stop the display of the messages on the screen, but not the export process itself. Instead, it will display the DPE prompt as shown below. The process is now said to be in "interactive" mode:

Export>
This approach allows several commands to be entered on that DPE job. To find a summary, use the STATUS command at the prompt:

Export> status
Job: CASES_EXPORT
  Operation: EXPORT                        
  Mode: TABLE                         
  State: EXECUTING                     
  Degree: 1
  Job Error Count: 0
  Dump file:  /u02/dpdata1/expCASES.dmp
      bytes written =  2048

Worker 1 Status:
  State: EXECUTING                     
  Object Schema: DWOWNER
  Object Name: CASES
  Object Type: TABLE_EXPORT/TBL_TABLE_DATA/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 1
  Completed Rows: 4687818

Remember, this is merely the status display. The export is working in the background. To continue to see the messages on the screen, use the command CONTINUE_CLIENT from the Export> prompt.
While Data Pump jobs are running, you can pause them by issuing STOP_JOB on the DPE or DPI prompts and then restart them with START_JOB. This functionality comes in handy when you run out of space and want to make corrections before continuing.

A simple way to gain insight into the status of a Data Pump job is to look into a few views maintained within the Oracle instance the Data Pump job is running. These views are DBA_DATAPUMP_JOBS, DBA_DATAPUMP_SESSIONS, and V$SESSION_LOGOPS. These views are critical in the monitoring of your export jobs so, as we will see in a later article, you can attach to a Data Pump job and modify the execution of the that job.

DBA_DATAPUMP_JOBS
This view will show the active Data Pump jobs, their state, degree of parallelism, and the number of sessions attached.

select * from dba_datapump_jobs

OWNER_NAME JOB_NAME               OPERATION  JOB_MODE   STATE         DEGREE    ATTACHED_SESSIONS
---------- ---------------------- ---------- ---------- ------------- --------- -----------------
JKOOP      SYS_EXPORT_FULL_01     EXPORT     FULL       EXECUTING     1          1
JKOOP      SYS_EXPORT_SCHEMA_01   EXPORT     SCHEMA     EXECUTING     1          1

DBA_DATAPUMP_SESSIONS
This view give gives the SADDR that assist in determining why a Data Pump session may be having problems. Join to the V$SESSION view for further information.

SELECT * FROM DBA_DATAPUMP_SESSIONS

OWNER_NAME JOB_NAME                       SADDR
---------- ------------------------------ --------
JKOOPMANN  SYS_EXPORT_FULL_01             225BDEDC
JKOOPMANN  SYS_EXPORT_SCHEMA_01           225B2B7C

V$SESSION_LONGOPS
This view helps determine how well a Data Pump export is doing. It also shows you any operation that is taking long time to execute.
Basically gives you a progress indicator through the MESSAGE column.

select username, opname, target_desc,
       sofar, totalwork, message 
from V$SESSION_LONGOPS

USERNAME OPNAME               TARGET_DES SOFAR TOTALWORK  MESSAGE
-------- -------------------- ---------- ----- ---------- ------------------------------------------------
JKOOP    SYS_EXPORT_FULL_01   EXPORT       132        132 SYS_EXPORT_FULL_01:EXPORT:132 out of 132 MB done
JKOOP    SYS_EXPORT_FULL_01   EXPORT        90        132 SYS_EXPORT_FULL_01:EXPORT:90 out of 132 MB done
JKOOP    SYS_EXPORT_SCHEMA_01 EXPORT        17         17 SYS_EXPORT_SCHEMA_01:EXPORT:17 out of 17 MB done
JKOOP    SYS_EXPORT_SCHEMA_01 EXPORT        19         19 SYS_EXPORT_SCHEMA_01:EXPORT:19 out of 19 MB done

set serveroutput on size 999999
exec print_table('select * from v$session_longops where sid = 9');

SID                           : 9
SERIAL#                       : 68
OPNAME                        : Transaction Rollback
TARGET                        :
TARGET_DESC                   : xid:0x000e.01c.00000067
SOFAR                         : 10234
TOTALWORK                     : 20554
UNITS                         : Blocks
START_TIME                    : 07-dec-2003 21:20:07
LAST_UPDATE_TIME              : 07-dec-2003 21:21:24
TIME_REMAINING                : 77
ELAPSED_SECONDS               : 77
CONTEXT                       : 0
MESSAGE                       : Transaction Rollback: xid:0x000e.01c.00000067 :
                                 10234 out of 20554 Blocks done
USERNAME                      : SYS
SQL_ADDRESS                   : 00000003B719ED08
SQL_HASH_VALUE                : 1430203031
SQL_ID                        : 306w9c5amyanr
QCSID                         : 0

• Metadata (WRM$)
• Historical data (WRH$)
• AWR tables related to advisor functions (WRI$)

AWR Automated Snapshots

Oracle Database 10g uses a scheduled job, GATHER_STATS_JOB, to collect AWR statistics. This job is created, and enabled automatically, when you create a new Oracle database under Oracle Database 10g. To see this job, use the DBA_SCHEDULER_JOBS view as seen in this example:
SELECT a.job_name, a.enabled, c.window_name, c.schedule_name, c.start_date, c.repeat_interval
FROM dba_scheduler_jobs a, dba_scheduler_wingroup_members b, dba_scheduler_windows c
WHERE job_name='GATHER_STATS_JOB'
  And a.schedule_name=b.window_group_name
  And b.window_name=c.window_name;

You can disable this job using the dbms_scheduler.disable procedure as seen in this example:
Exec dbms_scheduler.disable('GATHER_STATS_JOB');

And you can enable the job using the dbms_scheduler.enable procedure as seen in this example:
Exec dbms_scheduler.enable('GATHER_STATS_JOB');

• Statistics Analysis – the utility checks for stale or missing statistics, which may have a detrimental effect on the query's optimization.
• SQL Profiling – reviews past executions of the SQL statement to provide further information for recommendations.
• Access Path Analysis – determines if additional objects (indexes, materialized views) can be created to improve the statement's performance.
• SQL Structure Analysis – reviews the SQL statement's coding structure to determine if it can be altered to increase performance.

select account_no from accounts where old_account_no = 11

declare
   l_task_id     varchar2(20);
   l_sql         varchar2(2000);
begin
   l_sql := 'select account_no from accounts where old_account_no = 11';
   dbms_sqltune.drop_tuning_task ('FOLIO_COUNT');
   l_task_id := dbms_sqltune.create_tuning_task (
      sql_text  => l_sql,
      user_name  => 'ARUP',
      scope      => 'COMPREHENSIVE',
      time_limit => 120,
      task_name  => 'FOLIO_COUNT'
   );
   dbms_sqltune.execute_tuning_task ('FOLIO_COUNT');
end;
/

set serveroutput on size 999999
set long 999999
spool recommendations.txt
select dbms_sqltune.report_tuning_task ('FOLIO_COUNT') from dual;
spool off;

SET SERVEROUTPUT ON

-- Tuning task created for specific a statement from the AWR.
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          begin_snap  => 764,
                          end_snap    => 938,
                          sql_id      => '19v5guvsgcd1v',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => '19v5guvsgcd1v_AWR_tuning_task',
                          description => 'Tuning task for statement 19v5guvsgcd1v in AWR.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- Tuning task created for specific a statement from the cursor cache.
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '19v5guvsgcd1v',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => '19v5guvsgcd1v_tuning_task',
                          description => 'Tuning task for statement 19v5guvsgcd1v.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- Tuning task created from an SQL tuning set.
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sqlset_name => 'test_sql_tuning_set',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'sqlset_tuning_task',
                          description => 'Tuning task for an SQL tuning set.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- Tuning task created for a manually specified statement.
DECLARE
  l_sql               VARCHAR2(500);
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql := 'SELECT e.*, d.* ' ||
           'FROM   emp e JOIN dept d ON e.deptno = d.deptno ' ||
           'WHERE  NVL(empno, ''0'') = :empno';

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_text    => l_sql,
                          bind_list   => sql_binds(anydata.ConvertNumber(100)),
                          user_name   => 'scott',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'emp_dept_tuning_task',
                          description => 'Tuning task for an EMP to DEPT join query.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

select account_no from accounts a 
where account_name = 'HARRY' 
and sub_account_name not in 
  ( select account_name from accounts 
    where account_no = a.old_account_no and status is not null);

1- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  The optimizer could not unnest the subquery at line ID 1 of the execution plan.

  Recommendation
  --------------
    Consider replacing "NOT IN" with "NOT EXISTS" or ensure that columns used
    on both sides of the "NOT IN" operator are declared "NOT NULL" by adding
    either "NOT NULL" constraints or "IS NOT NULL" predicates.

  Rationale
  ---------
    A "FILTER" operation can be very expensive because it evaluates the
    subquery for each row in the parent query. The subquery, when unnested can
    drastically improve the execution time because the "FILTER" operation is
    converted into a join. Be aware that "NOT IN" and "NOT EXISTS" might
    produce different results for "NULL" values.

begin
   dbms_sqltune.accept_sql_profile (
      task_name   => 'FOLIO_COUNT',
      name        => 'FOLIO_COUNT_PROFILE'
      description => 'Folio Count Profile',
      category    => 'FOLIO_COUNT');
end;

• Database Buffer cache (The Default pool) (db_cache_size)
• Shared pool (shared_pool_size)
• Large pool (large_pool_size)
• Java pool (java_pool_size)

• Log Buffer
• Other Buffer Caches (KEEP/RECYCLE, other   block sizes)
• Streams Pool (new in Oracle Database 10g)
• Fixed SGA and other internal allocations

begin
   dbms_stats.restore_table_stats (
      'ARUP',
      'REVENUE',
      '31-MAY-04 10.00.00.000000000 PM -04:00');
end;

SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         31

SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------
17-MAY-04 03.21.33.594053000 PM -04:00

execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (45)

• output specifies the file where the output is generated. When this option is not specified, the standard output is used for the output.
• session consolidates the trace information for the session specified. The session Id is a combination of session index and session serial number.
• clientid consolidates the trace information given client Id.
• service consolidates the trace information for the given service name.
• action consolidates the trace information for the given action name.
• module consolidates the trace information for the given module name.
• trace_files is a list of all trace file names, separated by spaces, in which trcsess will look for trace information. The wild card character * can be used to specify the trace file names. If trace files are not specified, all the files in the current directory are checked by trcsess.

• Helps the debugging of performance problems in multi-tier environments. In multi-tier environments, a request from an end client is routed to different database sessions by the middle tier.
• Proper accounting of workload for applications using services. Service Name, Module, and Action name provide a means to set apart important transactions in an application.

• client_id_stat_enable: Used to enable statistics accumulation for a given client identifier
• client_id_stat_disable: Used to disable statistics accumulation for a given client identifier
• serv_mod_act_stat_enable: Used to enable statistics accumulation for a given hierarchy of service name, module name, and action name
• serv_mod_act_stat_disable: Used to disable statistics accumulation for a given hierarchy of service name, module name, and action name

• sql_trace—Shows (TRUE/FALSE) if SQL tracing has been enabled in the session
• sql_trace_waits—If session tracing is enabled, you can have the trace write wait information to the trace file; very useful in diagnosing performance issues.
• sql_trace_binds—If the session uses bind variables, you can have the trace write the bind variable values to the trace file. This column shows TRUE/FALSE.

• New columns in the v$session and v$session_wait views that track the resources sessions are waiting for.
• A history of waits per session, enabling diagnosis of performance problems for a desired time frame and period.
• Maintaining wait statistics of each SQL statement in the library cache
• Histograms of wait durations, rather than a simple accumulated average

How To Drop, Create And Recreate DB Control In 10g Database

A.Delete DB Control Objects:
There are several ways to delete DB Control Objects.
1).Delete DB Control Configuration Files and Repository Objects using EMCA
2).Delete DB Control Configuration Files using EMCA scripts
3).Delete DB Control Configuration Files Manually:
4).Delete DB Control Repository Objects using RepManager
5).Manually clean everything

1).Delete DB Control Configuration Files and Repository Objects using EMCA
In 10.1 run,   
emca -x SID
RepManager hostname listener_port sid -action drop

In 10.2 run,:
emca -deconfig dbcontrol db -repos drop

2).Delete DB Control Configuration Files using EMCA scripts
In 10.1g run, $emca -x sid
In 10.2g run, $emca -deconfig dbcontrol db Then enter sid as prompt and then y.

3).Delete DB Control Configuration Files Manually:
Remove the following directories from your filesystem:
$ORACLE_HOME/hostname_sid
ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_hostname_sid

On Windows you also need to delete the DB Console service:
- run regedit
- navigate to HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services
- locate the OracleDBConsole entry and delete it

On, Windows XP and Windows Server 2003 you can run the following from the command line:
'sc delete service_name'
- where service_name is the DB Control service name (typically: OracleDBConsolesid)

Connect as SYSMAN to SQL PLus
C:\>sqlplus sysman
select substr(target_name,1,20) target_name, substr(target_type,1,17) target_type, substr(emd_url,1,40) emd_url
  from mgmt_targets;
TARGET_NAME                        TARGET_TYPE       EMD_URL
---------------------------------  ----------------- ----------------------------------------
machinename.aa.something:1831       oracle_emd       http://machinename.aa.something:1831/emd/main
machinename.aa.something            host             http://machinename.aa.something:1831/emd/main
UTF8                                oracle_database  http://machinename.aa.something:1831/emd/main
LISTENER1_seny-10-79.be.sterianet   oracle_listener  http://machinename.aa.something:1831/emd/main
Management Services and Repository  oracle_emrep

or
TARGET_NAME          TARGET_TYPE       EMD_URL
-------------------- ----------------- ----------------------------------------
machinename.domain   oracle_emd        http://machinename.domain:3938/
machinename.domain   host              http://machinename.domain:3938/
DEV10g2              oracle_database   http://machinename.domain:3938/
LISTENER_mach.domain oracle_listener   http://machinename.domain:3938/
Management Services  oracle_emrep

Now I purge the obsolete entries:
exec mgmt_admin.delete_target('target_name','target_type');
SQL> exec mgmt_admin.delete_target('machinename.aa.something','host');
SQL> exec mgmt_admin.delete_target('LISTENER1_machinename.aa.something','oracle_listener');
SQL> exec mgmt_admin.delete_target('UTF8','oracle_database');
SQL> exec mgmt_admin.delete_target('machinename.aa.something:1831','oracle_emd');

4).Delete DB Control Repository using RepManager:
Invoke following command:
RepManager hostname listener_port sid -action drop

But don't delete by RepManager. It puts the database in quiescence mode.

3 - Connect as SYS to SQLPLus and:
drop user SYSMAN cascade;
drop role mgmt_user;
drop user mgmt_view cascade;
drop public synonym mgmt_target_blackouts;
drop public synonym setemviewusercontext;
drop public synonym mgmt_current_availability;
drop public synonym mgmt_availability;

4- Now take notes of you listener status, host information, network cards,  and all the names (like: Server Name, SID, etc)
lsnrctl status
more /etc/hosts
/sbin/ifconfig

5- Check that the variables
echo $ORACLE_SID
echo $ORACLE_HOME
echo $ORACLE_HOSTNAME

6- Now you are ready to configure all again:
On 10.2  emca -config dbcontrol db -repos create

B.Create DB Control Objects
1)Create only DB Control configuration files:
on 10.1   emca -r
On 10.2  emca -config dbcontrol db

2)Create both the DB Control Repository Objects and Configuration Files:
On 10.1  emca
On 10.2  emca -config dbcontrol db -repos create

STARTED EMCA at Apr 25, 2008 6:25:26 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Enter the following information:
Database SID: DIE
Database Control is already configured for the database DIE
You have chosen to configure Database Control for managing the database DIE
This will remove the existing configuration and the default settings and perform a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]: Y
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):

You have specified the following settings
Database ORACLE_HOME ................ C:\oracle\product\10.2.0\db_1
Database hostname ................ CONCORD
Listener port number ................ 1521
Database SID ................ DIE
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

Do you wish to continue? [yes(Y)/no(N)]: Y
Apr 25, 2008 6:26:29 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at C:\oracle\product\10.2.0\db_1\cfgtoollog
s\emca\DIE\emca_2008-04-25_06-25-25-PM.log.
Apr 25, 2008 6:26:44 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Apr 25, 2008 6:26:53 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Apr 25, 2008 6:30:54 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Apr 25, 2008 6:31:49 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Apr 25, 2008 6:32:39 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Apr 25, 2008 6:32:44 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://CONCORD:1158/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 25, 2008 6:32:44 PM

Now wait about 10 Minutes to complete!

Try to connect to the database Control:
http://server_name:1158/em                   For Oracle Enterprise Manager
http://server_name:5560/isqlplus            For iSQL*Plus
http://server_name:5620/ultrasearch       For Ultrasearch

If you have problems to connect, check the local configuration file located on:
$ORACLE_HOME/<hostname>_<service_name>/sysman/config/emoms.properties

Start and Stop the DB-Console
$ emctl start dbconsole
$ emctl stop dbconsole
$ emctl status dbconsole

C.Recreate/ReConfig DB Control:
1)Recreate only DB Control configuration files:
$ emca -config dbcontrol db

2)Recreate both the DB Control Repository Objects and Configuration Files:
$ emca -config dbcontrol db -repos recreate

References :

    BLOCKS
----------
         8

    BLOCKS
----------
         2

Optional Clauses

-- Both clauses present.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status);

-- No matched clause, insert only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status);

-- No not-matched clause, update only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status;

-- Both clauses present.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
    WHERE  b.status != 'VALID'
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status)
    WHERE  b.status != 'VALID';

-- No matched clause, insert only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status)
    WHERE  b.status != 'VALID';

-- No not-matched clause, update only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
    WHERE  b.status != 'VALID';

MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
    WHERE  b.status != 'VALID'
  DELETE WHERE (b.status = 'VALID');

--Whitespace Support in Windows Path and File Names

SPOOL "My Report.txt"
@"My Report.sql"

• _DATE - Contains the current date (dynamic) by default or else a fixed string
• _PRIVILEGE - Contains privilege level such as AS SYSDBA, AS SYSOPER or blank.
• _USER - Contains the current username (like SHOW USER).
• _CONNECT_IDENTIFER contains the connection identifer used to connect
  

SET FEED OFF
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
SET FEED ON
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER _DATE> "

-- SHOW RECYCLEBIN

show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
BONUS            BIN$F5d+By1uRvieQy5o0TVxJA==$0 TABLE        2004-03-23:11:03:38
DEPT             BIN$Ie1ifZzHTV6bDhFraYImTA==$0 TABLE        2004-03-23:11:03:38
EMP              BIN$Vu5i5jelR5yPGTP2M99vgQ==$0 TABLE        2004-03-23:11:03:38
SALGRADE         BIN$L/27VyBRRP+ZGWnZylVbZg==$0 TABLE        2004-03-23:11:03:38
TEST1            BIN$0lObShnuS0+6VS1cvLny0A==$0 TABLE        2004-03-24:15:38:42

show recyclebin test1
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST1            BIN$0lObShnuS0+6VS1cvLny0A==$0 TABLE        2004-03-24:15:38:42

utl_i18n.string_to_raw (p_in_val, 'AL32UTF8');

typ => dbms_cryptio.encrypt_aes256 + 
       dbms_cryptio.chain_cbc + 
	   dbms_cryptio.pad_pkcs5

utl_i18n.raw_to_char (l_enc_val, 'AL32UTF8')

Oracle10G's DBMS_FILE_TRANSFER PL/SQL package provides administrators with a mechanism to copy binary files between Oracle databases without using OS commands or FTP.   The transfer package can be executed locally to transfer files to another database server or can be executed remotely to transfer files between two remote databases.  Currently, the only files that can be copied using this mechanism are Data Pump dump sets and tablespace data files.  In addition, the file size must be a multiple of 512 bytes and less than 2 terabytes.  Using the file transfer package in conjunction with Oracle's transportable tablespace feature allows administrators to totally automate tablespace data transfers from one database to another.   The process to unplug tablespace data files from the source database, copy the files to the destination server and plug the tablespace data files into the target database can now be executed on a recurring basis by batch jobs initiated by DBMS_JOBS, OEM, KRON, AT and third-party schedulers. The transferred files created on the target platforms are owned by the Oracle account and can be accessed by all database processes. For long copy operations, progress is displayed in the V$SESSION_LONGOPS view

In previous release of Oracle, all parameters were considerd equally important. This made the administration and tuning of the database very difficult because database administrators need to become familiar with over 200 parameters. Oracle 10g introduces two classes of parameter: basic and advanced. In most cases, you need only set up the basic parameters for an Oracle 10g instance.

These basic parameters include:

• cluster_database
• compatible
• control_files
• db_block_size
• db_create_file_dest
• db_create_online_log_dest_n
• db_domain
• db_name
• db_recovery_file_dest
• db_recovery_file_dest_size
• instance_number
• job_queue_processes
• log_archive_dest_n
• log_archive_dest_state_n
• nls_language
• nls_territory
• open_cursors
• pga_aggregate_target
• processes
• remote_listener
• remote_login_passwordfile
• rollback_segments
• sessions
• sga_target
• shared_servers
• star_transformation_enabled
• undo_management
• undo_tablespace

The following is an example of the parameter file generated by DBCR utility (The basic parameters are in bold):

###########################################
# Archive
###########################################
log_archive_dest_1='LOCATION=/u03/arch/grid'
log_archive_format=%t_%s_%r.dbf

###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_cache_size=25165824
db_file_multiblock_read_count=16

###########################################
# Cursors and Library Cache
###########################################
open_cursors=300

###########################################
# Database Identification
###########################################
db_domain=world
db_name=grid

###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=/u01/app/oracle/admin/grid/bdump
core_dump_dest=/u01/app/oracle/admin/grid/cdump
user_dump_dest==/u01/app/oracle/admin/grid/udump
timed_statistics=TRUE

###########################################
# File Configuration
###########################################
control_files=("/u02/ctl/grid/control01.ctl", "/u02/ctl/grid/control02.ctl")

###########################################
# Job Queues
###########################################
job_queue_processes=10

###########################################
# Compatibility
###########################################
compatible=10.1.0.0.0

###########################################
# Optimizer
###########################################
query_rewrite_enabled=FALSE
star_transformation_enabled=FALSE

###########################################
# Pools
###########################################
java_pool_size=104857600
large_pool_size=8388608
shared_pool_size=104857600

###########################################
# Processes and Sessions
###########################################
processes=150

###########################################
# Redo Log and Recovery
###########################################
fast_start_mttr_target=300

###########################################
# Security and Auditing
###########################################
remote_login_passwordfile=EXCLUSIVE

###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=25165824
sort_area_size=524288

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_retention=10800
undo_tablespace=UNDOTBS1

In Oracle9i (9.2.0.3), there are 258 parameters in the v$parameter view. In Oracle 10g (10.1.0.0), there are more than 250 parameters in the v$parameter view. There are 233 parameters in both Oracle9i Release 2 and Oracle 10g Release 1.

Old Parameters

Twenty-five of the 258 parameters no longer exist in Oracle 10g's v$parameter view; these are:

New Parameters

There are twenty more new parameters in Oracle 10g Release 1:

V$SQLSTATS Performance View
Before we discuss the new V$SQLSTATS view, let's review some tuning information. V$SQLAREA is one of the best SQL tuning views. I use the two queries below to identify poorly performing SQL. I take the traditional "top down" tuning approach and start tuning the highest resource consuming SQL idenfified by the scripts below.

The following query dentifies the SQL responsible for the most disk reads:

SELECT disk_reads, executions, disk_reads/executions, hash_value, sql_text
  FROM v$sqlarea
  WHERE disk_reads > 5000
  ORDER BY disk_reads;

The following query dentifies the SQL responsible for the most buffer hits:

SELECT buffer_gets, executions, buffer_gets/executions, hash_value, sql_text
  FROM v$sqlarea
  WHERE buffer_gets > 100000
  ORDER BY buffer_gets;

You can create a more readable report in SQLPLUS by inserting report breaks between the output lines. To generate the report breaks in SQLPLUS, issue the following statement before running the query:

BREAK ON disk_reads SKIP 2 --- for the disk read report and
BREAK ON buffer_gets SKIP 2 --- for the buffer get report

It's common knowledge that poorly performing SQL is responsible for the majority of database performance problems. The first query returns SQL statements responsible for generating disk reads greater than 5,000 while the second query returns SQL statements responsible for generating buffer reads greater than 100,000. These are good numbers to start with and you can adjust them according to the size of the system you are tuning. You'll notice that I divide the number of disk and buffer reads by the number of statement executions. If a statement is generating 1,000,000 disk reads but is executed 500,000 times, it probably doesn't need tuning. Heavy disk reads per statement execution usually means a lack of proper indexing. Heavy buffer reads usually means the exact opposite - indexes are being used when they shouldn't be.

But the SQLTEXT column in V$SQLAREA does not provide the entire text of the SQL statement. That's why I include the HASH_VALUE column in the report. I can use that value to dump the entire SQL statement from V$SQLTEXT using the statement below (where xxxxxxxx is the value in the HASH_VALUE column from the V$SQLAREA reports above):

SELECT sql_text FROM v$sqltext WHERE hash_value = 'xxxxxxxxx' ORDER BY piece;

Oracle 10G R2 provides a new view called V$SQLSTATS that contains a combination of columns that appear in V$SQL and V$SQLAREA. The benefits that V$SQLSTATS provides are as follows:

• Since V$SQLSTATS contains the entire text of the SQL statement AND its associated performance statistics, we are no longer required to access both the V$SQLTEXT and V$SQLAREA to obtain the information we need.
• Oracle states that V$SQLSTATS is faster and more scalable.
• The data in V$SQLAREA has a tendency to get its contents flushed out just when you need to get additional information from it. The V$SQLSTATS view provides users with a longer access window.

SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
ACCOUNT          BIN$GJBH/nI3QIuQZWOapqJXHg==$0 TABLE        2007-10-17:09:42:45
CUSTOMER         BIN$qXjGDja/S2iCMedZ7wu3vg==$0 TABLE        2007-10-17:09:42:46
BANK             BIN$MFryFUanRRS2EVQD/hl9iQ==$0 TABLE        2007-11-09:09:42:45

This shows the original name of the table, BANK, as well as the new name in the recycle bin, which has the same name as the new table we saw created after the drop. (Note: the exact name may differ by platform.) To reinstate the table, all you have to do is use the FLASHBACK TABLE command:

SQL> FLASHBACK TABLE BANK TO BEFORE DROP;
FLASHBACK COMPLETE.

SQL> SELECT * FROM TAB;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BANK                           TABLE

Voila! The table is reinstated effortlessly. If you check the recycle bin now, it will be empty.

5) Flashback Drop (Recycle Bin)
Dropping of objects by accident has always been a problem for users and DBAs alike. Users soon realize their mistake but then it's too late and historically there is no easy way to recover those dropped tables, indexes, constraints, triggers, etc.
Flashback Drop provides a safety net when dropping objects in Oracle Database 10g. When a user drops a table, Oracle automatically places it into the Recycle Bin.

What is the Recycle Bin?
The Recycle Bin is a virtual container where all dropped objects reside. Underneath the covers, the objects are occupying the same space as when they were created. If table EMP was created in the USERS tablespace, the dropped table EMP remains in the USERS tablespace. Dropped tables and any associated objects such as indexes, constraints, nested tables, and other dependant objects are not moved, they are simply renamed with a prefix of BIN$$. You can continue to access the data in a dropped table or even use Flashback Query against it.
FLASHBACK TABLE dept_test TO BEFORE DROP;

Remember, placing tables in the recycle bin does not free up space in the original tablespace. To free the space, you need to purge the bin using:
PURGE RECYCLEBIN;

Managing the Recycle Bin
If the tables are not really dropped in this process--therefore not releasing the tablespace--what happens when the dropped objects take up all of that space? The answer is simple: that situation does not even arise. When a tablespace is completely filled up with recycle bin data such that the datafiles have to extend to make room for more data, the tablespace is said to be under "space pressure." In that scenario, objects are automatically purged from the recycle bin in a first-in-first-out manner. The dependent objects (such as indexes) are removed before a table is removed. Similarly, space pressure can occur with user quotas as defined for a particular tablespace. The tablespace may have enough free space, but the user may be running out of his or her allotted portion of it. In such situations, Oracle automatically purges objects belonging to that user in that tablespace. In addition, there are several ways you can manually control the recycle bin. If you want to purge the specific table named TEST from the recycle bin after its drop, you could issue
PURGE TABLE TEST;

6) Flashback Database
We have talked abt object recovery before this, now lets talk what if something happens to database, this quickly rewinds the complete database to a previous time, to correct any logical data corruption.This can be used with both RMAN & SQL*Plus. Some of the options are :
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-(1/24/12);
FLASHBACK DATABASE TO TIMESTAMP my_date;
FLASHBACK DATABASE TO BEFORE TIMESTAMP my_date;
FLASHBACK DATABASE TO SCN my_scn
After the system comes back with FLASHBACK COMPLETE, open the database with Resetlogs. Eg :
-- Create a dummy table.
CREATE TABLE flashback_database_test (id  NUMBER(10)

-Flashback 5 Minutes
CONN sys/password AS SYSDBA
SHUTDOWN IMMEDIATE
STARTUP MOUNT EXCLUSIVE
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-(1/24/12);
ALTER DATABASE OPEN RESETLOGS;

- Check that the table is gone.
DESC flashback_database_test

Querying Changes to a Table
In this example, I have used a bank's foreign currency management application. The database has a table called RATES to record exchange rate on specific times.

SQL> desc rates
 Name              Null?    Type
 ----------------- -------- ------------
 CURRENCY                   VARCHAR2(4)
 RATE                       NUMBER(15,10)