• 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

ATTACH Attach the client session to existing data pump jobs
CONTENT Specify what to export(ALL, DATA_ONLY, METADATA_ONLY)
DIRECTORY Location to write the dump file and log file.
ESTIMATE Show how much disk space each table in the export job consumes.
ESTIMATE_ONLY It estimate the space, but does not perform export
EXCLUDE List of objects to be excluded
INCLUDE List of jobs to be included
JOB_NAME Name of the export job
KEEP_MASTER Specify Y not to drop the master table after export
NETWORK_LINK Specify dblink to export from remote database
NOLOGFILE Specify Y if you do not want to create log file
PARALLEL Specify the maximum number of threads for the export job
VERSION DB objects that are incompatible with the specified version will not be exported.
ENCRYPTION_PASSWORD The table column is encrypted, then it will be written as clear text in the dump file set when the password is not specified. We can define any string as a password for this parameter.
COMPRESSION Specifies whether to compress metadata before writing to the dump file set. The default is METADATA_ONLY. We have two values(METADATA_ONLY,NONE). We can use NONE if we want to disable during the expdp.
SAMPLE - Allows you to specify a percentage of data to be sampled and unloaded from the source database. The sample_percent indicates the probability that a block of rows will be selected as part of the sample.

In all this cases I will be using ORCL as the original DB, and DEST as the destination DB.

Scenario1 Export the whole ORCL database.
expdp userid=system/password@ORCL dumpfile=expfulldp.dmp logfile=expfulldp.log full=y directory=dumplocation

Scenario2 Export the scott schema from ORCL and import into DEST database.
expdp userid=system/password@ORCL dumpfile=schemaexpdb.dmp logfile=schemaexpdb.log directory=dumplocation schemas=scott
impdp userid=system/password@DEST dumpfile=schemaexpdb.dmp logfile=schemaimpdb.log directory=dumplocation

Another Example: While import, exclude some objects(sequence,view,package,cluster,table). Load the objects which came from RES tablespace into USERS tablespace in target database.
impdp userid=system/password@DEST dumpfile=schemaexpdb.dmp logfile=schemaimpdb.log directory=dumplocation table_exists_action=replace remap_tablespace=res:users exclude=sequence,view,package,cluster,table:"in('LOAD_EXT')"

Scenario 3 Clone a User
In the past when a DBA had the need to create a new user with the same structure (All objects, tablespaces quota, synonyms, grants, system privileges, etc) was a very painful experience, now all can be done very easily using Data Pump, let use as an example that you want to create the user ”Z” exactly like the user “A”, to achieve this goal all you will need to do is first export the schema “A” definition and then import it again saying to the Data Pump to change the schema “A” for the new schema named “Z” using the “remap_schema” parameter available with impdp.
expdp user/password schemas=A directory=datapump dumpfile=Schema_A.dmp                 [optional: content=metadata_only]
impdp user/password remap_schema=A:Z directory=datapump dumpfile= Schema_A.dmp
And your new user Z is now created like your existing user A , that easy!

Scenario 4 Create a Metadata File
You can generate a SQL File from an existing exported file. As an Example, I am going to expdp the Schema Fraudguard, after that, I will use the impdp command with the sqlfile option to generate a sql containing all the objects that I already exported:
expdp system schemas=fraudguard content=metadata_only directory=EXPORTPATH dumpfile=metadata_24112010.dmp
impdp system directory=EXPORTPATH dumpfile= metadata_24112010.dmp sqlfile=metadata_24112010.sql

Scenario5 Export the emp table from scott schema at ORCL instance and import into DEST instance.
expdp userid=system/password@ORCL logfile=tableexpdb.log directory=dumplocation tables=scott.part_emp dumpfile=tableexpdb.dmp
impdp userid=system/password@DEST dumpfile=tableexpdb.dmp logfile=tabimpdb.log directory=dumplocation table_exists_action=REPLACE

Scenario 6 Create smaller Copies of PROD
That is a very common task for a DBA, you have a task to create a copy of your Database (for development or test purpose) but your destination server don’t have enough space to create a full copy of it!
This can be easily solved with Data Pump, for this example, let say that you only have space for 70% of your production database, now to know how to proceed, we need to decide if the copy will contain metadata only (no data/rows) or if it will include the data also. Let’s see how to do each way:

a) Metadata Only
First do a full export of your source database.
expdp user/password content=metadata_only full=y directory=datapump dumpfile=metadata_24112010.dmp

Then, let’s import the metadata and tell the Data Pump to reduce the size of extents to 70%, you can do it using the parameter “transform” available with “impdp”, it represent the percentage multiplier that will be used to alter extent allocations and datafiles size.
impdp user/password transform=pctspace:70 directory=datapump dumpfile=metadata_24112010.dmp

b) Metadata and data
First does a full export of your source database using the export parameter “sample”, this parameter specify a percentage of the data rows to be sampled and unload from your source database, in this case let’s use 70%.
expdp user/password sample=70 full=y directory=datapump dumpfile=expdp_70_24112010.dmp

