ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle10g的undrop功能一点实践

oracle10g的undrop功能一点实践

原创 Linux操作系统 作者:tolywang 时间:2007-07-09 00:00:00 0 删除 编辑
 看到oracle10g有防止表误删除的特点,我自己就小试了一把,
  下面是我的操作过程:
  bash-2.05$ sqlplus scott/tiger
  
  SQL*Plus: Release 10.1.0.0.0 - Beta on Mon Aug 18 16:50:18 2003
  
  Copyright (c) 1982, 2003, Oracle Corporation. All rights reserved.

  Connected to:
  Oracle10i Enterprise Edition Release 10.1.0.0.0 - Beta
  With the Partitioning and Oracle Data Mining options
  
  SQL> select * from tab;
  
  TNAME TABTYPE CLUSTERID
  ------------------------------
  BONUS TABLE
  DUMMY TABLE
  EMP TABLE
  EMP2 TABLE
  SALGRADE TABLE
  T TABLE
  
  6 rows selected.
  
  SQL> desc emp2;
  Name Null? Type
  ------------------------------
  EMPNO NOT NULL NUMBER(4)
  ENAME VARCHAR2(10)
  JOB VARCHAR2(9)
  MGR NUMBER(4)
  HIREDATE DATE
  SAL NUMBER(7,2)
  COMM NUMBER(7,2)
  DEPTNO NUMBER(2)
  
  SQL> select * from emp2; so.2168.net网管资料库任你搜
  
  EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  ---------- ---------- --------- -----
  7369 SMITH CLERK 7902 17-DEC-80 800 20
  7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
  7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
  
  SQL> drop table emp2;
  
  Table dropped.
  
  SQL> select * from emp2;   
  select * from emp2
  *
  ERROR at line 1:
  ORA-00942: table or view does not exist
  
  SQL> select * from tab;
  
  TNAME TABTYPE CLUSTERID
  -----------------------------
  BONUS TABLE
  DUMMY TABLE
  EMP TABLE
  RB$$8074$TABLE$0 TABLE(oracle在recycle bin里面生成的对象,就是emp2的拷贝)
  SALGRADE TABLE
  T TABLE
  
  6 rows selected.
  
  SQL> desc RB$$8074$TABLE$0;
  Name Null? Type
  ---------------------------
  EMPNO NOT NULL NUMBER(4)
  ENAME VARCHAR2(10)
  JOB VARCHAR2(9)
  MGR NUMBER(4)
  HIREDATE DATE
  SAL NUMBER(7,2) 需要什么来搜一搜吧so.2168.net
  COMM NUMBER(7,2)
  DEPTNO NUMBER(2)
  
  SQL> select * from RB$$8074$TABLE$0;
  
  EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  ---------- ---------- --------- ----------
  7369 SMITH CLERK 7902 17-DEC-80 800 20
  7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
  7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
  
  
  SQL> undrop table RB$$8074$TABLE$0;(应该是把表从Recycle Bin中去掉)
  
  Table undropped.
  
  SQL> alter table RB$$8074$TABLE$0 rename to emp2;
  
  Table altered.
  
  SQL> select * from emp2;
  
  EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  ---------- ---------- --------- ---------- ------
  7369 SMITH CLERK 7902 17-DEC-80 800 20
  7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
  7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
  
  可以看到所有的数据都在,对表可以执行DDL,DML操作
  SQL> insert into emp2 select * from emp;
  
  14 rows created.
  如果不执行“undrop table RB$$8074$TABLE$0”而直接执行“alter table RB$$8074$TABLE$0 rename to emp2”
  就会有如下问题:
  SQL> insert into emp2 select * from emp;
  insert into emp2 select * from emp
  *
  ERROR at line 1:
  ORA-38301: can not perform DDL/DML over objects in Recycle Bin
  
  如果要删除Recycle Bin中表可以:
  SQL> purge table RB$$8063$TABLE$0;
  
  Table purged.
  
  这样这个表就真的删除了,也就可能再undrop了!

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35489/viewspace-84746/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Oracle 10g flashback
请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13106021