Oracle EventsTracing

Initialization parameter:
EVENT="\
10210 trace name context forever, level 10:\
10211 trace name context forever, level 10:\
10231 trace name context forever, level 10:\
10232 trace name context forever, level 10"

You can specify almost all EVENT settings at the session level using the ALTER SESSION command or a call to the DBMS_SYSYTEM.SET_EV( ) procedure; doing so

does not require an instance bounce for the EVENT to take effect.

grant alter session to test;
alter session set events '10032 trace name context forever';

GRANT EXECUTE ON DBMS_SYSTEM TO username; --Not recommended to grant system privilege to user
CREATE PUBLIC SYNONYM dbms_system FOR dbms_system;

EXECUTE SYS.dbms_system.set_ev (42, 45529,10046,12,'');
or
EXECUTE SYS.dbms_system.set_sql_trace_in_session (42, 45529, TRUE);


In SPFILE:
------------
ALTER SYSTEM SET event='10235 trace name context forever,
level 2','27072 trace name errorstack level 3' COMMENT='TEST' SCOPE=SPFILE;

ALTER SYSTEM RESET EVENT SCOPE=SPFILE SID='*' ; //Remove all events

In MEMORY:
----------------
ALTER SYSTEM SET events='10235 trace name context forever,
level 2:27072 trace name errorstack level 3';

{ alter system set event = | alter session set events [=] }
" trace name context {forever, level | off}"

alter session set events [=] {
"immediate trace name
{ heapdump | blockdump | treedump | controlf | systemstate | buffers } level "
| " trace name errorstack level [; name processstate level ]"
}

{ alter system set event = | alter session set events [=] }
" trace name context {forever, level | off}"

alter session set events [=] {
"immediate trace name
{ heapdump | blockdump | treedump | controlf | systemstate | buffers } level "
| " trace name errorstack level [; name processstate level ]"
}

Script to check which events are set:

declare
lvl number;
begin
for n in 10000..10999 loop
 sys.dbms_system.read_ev(n,lvl);
 if (lvl > 0) then
 dbms_output.put_line('Event: ' || to_char(n) || ', Level: '
                      || to_char(lvl ));
 end if;
end loop;
end;
/

alter session set events '10046 trace name context off';

References:
https://netfiles.uiuc.edu/jstrode/www/orapack/DBMS_SYSTEM.html

When and how to rebuild index?

I would like to put before you when to rebuild an index...

Indexes are to be rebuilt if more than 20% of their records are changed

create table emp ( no number(3), name varchar2(30));
create index emp_ind on emp(no);
insert some records into the table ( eg 10k records)
delete records from table nearly 10%


execution of below command enters 1 record in index_stats view ( validates the structure of the index)

analyze index emp_ind validate structure;

select del_lf_rows * 100 / decode(lf_rows,0,1,lf_rows) from index_stats where
name ='EMP_IND';

if the out put is more than 20, you need to rebuild the index

in your case it will be 10 and u have done 10% deletes on the table, now try to delete another 15% of records from table, this will delete 15% records from index also

again run

analyze index emp_ind validate structure;

select del_lf_rows * 100 / decode(lf_rows,0,1,lf_rows) from index_stats where
name ='EMP_IND';

the result will be 25%
so you need to rebuild the index with below statement,

alter index emp_ind rebuild;

then again try

analyze index emp_ind validate structure;

select del_lf_rows * 100 / decode(lf_rows,0,1,lf_rows) from index_stats where
name ='EMP_IND';

your result is 0

Oracle 11g impdp / expdp parameters

[oracle@vinay ddump]$ impdp help=y

Import: Release 11.2.0.1.0 - Production on Fri Apr 9 23:29:35 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.


The Data Pump Import utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Import runs by entering the 'impdp' command followed
by various parameters. To specify parameters, you use keywords:

Format: impdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

USERID must be the first parameter on the command line.

------------------------------------------------------------------------------

The available keywords and their descriptions follow. Default values are listed within square brackets.

ATTACH
Attach to an existing job.
For example, ATTACH=job_name.

CONTENT
Specifies data to load.
Valid keywords are: [ALL], DATA_ONLY and METADATA_ONLY.

DATA_OPTIONS
Data layer option flags.
Valid keywords are: SKIP_CONSTRAINT_ERRORS.

DIRECTORY
Directory object to be used for dump, log and sql files.

DUMPFILE
List of dumpfiles to import from [expdat.dmp].
For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.

ENCRYPTION_PASSWORD
Password key for accessing encrypted data within a dump file.
Not valid for network import jobs.

ESTIMATE
Calculate job estimates.
Valid keywords are: [BLOCKS] and STATISTICS.

EXCLUDE
Exclude specific object types.
For example, EXCLUDE=SCHEMA:"='HR'".

FLASHBACK_SCN
SCN used to reset session snapshot.

FLASHBACK_TIME
Time used to find the closest corresponding SCN value.

FULL
Import everything from source [Y].

HELP
Display help messages [N].

INCLUDE
Include specific object types.
For example, INCLUDE=TABLE_DATA.

JOB_NAME
Name of import job to create.

LOGFILE
Log file name [import.log].

NETWORK_LINK
Name of remote database link to the source system.

NOLOGFILE
Do not write log file [N].

PARALLEL
Change the number of active workers for current job.

PARFILE
Specify parameter file.

PARTITION_OPTIONS
Specify how partitions should be transformed.
Valid keywords are: DEPARTITION, MERGE and [NONE].

QUERY
Predicate clause used to import a subset of a table.
For example, QUERY=employees:"WHERE department_id > 10".

REMAP_DATA
Specify a data conversion function.
For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.

REMAP_DATAFILE
Redefine datafile references in all DDL statements.

REMAP_SCHEMA
Objects from one schema are loaded into another schema.

REMAP_TABLE
Table names are remapped to another table.
For example, REMAP_TABLE=EMP.EMPNO:REMAPPKG.EMPNO.

REMAP_TABLESPACE
Tablespace object are remapped to another tablespace.

REUSE_DATAFILES
Tablespace will be initialized if it already exists [N].

SCHEMAS
List of schemas to import.

SKIP_UNUSABLE_INDEXES
Skip indexes that were set to the Index Unusable state.

SOURCE_EDITION
Edition to be used for extracting metadata.

SQLFILE
Write all the SQL DDL to a specified file.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

STREAMS_CONFIGURATION
Enable the loading of Streams metadata

TABLE_EXISTS_ACTION
Action to take if imported object already exists.
Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.

TABLES
Identifies a list of tables to import.
For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.

TABLESPACES
Identifies a list of tablespaces to import.

TARGET_EDITION
Edition to be used for loading metadata.

TRANSFORM
Metadata transform to apply to applicable objects.
Valid keywords are: OID, PCTSPACE, SEGMENT_ATTRIBUTES and STORAGE.

TRANSPORTABLE
Options for choosing transportable data movement.
Valid keywords are: ALWAYS and [NEVER].
Only valid in NETWORK_LINK mode import operations.

TRANSPORT_DATAFILES
List of datafiles to be imported by transportable mode.

TRANSPORT_FULL_CHECK
Verify storage segments of all tables [N].

TRANSPORT_TABLESPACES
List of tablespaces from which metadata will be loaded.
Only valid in NETWORK_LINK mode import operations.

VERSION
Version of objects to import.
Valid keywords are: [COMPATIBLE], LATEST or any valid database version.
Only valid for NETWORK_LINK and SQLFILE.

------------------------------------------------------------------------------

The following commands are valid while in interactive mode.
Note: abbreviations are allowed.

CONTINUE_CLIENT
Return to logging mode. Job will be restarted if idle.

EXIT_CLIENT
Quit client session and leave job running.

HELP
Summarize interactive commands.

KILL_JOB
Detach and delete job.

PARALLEL
Change the number of active workers for current job.

START_JOB
Start or resume current job.
Valid keywords are: SKIP_CURRENT.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

STOP_JOB
Orderly shutdown of job execution and exits the client.
Valid keywords are: IMMEDIATE.

[oracle@vinay ddump]$ expdp help=y

Export: Release 11.2.0.1.0 - Production on Sat Apr 10 00:25:02 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.


The Data Pump export utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Export runs by entering the 'expdp' command followed
by various parameters. To specify parameters, you use keywords:

Format: expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

------------------------------------------------------------------------------

The available keywords and their descriptions follow. Default values are listed within square brackets.

ATTACH
Attach to an existing job.
For example, ATTACH=job_name.

COMPRESSION
Reduce the size of a dump file.
Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.

CONTENT
Specifies data to unload.
Valid keyword values are: [ALL], DATA_ONLY and METADATA_ONLY.

DATA_OPTIONS
Data layer option flags.
Valid keyword values are: XML_CLOBS.

DIRECTORY
Directory object to be used for dump and log files.

