[ The imperialist Japanese military must officially acknowledge and apologize for the coercion of young women into sexual slaves. / êꪤҳàõªòàõªÎÒ¿Ö˪˪·ª¿ð¨ÏÐñ«ëùìíÜâÏÚªÎ?ð¤àõªòÍëãÒªËìãªá¡¢Þóñªª·ªÊª±ªìªÐªÊªéªÊª¤. ]

»ç¿ëÀÚ

ID:
PW:

INDEX
01.°Ô½ÃÆÇ
°Ô½ÃÆÇ [72]
02.File Book
File ÀÚ·á½Ç [96]
Site Link [14]
°³ÀÎÆú´õ [200]
03.Field Book
Altibase [19]
Tibero [31]
MS-SQL [32]
MySQL [47]
DB2 [79]
ORACLE [3121]
PSQL [139]
CUBRID [5]
±âŸÁ¤º¸ [178]
¿î¿µÃ¼Á¦ [618]
04.Q/A Book
Q/A [53]
05.¹æ¸í·Ï
¹æ¸í·Ï [54]
·¹º§¾÷ [37]
±¸ÀÎ/±¸Á÷ [2]

±â³äÀÏ
Search
LINK
+ °¡Á· Hompy
+ DNSEver
IOTN :: Field Book :: ORACLE

 È¸¿ø°¡ÀÔ

flashback table, flashback query [ 9i~ ] [ truncate ORA-01466 ]
ÃÖ±æÈ£ [LIST]   2006-01-18 11:58:54, Á¶È¸ : 21,997

Ãß°¡
select count(1) from sys.aud$
as of timestamp (sysdate-10/1440);

  COUNT(1)
----------
      3099

SYS@db11> select count(1) from sys.aud$ as of timestamp (sysdate-10/1440);
  COUNT(1)
----------
      3099

SYS@db11> truncate table sys.aud$;
Table truncated.

SYS@db11> select count(1) from sys.aud$ as of timestamp (sysdate-10/1440);
ORA-01466: unable to read data - table definition has changed



select distinct versions_xid, versions_startscn, versions_endscn from scott.emp
versions between scn minvalue and maxvalue ;

select operation,undo_sql from SYS.FLASHBACK_TRANSACTION_QUERY
where COMMIT_TIMESTAMP between
to_timestamp('2020-05-17 23:25', 'yyyy-mm-dd hh24:mi')
and
to_timestamp('2020-05-17 23:26', 'yyyy-mm-dd hh24:mi')
and table_name='EMP' and
xid = HEXTORAW('0009000F0000063F')
;


Ãß°¡
FLASHBACK TABLE emp TO BEFORE DROP
   RENAME TO int2_admin_emp;

SELECT OWNER,OBJECT_NAME, ORIGINAL_NAME, TYPE FROM DBA_RECYCLEBIN;
OWNER  OBJECT_NAME                    ORIGINAL_NAME             TYPE
-----  ------------------------------ ------------------------- --------
SCOTT  BIN$DBo9UChtZSbgQFeMiAdCcQ==$0 JHIST_JOB_IX              INDEX
SCOTT  BIN$DBo9UChuZSbgQFeMiAdCcQ==$0 JHIST_EMPLOYEE_IX         INDEX
SCOTT  BIN$DBo9UChvZSbgQFeMiAdCcQ==$0 JHIST_DEPARTMENT_IX       INDEX
SCOTT  BIN$DBo9UChwZSbgQFeMiAdCcQ==$0 JHIST_EMP_ID_ST_DATE_PK   INDEX
SCOTT  BIN$DBo9UChxZSbgQFeMiAdCcQ==$0 JOB_HISTORY               TABLE

SELECT OWNER,INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME = 'JOB_HISTORY';

ALTER INDEX SCOTT."BIN$DBo9UChtZSbgQFeMiAdCcQ==$0" RENAME TO JHIST_JOB_IX;


Ãß°¡
SELECT NAME, VALUE/60 MINUTES_RETAINED FROM V$PARAMETER WHERE NAME = 'undo_retention';

ALTER TABLE hr.employees ENABLE ROW MOVEMENT;

SELECT other.owner, other.table_name
FROM sys.all_constraints this, sys.all_constraints other
WHERE this.owner = 'HR'
AND this.table_name = 'EMPLOYEES'
AND this.r_owner = other.owner
AND this.r_constraint_name = other.constraint_name
AND this.constraint_type='R';