Then, all you need to do as the example before is to import it telling the Data Pump to reduce the size of extents to 70%, and that’s it!
impdp user/password transform=pctspace:70 directory=datapump dumpfile=expdp_70_24112010.dmp

Scenario 7 Export only specific partition in emp table from scott schema at orcl and import into ordb database.
expdp userid=system/password@ORCL dumpfile=partexpdb.dmp logfile=partexpdb.log directory=dumplocation tables=scott.part_emp:part10,scott.part_emp:part20

If we want to overwrite the exported data in target database, then we need to delete emp table for deptno in(10,20).
scott@DEST> delete part_emp where deptno in (10,20);
scott@DEST> commit;
impdp userid=system/password@DEST dumpfile=partexpdb.dmp logfile=tabimpdb.log directory=dumplocation table_exists_action=append

Scenario 8 Export only tables (no code) in scott schema at ORCL and import into DEST database
expdp userid=system/password@ORCL dumpfile=schemaexpdb.dmp logfile=schemaexpdb.log directory=dumplocation include=table schemas=scott
impdp userid=system/password@DEST dumpfile=schemaexpdb.dmp logfile=schemaimpdb.log directory=dumplocation table_exists_action=replace

Scenario 9 Export only rows belonging to department 10 and 20 in emp and dept table from ORCLdatabase. Import the dump file in @DESTdatabase. While importing, load only deptno 10 in target database.
expdp userid=system/password@ORCL dumpfile=data_filter_expdb.dmp logfile=data_filter_expdb.log directory=dumplocation content=data_only schemas=scott include=table:"in('EMP','DEPT')" query="where deptno in(10,20)"

impdp userid=system/password@DEST dumpfile=data_filter_expdb.dmp logfile=data_filter_impdb.log directory=dumplocation schemas=scott query="where deptno = 10" table_exists_action=APPEND

Scenario 10 Export the scott schema from ORCLdatabase and split the dump file into 50M sizes. Import the dump file into DEST datbase.
Expdp parfile content:
userid=system/password@ORCL
logfile=schemaexp_split.log
directory=dumplocation
dumpfile=schemaexp_split_%U.dmp
filesize=50M
schemas=scott
include=table

As per the above expdp parfile, initially, schemaexp_split_01.dmp file will be created. Once the file is 50MB, the next file called schemaexp_split_02.dmp will be created. Let us say, the dump file size is 500MB, then it creates 10 dump file as each file size is 50MB.
Impdp parfile content:
userid=system/password@DEST
logfile=schemaimp_split.log
directory=dumplocation
dumpfile=schemaexp_split_%U.dmp
table_exists_action=replace
remap_tablespace=res:users
exclude=grant

Scenario 11 Export the scott schema from ORCL database and split the dump file into four files. Import the dump file into DEST datbase.
Expdp parfile content:
userid=system/password@ORCL
logfile=schemaexp_split.log
directory=dumplocation
dumpfile=schemaexp_split_%U.dmp
parallel=4
schemas=scott
include=table

As per the above parfile content, initially four files will be created - schemaexp_split_01.dmp, schemaexp_split_02.dmp, schemaexp_split_03.dmp, schemaexp_split_04.dmp. Notice that every occurrence of the substation variable is incremented each time. Since there is no FILESIZE parameter, no more files will be created.
Impdp parfile content:
userid=system/password@DEST
logfile=schemaimp_split.log
directory=dumplocation
dumpfile=schemaexp_split_%U.dmp
table_exists_action=replace
remap_tablespace=res:users
exclude=grant

Scenario 12 Export the scott schema from ORCL database and split the dump file into three files.
The dump files will be stored in three different location. This method is especially useful if you do not have enough space in one file system to perform the complete expdp job. After export is successful, import the dump file into DEST database.
Expdp parfile content:
userid=system/password@ORCL
logfile=schemaexp_split.log
directory=dumplocation
dumpfile=dump1:schemaexp_%U.dmp,dump2:schemaexp_%U.dmp,dump3:schemaexp_%U.dmp
filesize=50M
schemas=scott
include=table

As per above expdp par file content, it place the dump file into three different location. Let us say, entire expdp dump file size is 1500MB. Then it creates 30 dump files(each dump file size is 50MB) and place 10 files in each file system.
Impdp parfile content:
userid=system/password@DEST
logfile=schemaimp_split.log
directory=dumplocation
dumpfile=dump1:schemaexp_%U.dmp,dump2:schemaexp_%U.dmp,dump3:schemaexp_%U.dmp
table_exists_action=replace


Scenario 13 Expdp scott schema in ORCL and impdp the dump file in training schema in DEST database.
expdp userid=scott/tiger@ORCL logfile=netwrokexp1.log directory=dumplocation dumpfile=networkexp1.dmp schemas=scott include=table

impdp userid=system/password@DEST logfile=networkimp1.log directory=dumplocation dumpfile=networkexp1.dmp table_exists_action=replace remap_schema=scott:training