DUMPFILE
Specify list of destination dump file names [expdat.dmp].
For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.

ENCRYPTION
Encrypt part or all of a dump file.
Valid keyword values are: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE.

ENCRYPTION_ALGORITHM
Specify how encryption should be done.
Valid keyword values are: [AES128], AES192 and AES256.

ENCRYPTION_MODE
Method of generating encryption key.
Valid keyword values are: DUAL, PASSWORD and [TRANSPARENT].

ENCRYPTION_PASSWORD
Password key for creating encrypted data within a dump file.

ESTIMATE
Calculate job estimates.
Valid keyword values are: [BLOCKS] and STATISTICS.

ESTIMATE_ONLY
Calculate job estimates without performing the export.

EXCLUDE
Exclude specific object types.
For example, EXCLUDE=SCHEMA:"='HR'".

FILESIZE
Specify the size of each dump file in units of bytes.

FLASHBACK_SCN
SCN used to reset session snapshot.

FLASHBACK_TIME
Time used to find the closest corresponding SCN value.

FULL
Export entire database [N].

HELP
Display Help messages [N].

INCLUDE
Include specific object types.
For example, INCLUDE=TABLE_DATA.

JOB_NAME
Name of export job to create.

LOGFILE
Specify log file name [export.log].

NETWORK_LINK
Name of remote database link to the source system.

NOLOGFILE
Do not write log file [N].

PARALLEL
Change the number of active workers for current job.

PARFILE
Specify parameter file name.

QUERY
Predicate clause used to export a subset of a table.
For example, QUERY=employees:"WHERE department_id > 10".

REMAP_DATA
Specify a data conversion function.
For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.

REUSE_DUMPFILES
Overwrite destination dump file if it exists [N].

SAMPLE
Percentage of data to be exported.

SCHEMAS
List of schemas to export [login schema].

SOURCE_EDITION
Edition to be used for extracting metadata.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

TABLES
Identifies a list of tables to export.
For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.

TABLESPACES
Identifies a list of tablespaces to export.

TRANSPORTABLE
Specify whether transportable method can be used.
Valid keyword values are: ALWAYS and [NEVER].

TRANSPORT_FULL_CHECK
Verify storage segments of all tables [N].

TRANSPORT_TABLESPACES
List of tablespaces from which metadata will be unloaded.

VERSION
Version of objects to export.
Valid keyword values are: [COMPATIBLE], LATEST or any valid database version.

------------------------------------------------------------------------------

The following commands are valid while in interactive mode.
Note: abbreviations are allowed.

ADD_FILE
Add dumpfile to dumpfile set.

CONTINUE_CLIENT
Return to logging mode. Job will be restarted if idle.

EXIT_CLIENT
Quit client session and leave job running.

FILESIZE
Default filesize (bytes) for subsequent ADD_FILE commands.

HELP
Summarize interactive commands.

KILL_JOB
Detach and delete job.

PARALLEL
Change the number of active workers for current job.

REUSE_DUMPFILES
Overwrite destination dump file if it exists [N].

START_JOB
Start or resume current job.
Valid keyword values are: SKIP_CURRENT.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

STOP_JOB
Orderly shutdown of job execution and exits the client.
Valid keyword values are: IMMEDIATE.

Ex:

impdp scott/TIGER TABLES=scott.dept dumpfile=dump_dir:test1.dmp logfile=dump_dir:test1.log
table_exists_action= replace

[oracle@vinay ddump]$ IMPDP scott/TIGER \
> TABLES=scott.dept \
> CONTENT=data_only \
> DUMPFILE=dump_dir:test1.dmp \
> LOGFILE=dump_dir:test1.log \
> VERSION=10.2.0

Viewing errors with oerr utility

The oerr utility (Oracle Error) is provided only with Oracle databases on UNIX platforms. oerr is not an executable, but instead, a shell script that retrieves messages from installed message files.

The syntax required of the oerr utility is:

oerr (facility) (error_no)
The facility is the prefix to the error number. These include ORA, PLS, EXP, etc. The error is the actual error number returned by Oracle.

Ex:

[oracle@vinay admin]$ oerr ora 12545
12545, 00000, "Connect failed because target host or object does not exist"
// *Cause: The address specified is not valid, or the program being
// connected to does not exist.
// *Action: Ensure the ADDRESS parameters have been entered correctly; the
// most likely incorrect parameter is the node name. Ensure that the
// executable for the server exists (perhaps "oracle" is missing.)
// If the protocol is TCP/IP, edit the TNSNAMES.ORA file to change the
// host name to a numeric IP address and try again.

Using autotrace in SQL*Plus

autotrace supports the following options:

• on – Enables all options.
• on explain – Displays returned rows and the explain plan.
• on statistics – Displays returned rows and statistics.
• trace explain – Displays the execution plan for a select statement without actually executing it. (set autotrace trace explain)
• traceonly – Displays execution plan and statistics without displaying the returned rows. This option should be used when a large result set is expected.

set autotrace off
set autotrace on
set autotrace traceonly

set autotrace on explain
set autotrace on statistics
set autotrace on explain statistics

set autotrace traceonly explain
set autotrace traceonly statistics
set autotrace traceonly explain statistics

set autotrace off explain
set autotrace off statistics
set autotrace off explain statistics

Prerequisites
The explain plan feature of autotrace requires a plan_table which can be created with $ORACLE_HOME/rdbms/admin/utlxplan.sql

The statistic feature requires that the user is granted select on v_$sesstat, v_$statname and v_$session.

An Oracle installation comes with $ORACLE_HOME/sqlplus/admin/plustrce.sql which installs the role plustrace. plustrace is granted those select rights. If now plustrace is granted to a user, he will then be able to turn autotrace on. Alternatively, plustrace can be granted to public.

SQL> set timing on
SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

Solution:
To use this feature, you must have the PLUSTRACE role granted to you and a PLAN_TABLE table created in your schema

Connect as system/manager

SQL> @?\sqlplus\admin\plustrce.sql;
SQL> grant plustrace to scott;
SQL> conn scott/tiger@sample;
SQL> @?/rdbms/admin/utlxplan.sql;
SQL> set autotrace on explain
SQL> select sysdate from dual;

SYSDATE
---------
22-AUG-08

Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------


SQL> set autotrace on statistics;
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

Renaming datafies in single/multiple tablespaces

Procedure for Renaming Datafiles in a Single Tablespace

To rename datafiles in a single tablespace, complete the following steps:

1.Take the tablespace that contains the datafiles offline. The database must be open.

For example:

ALTER TABLESPACE users OFFLINE NORMAL;

2. Rename the datafiles using the operating system.

3. Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to change the filenames within the database.

For example, the following statement renames the datafiles /u02/oracle/rbdb1/user1.dbf and /u02/oracle/rbdb1/user2.dbf to/u02/oracle/rbdb1/users01.dbf and /u02/oracle/rbdb1/users02.dbf, respectively:

ALTER TABLESPACE users
RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf',
'/u02/oracle/rbdb1/user2.dbf'
TO '/u02/oracle/rbdb1/users01.dbf',
'/u02/oracle/rbdb1/users02.dbf';

Always provide complete filenames (including their paths) to properly identify the old and new datafiles. In particular, specify the old datafile name exactly as it appears in the DBA_DATA_FILES view of the data dictionary.

4. Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

Procedure for Relocating Datafiles in a Single Tablespace

Here is a sample procedure for relocating a datafile.

Assume the following conditions:

* An open database has a tablespace named users that is made up of datafiles all located on the same disk.

* The datafiles of the users tablespace are to be relocated to different and separate disk drives.

* You are currently connected with administrator privileges to the open database.

* You have a current backup of the database.

Complete the following steps:

1. If you do not know the specific file names or sizes, you can obtain this information by issuing the following query of the data dictionary view DBA_DATA_FILES:

SQL> SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES
2> WHERE TABLESPACE_NAME = 'USERS';

FILE_NAME BYTES
------------------------------------------ ----------------
/u02/oracle/rbdb1/users01.dbf 102400000
/u02/oracle/rbdb1/users02.dbf 102400000

2. Take the tablespace containing the datafiles offline:

ALTER TABLESPACE users OFFLINE NORMAL;

3. Copy the datafiles to their new locations and rename them using the operating system. You can copy the files using the DBMS_FILE_TRANSFER package discussed in "Copying Files Using the Database Server".

Note:
You can temporarily exit SQL*Plus to execute an operating system command to copy a file by using the SQL*Plus HOST command.

4. Rename the datafiles within the database.

The datafile pointers for the files that make up the users tablespace, recorded in the control file of the associated database, must now be changed from the old names to the new names.

Use the ALTER TABLESPACE...RENAME DATAFILE statement.

ALTER TABLESPACE users
RENAME DATAFILE '/u02/oracle/rbdb1/users01.dbf',
'/u02/oracle/rbdb1/users02.dbf'
TO '/u03/oracle/rbdb1/users01.dbf',
'/u04/oracle/rbdb1/users02.dbf';