FLASHBACK TABLE hr.employees TO TIMESTAMP TO_TIMESTAMP('2012-03-27 09:30:00','YYYY-MM-DD HH24:MI:SS');



flashback table
============================================================================
select object_name,original_name,droptime from recyclebin order by droptime desc;
flashback table tname to before drop;
flashback table "BIN$i8mdH/ZDDBfgQKjABAoQOg==$0" to before drop rename to tname;

°£´ÜÇÑ »ç¿ë¿¹
============================================================================

SQL> select systimestamp from dual;

SYSTIMESTAMP
--------------------------------------------------------------
06/01/18 11:56:49.251000 +09:00

SQL> exec dbms_flashback.enable_at_time('06/01/18 11:54:34');


2006/03/27ÀÏ update
============================================================================
Flashback Query¸¦ ÀÌ¿ëÇÑ Table Recovery

1) °³¿ä

Å×À̺íÀÇ µ¥ÀÌÅ͸¦ »èÁ¦,º¯°æ ÈÄ Ä¿¹Ô ÇÏ¿´À» °æ¿ì º¹±¸ ¹æ¹ýÀº ¿©·¯°¡Áö°¡ ÀÖ´Ù.

* Point-In-Time Recovery : ¹é¾÷À» restore ÈÄ Æ¯Á¤½ÃÁ¡±îÁö media recovery ÈÄ
  resetlogs ¿É¼ÇÀ¸·Î ¿ÀÇÂ
* log miner : redo log fileÀ» ÀÌ¿ëÇÏ¿© undo statement¸¦ ÃßÃâÇØ º¹±¸
* flashback query : undo segment¸¦ ÀÌ¿ëÇÏ¿© undo_retention¿¡ ÁöÁ¤µÈ ½ÃÁ¡±îÁö º¹±¸
* FLASHBACK TABLE ¸í·É : 10g ¿¡¼­¸¸ °¡´É

2) °¡Á¤

* PITR ÀÇ °æ¿ì ½Ã½ºÅÛ ´Ù¿îÀÌ ÀÖÀ¸¹Ç·Î undo_retention ÀÌ Áö³µ°Å³ª
  redo log ¶Ç´Â archive log¸¦ »ç¿ëÇÒ ¼ö ¾øÀ» °æ¿ì ºÒ°¡ÇÇÇÏ°Ô Àû¿ë
  oracle 7 ÀÇ °æ¿ì´Â ¿À·ÎÁö PITR ¸¸ °¡´É

* undo_retention ¿¡ ÇØ´çÇÏ´Â ½Ã°£ÀÌ ÀÌ¹Ì °æ°úÇÏ¿© flashback query¸¦
  »ç¿ëÇÒ ¼ö ¾ø´Â °æ¿ì log miner Àû¿ë

* ¼³¸íÇÏ·Á´Â flashback queryÀÇ °æ¿ì 9.2.0.4 ¿¡¼­ Å×½ºÆ®ÇÏ¿´À¸¸ç
  8i ¿¡¼­µµ °¡´ÉÇÑÁö´Â Å×½ºÆ®ÇÏÁö ¸øÇÏ¿´À½

* flashback query¿¡ °üÇÑ ÀÚ¼¼ÇÑ ³»¿ëÀº ¾î´À Á¤µµ ¾Ë°í ÀÖ´Ù°í °¡Á¤

3) º¹±¸ ¹æ¹ý

flashback ¿¡ °ü·ÃµÈ ÀڷḦ ã¾Æº¸¸é 10g°¡ ¾Æ´Ñ ÇÑ ´ëºÎºÐ  PL/SQL À» ÀÌ¿ëÇÑ º¹±¸ ¹æ¹ýÀ» Á¦½ÃÇÏ°í ÀÖ´Ù.
ÇÏÁö¸¸ undo_retentionÀÌ ÀÛ°Ô ¼³Á¤µÇ¾î ÀÖÀ» °æ¿ì PL/SQL À» »ç¿ëÇÏ´õ¶óµµ º¹±¸´Â »¡¸® ÁøÇàµÇ±â ¾î·Æ´Ù.
´ëºÎºÐÀÇ ÀÚ·áµéÀº 8i¿¡¼­ Å×½ºÆ®µÈ ³»¿ëÀ» ´ã°í ÀÖÀ¸¹Ç·Î 8iÀÇ Á¦¾à»çÇ×ÀÌ´ø°¡
¾Æ´Ï¸é Å×½ºÆ®°¡ µÇÁö ¾Ê¾Ò´ø°¡ µÑÁßÀÇ Çϳª ÀÏ °ÍÀÌ´Ù.

