Chinaunix首页 | 论坛 | 博客
  • 博客访问: 663449
  • 博文数量: 779
  • 博客积分: 5000
  • 博客等级: 大校
  • 技术积分: 5000
  • 用 户 组: 普通用户
  • 注册时间: 2008-10-27 13:28
文章分类

全部博文(779)

文章存档

2011年(1)

2008年(778)

我的朋友

分类:

2008-10-27 13:38:12


  看到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, Corporation. All rights reserved.
  
  Connected to:
  10i 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;
  
  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)
  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了!
  
  
【责编:admin】

--------------------next---------------------

阅读(349) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~