5.Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

Procedure for Renaming and Relocating Datafiles in Multiple Tablespaces

You can rename and relocate datafiles in one or more tablespaces using the ALTER DATABASE RENAME FILE statement. This method is the only choice if you want to rename or relocate datafiles of several tablespaces in one operation. You must have the ALTER DATABASE system privilege.

Note:
To rename or relocate datafiles of the SYSTEM tablespace, the default temporary tablespace, or the active undo tablespace you must use this ALTER DATABASE method because you cannot take these tablespaces offline.

To rename datafiles in multiple tablespaces, follow these steps.

1. Ensure that the database is mounted but closed.

Note:
Optionally, the database does not have to be closed, but the datafiles (or tempfiles) must be offline.

2. Copy the datafiles to be renamed to their new locations and new names, using the operating system. You can copy the files using the DBMS_FILE_TRANSFER package discussed in "Copying Files Using the Database Server".

3. Use ALTER DATABASE to rename the file pointers in the database control file.

For example, the following statement renames the datafiles/u02/oracle/rbdb1/sort01.dbf and /u02/oracle/rbdb1/user3.dbf to /u02/oracle/rbdb1/temp01.dbf and /u02/oracle/rbdb1/users03.dbf, respectively:

ALTER DATABASE
RENAME FILE '/u02/oracle/rbdb1/sort01.dbf',
'/u02/oracle/rbdb1/user3.dbf'
TO '/u02/oracle/rbdb1/temp01.dbf',
'/u02/oracle/rbdb1/users03.dbf;

Always provide complete filenames (including their paths) to properly identify the old and new datafiles. In particular, specify the old datafile names exactly as they appear in the DBA_DATA_FILES view.

4. Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

Restore Point in Flashback Database

Remember the concept of savepoints in SQL? In a transaction, you can create a savepoint, make some modifications, create another savepoint, and so on. If the changes are not what you expected, all you have to do is roll them back to a specific savepoint.

Now pan over to a new functionality introduced in Oracle Database 10g, Flashback Database, which allows you to rewind the database to a previous point in time. Wouldn't it be nice to have functionality similar to savepoint in this situation—that is, to be able to rewind to a specific named point, not just a point in time?

In Oracle Database 10g Release 2, you can do that using a new functionality called restore points. Here's how it works. Suppose you have a long month-end processing involving several batch programs you have to run sequentially. Here is the sequence of events:

1. Create a restore point rp1
2. Run batch job 1
3. Create a restore point rp2
4. Run batch job 2

and so on. The batch job 2 fails in the middle of execution, and you need to take the database to a consistent state. You don't have to take it all the way to the beginning of the run. Because the restore point rp2 was created before the execution of the batch job, you can simply flashback the database to that restore point.

You create a restore point with

create restore point before_monthend_200503;

Restore point BEFORE_MONTHEND_200503 is now created based on the current database time and SCN. If you want to ensure that the database can be flashed back to a particular restore point, you can specify a guarantee by creating guaranteed restore points as shown below:

create restore point before_monthend_200503
guarantee flashback database;

You can confirm the existence of this restore point by SELECTing from a dynamic performance view V$RESTORE_POINT:

SQL> select * from v$restore_point;

SCN DATABASE_INCARNATION# GUA STORAGE_SIZE
---------- --------------------- --- ------------
TIME
---------------------------------------------------
NAME
---------------------------------------------------

1429811 1 YES 8192000
27-MAR-05 05.18.39.000000000 PM
BEFORE_MONTHEND_200503

Later when you want to flashback the database to that restore point, you could simply issue:

flashback database to restore point before_monthend_200503;

If you examine the alert log, it will show a line similar to:

Media Recovery Applied UNTIL CHANGE 1429814

Restore points—especially guaranteed restore points—are quite useful in many database-related tasks. A good example is QA databases, where you may want to establish a restore point, run some tests, and flashback to the restore point to make the database look as if nothing happened. Then you can perform another round of testing and again restore it to the restore point.

Peek into the Flash Recovery Area

You may have configured Flash Recovery Area to back up different types of files. But how do you know what types of backups are available there?

A new view, V$FLASH_RECOVERY_AREA_USAGE, shows what's available in the flashback area.

SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG .02 .02 1
BACKUPPIECE 68.98 1.02 10
IMAGECOPY 0 0 0
FLASHBACKLOG .95 0 3

Using this view you can immediately see what kind of files are available in the Flash Recovery Area. It only shows a percentage however, so how do you determine actual values? Simply query the view $RECOVERY_FILE_DEST.

SQL> select * from V$RECOVERY_FILE_DEST;

NAME
----------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
/home/oracle
2147483648 1502122496 22201856 14

This query shows that the total size of the recovery area is 16384000. Flashback logs occupy 0.95% of the column SPACE_LIMIT as shown in the previous query, so you can calculate the actual size of the space occupied. It also shows you how much space can be reclaimed from the different types of backups in the Flash Recovery Area. For instance, you can reclaim 1.02% of the space occupied by backup pieces, perhaps due to obsolete backups. Using this view you can make intelligent predictions about Flash Recovery Area usage and sizing.

Source: Oracle 10g R2 Top Features for DBAs

Master table reorganization with a primary key materialized view

An example is as follows:

1) This test is done with the scott.dept table

connect scott/tiger

2) If the dept table do not have primary key, add it using the following command:

alter table dept add constraint pk_dept primary key(deptno);

3) As a primary key exists on master table (dept), default materialized view log is
created with primary key. In case a primary key does not exist on the master table
an error ORA-12014 will be raised.

create materialized view log on dept;

4) Creation of materialized view ( default is primary key):
ORA-23415 will be raised in case the materialized view log do not record the
primary key

create materialized view s_dept refresh fast as select * from dept;

5) Perform insert in materialized view and commit it:

insert into dept (deptno) values (49);
commit;

If you select from the materialized view log you should see a row count of 1.

select count(*) from mlog$_dept;
1 row selected

6) Now we want to perform a reorganization of the master table
The below procedure will devalidate internal triggers which populates
the materialized view log when modification are done on the master table:

execute DBMS_MVIEW.BEGIN_TABLE_REORGANIZATION('SCOTT','DEPT');

7) Now export the dept table:
exp scott/tiger tables=dept

8) Truncate the table to prepare it for import.

truncate table dept;

9) Import table dept.

imp scott/tiger tables=dept ignore=y

10) Turn on internals triggers.

execute DBMS_MVIEW.END_TABLE_REORGANIZATION('SCOTT','DEPT');

11) Refresh the materialized view, default is refresh method indicated in DBA_MVIEWS.

execute dbms_mview.refresh('S_DEPT','f');

12) If the materialized view is a rowid materialized view, then the error
ORA-12034 will appear at refresh time, and the materialized view will need
a complete refresh.

execute dbms_mview.refresh('S_DEPT','c');

Source: Metalink ID 254593.1

Block storage parameters

PCTFREE - The percentage of space reserved for future update of existing data.

PCTUSED - The percentage of minimum space used for insertion of new row data. This value determines when the block gets back into the FREELISTS structure.

FREELIST - Structure where Oracle maintains a list of all free available blocks.

Suppose you have specified PCTUSED as 40 %. and PCTFREE as 10 %.

• Oracle will keep on inserting new rows till the space is 90 % used. It will reserve the remaining 10% for future updates.
• To start adding new rows again to the block, Oracle will check the space in the Block and the PCTUSED parameter.
• When the space falls below 40 %, Oracle will start adding new rows to the block.

Automatic Segment Space Management (ASSM) tablespaces automate freelist management and remove the ability to specify PCTFREE, PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters.

Each segment has one or more freelist (in the segment header) that keeps track of data blocks under the high-water mark. When records are inserted into the segment, Oracle examines the freelist to determine which data blocks have available space to store the new rows. When rows are deleted (or updated), and the amount of free space in the data block falls below a threshold value (PCTUSED), the data block will be returned to the freelist. The high water mark is the boundary between used and unused space in a segment.

more...

more...

startup migrate/startup upgrade

startup migrate
=> Used to upgrade a database till 9i.
See the step 11 from the 9.2 manual upgrade guide :
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96530/upgrade.htm#1009472

=> Used to downgrade a database till 9i.
See the step 5 from the 9.2 downgrade guide :
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96530/downgrad.htm#248958

=> Used to downgrade a database since 10g.
See the step 18 from the 10.2 downgrade guide :
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14238/downgrade.htm#sthref415

startup upgrade
=> Used to upgrade a database since 10g.
See the step 7 from the 10.2 manual upgrade guide :
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14238/upgrade.htm#CACGGHJC

Database Migration from Solaris to AIX

Check Pre-Conditions on database SRTR1

Check list of platforms that we can convert source database into as below.