µû¶ó¼­ flashback query¸¦ º¸´Ù ÆíÇÏ°Ô »ç¿ëÇÏ¿© º¹±¸¸¦ ÁøÇàÅä·Ï ÇÏ´Â ¹æ¹ýÀ» ¾Ë¾Æº»´Ù.
½Ã°£¿¡ µû¶ó ´ÙÀ½ SQL ¹®ÀåµéÀ» µû¶óÇØ º¸¸é °£´ÜÈ÷ º¹±¸ÀÛ¾÷À» ÇÒ ¼ö ÀÖ´Ù.

***********************************************************
SCOTT USER
***********************************************************

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),
  2         dbms_flashback.get_system_change_number
  3    from dual;

TO_CHAR(SYSDATE,'YY GET_SYSTEM_CHANGE_NUMBER
------------------- ------------------------
2005-10-06 10:53:17               1761113450

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> delete from dept;

4 rows deleted.

SQL> commit;

Commit complete.

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),
  2         dbms_flashback.get_system_change_number
  3    from dual;

TO_CHAR(SYSDATE,'YY GET_SYSTEM_CHANGE_NUMBER
------------------- ------------------------
2005-10-06 10:54:36               1761121544

***********************************************************
SYTEM USER
***********************************************************

SQL> grant execute on dbms_flashback to scott;

Grant succeeded.

=> undo_retention ¸¸ Áö³ªÁö ¾Ê¾Ò´Ù¸é ½ÇÁ¦·Î ÀÌ ½ÃÁ¡¿¡¼­ grant ÇØÁ൵ »ó°ü¾ø´Ù.
   ¶ÇÇÑ scott user´Â ÀÌ¹Ì ¼¼¼ÇÀ» ´Ý¾Ò¾îµµ »ó°ü¾ø´Ù.

***********************************************************
SCOTT USER

#1
***********************************************************

SQL> select * from dept;

no rows selected

SQL> exec dbms_flashback.enable_at_time(sysdate-5/1440);
=> ½Ã°£À» ÁöÁ¤ÇÏ¿© flashback

PL/SQL procedure successfully completed.

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> create table dept_recovered as select * from dept;
create table dept_recovered as select * from dept
*
ERROR at line 1:
ORA-08182: operation not supported while in Flashback mode

SQL> delete from dept;
delete from dept
            *
ERROR at line 1:
ORA-08182: operation not supported while in Flashback mode

=> flashback mode¿¡¼­´Â dml ¶Ç´Â ddl ÀÌ ¾ÈµÈ´Ù.

SQL> exec dbms_flashback.disable;

PL/SQL procedure successfully completed.

SQL> select * from dept;

no rows selected

***********************************************************
SCOTT USER

#2
***********************************************************

SQL> exec dbms_flashback.enable_at_system_change_number(1761113450);
=> SCNÀ» ÁöÁ¤ÇÏ¿© flashback

PL/SQL procedure successfully completed.

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> create table dept_recovered as select * from dept;
create table dept_recovered as select * from dept
*
ERROR at line 1:
ORA-08182: operation not supported while in Flashback mode

SQL> delete from dept;
delete from dept
            *
ERROR at line 1:
ORA-08182: operation not supported while in Flashback mode

=> flashback mode¿¡¼­´Â dml ¶Ç´Â ddl ÀÌ ¾ÈµÈ´Ù.

SQL> exec dbms_flashback.disable;

PL/SQL procedure successfully completed.

***********************************************************
SCOTT USER

#3
***********************************************************

SQL> select * from dept;

no rows selected

SQL> create table dept_backup as select * from dept where 1=2;

Table created.

SQL> exec dbms_flashback.enable_at_system_change_number(1761113450);

PL/SQL procedure successfully completed.

SQL> insert into dept_backup select * from dept;
insert into dept_backup select * from dept
            *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

=> dept_backup Å×À̺íÀ» flashback ½ÃÁ¡¿¡¼­´Â ¾Ë ¼ö ¾ø´Â°Ô ´ç¿¬ÇÏ´Ù.

SQL> exec dbms_flashback.disable;

PL/SQL procedure successfully completed.

******************************************************
Àá±ñ Á¤¸®

- flashback ¸ðµå¿¡¼­´Â dml, ddl ÀÌ ¾ÈµÇ¹Ç·Î À§¿Í °°Àº CTAS(create table as select) µµ ºÒ°¡´É
- ¹Ì¸® ¸¸µé¾î ³õÀº Å×ÀÌºí¿¡µµ insert ºÒ°¡´É
- ±×·¡¼­ PL/SQL À» »ç¿ëÇϴµ¥ º¹±¸¿¡´Â ¼Óµµ°¡ Áß¿äÇϹǷΠ»ç¿ë»ó¿¡ Á¦¾àÀÌ ¸¹À½

Á¦¾à ȸÇÇ ¹æ¹ý

- dbms_flashback package¸¦ »ç¿ëÇÏ¿© scn À» ÀÌÀü ½ÃÁ¡À¸·Î º¯°æÇÏÁö ¾Ê°í
  query ½ÇÇà ½ÃÁ¡¿¡¸¸ Àá±ñ¾¿ ¿Å°Ü backup Å×À̺íÀ» ¸¸µå´Â ¹æ¹ýÀ» »ç¿ëÇÑ´Ù.
- º¯°æµÈ °Ç¼ö°¡ ¸¹¾Æ¼­ Çϳª¾¿ üũÇÏ¿© º¹±¸ÇÏ´Â °Í º¸´Ù´Â backup Å×À̺íÀ» ¸¸µé¾î ¿¹Àüµ¥ÀÌÅ͸¦
  ¸ðµÎ º¯°æ½ÃÄÑ ³õ°í ÃßÈÄ minus ³ª hash anti-join, outer join µîÀ» ÀÌ¿ëÇÏ¿©
  º¯°æ ¶Ç´Â »èÁ¦µÈ ºÎºÐ¸¸ ÃßÃâÇÏ¿© º¹±¸ÇÏ´Â °ÍÀÌ È¿À²ÀûÀÌ´Ù.
******************************************************

SQL> select * from dept;

no rows selected

SQL> select * from dept as of scn 1761113450;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> select * from dept as of timestamp (sysdate-30/1440);

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> insert into dept_backup select * from dept as of scn 1761113450;

4 rows created.

SQL> create table dept_recovered as select * from dept as of scn 1761113450;

Table created.

SQL> select * from dept_backup;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> select * from dept_recovered;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

4) °á·Ð

