select * from &&TABLE as of timestamp to_timestamp(’&&DD-&&MON-&&YY &HOUR:00:00′,’DD-MON-YY HH24: MI: SS’) minus select * from &&TABLE;
– this will allow you to browse backward to find the time when the change occurred
alter table &TABLE enable row movement;
– row movement must be enabled for flashback queries
flashback table &TABLE to timestamp to_timestamp(’&DD-&MON-&YY &HOUR:00:00′,’DD-MON-YY HH24:Mi:SS’);
http://www.dba-oracle.com/oracle_tips_db_copy.htm
Actual commands:
1. login to source db and backup the controlfile:
alter database backup controlfile to trace as ‘<filename>’ reuse;
2. shut down source database and target database
3. Copy all data files into the new directories on the new server. You may change the file names if you want, but you must
edit the controlfile to reflect the new data files names on the new server. DO NOT copy over the control files.
4. edit the trace control file and change all references to the old database name to the new database name. BEWARE that there
are 2 cases. Just delete all of the lines for the second case.
5. Look out for comments in sql blocks and remove them!! they cause the script to blow up.
6. Move the control files for the target database to backup.
7. Comment out the ‘RECOVER DATABASE’ — it is not necessary as the source was shutdown cleanly.
8. change the line:
CREATE CONTROLFILE REUSE DATABASE “OLDSQL” NORESETLOGS ARCHIVELOG
to
CREATE CONTROLFILE SET DATABASE “NEWLSQ” RESETLOGS ARCHIVELOG
9. The OPEN statement needs to be with the resetlogs clause.
10. login to the idle target database and run the updated trace script!
set pages 0
set long 999999
select dbms_metadata.get_ddl(’TABLE’, ‘EMP’, ‘SCOTT’) from dual;
NOTE: all of the parameters must be uppercase or the query will fail.
http://www.oracle.com/technology/products/manageability/database/pdf/ow03p/40091p.pdf
This can be seen in Enterprise Manager under ‘Server’ -> ‘Memory Advisors’.
Parts of the SGA:
Shared Pool –> library cache (storage for parsed queries)
Large Pool — used for RMAN and when MTS is configured the users’ session data is stored here.
Java Pool
Fixed SGA
Database Buffer Cache (where results of queries are stored)
Redo Log Buffer
When changing from dedicated server to shared (MTS – multithreaded shared server) the Large Pool may need to be increased because the users’s session data, etc is stored in the Large Pool. Also, the PGA (Program Global Area) is used only for stack space in MTS.
SQL> help index
Enter Help [topic] for help.
@ COPY PAUSE SHUTDOWN
@@ DEFINE PRINT SPOOL
/ DEL PROMPT SQLPLUS
ACCEPT DESCRIBE QUIT START
APPEND DISCONNECT RECOVER STARTUP
ARCHIVE LOG EDIT REMARK STORE
ATTRIBUTE EXECUTE REPFOOTER TIMING
BREAK EXIT REPHEADER TTITLE
BTITLE GET RESERVED WORDS (SQL) UNDEFINE
CHANGE HELP RESERVED WORDS (PL/SQL) VARIABLE
CLEAR HOST RUN WHENEVER OSERROR
COLUMN INPUT SAVE WHENEVER SQLERROR
COMPUTE LIST SET XQUERY
CONNECT PASSWORD SHOW
SQL>