Scenario 14 Expdp table on ORCL database and imdp in DEST. When we export the data, export only 20 percent of the table data. We use SAMPLE parameter to accomplish this task.
SAMPLE parameter allows you to export subsets of data by specifying the percentage of data to be sampled and exported. The sample_percent indicates the probability that a block of rows will be selected as part of the sample. It does not mean that the database will retrieve exactly that amount of rows from the table. The value you supply for sample_percent can be anywhere from .000001 up to, but not including, 100.
If no table is specified, then the sample_percent value applies to the entire export job. The SAMPLE parameter is not valid for network exports.
expdp userid=system/password@ORCL dumpfile=schemaexpdb.dmp logfile=schemaexpdb.log directory=dumplocation tables=scott.part_emp SAMPLE=20

As per the above expdp parfile, it exports only 20 percent of the data in part_emp table.
impdp userid=system/password@DEST dumpfile=schemaexpdb.dmp logfile=schemaimpdb.log directory=dumplocation table_exists_action=replace

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

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%.

Monitoring Export:
While Data Pump Export 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 Data Pump Export 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 Data Pump Export 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 Data Pump Export or Data Pump Import 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 and they are critical in the monitoring of your export jobs so, 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');

• awrrpt.sql, awrrpti.sql:  Generate report for pairs of snapshot ids. awrrpti.sql allows you to pick a database, awrrpt.sql defaults to the current database
• awrddrpt.sql, awrddrpi.sql: Generates a comparison report between two AWR reports.  Can be for single database (compare prod at 2pm to prod at 4pm) or for two different databases (compare prod at 2pm to test at 2pm)
• awrsqrpt.sql, awrsqrpi.sql: Generates a report for a single SQL statement over multiple snapshots.  Report on plans used, resources used.
  
• awrinfo.sql, (must be connected as sys): To review general AWR information.  Tablespace usage, Schema/option usage, Segment usage, snapshot information, retention policies and so on.
• awrextr.sql, awrload.sql, (must be connected as sys) : Datapump exports/imports AWR data so you can move it to another system for analysis/maintaining a longer history.
• awrgrpti.sql, awrgrpt.sql: Generate RAC aware reports by choosing specific instances to include – or to use all available instances
• awrgdrpt.sql, awrgdrpi.sql: Generate RAC aware time period comparison reports – within a set of database instances or across databases
• ashrpt.sql, ashrpti.sql: Generates an ASH report … filter by
  Session
  SQL’s
  Wait Classes
  Database Services
  Modules/Actions/Client_ID
  PL/SQL entry point
  Shows major waits, top services/modules, top clients, SQL activity (parsing, executions, etc), top SQL and more…
• ashrpti.sql: Generates an ASH report.  The “I” version filters by
  Session
  SQL’s
  Wait Classes
  Database Services
  Modules/Actions/Client_ID
  PL/SQL entry point
  Shows major waits, top services/modules, top clients, SQL activity (parsing, executions, etc), top SQL and more…
  

• 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.

• •Create a new SQL tuning task using the function Create_Tuning_Task
• •Execute the SQL tuning task using the procedure Execute_Tuning_Task
•   Review the tuning recommendations using the function Report_Tuning_Task

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  => 'SCOTT',
      scope      => 'COMPREHENSIVE',
      time_limit => 120,
      task_name  => 'FOLIO_COUNT' );
   DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);     
   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 a Specific SQL
------------------------------------------------------------------------------
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 ('TUNE_ACCOUNT');
   l_task_id := dbms_sqltune.create_tuning_task (
      sql_text  => l_sql,
      user_name  => 'SCOTT',
      scope      => 'COMPREHENSIVE',
      time_limit => 120,
      task_name  => 'TUNE_ACOUNT' );
   DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);  
   dbms_sqltune.execute_tuning_task ('TUNE_ACOUNT');
end;
/

------------------------------------------------------------------------------
-- Tuning task created for a Specific SQL sending parameters
------------------------------------------------------------------------------
DECLARE
  l_sql               VARCHAR2(500);
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql := 'SELECT e.*, d.* FROMM emp e , dept d WHERE e.deptno = d.deptno  AND 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);  
  DBMS_SQLTUNE.execute_tuning_task('EMP_DEPT_TUNING_TASK');END;
/

------------------------------------------------------------------------------
-- 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);
  DBMS_SQLTUNE.execute_tuning_task('19v5guvsgcd1v_AWR_TUNING_TASK');
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 in Memory');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
  DBMS_SQLTUNE.execute_tuning_task('19v5guvsgcd1v_TUNING_TASK');
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);
  DBMS_SQLTUNE.execute_tuning_task('SQLSET_TUNING_TASK');
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');

ALTER SESSION ENABLE PARALLEL DML;

MERGE /*+ first_rows parallel(test) parallel(test2) */ INTO test
USING test5 new ON (test.pk = new.pk)
WHEN MATCHED THEN UPDATE SET
        fk = new.fk, fill = new.fill;

--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.