Ãʱ⠴ëÀÀ¸¸ »¡¸® ÇÒ ¼ö ÀÖ´Ù¸é µ¥ÀÌÅÍ º¯°æ ½ÃÁ¡ ÀüÀÇ µ¥ÀÌÅ͸¦ ¿Â¶óÀÎ Áß¿¡¼­ ½±°Ô º¹±¸ÇÒ ¼ö ÀÖ´Ù.
18.97.9.172


ÃÖ±æÈ£
SET NLS_LANG=AMERICAN_AMERICA.KO16MSWIN949

-- sys or system
alter system set undo_retention=10800 --3H;
GRANT EXECUTE ON DBMS_FLASHBACK TO SCOTT;

-- user
-- oops delete -.-
delete emp where EMPNO=7934;
-- oops commit -.-^
commit;

alter session set nls_date_format='YY/MM/DD HH24:MI:SS';
select sysdate from dual;
exec dbms_flashback.enable_at_time( SYSDATE - 10 / 1440 );
-- exec dbms_flashback.enable_at_time( to_timestamp('08/10/13 11:30:00','YY/MM/DD HH24:MI:SS') );
select * from emp where EMPNO=7934;
exec dbms_flashback.disable();

-- select as of
select * from emp as of timestamp to_timestamp('08/10/13 11:30:00','YY/MM/DD HH24:MI:SS') where EMPNO=7934;
-- recover EMPNO=7934
insert into emp select * from emp as of timestamp to_timestamp('08/10/13 11:30:00','YY/MM/DD HH24:MI:SS')
where EMPNO=7934;

as of timestamp (sysdate-10/1440);
2008-10-13
17:17:52