SQL> SELECT * FROM v$db_transportable_platform;

Shutdown and start newly created SRTR1 database in "READ ONLY" mode.

SQL> shutdown immediate;
Comment out below parameters:
*.audit_trail ='DB'
*.audit_file_dest='/backup/SRTR1/oradata'

SQL> startup mount;
SQL> alter database open read only;

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
--------- ----------
SRTR1 READ ONLY


Transfer SRTR1 files from Solaris to AIX

Backup control file to trace using the below command and shutdown database
SQL> alter database backup controlfile to trace;

The trace file is generated in the udump directory by the format xxxxxx.trc. Copy the control file trace to AIX_Server:/backup.

Copy the init.ora file of SRTR1 to AIX_Server:/backup

$ scp initSRTR1.ora AIX_Server:/backup/SRTR1

Go to /optware/oracle/oradata on Solaris_Server and run the below command to get a count of the datafiles of SRTR1

Sql> spool SRTR1_data_file_count.log
Sql> select count(*) from dba_data_files;
Sql> spool off

Shutdown the database SRTR1 on Solaris_Server.

Sql > select instance_name from v$instance;

SRTR1

Sql> shutdown immediate;

Change back the init parameters and copy the init.ora file of SRTR1 to AIX_Server:/backup
$ cd /optware/oracle/SRTR1
Delete below entries:
*.db_cache_size =1000M
*.pga_aggregate_target =2000M
*.shared_pool_size =1000M

Uncomment below parameters:
*.db_cache_size =4000M
*.pga_aggregate_target =9000M
*.shared_pool_size =5000M

$ cd /optware/oracle/SRTR1
$ scp initSRTR1.ora AIX_Server:/backup/SRTR1

Comment out SRTR1 entries in oratab file to avoid future auto startup of the database incase server is rebooted.
$cd /var/opt/oracle
And comment out below entry:

SRTR1:/optware/oracle/10.2.0.4:Y

Transfer the files from Solaris_Server to AIX_Server

Make directories with 50Gb each in size within the filesystems to have the datafiles to be copied in multiple streams. Below are the directories to be created:

/u11=171GB db=57.3 GB db1=57.9 GB db2=56.3 GB
$ cd /backup/SRTR1/u11
$ mkdir db db1 db2

$ mv *hrlarge.0*dbf db; mv *hrlarge.12.dbf db; mv *hrlarge.13.dbf db
$ mv *hrlarge*.dbf db1; mv *usrtblspc*dbf db1;mv srtr1.pttbl.01.dbf db1
$ mv *.dbf db2

cd /backup/SRTR1/u11/db
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/u11 ;zcat | tar xvf -" &

cd /backup/SRTR1/u11/db1
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/u11 ;zcat | tar xvf -" &

cd /backup/SRTR1/u11/db2
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/u11 ;zcat | tar xvf -" &


/u13=169.8 GB db=57.3 GB db1= 56.6 GB db2= 55.8 GB
$ cd /backup/SRTR1/u13
$ mkdir db db1 db2

$ mv *hrlarge.0*dbf db; mv *hrlarge.10.dbf db; mv *hrlarge.11.dbf db; mv *hrlarge.14.dbf db
$ mv *hrlarge*.dbf db1; mv *ewlarge.0* db1;
$ mv *.dbf db2; mv db1/srt1.hrlarge.27.dbf db2;

cd /backup/SRTR1/u13/db
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/u13 ;zcat | tar xvf -" &

cd /backup/SRTR1/u13/db1
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/u13 ;zcat | tar xvf -" &

cd /backup/SRTR1/u13/db2
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/u13 ;zcat | tar xvf -" &


/u02=4.6 GB
cd /backup/SRTR1/u02
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/u02 ;zcat | tar xvf -" &

/u09=4.2 GB
cd /u11/GDPFCNV1/u09
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/u09 ;zcat | tar xvf -" &

Stop here, once /u02 and /u09 finish, start /12 and /14

/u12=106.9 GB db=52.8 GB db1=54.1 GB
$ cd /backup/SRTR1/u12
$ mkdir db db1

$ mv *psindex.0*dbf db; mv *psindex.11.dbf db; mv *psindex.13.dbf db; mv *psindex.15.dbf db; mv *psindex.17.dbf db
$ mv *.dbf db1

cd /backup/SRTR1/u12/db
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/u12 ;zcat | tar xvf -" &

cd /backup/SRTR1/u12/db1
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/SRTR1/u12 ;zcat | tar xvf -" &

/u14=92.69 GB db=48.12 GB db1=44.56 GB
$ cd /u11/SRTR1/u14
$ mkdir db db1
$ mv *.0* db; mv *.1* db
$ mv *.dbf db1


cd /u11/SRTR1/u14/db
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/SRTR1/u14 ;zcat | tar xvf -" &

cd /u11/SRTR1/u14/db1
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/SRTR1/u14 ;zcat | tar xvf -" &



Also make sure that you have copied the correct control file trace generated and init.ora files to /backup on AIX_Server
Locations of the files on Solaris_Server:
a. trace file location: /backup/SRTR1/oradump
b. init.ora file location: /optware/oracle/SRTR1

Check if all datafiles have been copied correctly

Note: After completion of above confirm the transfer of files.

Ways to check the datafiles have been copied correctly

Compare the datafile count of the file systems of PHPRDH and SRTR1. Run PHPRDH_datafile_count.ksh and SRTR1_datafile_count.ksh

Ex- cat SRTR1_datafile_count.ksh on SOLARIS

