How to Export and Import Statistics.
A critical application suddenly seems to hang, wait events show long table scans running on the OLTP environment.
It comes out that the DBA in charge of this system did run statistics on the tables of the user that owns the application.
The gather statistics got stuck and the process was killed. Since this moment the application started to perform extremely slowly.
The production database has several clones; we decide to export back statistics from one of these clones, to the production database.
This document will descripe the steps to perform the export and import statistics.
1. Create the stat table
SQL> exec DBMS_STATS.CREATE_STAT_TABLE('SCOTT','OLD_STATS');
PL/SQL procedure successfully completed.
SQL> GRANT SELECT,INSERT,UPDATE,DELETE ON SCOTT.OLD_STATS TO PUBLIC;
Grant succeeded.
SQL>
SQL> select 'exec dbms_stats.export_table_stats('||chr(39)||owner||chr(39)||','||chr(39)||table_name||chr(39)||',nu
2 ||chr(39)||',null,true,'||chr(39)||'SCOTT'||chr(39)||')'
3 from dba_tables where owner ='&usrname'
4
SQL> /
Enter value for usrname: SCOTT
old 3: from dba_tables where owner ='&usrname'
new 3: from dba_tables where owner ='SCOTT'
exec dbms_stats.export_table_stats('SCOTT','DEPT',null,'DEPT',null,true,'INV')
exec dbms_stats.export_table_stats('SCOTT','EMP',null,'EMP',null,true,'INV')
exec dbms_stats.export_table_stats('SCOTT','BONUS',null,'BONUS',null,true,'INV')
exec dbms_stats.export_table_stats('SCOTT','SALGRADE',null,'SALGRADE',null,true,'INV')
exec dbms_stats.export_table_stats('SCOTT','OLD_STATS',null,'OLD_STATS',null,true,'INV')
SQL>
SQL> select num_rows,last_analyzed from dba_tables where owner='SCOTT';
NUM_ROWS LAST_ANAL
---------- ---------
4 25-DEC-10
14 25-DEC-10
0 25-DEC-10
5 25-DEC-10
SQL> exec DBMS_STATS.EXPORT_SCHEMA_STATS('&username','&stattablename');
Enter value for username: SCOTT
Enter value for stattablename: OLD_STATS
PL/SQL procedure successfully completed.
SQL> COMMIT;
Commit complete.
SQL>
SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
E:\>exp system/sys file=soctt_stat.dmp tables=scott.old_stats
Export: Release 10.2.0.1.0 - Production on Sat Dec 25 14:58:20 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Product
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table OLD_STATS 24 rows exported
Export terminated successfully without warnings.
E:\>
I'm deleting statistics by using the below method.
SQL> exec DBMS_STATS.delete_SCHEMA_STATS('&username');
Enter value for username: SCOTT
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL>
SQL> seLEct stale_stats ,num_rows from dba_tab_statistics where owner='SCOTT' and stale_stats is not null;
no rows selected
Here, Im not importing existing statistics by using export dump. Im trying to generate the new stats
SQL> exec dbms_stats.gather_schema_stats -
> (ownname=> 'SCOTT', -
> cascade=>TRUE, -
> degree => 4, -
> estimate_percent=>dbms_stats.auto_sample_size,-
> options =>'GATHER');
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> seLEct stale_stats ,num_rows from dba_tab_statistics where owner='SCOTT' and stale_stats is not null;
NO 4
NO 14
NO 0
NO 5
NO 24
SQL>
Monday, 10 October 2011
How to Disable RecycleBin in Oracle 10g R1 & R2
On 10gR1, in case we want to disable the behavior of recycling, there is an underscore parameter "_recyclebin" which defaults to TRUE. We can disable recyclebin by setting it to FALSE.
From SYSDBA user:
SQL> SELECT a.ksppinm, b.ksppstvl, b.ksppstdf FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx AND a.ksppinm like '%recycle%'
ORDER BY a.ksppinm
/
Parameter Value Default?
---------------------------- ---------------------------------------- --------
_recyclebin TRUE TRUE
From BH user:
SQL> CREATE TABLE t1(a NUMBER);
Table created.
SQL> DROP TABLE t1;
Table dropped.
SQL> SELECT original_name FROM user_recyclebin;
ORIGINAL_NAME
--------------
T1
From SYSDBA user:
SQL> ALTER SYSTEM SET "_recyclebin"=FALSE SCOPE = BOTH;
System altered.
SQL> SELECT a.ksppinm, b.ksppstvl, b.ksppstdf FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx AND a.ksppinm like '%recycle%'
ORDER BY a.ksppinm
/
Parameter Value Default?
---------------------------- ---------------------------------------- --------
_recyclebin FALSE TRUE
From BH user:
SQL> CREATE TABLE t1(a NUMBER);
Table created.
SQL> DROP TABLE t1;
Table dropped.
SQL> SELECT original_name
FROM user_recyclebin;
no rows selected
There is no need to PURGE.
PS: Please avoid to user oracle hidden parameter. But before using hidden parameter please discuss with oracle support people.
In oracle 10g R2
On 10gR2 recyclebin is a initialization parameter and bydefault its ON. We can disable recyclebin by using the following commands:
SQL> ALTER SESSION SET recyclebin = OFF;
SQL> ALTER SYSTEM SET recyclebin = OFF;
The dropped objects, when recyclebin was ON will remain in the recyclebin even if we set the recyclebin parameter to OFF.
Posted by NITISH at Saturday, June 14, 2008 0 comments
Labels: Flashback Technology
Wednesday, July 11, 2007
Flashback Tech
In Oracle 10g, the flashback functionality has been greatly extended.
Flashback Database
Flashback Database is faster than traditional point-in-time recovery. Traditional recovery uses redo log files and backups. Flashback Database is implemented using a new type of log file called Flashback Database logs. The Oracle databa se server periodically logs before images of data blocks in the Flashback Database logs. The data block images are used to quickly back out changes to the database during Flashback Database.
Flashback Database reduces the time required to recover the database to a previous point. The time to restore a database is proportional to the number of changes that need to be backed out, not the size of the database.
RVWR Background Process
When Flashback Database is enabled, a new RVWR background process is started. This process is similar to the LGWR (log writer) process. The new process writes Flashback Database data to the Flashback Database logs.
RVWR Background process and Flashback Database Logs,
The list below shows all the background processes for the 'grid' instance.
$ ps -ef | grep grid
oracle 25169 1 0 16:32:22 ? 0:00 ora_rvwr_grid
Enabling Flashback Database
You can enable Flashback Database using the following steps:
1. Make sure the database is in archive mode.
2. Configure the recovery area by setting the two parameters:
- db_recovery_file_dest
- db_recovery_file_dest_size
3. Open the database in MOUNT EXCLUSIVE mode and turn on the flashback feature:
SQL> STARTUP MOUNT EXCLUSIVE;
SQL> ALTER DATABASE FLASHBACK ON;
4. Set the Flashback Database retention target:
- db_flashback_retention_target
5. Determine if Flashback Database is enabled:
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------
YES
Flashback Drop
Prior to Oracle 10g, a DROP command permanently removed objects from the database. In Oracle 10g, a DROP command places the object in the recycle bin. The extents allocated to the segment are not reallocated until you purge the object. You can restore the object from the recycle bin at any time.
This feature eliminates the need to perform a point-in-time recovery operation. Therefore, it has minimum impact to other database users.
Recycle Bin
A recycle bin contains all the dropped database objects until,
- You permanently drop them with the PURGE command.
- Recover the dropped objects with the UNDROP command.
- There is no room in the tablespace for new rows or updates to existing rows.
- The tablespace needs to be extended.
You can view the dropped objects in the recycle bin from two dictionary views:
- user_recyclebin - lists all dropped user objects
- dba_recyclebin - lists all dropped system-wide objects
Example :
QL> show user
USER is "SCOTT"
SQL>
SQL> create table tst (a int);
Table created.
SQL> select object_name,original_name,operation,type from user_recyclebin;
no rows selected
SQL> drop table tst;
Table dropped.
SQL> select object_name,original_name,operation,type from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE
------------------------------ -------------------------------- --------- -------------------------
BIN$NPgvUqA322DgQKjAAwIuMw==$0 TST DROP TABLE
SQL> create table tst (a int);
Table created.
SQL> drop table tst;
Table dropped.
SQL> select object_name,original_name,operation,type from user_recyclebin
SQL> /
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE
------------------------------ -------------------------------- --------- -------------------------
BIN$NPgvUqA322DgQKjAAwIuMw==$0 TST DROP TABLE
BIN$NPgvUqA422DgQKjAAwIuMw==$0 TST DROP TABLE
Restoring a Dropped Object
SQL> flashback table TST to before drop;
Flashback complete.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
BIN$NPgvUqA322DgQKjAAwIuMw==$0 TABLE
TST TABLE
6 rows selected.
SQL> select object_name,type from user_recyclebin;
OBJECT_NAME TYPE
------------------------------ -------------------------
BIN$NPgvUqA322DgQKjAAwIuMw==$0 TABLE
Dropping a Table Permanently
SQL> DROP TABLE TST PURGE;
Table dropped.
SQL> purge table tst;
Table purged.
SQL> purge recyclebin;
Recyclebin purged.
SQL> purge dba_recyclebin;
purge dba_recyclebin
*
ERROR at line 1:
ORA-01031: insufficient privileges
This statement purges all objects from tablespace users in the recycle bin
SQL> purge tablespace users;
Tablespace purged.
SQL>
Flashback Table
Flashback Table allows you to recover a table or tables to a specific point in time without restoring a backup. When you use the Flashback Table feature to restore a table to a specific point in time, all associated objects, such as indexes, constraints, and triggers will be restored.
Flashback Table operations are not valid for the following object types:
- Tables that are part of a cluster
- Materialized views
- Advanced Queuing tables
- Static data dictionary tables
- System tables
- Partitions of a table
- Remote tables (via database link)
Flashback Table is extremely useful when a user accidentally inserts, deletes, or updates the wrong rows in a table. It provides a way for users to easily and quickly recover a table to a previous point in time.
However, if the following DDL commands are issued, the flashback table command does not work:
- ALTER TABLE ... DROP COLUMN
- ALTER TABLE ... DROP PARTITION - CREATE CLUSTER - TRUNCATE TABLE
- ALTER TABLE ... MOVE
Flashback versions
Flashback Query was first introduced in Oracle9i to provide a way to view historical data. In Oracle 10g, this feature has been extended. You can now retrieve all versions of the rows that exist or ever existed between the time the query was issued and a point back in time. This type of query is called Flashback Row History.
You can use the VERSIONS BETWEEN clauses to retrieve all historical data related to a row.
Example:
SQL> CREATE TABLE NITISH (S INT);
Table created.
SQL> INSERT INTO NITISH VALUES (123);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
575002
SQL> UPDATE NITISH SET S=321;
1 row updated.
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
575090
SQL> DELETE FROM NITISH WHERE S=321;
1 row deleted.
SQL> L
1* DELETE FROM NITISH WHERE S=321
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
575105
SQL>
SQL> INSERT INTO NITISH VALUES (258);
1 row created.
SQL>
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
575112
SQL> UPDATE NITISH SET S=456;
1 row updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
575120
SQL> select versions_startscn as startscn, versions_endscn as endscn, versions_xid as xid,
2* versions_operation as oper, s from NITISH versions between scn 575002 and 575120
SQL> /
STARTSCN ENDSCN XID O S
---------- ---------- ---------------- - ----------
575118 09000E003A010000 I 456
575118 09000E003A010000 D 123
575118 123
Flashback Transaction
The Flashback Transaction History feature provides a way to view changes made to the database at the transaction level. It allows you to diagnose problems in your database and perform analysis and audit transactions. You can use this feature in conjunction with the Flash Row History feature to roll back the changes made by a transaction. You can also use this feature to audit user and application transactions. The Flashback Transaction History provides a faster way to undo a transaction than LogMiner.
You can retrieve the transaction history from dba_transaction_queryview:
Example :
SQL> select start_scn,commit_scn,logon_user,operation,table_name,undo_sql
2 from flashback_transaction_query
3* where xid= '09000E003A010000'
SQL> /
START_SCN COMMIT_SCN LOGON_USER OPERATION TABLE_NAME
---------- ---------- ------------------------------ -------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
UNDO_SQL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
574990 575118 SCOTT UPDATE NITISH
update "SCOTT"."NITISH" set "S" = '258' where ROWID = 'AAAM0ZAAEAAAAGEAAB';
574990 575118 SCOTT INSERT NITISH
delete from "SCOTT"."NITISH" where ROWID = 'AAAM0ZAAEAAAAGEAAB';
574990 575118 SCOTT DELETE NITISH
insert into "SCOTT"."NITISH"("S") values ('321');
From SYSDBA user:
SQL> SELECT a.ksppinm, b.ksppstvl, b.ksppstdf FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx AND a.ksppinm like '%recycle%'
ORDER BY a.ksppinm
/
Parameter Value Default?
---------------------------- ---------------------------------------- --------
_recyclebin TRUE TRUE
From BH user:
SQL> CREATE TABLE t1(a NUMBER);
Table created.
SQL> DROP TABLE t1;
Table dropped.
SQL> SELECT original_name FROM user_recyclebin;
ORIGINAL_NAME
--------------
T1
From SYSDBA user:
SQL> ALTER SYSTEM SET "_recyclebin"=FALSE SCOPE = BOTH;
System altered.
SQL> SELECT a.ksppinm, b.ksppstvl, b.ksppstdf FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx AND a.ksppinm like '%recycle%'
ORDER BY a.ksppinm
/
Parameter Value Default?
---------------------------- ---------------------------------------- --------
_recyclebin FALSE TRUE
From BH user:
SQL> CREATE TABLE t1(a NUMBER);
Table created.
SQL> DROP TABLE t1;
Table dropped.
SQL> SELECT original_name
FROM user_recyclebin;
no rows selected
There is no need to PURGE.
PS: Please avoid to user oracle hidden parameter. But before using hidden parameter please discuss with oracle support people.
In oracle 10g R2
On 10gR2 recyclebin is a initialization parameter and bydefault its ON. We can disable recyclebin by using the following commands:
SQL> ALTER SESSION SET recyclebin = OFF;
SQL> ALTER SYSTEM SET recyclebin = OFF;
The dropped objects, when recyclebin was ON will remain in the recyclebin even if we set the recyclebin parameter to OFF.
Posted by NITISH at Saturday, June 14, 2008 0 comments
Labels: Flashback Technology
Wednesday, July 11, 2007
Flashback Tech
In Oracle 10g, the flashback functionality has been greatly extended.
Flashback Database
Flashback Database is faster than traditional point-in-time recovery. Traditional recovery uses redo log files and backups. Flashback Database is implemented using a new type of log file called Flashback Database logs. The Oracle databa se server periodically logs before images of data blocks in the Flashback Database logs. The data block images are used to quickly back out changes to the database during Flashback Database.
Flashback Database reduces the time required to recover the database to a previous point. The time to restore a database is proportional to the number of changes that need to be backed out, not the size of the database.
RVWR Background Process
When Flashback Database is enabled, a new RVWR background process is started. This process is similar to the LGWR (log writer) process. The new process writes Flashback Database data to the Flashback Database logs.
RVWR Background process and Flashback Database Logs,
The list below shows all the background processes for the 'grid' instance.
$ ps -ef | grep grid
oracle 25169 1 0 16:32:22 ? 0:00 ora_rvwr_grid
Enabling Flashback Database
You can enable Flashback Database using the following steps:
1. Make sure the database is in archive mode.
2. Configure the recovery area by setting the two parameters:
- db_recovery_file_dest
- db_recovery_file_dest_size
3. Open the database in MOUNT EXCLUSIVE mode and turn on the flashback feature:
SQL> STARTUP MOUNT EXCLUSIVE;
SQL> ALTER DATABASE FLASHBACK ON;
4. Set the Flashback Database retention target:
- db_flashback_retention_target
5. Determine if Flashback Database is enabled:
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------
YES
Flashback Drop
Prior to Oracle 10g, a DROP command permanently removed objects from the database. In Oracle 10g, a DROP command places the object in the recycle bin. The extents allocated to the segment are not reallocated until you purge the object. You can restore the object from the recycle bin at any time.
This feature eliminates the need to perform a point-in-time recovery operation. Therefore, it has minimum impact to other database users.
Recycle Bin
A recycle bin contains all the dropped database objects until,
- You permanently drop them with the PURGE command.
- Recover the dropped objects with the UNDROP command.
- There is no room in the tablespace for new rows or updates to existing rows.
- The tablespace needs to be extended.
You can view the dropped objects in the recycle bin from two dictionary views:
- user_recyclebin - lists all dropped user objects
- dba_recyclebin - lists all dropped system-wide objects
Example :
QL> show user
USER is "SCOTT"
SQL>
SQL> create table tst (a int);
Table created.
SQL> select object_name,original_name,operation,type from user_recyclebin;
no rows selected
SQL> drop table tst;
Table dropped.
SQL> select object_name,original_name,operation,type from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE
------------------------------ -------------------------------- --------- -------------------------
BIN$NPgvUqA322DgQKjAAwIuMw==$0 TST DROP TABLE
SQL> create table tst (a int);
Table created.
SQL> drop table tst;
Table dropped.
SQL> select object_name,original_name,operation,type from user_recyclebin
SQL> /
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE
------------------------------ -------------------------------- --------- -------------------------
BIN$NPgvUqA322DgQKjAAwIuMw==$0 TST DROP TABLE
BIN$NPgvUqA422DgQKjAAwIuMw==$0 TST DROP TABLE
Restoring a Dropped Object
SQL> flashback table TST to before drop;
Flashback complete.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
BIN$NPgvUqA322DgQKjAAwIuMw==$0 TABLE
TST TABLE
6 rows selected.
SQL> select object_name,type from user_recyclebin;
OBJECT_NAME TYPE
------------------------------ -------------------------
BIN$NPgvUqA322DgQKjAAwIuMw==$0 TABLE
Dropping a Table Permanently
SQL> DROP TABLE TST PURGE;
Table dropped.
SQL> purge table tst;
Table purged.
SQL> purge recyclebin;
Recyclebin purged.
SQL> purge dba_recyclebin;
purge dba_recyclebin
*
ERROR at line 1:
ORA-01031: insufficient privileges
This statement purges all objects from tablespace users in the recycle bin
SQL> purge tablespace users;
Tablespace purged.
SQL>
Flashback Table
Flashback Table allows you to recover a table or tables to a specific point in time without restoring a backup. When you use the Flashback Table feature to restore a table to a specific point in time, all associated objects, such as indexes, constraints, and triggers will be restored.
Flashback Table operations are not valid for the following object types:
- Tables that are part of a cluster
- Materialized views
- Advanced Queuing tables
- Static data dictionary tables
- System tables
- Partitions of a table
- Remote tables (via database link)
Flashback Table is extremely useful when a user accidentally inserts, deletes, or updates the wrong rows in a table. It provides a way for users to easily and quickly recover a table to a previous point in time.
However, if the following DDL commands are issued, the flashback table command does not work:
- ALTER TABLE ... DROP COLUMN
- ALTER TABLE ... DROP PARTITION - CREATE CLUSTER - TRUNCATE TABLE
- ALTER TABLE ... MOVE
Flashback versions
Flashback Query was first introduced in Oracle9i to provide a way to view historical data. In Oracle 10g, this feature has been extended. You can now retrieve all versions of the rows that exist or ever existed between the time the query was issued and a point back in time. This type of query is called Flashback Row History.
You can use the VERSIONS BETWEEN clauses to retrieve all historical data related to a row.
Example:
SQL> CREATE TABLE NITISH (S INT);
Table created.
SQL> INSERT INTO NITISH VALUES (123);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
575002
SQL> UPDATE NITISH SET S=321;
1 row updated.
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
575090
SQL> DELETE FROM NITISH WHERE S=321;
1 row deleted.
SQL> L
1* DELETE FROM NITISH WHERE S=321
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
575105
SQL>
SQL> INSERT INTO NITISH VALUES (258);
1 row created.
SQL>
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
575112
SQL> UPDATE NITISH SET S=456;
1 row updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
575120
SQL> select versions_startscn as startscn, versions_endscn as endscn, versions_xid as xid,
2* versions_operation as oper, s from NITISH versions between scn 575002 and 575120
SQL> /
STARTSCN ENDSCN XID O S
---------- ---------- ---------------- - ----------
575118 09000E003A010000 I 456
575118 09000E003A010000 D 123
575118 123
Flashback Transaction
The Flashback Transaction History feature provides a way to view changes made to the database at the transaction level. It allows you to diagnose problems in your database and perform analysis and audit transactions. You can use this feature in conjunction with the Flash Row History feature to roll back the changes made by a transaction. You can also use this feature to audit user and application transactions. The Flashback Transaction History provides a faster way to undo a transaction than LogMiner.
You can retrieve the transaction history from dba_transaction_queryview:
Example :
SQL> select start_scn,commit_scn,logon_user,operation,table_name,undo_sql
2 from flashback_transaction_query
3* where xid= '09000E003A010000'
SQL> /
START_SCN COMMIT_SCN LOGON_USER OPERATION TABLE_NAME
---------- ---------- ------------------------------ -------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
UNDO_SQL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
574990 575118 SCOTT UPDATE NITISH
update "SCOTT"."NITISH" set "S" = '258' where ROWID = 'AAAM0ZAAEAAAAGEAAB';
574990 575118 SCOTT INSERT NITISH
delete from "SCOTT"."NITISH" where ROWID = 'AAAM0ZAAEAAAAGEAAB';
574990 575118 SCOTT DELETE NITISH
insert into "SCOTT"."NITISH"("S") values ('321');
What is Oracle Stream?
What is Oracle Stream?
Oracle Streams enables information sharing. Using Oracle Streams, each unit of shared information is called a message, and you can share these messages in a stream. The stream can propagate information within a database or from one database to another. The stream routes specified information to specified destinations. The result is a feature that provides greater functionality and flexibility than traditional solutions for capturing and managing messages, and sharing the messages with other databases and applications. Streams provides the capabilities needed to build and operate distributed enterprises and applications, data warehouses, and high availability solutions. You can use all of the capabilities of Oracle Streams at the same time. If your needs change, then you can implement a new capability of Streams without sacrificing existing capabilities.
Streams Information Flow
What Can Streams Do?
The following sections provide an overview of what Streams can do.
· Capture Messages at a Database
· Stage Messages in a Queue
· Propagate Messages from One Queue to Another
· Consume Messages
· Other Capabilities of Streams
A capture process can capture database events, such as changes made to tables, schemas, or an entire database. Such changes are recorded in the redo log for a database, and a capture process captures changes from the redo log and formats each captured change into a message called a logical change record (LCR). The rules used by a capture process determine which changes it captures, and these captured changes are called captured messages.
Etc…
What Are the Uses of Streams?
The following sections briefly describe some of the reasons for using Streams. In some cases, Streams components provide infrastructure for various features of Oracle.
• Message Queuing
• Data Replication
• Event Management and Notification
• Data Warehouse Loading
• Data Protection
• Database Availability During Upgrade and Maintenance Operations
Oracle Streams enables information sharing. Using Oracle Streams, each unit of shared information is called a message, and you can share these messages in a stream. The stream can propagate information within a database or from one database to another. The stream routes specified information to specified destinations. The result is a feature that provides greater functionality and flexibility than traditional solutions for capturing and managing messages, and sharing the messages with other databases and applications. Streams provides the capabilities needed to build and operate distributed enterprises and applications, data warehouses, and high availability solutions. You can use all of the capabilities of Oracle Streams at the same time. If your needs change, then you can implement a new capability of Streams without sacrificing existing capabilities.
Streams Information Flow
What Can Streams Do?
The following sections provide an overview of what Streams can do.
· Capture Messages at a Database
· Stage Messages in a Queue
· Propagate Messages from One Queue to Another
· Consume Messages
· Other Capabilities of Streams
A capture process can capture database events, such as changes made to tables, schemas, or an entire database. Such changes are recorded in the redo log for a database, and a capture process captures changes from the redo log and formats each captured change into a message called a logical change record (LCR). The rules used by a capture process determine which changes it captures, and these captured changes are called captured messages.
Etc…
What Are the Uses of Streams?
The following sections briefly describe some of the reasons for using Streams. In some cases, Streams components provide infrastructure for various features of Oracle.
• Message Queuing
• Data Replication
• Event Management and Notification
• Data Warehouse Loading
• Data Protection
• Database Availability During Upgrade and Maintenance Operations
Oracle ASM Commands
Running ASMCMD
You can run the ASMCMD utility in either interactive or noninteractive mode. Before running ASMCMD, you must ensure that you are logged in properly and that your environment is properly configured.
Preparing to Run ASMCMD
To prepare to run ASMCMD:
•Ensure that the ASM instance is started and the ASM disk groups are mounted. (You cannot use ASMCMD to mount disk groups.)
•Log in to the host that is running the ASM instance that you want to work with. You must log in as a user that has SYSDBA privileges through operating system authentication.
•Set the ORACLE_HOME and ORACLE_SID environment variables to select the ASM instance. Depending on your operating system, you may have to set other environment variables to properly connect to the ASM instance.
• The default value of the ASM SID for a single instance database is +ASM. On a Real Application Clusters system, the default value of the ASM SID on any node is+ASMnode#.
•Ensure that bin subdirectory of your Oracle home is in your PATH environment variable.
Running ASMCMD in Interactive Mode
The interactive mode of the ASMCMD utility provides a shell-like environment where you are prompted to enter ASMCMD commands.
To run ASMCMD in interactive mode:
1.At the operating system command prompt, enter:
asmcmd
An ASMCMD command prompt is displayed.
ASMCMD>
2.Enter an ASMCMD command and press the Enter key.
The command runs and displays its output, if any, and then ASMCMD prompts for the next command.
3.Continue entering ASMCMD commands. Enter the command exit to exit ASMCMD.
Including the Current Directory in the ASMCMD Prompt
You can specify the -p option with the asmcmd command to include the current directory in the ASMCMD prompt, as shown in the following example:
% asmcmd -p
ASMCMD [+] > cd dgroup1/mydir
ASMCMD [+DGROUP1/MYDIR] >
Running ASMCMD in Noninteractive Mode
In noninteractive mode, you run a single ASMCMD command by including the command and command arguments on the command line that invokes ASMCMD. ASMCMD runs the command, generates output if any, and then exits. The noninteractive mode is especially useful for running scripts.
To run ASMCMD in noninteractive mode:
•At the command prompt, enter:
asmcmd command arguments
where:
command is any valid ASMCMD command. arguments is a list of command flags and arguments.
The following example demonstrates running ASMCMD in the noninteractive mode.
% asmcmd ls -l
State Type Rebal Unbal Name
MOUNTED NORMAL N N DGROUP1/
MOUNTED NORMAL N N DGROUP2/
%
Getting Help
Type help at the ASMCMD prompt or as a command in noninteractive mode to view general ASMCMD help and a list of available commands.
ASMCMD Command Reference
You can run the ASMCMD utility in either interactive or noninteractive mode. Before running ASMCMD, you must ensure that you are logged in properly and that your environment is properly configured.
Preparing to Run ASMCMD
To prepare to run ASMCMD:
•Ensure that the ASM instance is started and the ASM disk groups are mounted. (You cannot use ASMCMD to mount disk groups.)
•Log in to the host that is running the ASM instance that you want to work with. You must log in as a user that has SYSDBA privileges through operating system authentication.
•Set the ORACLE_HOME and ORACLE_SID environment variables to select the ASM instance. Depending on your operating system, you may have to set other environment variables to properly connect to the ASM instance.
• The default value of the ASM SID for a single instance database is +ASM. On a Real Application Clusters system, the default value of the ASM SID on any node is+ASMnode#.
•Ensure that bin subdirectory of your Oracle home is in your PATH environment variable.
Running ASMCMD in Interactive Mode
The interactive mode of the ASMCMD utility provides a shell-like environment where you are prompted to enter ASMCMD commands.
To run ASMCMD in interactive mode:
1.At the operating system command prompt, enter:
asmcmd
An ASMCMD command prompt is displayed.
ASMCMD>
2.Enter an ASMCMD command and press the Enter key.
The command runs and displays its output, if any, and then ASMCMD prompts for the next command.
3.Continue entering ASMCMD commands. Enter the command exit to exit ASMCMD.
Including the Current Directory in the ASMCMD Prompt
You can specify the -p option with the asmcmd command to include the current directory in the ASMCMD prompt, as shown in the following example:
% asmcmd -p
ASMCMD [+] > cd dgroup1/mydir
ASMCMD [+DGROUP1/MYDIR] >
Running ASMCMD in Noninteractive Mode
In noninteractive mode, you run a single ASMCMD command by including the command and command arguments on the command line that invokes ASMCMD. ASMCMD runs the command, generates output if any, and then exits. The noninteractive mode is especially useful for running scripts.
To run ASMCMD in noninteractive mode:
•At the command prompt, enter:
asmcmd command arguments
where:
command is any valid ASMCMD command. arguments is a list of command flags and arguments.
The following example demonstrates running ASMCMD in the noninteractive mode.
% asmcmd ls -l
State Type Rebal Unbal Name
MOUNTED NORMAL N N DGROUP1/
MOUNTED NORMAL N N DGROUP2/
%
Getting Help
Type help at the ASMCMD prompt or as a command in noninteractive mode to view general ASMCMD help and a list of available commands.
ASMCMD Command Reference
Subscribe to:
Posts (Atom)