ÃÖ±æÈ£
insert into emp
(select * from emp as of timestamp
to_timestamp('08/10/13 11:30:00','YY/MM/DD HH24:MI:SS'
minus select * from emp
);

081020 oracle magazine
2008-10-20
12:20:51



ÃÖ±æÈ£
-- flashback 10ºÐÀü data °Ë»ö
select count(1) from emp
as of timestamp (sysdate-10/1440)
where EMPNO=7934;

-- Àӽà Å×ÀÌºí »ý¼º
create table r_emp tablespace users
as select * from emp where 1=2;

-- 10ºÐÀü data recovery
insert into r_emp select * from emp
as of timestamp (sysdate-10/1440)
where EMPNO=7934;

--
INSERT INTO emp
(SELECT * FROM emp
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE)
where EMPNO=7934;
)
MINUS SELECT * FROM emp) where EMPNO=7934; ;
2008-11-20
17:41:16



ÃÖ±æÈ£
pkg È®ÀÎ

SELECT * FROM ALL_SOURCE
AS OF timestamp(systimestamp - interval '30' minute)
WHERE NAME = 'PKG_NAME'
ORDER BY LINE;
2019-05-02
16:47:58



  LIST

Á¦¸ñ ÀÛ¼ºÀÚ ÀÛ¼ºÀÏ Á¶È¸
Admin  [ °øÁö ] 😃 ¼­ºñ½º ½Ã°£ 08:00 ~ 23:00 / ¾ÈµÇ¸é ³ªÁß¿¡ ´Ù½Ã ¹æ¹® ÇØÁÖ¼¼¿ä~.😅    ÃÖ±æÈ£ 2008/11/26 114
Admin  [ ÇÊ¿ä ÇÏ½Å°Ô ÀÖÀ¸½Ã¸é ÂÊÁö ¶Ç´Â gilho.kr@gmail.com ¸ÞÀÏ ÁÖ¼¼¿ä~ ]    ÃÖ±æÈ£ 2006/07/14 135
Troubleshoot  Exception [type: SIGSEGV, Invalid permissions for mapped object] [ADDR:0x0] [PC:0x100137434, kggibr()+52] [flags: 0x0, count: 1]    ÃÖ±æÈ£ 2024/10/17 8
Admin  external table blob clob / archive log file    ÃÖ±æÈ£ 2024/10/16 12
Admin  ORA-01653: unable to extend table OGG.GGS_DDL_HIST or OGG.GGS_MARKER by 1024 in tablespace OGG    ÃÖ±æÈ£ 2024/10/15 6
Admin  ORA-12838 [ commit ÇÊ¿ä ]    ÃÖ±æÈ£ 2024/10/04 7
Admin  WARNING: Not enough physical memory for SHM_SHARE_MMU segment of size    ÃÖ±æÈ£ 2024/09/18 16
Admin  ORA-07217 [ Æú´õ ¿ÀŸ ±ÇÇÑ ¹®Á¦ ]    ÃÖ±æÈ£ 2024/09/02 5
Admin  C:\Windows\SYSTEM32\ntdll.dll    ÃÖ±æÈ£ 2024/08/26 21
Admin  19c sysaux SYS.WRI$_ADV_OBJECTS    ÃÖ±æÈ£ 2024/08/16 11
Admin  12c~ Unified Audit record write to audit trail table failed due to ORA-30032.    ÃÖ±æÈ£ 2024/08/16 14
Backup and Recovery  ORA-01141: error renaming data file 349 - new file '/oradata/data.dbf' not found    ÃÖ±æÈ£ 2024/08/13 9

    ¸ñ·Ïº¸±â   ´ÙÀ½ÆäÀÌÁö 1 [2][3][4][5][6][7][8][9][10]..[156]   [´ÙÀ½ 10°³]
       

Copyright 1999-2024 Zeroboard / skin by ÃÖ±æÈ£(gilho.kr@gmail.com)
ÃÖ±Ù ´ñ±Û
2021/06/30
À©µµ¿ì ¼­¹ö¿¡¼­ sqlplus .... ORA-...
by ÃÖ±æÈ£
2021/06/30
ORA-28500: connection from O...
by ÃÖ±æÈ£
2019/05/02
pkg È®ÀÎ SELECT * FROM AL...
by ÃÖ±æÈ£
ÃÖ±Ù °Ô½Ã¹°
11/21
[PSQL]
CentOS 5 ¼³Ä¡ °¡´É psql 11.22 ¼³Ä¡.
by ÃÖ±æÈ£
10/28
[PSQL]
ERROR: permission denied: "RI....
by ÃÖ±æÈ£
10/25
[¿î¿µÃ¼Á¦]
yum install / "Could not resol....
by ÃÖ±æÈ£