ls -ltr /backup/SRTR1/d11/*.dbf | wc -l
ls -ltr /backup/SRTR1/d13/*.dbf | wc -l
ls -ltr /backup/SRTR1/d12/*.dbf | wc -l
ls -ltr /u11/SRTR1/d14/*.dbf | wc -l
ls -ltr /backup/SRTR1/d02/*.dbf | wc -l
ls -ltr /u11/SRTR1/d09/*.dbf | wc –l

cat SRTR1_datafile_count.ksh on AIX

ls -ltr /backup/SRTR1/d11/*.dbf | wc -l
ls -ltr /backup/SRTR1/d13/*.dbf | wc -l
ls -ltr /backup/SRTR1/d12/*.dbf | wc -l
ls -ltr /backup/SRTR1/d14/*.dbf | wc -l
ls -ltr /backup/SRTR1/d02/*.dbf | wc -l
ls -ltr /backup/SRTR1/d09/*.dbf | wc –l


Run ps –ef | grep SRTR1 on Solaris_Server which will show copy processes since we run them in background.

Conversion
Bring up the database SRTR1and Convert the datafiles of SRTR1on the target server (AIX_Server)

Note: Create any missing directories

1. Enable the entry for SRTR1in /etc/oratab for the 10.2.0.3_Q108 ORACLE_HOME(already there)

# SRTR1:/optware/oracle/10.2.0.3_Q108:Y


2. Copy the init.ora file from /backup to/ dbms/oracle/local/SRTR1/etc

Cp initSRTR1.ora /dbms/oracle/local/SRTR1/etc

3. Go to /dbms/oracle/local/SRTR1/etc and
Edit the following in init.ora for SRTR1located in /dbms/oracle/local/SRTR1/etc to reflect the correct file systems.

AUDIT_FILE_DEST ='/backup/ SRTR1/oradump /adump'
background_dump_dest='/backup/SRTR1/oradump/bdump'
core_dump_dest='/backup/ SRTR1/oradump /cdump'
user_dump_dest='/backup/ SRTR1/oradump /udump'
control_files='/backup/SRTR1/u11/SRTR1.control.01.ctl',
'/backup/SRTR1/u13/SRTR1.control.02.ctl',
'/backup/SRTR1/u13/SRTR1.control.03.ctl'
db_name=’SRTR1’
log_archive_dest_1='LOCATION=/backup/SRTR1/archive'
log_archive_format='SRTR1_%s_%t_%r.arc'
service_names=' SRTR1.world',' SRTR1'

4. Run the env file in /dbms/oracle/local/SRTR1/etc to set up environment for SRTR1

oracle@erpstedb202p[GDPFPRG] /dbms/oracle/local/SRTR1/etc > . SRTR1.env


5. Make sure the environment is set correctly for SRTR1. Use below

echo $ORACLE_SID

echo $ORACLE_HOME

echo $PATH

6. Create a link in $ORACLE_HOME/dbs for init.ora

$ cd $ORACLE_HOME/dbs
$ ln -s /dbms/oracle/local/SRTR1/etc/initSRTR1.ora initSRTR1.ora

7. Make a copy of the trace file under /backup to /optware/oracle/work/UPGRADE_PROD and rename it from xxxx.trc to controlfile_create_SRTR1.sql
$ cp SRTR1_ora_22678.trc /optware/oracle/work/UPGRADE_PROD
$ cd /optware/oracle/work/UPGRADE_PROD
$ mv xxxxxx.trc create_controlfile_SRTR1.sql

8. Modify the create_controlfile_SRTR1.sql under /optware/oracle/work/UPGRADE to reflect the correct paths for data files and the correct SID name like below

a. Modify “REUSE” to “SET” in the control file trace and
b. /u11/SRTR1/u14/*.dbf to /backup/SRTR1/u14/*.dbf
c. /u11/SRTR1/u09/*.dbf to /backup/GDPFDNV1/u09/*.dbf


9. Check if all the datafiles are in correct location where the database needs to be staged. Cd to /optware/oracle/work/upgrade. Log on to sqlplus and Startup nomount the database
$cd /optware/oracle/work/UPGRADE
$ sqlplus
SQL> startup nomount

10. Run the control file create sql
SQL> @controlfile_create_SRTR1.sql

Note: Check for any errors in the alert log while creating the control files and resolve them
before proceeding with next step.

11. Open the database with reset logs option
SQL> alter database open resetlogs;


12. Add a temp file to the temporary tablespace.

Sql> ALTER TABLESPACE PSTEMP ADD TEMPFILE '/backup/SRTR1/SRTR1.pstemp.10.dbf' SIZE 2000M REUSE AUTOEXTEND OFF;

13. Check list of platforms that we can convert source database into as below
SQL> SELECT * FROM v$db_transportable_platform;

Open the database SRTR1in READ ONLY MODE;
SQL> shutdown immediate
SQL> startup mount
SQL> alter database open read only;


14. Make sure that the database in READ ONLY mode

SQL> select NAME,OPEN_MODE from v$database;

NAME OPEN_MODE
--------- ----------
SRTR1 READ ONLY

Sql> exit


13. Create a directory named convertdb under /optware/oracle/10.2.0.3_Q108/dbs

$cd $ORACLE_HOME/dbs
$mkdir convertdb

15. Open another session and monitor the alert log to see any error during conversion
16. At this point the RMAN convert database can be used:
$ cd /optware/oracle/10.2.0.3_Q108/
$ rman

--Connect to the target
RMAN> connect target;

--the below converts the datafiles under /backup/SRTR1/dXX to AIX format and places them under /dXX/oradata/GDPFPRG/. It also creates transport script in /optware/oracle/10.2.0.3_Q108/dbs/convertdb/transportscript. It took 4.5 hours on PHUATH

RMAN> CONVERT DATABASE NEW DATABASE 'GDPFPRG'
transport script '/optware/oracle/10.2.0.3_Q108/dbs/convertdb/transportscript'
to platform 'AIX-Based Systems (64-bit)'
db_file_name_convert
'/backup/SRTR1/u11' '/u11/oradata/GDPFPRG',
'/backup/SRTR1/u13' '/u13/oradata/GDPFPRG',
'/backup/SRTR1/u12' '/u12/oradata/GDPFPRG',
'/backup/SRTR1/u14' '/u14/oradata/GDPFPRG',
'/backup/SRTR1/u02' '/u02/oradata/GDPFPRG',
'/backup/SRTR1/u09' '/u09/oradata/GDPFPRG' ;


Note: After successful conversion of the data files success messages are prompted and the converted data files are created under /oradata/GDPFPRG/db. The conversion should take approximately 6 hours.

The conversion success message at the end of the conversion is as the sample below

************************************************************
input datafile fno=00101 name=/backup/SRTR1/u13/SRTR1.ptrpts.01.dbf
converted datafile=/u13/oradata/GDPFPRG/SRTR1.ptrpts.01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00102 name=/backup/SRTR1/u11/SRTR1.rwlarge.01.dbf
converted datafile=/u11/oradata/GDPFPRG/SRTR1.rwlarge.01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00103 name=/backup/SRTR1/u13/SRTR1.scapp.01.dbf
converted datafile=/u13/oradata/GDPFPRG/SRTR1.scapp.01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00104 name=/backup/SRTR1/u11/SRTR1.bplarge.01.dbf
converted datafile=/u11/oradata/GDPFPRG/SRTR1.bplarge.01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Run SQL script /optware/oracle/10.2.0.3_Q108/dbs/convertdb/transportscript on the target platform to create database
Edit init.ora file /optware/oracle/10.2.0.3_Q108/dbs/init_00k7onuv_1_0.ora. This PFILE will be used to create the database on the target platform
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility
Finished backup at 19-FEB-09



15. After successful completion of RMAN conversion exit out.
RMAN > exit

Task6. Bring up database SRTR1

01. A new control file creation script “transportscript” is created under /optware/oracle/10.2.0.3_Q108/dbs/convertdb



02. Rename the datafiles from SRTR1to SRTR1by executing the script rename_files_from_SRTR1_to_gdpfprg.ksh located in /optware/oracle/work/UPGRADE_PROD

03. Point the Oracle Home of SRTR1to 10.2.0.3_Q108 in /etc/oratab like below.


GDPFPRG:/optware/oracle/10.2.0.3_Q108_dbv:Y

04. Run the env file located in /dbms/oracle/local/GDPFPRG/etc.

$ /dbms/oracle/local/GDPFPRG/etc > . GDPFPRG.env


05. Make sure the environment is set correctly for GDPFPRG. Use below

echo $ORACLE_SID

echo $ORACLE_HOME

echo $PATH


06. Go to $ORACLE_HOME/dbs and create a soft link for initGDPFPRG.ora in $ORACLE_HOME/dbs to point to /dbms/oracle/local/GDPFPRG/etc. Use below

ln -s /dbms/oracle/local/GDPFPRG/etc/initGDPFPRG.ora initGDPFPRG.ora

07. Make sure the initSRTR1to reflect
AUDIT_FILE_DEST =' /u01/oradump/GDPFPRG/adump'
background_dump_dest=' /u01/oradump/GDPFPRG/bdump'
core_dump_dest=' /u01/oradump/GDPFPRG/cdump'
user_dump_dest=' /u01/oradump/GDPFPRG/udump'
control_files ='/u11/oradata/GDPFPRG/gdpfprg.control.01.ctl'
,'/u13/oradata/GDPFPRG/gdpfprg.control.02.ctl'
,'/u13/oradata/GDPFPRG/gdpfprg.control.03.ctl'
db_name=’GDPFPRG’
log_archive_dest_1='LOCATION=/u07/oradata/GDPFPRG'
undo_tablespace ='PSRBS1'
log_archive_format='GDPFPRG_%s_%t_%r.arc'
service_names=' GDPFPRG.world',' GDPFPRG'


08. Make a copy of create_controlfile_SRTR1.sql in /optware/oracle/work/UPGRADE_PROD to create_controlfile_GDPFPRG.sql (RECOMMENDED)

$/optware/oracle/work/UPGRADE_PROD>cp create_controlfile_SRTR1.sql create_controlfile_GDPFPRG.sql


OR use the transport script to create a control file…

*************************
Modify "transportscript" script to reflect the converted data files location, intiPHUATH.ora location and the location for utlxxx scripts as per the ORACLE_HOME. Also make other any other necessary changes.

*********************

09. Modify the create_controlfile_GDPFPRG.sql to reflect the following:

SRTR1 to GDPFPRG


Redo log locations as follows:

GROUP 1 (
'/dev/roraGDPFPRGr1_01',
'/dev/roraGDPFPRGr2_01'
) SIZE 495M,
GROUP 2 (
'/dev/roraGDPFPRGr1_02',
'/dev/roraGDPFPRGr2_02'
) SIZE 495M,
GROUP 3 (
'/dev/roraGDPFPRGr1_03',
'/dev/roraGDPFPRGr2_03'
) SIZE 495M,
GROUP 4 (
'/dev/roraGDPFPRGr1_04',
'/dev/roraGDPFPRGr2_04'
) SIZE 495M,
GROUP 5 (
'/dev/roraGDPFPRGr1_05',
'/dev/roraGDPFPRGr2_05'
) SIZE 495M

Modify the datafile locations as below

'/backup/SRTR1/u11' to '/u11/oradata/GDPFPRG',
'/backup/SRTR1/u13' to '/u13/oradata/GDPFPRG',
'/backup/SRTR1/u12' to '/u12/oradata/GDPFPRG',
'/backup/SRTR1/u14' to '/u14/oradata/GDPFPRG',
'/backup/SRTR1/u02' to '/u02/oradata/GDPFPRG',
'/backup/SRTR1/u09' to '/u09/oradata/GDPFPRG';



10. Open another session and monitor alert log for SRTR1in /u01/oradump/GDPFPRG/bdump
11. cd to /optware/oracle/work/UPGRADE_PROD directory Run the create_controfile_GDPFPRG.sql script in your work directory

Ex: $SQLPLUS

Sql> startup nomount

SQL>@/optware/oracle/work/UPGRADE_PROD/ create_controlfile_GDPFPRG.sql

SQL> alter database open resetlogs;

12. Add temp file to PSTEMP tablespace


Sql>ALTER TABLESPACE PSTEMP ADD TEMPFILE '/u11/oradata/GDPFPRG/gdpfprg.pstemp.10.dbf' SIZE 2000M REUSE AUTOEXTEND OFF;

13. Shutdown and startup just to see if any errors in the alert log. Closely monitor the alert log during this

Sql> shutdown immediate

Sql> startup

14. Run utlirp.sql by shutting down and startup in upgrade mode.

Sql> shutdown immediate

Sql> startup upgrade;

Sql> @?/rdbms/admin/utlirp.sql

15. Run utlrp.sql after shutting down the database

Sql> shutdown immediate

Sql> startup

Sql> @ ?/rdbms/admin/utlrp.sql

16. Go to /optware/oracle/work/UPGRADE_PROD and run add_undo_files_GDPFPRG.sql

Sql> spool add_undo_files_GDPFPRG.log

Sql> @add_undo_files_GDPFPRG.sql


17. Drop the temporary tablespace PSTEMP

Sql> drop tablespace PSTEMP including datafiles and contents cascade constraints;

drop tablespace pstemp including contents and datafiles;


18. Create temporary tablespace PSTEMP

Sql> CREATE TEMPORARY TABLESPACE "PSTEMP" TEMPFILE '/dev/roraGDPFPRGt_001' SIZE 3990M REUSE ;

19. Go to /optware/oracle/work/UPGRADE_PROD and run add_temp_files_GDPFPRG.sql to add temp files
Sql > spool add_temp_files_GDPFPRG.log

Sql> add_temp_files_GDPFPRG.sql

20. Create block changing data file

Sql> alter database enable block change tracking using file '/u13/oradata/GDPFPRG/GDPFPRG.bct.01.dbf' reuse;

21. Update the PS OWNER


SQL> select * from psdbowner;

DBNAME OWNERID
-------- --------
PHPRDH SYSADM

SQL> update psdbowner set dbname='GDPFPRG';

Sql> commit;

SQL> select * from psdbowner;

DBNAME OWNERID
-------- --------
SRTR1 SYSADM

24. Make the sysaux tablespace autoextend
Make sure that the database SRTR1is up and running.
25. Bring up the listener.
26. Shutdown the SRTR1database
SQL> select instance_name from v$instance ;

SRTR1

SQL> shutdown immediate

Column Statistics and Histograms

When gathering statistics on a table, DBMS_STATS gathers information about the data distribution of the columns within the table. The most basic information about the data distribution is the maximum value and minimum value of the column. However, this level of statistics may be insufficient for the optimizer's needs if the data within the column is skewed. For skewed data distributions, histograms can also be created as part of the column statistics to describe the data distribution of a given column.

Histograms are specified using the METHOD_OPT argument of the DBMS_STATS gathering procedures. Oracle Corporation recommends setting the METHOD_OPT to FOR ALL COLUMNS SIZE AUTO. With this setting, Oracle automatically determines which columns require histograms and the number of buckets (size) of each histogram. You can also manually specify which columns should have histograms and the size of each histogram.

Height-Balanced Histograms

In a height-balanced histogram, the column values are divided into bands so that each band contains approximately the same number of rows. The useful information that the histogram provides is where in the range of values the endpoints fall.

Consider a column C with values between 1 and 100 and a histogram with 10 buckets. If the data in C is uniformly distributed, then the histogram looks similar to Figure 14-1, where the numbers are the endpoint values.


The number of rows in each bucket is one tenth the total number of rows in the table. Four-tenths of the rows have values that are between 60 and 100 in this example of uniform distribution.

If the data is not uniformly distributed,in this case, most of the rows have the value 5 for the column. Only 1/10 of the rows have values between 60 and 100.

Height-balanced histograms can be viewed using the *TAB_HISTOGRAMS tables, as shown below:

Viewing Height-Balanced Histogram Statistics

BEGIN
DBMS_STATS.GATHER_table_STATS (OWNNAME => 'OE', TABNAME => 'INVENTORIES',
METHOD_OPT => 'FOR COLUMNS SIZE 10 quantity_on_hand');
END;
/

SELECT column_name, num_distinct, num_buckets, histogram
FROM USER_TAB_COL_STATISTICS
WHERE table_name = 'INVENTORIES' AND column_name = 'QUANTITY_ON_HAND';

COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ----------- ---------------
QUANTITY_ON_HAND 237 10 HEIGHT BALANCED

SELECT endpoint_number, endpoint_value
FROM USER_HISTOGRAMS
WHERE table_name = 'INVENTORIES' and column_name = 'QUANTITY_ON_HAND'
ORDER BY endpoint_number;

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
0 0
1 27
2 42
3 57
4 74
5 98
6 123
7 149
8 175
9 202
10 353

In the query output, one row corresponds to one bucket in the histogram.

Frequency Histograms

In a frequency histogram, each value of the column corresponds to a single bucket of the histogram. Each bucket contains the number of occurrences of that single value. Frequency histograms are automatically created instead of height-balanced histograms when the number of distinct values is less than or equal to the number of histogram buckets specified. Frequency histograms can be viewed using the *TAB_HISTOGRAMS tables, as below.

Viewing Frequency Histogram Statistics

BEGIN
DBMS_STATS.GATHER_table_STATS (OWNNAME => 'OE', TABNAME => 'INVENTORIES',
METHOD_OPT => 'FOR COLUMNS SIZE 20 warehouse_id');
END;
/

SELECT column_name, num_distinct, num_buckets, histogram
FROM USER_TAB_COL_STATISTICS
WHERE table_name = 'INVENTORIES' AND column_name = 'WAREHOUSE_ID';

COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ----------- ---------------
WAREHOUSE_ID 9 9 FREQUENCY

SELECT endpoint_number, endpoint_value
FROM USER_HISTOGRAMS
WHERE table_name = 'INVENTORIES' and column_name = 'WAREHOUSE_ID'
ORDER BY endpoint_number;

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
36 1
213 2
261 3
370 4
484 5
692 6
798 7
984 8
1112 9

Recreating Queues and Queue Tables (Oracle Streams Advanced Queuing)

Stop all the queues in the queue table.

When you create a queue table, it also creates an exception queue implicitly. Make sure that you also stop the exception queue.
begin dbms_aqadm.stop_queue('TEST_OUTBOUND_QUEUE'); end; /

begin dbms_aqadm.stop_queue('AQ$_TEST_OUTBOUND_QT_E'); end; /

Drop the queue table.

begin dbms_aqadm.drop_queue_table(queue_table => 'TEST_OUTBOUND_QT', force => TRUE); end; /

Create the Queue Table.
begin dbms_aqadm.create_queue_table( queue_table => 'TEST_OUTBOUND_QT', queue_payload_type => 'TEST_TYPE', comment => 'Queue Table for Outbound messages', multiple_consumers => TRUE ); end; /

Create the Queues.

begin dbms_aqadm.create_queue( queue_name => 'TEST_OUTBOUND_QUEUE', queue_table => 'TEST_OUTBOUND_QT', queue_type => DBMS_AQADM.NORMAL_QUEUE, max_retries => 5, retry_delay => 0, comment => 'test queue for all outbound messages'); end; /

Screen shots:

SET VERIFY command in SQLPlus

Suppressing old and new values
By default, when SQL*Plus encounters a defined variable, it prints the original line and the line with the substitued values:

define s="'some string'"
define d=dual

select &s from &d;

old 1: select &s from &d
new 1: select 'some string' from dual

'SOMESTRING
-----------
some string

This behavior can be turned with setting verify to off:

SET VERIFY OFF

define thousand=1000
define twelve = 12
define plus = +

select &thousand &plus &twelve from dual;

1000+12
----------
1012

PRAGMA in PL/SQL

This keyword signifies that the statement is a pragma (compiler directive). Pragmas are processed at compile time, not at run time. They do not affect the meaning of a program; they simply convey information to the compiler.

In this context, the term routine includes

• Top-level (not nested) anonymous PL/SQL blocks
• Local, standalone, and packaged functions and procedures
• Methods of a SQL object type
• Database triggers

You cannot use the pragma to mark all subprograms in a package (or all methods in an object type) as autonomous. Only individual routines can be marked autonomous. You can code the pragma anywhere in the declarative section of a routine. But, for readability, code the pragma at the top of the section.

AUTONOMOUS_TRANSACTION Pragma
Instructs the PL/SQL compiler to mark a routine as autonomous (independent). An autonomous transaction is an independent transaction started by another transaction, the main transaction. Autonomous transactions let you suspend the main transaction, do SQL operations, commit or roll back those operations, then resume the main transaction.

EXCEPTION_INIT Pragma
The pragma EXCEPTION_INIT associates an exception name with an Oracle error number. That lets you refer to any internal exception by name and to write a specific handler for it instead of using the OTHERS handler.

You can use EXCEPTION_INIT in the declarative part of any PL/SQL block, subprogram, or package. The pragma must appear in the same declarative part as its associated exception, somewhere after the exception declaration.

Be sure to assign only one exception name to an error number.

RESTRICT_REFERENCES Pragma
To be callable from SQL statements, a stored function must obey certain "purity" rules, which are meant to control side effects. If any SQL statement inside the function body violates a rule, you get an error at run time (when the statement is parsed). To check for violations of the rules, you can use the pragma (compiler directive) RESTRICT_REFERENCES. The pragma asserts that a function does not read and/or write database tables and/or package variables.

You can declare the pragma RESTRICT_REFERENCES only in a package spec or object type spec. You can specify up to four constraints (RNDS, RNPS, WNDS, WNPS) in any order. To call the function from parallel queries, you must specify all four constraints. No constraint implies another. For example, WNPS does not imply RNPS.

SERIALLY_REUSABLE Pragma
The pragma SERIALLY_REUSABLE lets you mark a package as serially reusable. You can so mark a package if its state is needed only for the duration of one call to the server (for example, an OCI call to the server or a server-to-server RPC). You can mark a bodiless package as serially reusable. If a package has a spec and body, you must mark both. You cannot mark only the body.

more...

Using new_value to define a variable in SQLPlus

SQL> col run_date_time new_value today
SQL> select to_char(sysdate,'yyyymmdd hh24:mi') run_date_time from dual;

SQL> select '&&today' from dual;
old 1: select '&&today' from dual
new 1: select '20100223 09:36' from dual

'2010022309:36
--------------
20100223 09:36

SQL> select '&&run_date_time' from dual;
old 1: select '&&run_date_time' from dual
new 1: select '20100223_101323' from dual

'20100223_10132
---------------
20100223_101323

SQL> select '&run_date_time' from dual;
old 1: select '&run_date_time' from dual
new 1: select '20100223_101323' from dual

'20100223_10132
---------------
20100223_101323

Vi editor commands

All commands listed below should be run in escape mode:

h -> move left by one char
j -> move down by one line
k -> move up by one line
l -> move right by one char

q -> quit
q! -> force quit without any changes
wq -> save and quit

i -> insert mode where cursor is
a -> insert after the cursor

dd -> delete a line
2dd [OR] 2D --> delete 2 lines from cursor
x -> delete a char
dw -> To delete from cursor to start of next word
d3w [OR] 3dw -> To delete 3 words from cursor
d$ [OR] D-> To delete from cursor to end of line
dL -> To delete from current line to bottom of screen
dh -> To delete one character before cursor


yy -> copy a line
5yy -> copy 5 lines
p -> paste after cursor
P -> paste before cursor

0 -> move cursor to start of the line
$ -> move cursor to end of the line

w -> move forward by one word
b -> move backward by preceding word
CTRL+f OR CTRL+u -> move up by one screen
CTRL+b OR CTRL+d -> move down by one screen

Move to top of screen H (home)
Move to middle of screen M (middle)
Move to last line of screen L (last)
Last line in file -> G
First line in a file -> 1G
Nth line in a file -> nG


u -> undo

CTRL+G [OR] :f--> Status of the current position and the file you are editing

Global search and replace --> :1,$ s/old/new/g

Open another file -> :e filename
Open another file (don't save most recent changes) -> :e! filename

Search for 'test' -> /test
search for next word foward -> n
Repeat search backward -> N

Convert lower-case to upper-case or vice-versa -> ~
See the hidden format in file -> :set list
ignore the case when searching in a file -> :set ic and then use / to search in a file

Set numbering in a file -> :set number [OR] :set nu
To unset numbering in a file -> :set nonumber [OR] :set nu!

CDC Vs Streams

Oracle CDC is all about capturing changes in DB tables and the changes are stored in special Oracle tables. There are two modes of CDC operation: asynchronous (based on Java) or synchronous (based on DB triggers, more performance overhead).

Oracle Streams sits on top of Oracle CDC and it's a full transport mechanism (over e.g. HTTP) for data synchronization between 2 servers. It's based on Oracle Advanced Queues technology and it's designed for high performance and reliability.

Both Oracle CDC and Streams are generally used for data synchronization between Oracle DB servers... With Oracle CDC, you don't have to use Oracle Streams for, e.g. you could write your own data export routines which create flat files for the purpose of synchronization between 2 DB servers, whereas with Streams you must have a network link between the 2 servers.

Database Change Notification is something else again, it's not used for server-to-server synch but instead more for server notification of resultset changes to clients, mostly in the context of data caches on the client side

An Overview of Workload Repository

The AWR adds persistence to the statistics collection facility. On a regular basis, MMON process transfers cumulative statistics in memory to the workload repository tables on disk. This ensures that statistics can survive through instance crashes, and aren’t lost when they are replaced by newer statistics.
Workload repository also ensures that historical data will be available for baseline comparisons. Before AWR, collecting this type of data required manual collection and management using Statspack or custom code. Workload repository data is owned by SYS user and is stored in SYSAUX tablespace. The data is stored in a collection of tables, all of which are named beginning with WR.

SQL> select table_name from dba_tables where tablespace_name=’SYSAUX’
and substr(table_name, 1, 2) = ‘WR’
and rownum<=20 order by 1;

Once in repository, the statistics can be accessed using data dictionary views.

Enabling AWR
To enable AWR, the STATISTICS_LEVEL initialization parameter must be set to TYPICAL or ALL. If it is set to BASIC, AWR statistics will not be gathered automatically, but they can be gathered manually using procedures in the built-in DBMS_WORKLOAD_REPOSITORY package. Note that manually gathered statistics will not be as complete as statistics gathered automatically through AWR. The workload repository is created automatically at the database creation time. No manual action is required.

AWR Space Considerations
A rough guideline is that an average system with an average of 10 concurrent active sessions will generate 200MB to 300MB of AWR data. This estimate assumes the default retention period of 7 days. The space used is determined by the number of active sessions, the snapshot interval, and the retention period. Space consumption can be reduced by either increasing the snapshot interval (resulting in less snapshots) or decreasing the retention period. Technically, you can also decrease your active sessions, but undoubtedly your users would not appreciate it. By reducing the available statistics, the accuracy and validity of the following components may be reduced as well:

• ADDM
• SQL Tuning Advisor
• Undo Advisor
• Segment Advisor

It is the responsibility of MMON process to purge data from repository when it has reached the end of the retention period.

Active Session History
In order to provide statistics on current session activity, Oracle 10g has introduced ASH.
Sizing ASH
ASH is actually a FIFO buffer in memory that collects statistics on current session activity. These statistics are gathered by extracting sampled from V$SESSION every second. Because this kind of frequent gathering could quickly overwhelm the system, ASH continually ages out old statistics to make room for new ones. ASH resides in SGA and its size is fixed for the lifetime of the instance. Its size is calculated by using: The lesser of:

• Total number of CPUs * 2MB of memory
• 5 percent of shared pool size

Therefore, two ways to increase the ASH buffer size:
Increase number of CPUs
Increase the shared pool size ASH Statistics

The following types of data are sampled by ASH:
SQL_ID
SID
Client ID, Service ID
Program, module, action
Object, file, block
Wait event number, actual wait time (if session is waiting) NOTE: SQL_ID is a hash value that uniquely identifies a SQL statement in the database. SQL_ID is new to 10g ASH Views

The statistics in ASH can be viewed using the V$ACTIVE_SESSION_HISTORY fixed view. ASH and AWR Because the data in ASH represents a unique set of statistics, Oracle captures some of the ASH statistics to the workload repository for persistent storage. This process is handled in two ways:

• Every 30 minutes, MMON process flushed ASH buffer of all data. In the process, it filters some of the data into the AWR. Due to the high volume of data, MMON process doesn’t filter all of the ASH data into AWR.

• If the ASH buffer fills in less than 30 minutes, MMNL (Memory Monitor Light) process will flush out a portion of the buffer (to make room for new statistics) and filter a portion of data to the AWR. Using AWR The primary interface for AWR is through Oracle EM Database Control. The link to access AWR can be found in Administration page. Under Workload, click Workload Repository link. From this page, you can manage AWR settings and snapshots. From this page, you can manage AWR settings and snapshots.

Oracle also provides DBMS_WORKLOAD_REPOSITORY package. Procedures in this package include:
CREATE_SNAPSHOT -> create manual snapshots
DROP_SNAPSHOT_RANGE ->Drops a range of snapshots at once
CREATE_BASELINE -> Creates a single baseline
DROP_BASELINE -> Drops a single baseline
MODIFY_SNAPSHOT_SETTINGS -> Changes the RETENTION and INTERVAL settings AWR Snapshots AWR collects performance statistics by taking snapshots of the system at regular intervals. Using Snapshots The snapshot pulls information from fixed tables that hold performance statistics in memory. By default, AWR generates performance data snapshots once every hour. This is known as snapshot interval. It also retains the snapshot statistics for seven days before automatically purging them. This is known as retention period. The data from these snapshots is analyzed by the ADDM for problem-detection and self-tuning.
To view, the current AWR settings, you can use the DBA_HIST_WR_CONTROL view, as shown here:
SQL> select snap_interval, retention from dba_hist_wr_control;

Each snapshot is assigned a unique snapshot ID, which is a sequence number guaranteed to be unique within the repository. The only exception to this is when using RAC. In an RAC environment, AWR snapshots will query every node within the cluster. In this situation, the snapshots for all nodes will share a snapshot ID. Instead they can be differentiated by the instance ID.

Creating Snapshots
To create a snapshot manually,

BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
END;

Modifying Snapshot Frequency
To make changes, use DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure.

Ex:
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
RETENTION => 14400, INTERVAL => 45);
END;

Dropping Snapshots
Exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
LOW_SNAP_ID => 316, HIGH_SNAP_ID => 320);

NOTE: This procedure can also be used to drop individual snapshots by using the same snapshot ID for both LOW_SNAP_ID and HIGH_SNAP_ID parameters.

AWR Baselines
A baseline is defined as a pair of snapshots that denote a significant workload period. This baseline can be retained for comparison to current system performance.

Using Baselines
Baselines can also be used to define threshold settings for Oracle’s server-generated alerts facility. AWR baselines also make an excellent tool for application performance and scalability testing.

Creating Baselines
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(
START_SNAP_ID => 42, END_SNAP_ID => 43,
BASELINE_NAME => ‘REPORTS’);
END;

Dropping Baselines
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(‘REPORTS’, FALSE);

DROP_BASELINE procedure parameters
BASELINE_NAME  Name of the baseline to be dropped
CASCADE  Boolean to determine whether associated snapshots will be dropped
DBID  Optional database ID

Using AWR Views
DBA_HIST_ACTIVE_SESS_HISTORY -> displays the session statistics gathered from ASH
DBA_HIST_BASELINE -> displays information on baselines in the repository
DBA_HIST_DATABASE_INSTANCE -> displays database environment data
DBA_HIST_SQL_PLAN -> displays SQL execution path data
DBA_HIST_WR_CONTROL -> displays current AWR settings
DBA_HIST_SNAPSHOT -> displays information regarding snapshots stored in AWR

SQL> select snap_id, begin_interval_time, end_interval_time from
dba_hist_snapshot order by 1;

Using AWR Reports
Oracle offers a standard summary report that can be run at any time against the statistics stored in AWR. This report provides an analysis of system performance over a specified period of time. This report is run through one of the two SQL*Plus scripts:
awrrpt.sql, which generates text file report
awrrpti.sql, which generates an HTML version of report

Granting Privileges needed to use AWR
GRANT SELECT ON SYS.V_$DATABASE TO ...
GRANT SELECT ON SYS.V_$INSTANCE TO ...
GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO ...
GRANT SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE TO ...
GRANT SELECT ON SYS.DBA_HIST_SNAPSHOT TO ...
GRANT ADVISOR TO ...

IMPDP using remap_schema and table_exists_action

TABLE_EXISTS_ACTION in IMPDP

• SKIP: The default value for TABLE_EXISTS_ACTION. If the table exists, then SKIP will cause the table (and any related data) to not be loaded. The table will be skipped.
• TRUNCATE: This will truncate the data in the table and load the data. If the table already exists, no metadata will be applied.
• APPEND: This will append the data to the end of the table. Again, if the table already exists, no metadata will be applied.
• REPLACE: This will drop the existing object, recreate the object with the information from the dumpfile, and then load the data.

IMPDP scenario

EXPDP scenario

To resolve the issue, set streams_pool_size in the init file.

PL/SQL Performance init parameters

Configuring PL/SQL for better Performance:
PLSQL_WARNING
PLSQL_DEBUG
PLSQL_OPTIMIZE_MODE
PLSQL_CODE_TYPE: specifies whether to compile PL/SQL code into default interpreted byte code or native machine code.

Procedures, Functions and Packages

Working with Functions:
Functions are PL/SQL programs that execute zero or more statements and returns value through a RETURN statement. Functions can also receive or return zero or more values through their parameters.

Ex: CREATE OR REPLACE FUNCTION is_weekend (check_date IN DATE DEFAULT SYSDATE)
RETURN VARCHAR2 AS
BEGIN
CASE TO_CHAR(check_date, ‘DY’)
WHEN ‘SAT’ THEN
RETURN ‘YES’;
WHEN ‘SUN’ THEN
RETURN ‘YES’;
ELSE
RETURN ‘NO’;
END CASE;
END;

SQL> call dbms_output.put_line(is_weekend(SYSDATE));

Call completed.

SQL> set serveroutput on;
SQL> call dbms_output.put_line(is_weekend(SYSDATE));
NO

Call completed.

Working with Procedures
Procedures are PL/SQL programs that execute one or more statements.

CREATE OR REPLACE PROCEDURE archive_orders
(cust_id IN NUMBER, retention IN NUMBER) IS
BEGIN
DELETE orders
WHERE customer = cust_id
AND order_date < style="color: rgb(255, 0, 0);">Working with Packages:
A package is a container for functions, procedures, and data structures, such as records, cursors, variables and constants. A package has a publicly visible portion, called the specification (or spec) and a private portion called the package body. Spec describes the programs and the data structures that can be accessed from other programs. The package body contains the implementation of functions and procedures. Package spec is identified in the data dictionary as the type PACKAGE, and the package body is identified as the type PACKAGE BODY.

Creating a package spec
CREATE OR REPLACE PACKAGE table_util IS
FUNCTION version RETURN varchar2;
PROCEDURE truncate (table_name IN varchar2);
END table_util;

Privileges on packages are granted at the package-spec level. The EXECUTE privilege on a package allows the grantee to execute any program or use any data structure declared in the package specification. A package body depends on a package spec having the same name.

Creating a package body
CREATE OR REPLACE PACKAGE BODY table_util IS
version_string VARCHAR2(8) := ‘1.0.0’;
FUNCTION version RETURN varchar2 IS
BEGIN
RETURN version_string;
END;
PROCEDURE truncate(table_name IN varchar2) IS
BEGIN
IF UPPER(table_name) = ‘ORDER_STAGE’
THEN
EXECUTE IMMEDIATE UPPER(table_name);
ELSE
RAISE_APPLICATION_ERROR(-20010, ‘Invalid table name ‘ || table_name);
END IF;
END;
END table_util;

To explicitly compile a PL/SQL program
ALTER PROCEDURE archive_orders COMPILE;
ALTER FUNCTION is_weekend COMPILE;
ALTER PACKAGE table_util COMPILE BODY;

Create Stat table, Export Stats, and Set table stat

Connect as sysdba and run this procedure to export the statistics to user defined statistics table i.e STATS_EMP in this example.

SQL> exec dbms_stats.export_table_stats ( -
> ownname => 'SCOTT', -
> tabname => 'EMP', -
> partname => NULL, -
> stattab => 'STATS_EMP', -
> statid => NULL, -
> cascade => TRUE, -
> statown => 'SCOTT');

PL/SQL procedure successfully completed.

SQL> conn scott/tiger
SQL> select * from STATS_EMP;

Transfering Stats
It is possible to transfer statistics between servers allowing consistent execution plans between servers with varying amounts of data. First the statistics must be collected into a statistics table. In the following examples the statistics for the APPSCHEMA user are collected into a new table, STATS_TABLE, which is owned by DBASCHEMA:

SQL> EXEC DBMS_STATS.create_stat_table('DBASCHEMA','STATS_TABLE');
SQL> EXEC DBMS_STATS.export_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');

This table can then be transfered to another server using your preferred method (Export/Import, SQLPlus Copy etc.) and the stats imported into the data dictionary as follows:

SQL> EXEC DBMS_STATS.import_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');
SQL> EXEC DBMS_STATS.drop_stat_table('DBASCHEMA','STATS_TABLE');

DBMS_STATS.SET_TABLE_STATS
exec DBMS_STATS.SET_TABLE_STATS ( -
ownname => 'SCOTT', -
tabname => 'DEPT', -
stattab => 'STATS_DEPT', -
statid => NULL, -
numrows => NULL, -
numblks => NULL, -
avgrlen => NULL, -
flags => NULL, -
statown => 'SCOTT');

SQL> @/database/test1/scripts/set_table_stats.sql

PL/SQL procedure successfully completed.

[oracle@vinay scripts]$ export ORACLE_SID=test1
[oracle@vinay scripts]$ ./gather_tab_stat.sh
Table: DEPT Completed

SQL> select count(*) from stats_dept;

COUNT(*)
----------
1