How to recycle and drop the space of a table

How to recycle and drop the space of a table? Q: oracle will not recycle the data blocks occupied by dropping a table, isn't the data that has been deleted logically backed up by RMAN in vain? Is there any way to recycle the space occupied by the drop table? Rhys answer: 1) After the database version 10.2.0.3, rman will not back up even the data blocks used but empty. 2) Explanation of the recycle bin: by default, the recycle bin function is enabled for 11g and 10g oracle: (9i no environment is determined) SYS @ orcl # show parameter recyclebin name type value =-------------------- restart recyclebin string onSYS @ orcl # We can choose nomount or open state change whether to enable alter system set recyclebin = off scope = spfile; when we use drop to delete a table, instead of deleting the table, we put it in the recycle bin, the table retention time of the recycle bin must be dynamically maintained based on the user's default tablespace size. Next I will do an experiment using purge and hope to help you: create a user: SYS @ orcl # create user rhys identified by root default tablespace test quota 2 M on test; the user has been created. SYS @ orcl # create user lily identified by root default tablespace test quota 2 M on test; the user has been created. SYS @ orcl # grant create session to rhys; authorization successful. SYS @ orcl # grant resource to rhys; authorization successful. SYS @ orcl # grant create session, resource to lily; authorization successful. SYS @ orcl ##### create a table RHYS @ orcl # create table t as select * from scott. emp; the table has been created. RHYS @ orcl # select count (*) from t; COUNT (*) ---------- 16 RHYS @ orcl # commit; the submission is complete. RHYS @ orcl # show recycleRHYS @ orcl # drop table t; the table has been deleted. RHYS @ orcl # show recycleORIGINAL name recyclebin name object type drop time ---------------- hour ------------ --------------------- t bin $ hour = $0 TABLE 2013-06-22: 12: 59: 42RHYS @ orcl # create table t1 as select * from scott. emp; the table has been created. RHYS @ orcl # drop table t1; the table has been deleted. RHYS @ orcl # show recyclebinORIGINAL name recyclebin name object type drop time ---------------- hour ----------------------------- t bin $ hour = $0 TABLE 2013-06-22: 12: 59: 42T1 BIN $ 37gMKgN5rWzgQKjABIlC2A = $0 TABLE 2013-06-22: 13: 00: 12RHYS @ orcl # select * from "BIN $ 37gMKgN4rWzgQKjABIlC2A = $0 "; empno ename job mgr hiredate sal comm deptno sex -------------------- ---------- Jun ---------- ------------ ---------- Jun 7369 SMITH hhhhh 7902-12 months-80 3020 20 WOMEN 7499 allen salesman 7698 20-2 months-81 4820 300 30 WOMEN 7521 ward salesman 7698 22-2 month-81 4470 500 30 WOMEN 7566 jones manager 7839 4195-81 7654 20 WOMEN 7698 martin salesman 4470 28-9 month-81 1400 7698 30 WOMEN 7839 blke MANAGER 01-5 month-81 40 70 30 WOMEN 7782 CLARK hello 7839-81 3670 10 WOMEN 7788 SCOTT h 7566-87 422 20 WOMEN 7839 king president 17-11 month-81 5220 10 WOMEN 7844 TURNER hello 7698 08-9 month-81 4720 0 30 WOMEN 7876 ADAMS hello 7788 3320 month-87 20 women empno ename job mgr hiredate sal comm deptno sex ---------- hour ---------------------------------------------------------------------------------------------------------- ---------------- 7000 MILLER hello 2000 23-1 month-82 3220 10 WOMEN 2000 rhys DBA 2000 01-5 month-13 3001 200 30 MEN 1000 xiaohai 20000 MEN 2003 xiao 2002 10 MEN 1113 blke 2000 50 MEN selected 16 rows. RHYS @ orcl #################### delete a table under a user and release space ###### RHYS @ orcl # show recyclebinORIGINAL name recyclebin name object type drop time -------------------------------------------- --------------------- t bin $ partition = $0 TABLE 2013-06-22: 12: 59: 42T1 BIN $ 37gMKgN5rWzgQKjABIlC2A ===$ 0 TABLE 2013-06-22: 13: 00: 12RHYS @ orcl # purge table t; the TABLE has been cleared. RHYS @ orcl # show recyclebinORIGINAL name recyclebin name object type drop time ---------------- hour ----------------------------- T1 BIN $ hour = $0 TABLE 2013-06-22: 13: 00: 12RHYS @ orcl ################### delete all tables under a user and release space ########## RHYS @ orcl # show recyclebinORIGINAL name recyclebin name object type drop time -------------------------------------------------------- -- ------------------- T bin $ partition = $0 TABLE 2013-06-22: 13: 03: 38T1 BIN $ 37gMKgN5rWzgQKjABIlC2A = $0 TABLE 2013-06-22: 13: 00: 12RHYS @ orcl # purge tablespace test user rhys; The tablespace has been cleared. RHYS @ orcl # show recyclebinRHYS @ orcl ################## specify purge when dropping a table to completely delete the table and recycle space # ###### RHYS @ orcl # drop table rhys_t purge; the table has been deleted. RHYS @ orcl # show recyclebinRHYS @ orcl ################## Delete tables of all users in a tablespace to release space ##### ### RHYS @ orcl # drop table rhys_t; the table has been deleted. RHYS @ orcl # drop table rhys_t1; the table has been deleted. RHYS @ orcl # show recyclebinORIGINAL name recyclebin name object type drop time ---------------- hour ----------------------------- RHYS_T BIN $ hour = $0 TABLE 2013-06-22: 13: 07: 46RHYS_T1 BIN $ response ==$ 0 TABLE 2013-06-22: 13: 07: 51RHYS @ orcl # LILY @ orcl # show recycleORIGINAL name recyclebin name object type drop time --------------------------------- ------------- ------------ ----------------- LILY_T BIN $ partition = $0 TABLE 2013-06-22: 13: 10: 09LILY_T1 BIN $ partition = $0 TABLE 2013-06-22: 13: 10: 13LILY @ orcl # LILY @ orcl # conn sys/root as sysdba; connected. SYS @ orcl # show recycleSYS @ orcl # purge tablespace test; the tablespace has been cleared. SYS @ orcl # conn lily/root is connected. LILY @ orcl # show recycleLILY @ orcl # conn rhys/root is connected. RHYS @ orcl # show recycleRHYS @ orcl #################### Delete the recyclebin of the current user ###### RHYS @ orcl # conn lily/root is connected. LILY @ orcl # create table lily_t as select * from scott. emp; the table has been created. LILY @ orcl # create table lily_t1 as select * from scott. emp; the table has been created. LILY @ orcl # drop table lily_t; the table has been deleted. LILY @ orcl # drop table lily_t1; the table has been deleted. LILY @ orcl # show recycleORIGINAL name recyclebin name object type drop time ------------------ hour --------------------------- LILY_T BIN $37 hour = $0 TABLE 2013-06-22: 13: 18: 10LILY_T1 BIN $37 hOKoIzdnrgQKjABIlDcw ==$ 0 TABLE 2013-06-22: 13: 18: 16LILY @ orcl # purge recyclebin 2; the recycle BIN has been cleared. LILY @ orcl # show recycleLILY @ orcl ################################ # deleting recycle Under all users, you must use the dba permission ##### LILY @ orcl # show recycleLILY @ orcl # create table lily_t as select * from scott. emp; the table has been created. LILY @ orcl # create table lily_t1 as select * from scott. emp; the table has been created. LILY @ orcl # drop table lily_t; the table has been deleted. LILY @ orcl # drop table lily_t1; the table has been deleted. LILY @ orcl # show recyclebinORIGINAL name recyclebin name object type drop time ---------------- hour ----------------------------- LILY_T BIN $ hour = $0 TABLE 2013-06-22: 13: 21: 45LILY_T1 BIN $ 37hOKoI1dnrgQKjABIlDcw ==$ 0 TABLE 2013-06-22: 13: 21: 50SCOTT @ orcl # select default_tablespace from user_users; DEFAULT_TABLESPACE limit --------------------------------------------- --------------- Users scott @ orcl # conn lily/root is connected. LILY @ orcl # select default_tablespace from user_users; DEFAULT_TABLESPACE------------------------------------------------------------TEST LILY @ orcl # conn scott/root SCOTT @ orcl # show recycleORIGINAL name recyclebin name object type drop time ---------------- hour ------------------------- SCOTT_T BIN $ detail = $0 TABLE 2013-06-22: 13: 23: 52SCOTT_T1 BIN $37 hikMvy SS3gQKjABIlDig = $0 TABLE 2013-06-22: 13: 23: 57LILY @ orcl # conn sys/root as sysdba is connected. SYS @ orcl # purge dba_recyclebin; the DBA Recycle Bin has been cleared. SYS @ orcl # conn lily/root is connected. LILY @ orcl # show recyclebin; LILY @ orcl # conn scott/root; connected. SCOTT @ orcl # show recyclebinSCOTT @ orcl ######################### delete a user's all recyclebin reclaim space ########## RHYS @ orcl # show recycle RHYS @ orcl # create table rhys_t as select * from scott. emp; the table has been created. RHYS @ orcl # create table rhys_t1 as select * from scott. emp; the table has been created. RHYS @ orcl # drop table rhys_t; the table has been deleted. RHYS @ orcl # drop table rhys_t1; the table has been deleted. RHYS @ orcl # show recycleORIGINAL name recyclebin name object type drop time ---------------- hour ----------------------------- RHYS_T BIN $ hour = $0 TABLE 2013-06-22: 13: 29: 02RHYS_T1 BIN $ 37h1A4fCz5HgQKjABIlDoA = $0 TABLE 2013-06-22: 13: 29: 06RHYS @ orcl # conn lily/rootSYS @ orcl # purge tablespace test user rhys; The tablespace has been cleared. SYS @ orcl # conn rhys/root is connected. RHYS @ orcl # show recyclebinRHYS @ orcl # RHYS @ orcl # conn lily/root is connected. LILY @ orcl # show recyclebinORIGINAL name recyclebin name object type drop time ---------------- hour ----------------------------- LILY_T BIN $ hour = $0 TABLE 2013-06-22: 13: 30: 16LILY_T1 BIN $ 37h5bvHQAg7gQKjABIlDpA ==$ 0 TABLE 2013-06-22: 13: 30: 19