One .1  BLOG Document structure chart

wps6D0A.tmp 

wps6D1B.tmp 

 

 

One .2   Preface

 

One .2.1   Guidance and notes

Technology enthusiasts , After reading this article , You can master the following skills , You can also learn something else you don't know ,~O(∩_∩)O~:

① Oracle An introduction to the recycle bin ( Turn on 、 close 、 Empty )

②  Flashback in the system flashback drop Introduction to

③ job Batch delete objects in the recycle bin

④ dba_free_space The problem of slow query speed (MOS: Queries on DBA_FREE_SPACE are Slow ( file  ID 271169.1))

 

  Tips:

① In this paper ITpub(http://blog.itpub.net/26736162) And blog Park (http://www.cnblogs.com/lhrbest) There are synchronous updates

② All the code used in this article , Related software , Please go to Xiaomai Miao's cloud disk to download relevant information (http://blog.itpub.net/26736162/viewspace-1624453/)

③  If the article code format is wrong , Recommend Sogou 、360 or QQ browser , You can also download pdf Format to view ,pdf Document download address :http://blog.itpub.net/26736162/viewspace-1624453/

④  This article BLOG I use gray background and pink font to show the output part of the command that needs special attention , For example, in the following example ,thread 1 The maximum archive log number for is 33,thread 2 The maximum archive log number for is 43 It's something that needs special attention ; Commands are usually marked with yellow background and red font ; Comments on the code or the output part of the code are generally in blue font .

 

  List of Archived Logs in backup set 11

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

  ---- ------- ---------- ------------------- ---------- ---------

  1    32      1621589    2015-05-29 11:09:52 1625242    2015-05-29 11:15:48

  1    33      1625242    2015-05-29 11:15:48 1625293    2015-05-29 11:15:58

  2    42      1613951    2015-05-29 10:41:18 1625245    2015-05-29 11:15:49

  2    43      1625245    2015-05-29 11:15:49 1625253    2015-05-29 11:15:53

 

 

 

 

[ZHLHRDB1:root]:/>lsvg -o

T_XDESK_APP1_vg

rootvg

[ZHLHRDB1:root]:/>

00:27:22 SQL> alter tablespace idxtbs read write;

 

 

====》2097152*512/1024/1024/1024=1G 

 

 

 

If there are any mistakes or imperfections in this article, please correct them ,ITPUB Message or QQ All can , Your criticism is the biggest motivation of my writing .

 

 

One .2.2   Links to related reference articles

 

【TSPITR】RMAN Automatic recovery of table space based on point in time  http://blog.itpub.net/26736162/viewspace-1671741/

 

More flashback knowledge reference :http://blog.csdn.net/tianlesoftware/article/details/4677378

 

 

 

 

One .2.3   Brief introduction

When executing the health check script today, the script has been stuck in the table space query , take a look at SQL, According to experience, it is estimated that wheat seedling is due to DBA_FREE_SPACE Because of the view , If there are many objects in this view, the query will be very slow , Next, do it alone select count(1) from dba_free_space; It's really slow , There is no way but to clean up the data in the recycle bin before querying the table space , And the recycle bin has about 200W The amount of data , perform purge DBA_RECYCLEBIN Very slow , Then we can only use job It's parallel technology , This script is given at the end of this article , This script is more general , This script is often used in the development of wheat seedlings , I hope you can master .

When it comes to recycle bin, it's about flashback , There are many types of flashbacks , Let's focus on flashback drop And recycle bin .

 

One .3   Related knowledge points literacy ( From the Internet + Personal summary )

 

 

One .3.1   Flashback

When an authorized user makes a mistake , You need to use tools to correct these errors .Oracle  database  10g  Provides a range of human error correction techniques , It's called flashback . Flashback fundamentally changes data recovery . In the past , The database can be corrupted in a few minutes , But it will take hours to recover . Using flashback Technology , The time to correct the error is almost the same as when the error occurred . And it's very easy to use , The entire database can be recovered with a short command , Instead of having to execute complex programs . Flashback technology provides a  SQL  Interface , Ability to quickly analyze and fix human errors . Flashback technology provides fine-grained external analysis and repair for local data corruption , For example, when a customer order is deleted by mistake . Flashback technology also supports the repair of more extensive damage , At the same time, it can quickly avoid long downtime , For example, when all customer orders of this month are deleted . Flashback technology is  Oracle  Unique features of database , Support recovery at all levels , Including line 、 Business 、 surface 、 Table space and database scope . Flashback technology is a great progress in the history of database recovery technology , Fundamentally changed data recovery .

Oracle 9i Implemented the flashback query based on rollback segment (Flashback Query) technology , That is, read the data of table operation within a certain period of time from the rollback segment , Recover the wrong DML operation .

stay Oracle 10g in , In addition to improving the flashback query function , Realize the flashback version query 、 Flashback outside the transaction query , Flashback table is also implemented 、 Flash delete and flash database functions .

Using flashback Technology , It can recover the changed data at the row level and transaction level , Reduced data recovery time , And it's easy to operate , adopt SQL Statement can achieve data recovery , Greatly improve the efficiency of database recovery .

One .3.2   Flashback technology classification  

1.  Flashback query (Flashback Query): Query a point in the past or a SCN The data information in the table ;

2.  Flashback version query (Flashback Version Query): Query a time period in the past or a SCN Changes in the data in the table within the segment ;

3.  Flashback transaction query (Flashback Transaction Query): View changes to data made by a transaction or all transactions over the past period of time ;

4.  Flashback table (Flashback Table): Restore the table to a past point in time or SCN The state of value ;

5.  flashback drop (Flashback Drop): Restore the deleted table and its associated objects to the state before deletion ;

6.  Flashback to the database (Flashback Database): Restore the database to a point in the past or SCN The state of value .

 

Be careful

①  Flashback query 、 Flashback version query 、 Flashback transaction query and flashback table are mainly based on rollback information in undo table space ;

②  Flashback deletion is based on Oracle 10g Recycle bin in (Recycle Bin) Feature implementation ;

③  The flashback database is based on the flashback recovery area (Flash Recovery Area) In the flash log to achieve ;

④  In order to use the flashback technology of database , Undo table space automatic management rollback information must be enabled .

⑤  If you want to use flash delete technology and flash database technology , You also need to enable the recycle bin 、 Flashback to recovery area .

 

 

One .3.3   flashback drop (Flashback Drop)

 Oracle10g Before , Once a table is deleted , Then the table will be deleted from the data dictionary . To recover the table , Incomplete recovery is required .Oracle10g in the future , When we delete a table , Default Oracle Just rename the deleted table in the database dictionary , There's no real table deletion . Flashback Drop  It's from Oracle 10g  The beginning of , It is used to recover the objects deleted by users ( Including table , Index, etc. ), This technology depends on Tablespace Recycle Bin( The table space recycle bin ), This function and windows Our recycle bin is very similar .

The recycle bin : Data dictionary used to maintain the corresponding relationship between the name of the table before deletion and the name generated by the system after deletion , Related objects on the table ( Indexes 、 The trigger etc. ) It'll go into the recycle bin as well

By drop Whether the dropped watch can flash back is related to two factors :

1、 This table is related to the size of the table space , That is, if the table space is large enough , use drop Statement , It's not really removed from the database , Instead, change the watch to BIN$ Table at the beginning , But if the table space is not large enough , When there is new data to be stored in the table space , It will cover these BIN$ The physical space of the table , At this time, there is no way to use flashback to recover the table

2、 Whether to use when deleting the table purge, If in drop It's time to use it purge, The table is completely deleted from the table space , If you want to recover , Must be restored with previous backup , It can be used TSPITR or 12c You can restore a single table directly from the backup set .

  Table space Recycle Bin  Area is just a logical area , Instead of physically delimiting an area from the table space and fixing it for the recycle bin , therefore Recycle Bin It's a storage area that shares the table space with ordinary objects , Or rather, Recycle Bin And ordinary objects grab storage space .

When there is not enough space ,Oracle Will be covered in first in first out order Recycle Bin Objects in the .

Flashback Drop  What to pay attention to :

1).  Can only be used for non system and locally managed table spaces

2).  Object reference constraints are not restored , The foreign key constraint pointing to the object needs to be rebuilt .

3).  Whether the object can be restored successfully , Depending on whether the object space is overridden and reused .

4). When you drop a table , Materialized views that rely on the table are also deleted , But because materialized views are not put in recycle bin, So when you execute flashback table to before drop  when , You can't recover materialized views that depend on it , need dba  Manual intervention to recreate .

5). about Recycle Bin Objects in the , Only query is supported .

 

One .3.4   Flashback the specified table

Sometimes a table can be created and drop, In this way recycle One origianl name There are multiple records relative to , The default will be to restore the last , If you want to restore the specified one, you can use their OBJECT_name By designation name The way .

select * from user_recyclebin t where t.original_name='OLD_T';

wps6D1C.tmp 

select * from "BINzltzJRsMB0PgRAAY/i3Kdw==zltzJRsMB0PgRAAY/i3Kdw==0";

 

Flashback to the specified table in the recycle bin :

flashback table "BINzltzJRsMB0PgRAAY/i3Kdw==zltzJRsMB0PgRAAY/i3Kdw==0" to before drop;

The default mode is "first in, last out" , Always restore the last deleted table .

One .3.5   Index processing after flashback operation

After the watch was restored , The index on the table , Need reconstruction , Although the index can flash back with the table , But the flashback index is still used recyclebin Name , So we need to rebuild the index . 

 

One .3.6   Recycle space

Since the deleted object has not been physically released , So how is the physical space recycled ?Oracle Recycling in two ways .

1、 Automatic recovery

When there is pressure in the table space ,Oracle The free space occupied by objects that do not belong to the recycle bin in the table space will be used first , If this space runs out , There's still space pressure , Then release the space occupied by the oldest objects in the recycle bin . Until all the space is released , Then extend the data file ( The premise is that the data file supports automatic expansion )

2、 Manual recycling

Use purge Command to release the space occupied by objects in the recycle bin

wps6D1D.tmp 

 

One .3.7   The size of the recycle bin object

The size of the table space dba_free_space Does not include the size of objects in the recycle bin .

SELECT nvl(a.owner, ' total ') owner,

       round(SUM(a.space *

                 (SELECT value FROM v$parameter WHERE name = 'db_block_size')) / 1024 / 1024,

             2) recyb_size_M,

       count(1) recyb_cnt

  FROM dba_recyclebin a

 GROUP BY ROLLUP(a.owner);

 

One .3.8   Open the recycle bin

Property

Description

Parameter type

String

Syntax

RECYCLEBIN = { on | off }

Default value

on

Modifiable

ALTER SESSION, ALTER SYSTEM ... DEFERRED

Basic

No

 

RECYCLEBIN is used to control whether the Flashback Drop capability is turned on or off. If the parameter is set to off, then dropped tables do not go into the recycle bin. If this parameter is set to on, then dropped tables go into the recycle bin and can be recovered

alter system set recyclebin = on scope=spfile;

alter session set recyclebin= on;

 

One .3.9   Recycle bin related SQL command

select * from "BINzltzJRsMB0PgRAAY/i3Kdw==zltzJRsMB0PgRAAY/i3Kdw==0";

select * from user_recyclebin;

select * from dba_recyclebin;

 

flashback table "BINzltzJRsMB0PgRAAY/i3Kdw==zltzJRsMB0PgRAAY/i3Kdw==0" to before drop;

flashback table t  to before drop rename to old_t;

show recyclebin

purge table test;// We purge In the recycle bin , The corresponding index will also be deleted .

purge index “ Index name ”;

purge tablespace users; Clear the recycle bin that belongs to users The space occupied by objects in a table space

purge user_recyclebin: Clear the space occupied by all objects belonging to the current user in the recycle bin

purge dba_recyclebin: Clear the space occupied by all objects in the recycle bin

drop table xxxx purge; Delete the table directly , Don't go into the recycle bin .

 

One .3.10   utilize job To empty the recycle bin

If the recycle bin has more content , Then use dba_recyclebin It's slow to empty the recycle bin , At this time, we can consider adopting job Split the way to clear the sky recycle bin , The script is as follows :

SELECT D.owner,COUNT(1) FROM dba_recyclebin D GROUP BY D.owner;

 

CREATE TABLE XB_recyclebin_LHR NOLOGGING AS 

SELECT ROWNUM RN, 'PURGE ' || A.type || ' ' || A.owner || '."' || A.object_name || '"' EXEC_SQL

  FROM dba_recyclebin A

 where a.type = 'TABLE';

 

CREATE INDEX IDX_recyclebin_rn  on XB_recyclebin_LHR(rn) NOLOGGING   PARALLEL;

 

create table XB_SPLIT_JOB_LHR

(

startrownum NUMBER(18),

endrownum   NUMBER(18),

flag        NUMBER(1)

); 

SELECT * FROM xb_split_job_lhr;

 

 

CREATE OR REPLACE PROCEDURE pro_split_job_lhr AUTHID CURRENT_USER IS

    ---------------------------------------------------------------------

    -- copy on 2012/4/2 23:28:21 by lhr

    --function: The save is used to separate data to create job

    -- The amount of data that needs to be processed  , The tables that need to be processed add rn Column , Value taking rownum,rn Columns with index

 

    --alter table tmp_dp_idp_lhr add rn number;

    /* CREATE INDEX IDX_tmp_dp_idp_lhr_rn  on tmp_dp_idp_lhr(rn)

    TABLESPACE SDH_INDEX ONLINE  NOLOGGING COMPUTE STATISTICS PARALLEL;*/

 

    /*  create table XB_SPLIT_JOB_LHR

    (

      startrownum NUMBER(18),

      endrownum   NUMBER(18),

      flag        NUMBER(1)

    )*/

    --------------------------------------------------------------------

 

    n               NUMBER; -- Created job Count

    j               NUMBER := 0;

    n_startrownum   NUMBER;

    n_endrownum     NUMBER;

    n_patchnum      NUMBER := 20000; -- Number of records per batch       ----modify

    v_jobname       VARCHAR2(200); 

    v_count         NUMBER; -- The amount of table data that needs to be processed

 

BEGIN

 

    SELECT COUNT(1) INTO v_count FROM XB_recyclebin_LHR; ----modify

 

    -- Need to create job Number

    n := trunc(v_count / n_patchnum) + 1;

 

     EXECUTE IMMEDIATE 'truncate table xb_split_job_lhr';

    WHILE j < n LOOP

 

        -- obtain rownum

        n_startrownum := j * n_patchnum + 1;

 

        IF j = n - 1 THEN

 

            n_endrownum := v_count;

        ELSE

            n_endrownum := (j + 1) * n_patchnum;

        END IF;

 

        INSERT INTO xb_split_job_lhr

            (startrownum, endrownum)

        VALUES

            (n_startrownum, n_endrownum);

        COMMIT;

 

        j := j + 1;

    END LOOP;

 

    -- Loop creation job

    j               := 0;

 

    FOR cur IN (SELECT * FROM xb_split_job_lhr) LOOP

 

        v_jobname := 'JOB_SUBJOB_SPLIT_LHR' || (j + 1);

        dbms_scheduler.create_job(job_name            => v_jobname,

                                  job_type            => 'STORED_PROCEDURE',

                                  job_action          => 'PRO_SUB_SPLIT_LHR', --modify

                                  number_of_arguments => 2,

                                  start_date          => SYSDATE + 1 / 5760, -- 15 Start the job in seconds

                                  repeat_interval     => NULL,

                                  end_date            => NULL,

                                  job_class           => 'DEFAULT_JOB_CLASS',

                                  enabled             => FALSE,

                                  auto_drop           => TRUE,

                                  comments            => 'to split job_subjob_Split_lhr');

        COMMIT;

 

        dbms_scheduler.set_job_argument_value(job_name          => v_jobname,

                                              argument_position => 1,

                                              argument_value    => cur.startrownum);

        COMMIT;

        dbms_scheduler.set_job_argument_value(job_name          => v_jobname,

                                              argument_position => 2,

                                              argument_value    => cur.endrownum);

        COMMIT;

        dbms_scheduler.enable(v_jobname);

        j := j + 1;

    END LOOP;

    COMMIT;

 

    ----- Waiting for all the children job After execution

 

    LOOP

 

        SELECT COUNT(1)

        INTO   v_count

        FROM   xb_split_job_lhr t

        WHERE  t.flag IS NULL;

 

        IF v_count = 0 THEN

            EXIT;

        ELSE

            dbms_lock.sleep(10); --- I had a rest 10 second

        END IF;

 

    END LOOP;

   EXECUTE IMMEDIATE 'purge dba_recyclebin';

EXCEPTION

    WHEN OTHERS THEN

       NULL;

 

END pro_split_job_lhr;

 

 

create or replace procedure pro_sub_split_lhr(p_startrownum number,

                                              p_endrownum   number) is

 

begin

 

  for cur in (SELECT A.EXEC_SQL

                FROM XB_recyclebin_LHR A ---modify

               where A.rn <= p_endrownum

                 and A.rn >= p_startrownum) loop

    begin

      EXECUTE IMMEDIATE CUR.EXEC_SQL;

    exception

      when others then

        null;

    end;

  end loop;

 

  commit;

 

  -- Update logo

  update xb_split_job_lhr t

     set t.flag = 1

   where t.startrownum = p_startrownum

     and t.endrownum = p_endrownum;

  commit;

 

exception

 

  when others then

 

    null;

 

end pro_sub_split_lhr;

 

One .3.11  MOS

wps6D2D.tmpwps6D2E.tmpwps6D2F.tmp

 

One .3.12   Official documents

wps6D40.tmp

When you drop a table, normally the database does not immediately release the space associated with the table. Rather, the database renames the table and places it in a recycle bin, where it can later be recovered with the FLASHBACK TABLE statement if you find that you dropped the table in error. If you should want to immediately release the space associated with the table at the time you issue the DROP TABLEstatement, include the PURGE clause as shown in the following statement:

DROP TABLE hr.admin_emp PURGE;

 

One .4  Using Flashback Drop and Managing the Recycle Bin

When you drop a table, the database does not immediately remove the space associated with the table. The database renames the table and places it and any associated objects in a recycle bin, where, in case the table was dropped in error, it can be recovered at a later time. This feature is called Flashback Drop, and the FLASHBACK TABLE statement is used to restore the table. Before discussing the use of theFLASHBACK TABLE statement for this purpose, it is important to understand how the recycle bin works, and how you manage its contents.

This section contains the following topics:

· 

What Is the Recycle Bin?

· 

· 

Viewing and Querying Objects in the Recycle Bin

· 

· 

Purging Objects in the Recycle Bin

· 

· 

Restoring Tables from the Recycle Bin

· 

One .4.1  What Is the Recycle Bin?

The recycle bin is actually a data dictionary table containing information about dropped objects. Dropped tables and any associated objects such as indexes, constraints, nested tables, and the likes are not removed and still occupy space. They continue to count against user space quotas, until specifically purged from the recycle bin or the unlikely situation where they must be purged by the database because of tablespace space constraints.

Each user can be thought of as having his own recycle bin, because, unless a user has the SYSDBAprivilege, the only objects that the user has access to in the recycle bin are those that the user owns. A user can view his objects in the recycle bin using the following statement:

SELECT * FROM RECYCLEBIN;

When you drop a tablespace including its contents, the objects in the tablespace are not placed in the recycle bin and the database purges any entries in the recycle bin for objects located in the tablespace. The database also purges any recycle bin entries for objects in a tablespace when you drop the tablespace, not including contents, and the tablespace is otherwise empty. Likewise:

· 

When you drop a user, any objects belonging to the user are not placed in the recycle bin and any objects in the recycle bin are purged.

· 

· 

When you drop a cluster, its member tables are not placed in the recycle bin and any former member tables in the recycle bin are purged.

· 

· 

When you drop a type, any dependent objects such as subtypes are not placed in the recycle bin and any former dependent objects in the recycle bin are purged.

· 

Object Naming in the Recycle Bin

When a dropped table is moved to the recycle bin, the table and its associated objects are given system-generated names. This is necessary to avoid name conflicts that may arise if multiple tables have the same name. This could occur under the following circumstances:

· 

A user drops a table, re-creates it with the same name, then drops it again.

· 

· 

Two users have tables with the same name, and both users drop their tables.

· 

The renaming convention is as follows:

BINuniqueiduniqueidversion

where:

· 

unique_id is a 26-character globally unique identifier for this object, which makes the recycle bin name unique across all databases

· 

· 

version is a version number assigned by the database

· 

One .4.2  Enabling and Disabling the Recycle Bin

When the recycle bin is enabled, dropped tables and their dependent objects are placed in the recycle bin. When the recycle bin is disabled, dropped tables and their dependent objects are not placed in the recycle bin; they are just dropped, and you must use other means to recover them (such as recovering from backup).

Disabling the recycle bin does not purge or otherwise affect objects already in the recycle bin. The recycle bin is enabled by default.

You enable and disable the recycle bin by changing the recyclebin initialization parameter. This parameter is not dynamic, so a database restart is required when you change it with an ALTER SYSTEMstatement.

To disable the recycle bin:

1. 

Issue one of the following statements:

2. 

ALTER SESSION SET recyclebin = OFF;

 

ALTER SYSTEM SET recyclebin = OFF SCOPE = SPFILE;

3. 

4. 

If you used ALTER SYSTEM, restart the database.

5. 

To enable the recycle bin:

1. 

Issue one of the following statements:

2. 

ALTER SESSION SET recyclebin = ON;

 

ALTER SYSTEM SET recyclebin = ON SCOPE = SPFILE;

3. 

4. 

If you used ALTER SYSTEM, restart the database.

5. 

See Also:

· 

"About Initialization Parameters and Initialization Parameter Files" for more information on initialization parameters

· 

· 

"Changing Initialization Parameter Values" for a description of dynamic and static initialization parameters

· 

One .4.3  Viewing and Querying Objects in the Recycle Bin

Oracle Database provides two views for obtaining information about objects in the recycle bin:

View

Description

USER_RECYCLEBIN

This view can be used by users to see their own dropped objects in the recycle bin. It has a synonym RECYCLEBIN, for ease of use.

DBA_RECYCLEBIN

This view gives administrators visibility to all dropped objects in the recycle bin

 

One use for these views is to identify the name that the database has assigned to a dropped object, as shown in the following example:

SELECT object_name, original_name FROM dba_recyclebin

   WHERE owner = 'HR';

 

OBJECT_NAME                    ORIGINAL_NAME

------------------------------ --------------------------------

BINyrMKlZaLMhfgNAgAIMenRA==yrMKlZaLMhfgNAgAIMenRA==0 EMPLOYEES

You can also view the contents of the recycle bin using the SQL*Plus command SHOW RECYCLEBIN.

SQL> show recyclebin

 

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

---------------- ------------------------------ ------------ -------------------

EMPLOYEES        BINyrMKlZaVMhfgNAgAIMenRA==yrMKlZaVMhfgNAgAIMenRA==0 TABLE        2003-10-27:14:00:19

You can query objects that are in the recycle bin, just as you can query other objects. However, you must specify the name of the object as it is identified in the recycle bin. For example:

SELECT * FROM "BINyrMKlZaVMhfgNAgAIMenRA==yrMKlZaVMhfgNAgAIMenRA==0";

One .4.4  Purging Objects in the Recycle Bin

If you decide that you are never going to restore an item from the recycle bin, you can use the PURGEstatement to remove the items and their associated objects from the recycle bin and release their storage space. You need the same privileges as if you were dropping the item.

When you use the PURGE statement to purge a table, you can use the name that the table is known by in the recycle bin or the original name of the table. The recycle bin name can be obtained from either the DBA_or USER_RECYCLEBIN view as shown in "Viewing and Querying Objects in the Recycle Bin". The following hypothetical example purges the table hr.int_admin_emp, which was renamed to BINjsleilx392mk2=293jsleilx392mk2=2930 when it was placed in the recycle bin:

PURGE TABLE "BINjsleilx392mk2=293jsleilx392mk2=2930";

You can achieve the same result with the following statement:

PURGE TABLE int_admin_emp;

You can use the PURGE statement to purge all the objects in the recycle bin that are from a specified tablespace or only the tablespace objects belonging to a specified user, as shown in the following examples:

PURGE TABLESPACE example;

PURGE TABLESPACE example USER oe;

Users can purge the recycle bin of their own objects, and release space for objects, by using the following statement:

PURGE RECYCLEBIN;

If you have the SYSDBA privilege, then you can purge the entire recycle bin by specifying DBA_RECYCLEBIN, instead of RECYCLEBIN in the previous statement.

You can also use the PURGE statement to purge an index from the recycle bin or to purge from the recycle bin all objects in a specified tablespace.

See Also:

Oracle Database SQL Language Reference for more information on the PURGE statement

One .4.5  Restoring Tables from the Recycle Bin

Use the FLASHBACK TABLE ... TO BEFORE DROP statement to recover objects from the recycle bin. You can specify either the name of the table in the recycle bin or the original table name. An optional RENAME TOclause lets you rename the table as you recover it. The recycle bin name can be obtained from either theDBA_ or USER_RECYCLEBIN view as shown in "Viewing and Querying Objects in the Recycle Bin". To use the FLASHBACK TABLE... TO BEFORE DROP statement, you need the same privileges required to drop the table.

The following example restores int_admin_emp table and assigns to it a new name:

FLASHBACK TABLE int_admin_emp TO BEFORE DROP

   RENAME TO int2_admin_emp;

The system-generated recycle bin name is very useful if you have dropped a table multiple times. For example, suppose you have three versions of the int2_admin_emp table in the recycle bin and you want to recover the second version. You can do this by issuing two FLASHBACK TABLE statements, or you can query the recycle bin and then flashback to the appropriate system-generated name, as shown in the following example. Including the create time in the query can help you verify that you are restoring the correct table.

SELECT object_name, original_name, createtime FROM recyclebin;   

 

OBJECT_NAME                    ORIGINAL_NAME   CREATETIME

------------------------------ --------------- -------------------

BINyrMKlZaLMhfgNAgAIMenRA==yrMKlZaLMhfgNAgAIMenRA==0 INT2_ADMIN_EMP  2006-02-05:21:05:52

BINyrMKlZaVMhfgNAgAIMenRA==yrMKlZaVMhfgNAgAIMenRA==0 INT2_ADMIN_EMP  2006-02-05:21:25:13

BINyrMKlZaQMhfgNAgAIMenRA==yrMKlZaQMhfgNAgAIMenRA==0 INT2_ADMIN_EMP  2006-02-05:22:05:53

 

FLASHBACK TABLE "BINyrMKlZaVMhfgNAgAIMenRA==yrMKlZaVMhfgNAgAIMenRA==0" TO BEFORE DROP;

Restoring Dependent Objects

When you restore a table from the recycle bin, dependent objects such as indexes do not get their original names back; they retain their system-generated recycle bin names. You must manually rename dependent objects to restore their original names. If you plan to manually restore original names for dependent objects, ensure that you make note of each dependent object's system-generated recycle bin name before you restore the table.

The following is an example of restoring the original names of some of the indexes of the dropped tableJOB_HISTORY, from the HR sample schema. The example assumes that you are logged in as the HR user.

1. 

After dropping JOB_HISTORY and before restoring it from the recycle bin, run the following query:

2. 

SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE FROM RECYCLEBIN;

 

OBJECT_NAME                    ORIGINAL_NAME             TYPE

------------------------------ ------------------------- --------

BINDBo9UChtZSbgQFeMiAdCcQ==DBo9UChtZSbgQFeMiAdCcQ==0 JHIST_JOB_IX              INDEX

BINDBo9UChuZSbgQFeMiAdCcQ==DBo9UChuZSbgQFeMiAdCcQ==0 JHIST_EMPLOYEE_IX         INDEX

BINDBo9UChvZSbgQFeMiAdCcQ==DBo9UChvZSbgQFeMiAdCcQ==0 JHIST_DEPARTMENT_IX       INDEX

BINDBo9UChwZSbgQFeMiAdCcQ==DBo9UChwZSbgQFeMiAdCcQ==0 JHIST_EMP_ID_ST_DATE_PK   INDEX

BINDBo9UChxZSbgQFeMiAdCcQ==DBo9UChxZSbgQFeMiAdCcQ==0 JOB_HISTORY               TABLE

3. 

4. 

Restore the table with the following command:

5. 

FLASHBACK TABLE JOB_HISTORY TO BEFORE DROP;

6. 

7. 

Run the following query to verify that all JOB_HISTORY indexes retained their system-generated recycle bin names:

8. 

SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'JOB_HISTORY';

 

INDEX_NAME

------------------------------

BINDBo9UChwZSbgQFeMiAdCcQ==DBo9UChwZSbgQFeMiAdCcQ==0

BINDBo9UChtZSbgQFeMiAdCcQ==DBo9UChtZSbgQFeMiAdCcQ==0

BINDBo9UChuZSbgQFeMiAdCcQ==DBo9UChuZSbgQFeMiAdCcQ==0

BINDBo9UChvZSbgQFeMiAdCcQ==DBo9UChvZSbgQFeMiAdCcQ==0

9. 

10. 

Restore the original names of the first two indexes as follows:

11. 

ALTER INDEX "BINDBo9UChtZSbgQFeMiAdCcQ==DBo9UChtZSbgQFeMiAdCcQ==0" RENAME TO JHIST_JOB_IX;

ALTER INDEX "BINDBo9UChuZSbgQFeMiAdCcQ==DBo9UChuZSbgQFeMiAdCcQ==0" RENAME TO JHIST_EMPLOYEE_IX;

12. 

Note that double quotes are required around the system-generated names.

13. 

 

---------------------------------------------------------------------------------------------------------------------

 

 

Chapter two   Experimental part

Two .1   Introduction to the experimental environment

project

primary db

db  type

Single instance

db version

11.2.0.2.0

db  Storage

ASM

 

 

Two .2   The goal of the experiment

This time we simulate 2 An experiment :

1、 Objects in the system table space cannot be flashback

2、 In the version 11.2.0.3 And below , When there are too many objects in the recycle bin, querying the table space involves dba_free_space Very slowly , use purge dba_recyclebin It's too slow , So using job To delete in batches

 

Two .3   Experimental process

Two .3.1   Experiment 1 :

First, set up the test library and open the recycle bin function :

[ZT1MXP11:oracle]:/oracle>dbca -silent -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE \

> -gdbname oralhr  -sid oralhr \

> -sysPassword oracle -systemPassword lhr \

> -datafileDestination 'DATA2/' -recoveryAreaDestination 'DATA2/' \

> -redoLogFileSize 50 \

> -storageType ASM -asmsnmpPassword lhr  -diskGroupName 'DATA2' \

> -characterset AL32UTF8 -nationalCharacterSet AL16UTF16 \

> -sampleSchema false \

> -automaticMemoryManagement true -totalMemory 2048 \

> -databaseType OLTP  \

> -emConfiguration NONE

Copying database files

1% complete

3% complete

10% complete

17% complete

24% complete

31% complete

35% complete

Creating and starting Oracle instance

37% complete

42% complete

47% complete

52% complete

53% complete

56% complete

58% complete

Registering database with Oracle Restart

64% complete

Completing Database Creation

68% complete

71% complete

75% complete

85% complete

96% complete

100% complete

Look at the log file "/oracle/app/oracle/cfgtoollogs/dbca/oralhr/oralhr.log" for further details.

[ZT1MXP11:oracle]:/oracle>more /oracle/app/oracle/cfgtoollogs/dbca/oralhr/oralhr.log

Copying database files

DBCA_PROGRESS : 1%

DBCA_PROGRESS : 3%

DBCA_PROGRESS : 10%

DBCA_PROGRESS : 17%

DBCA_PROGRESS : 24%

DBCA_PROGRESS : 31%

DBCA_PROGRESS : 35%

Creating and starting Oracle instance

DBCA_PROGRESS : 37%

DBCA_PROGRESS : 42%

DBCA_PROGRESS : 47%

DBCA_PROGRESS : 52%

DBCA_PROGRESS : 53%

DBCA_PROGRESS : 56%

DBCA_PROGRESS : 58%

Registering database with Oracle Restart

DBCA_PROGRESS : 64%

Completing Database Creation

DBCA_PROGRESS : 68%

DBCA_PROGRESS : 71%

DBCA_PROGRESS : 75%

DBCA_PROGRESS : 85%

DBCA_PROGRESS : 96%

DBCA_PROGRESS : 100%

Database creation complete. For details check the logfiles at:

/oracle/app/oracle/cfgtoollogs/dbca/oralhr.

Database Information:

Global Database Name:oralhr

System Identifier(SID):oralhr

[ZT1MXP11:oracle]:/oracle>

 

[ZT1MXP11:oracle]:/oracle>ORACLE_SID=oralhr

[ZT1MXP11:oracle]:/oracle>sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jun 27 10:12:18 2016

 

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

 

SYS@oralhr> show parameter recy

 

NAME                                 TYPE                   VALUE

------------------------------------ ---------------------- ------------------------------

buffer_pool_recycle                  string

db_recycle_cache_size                big integer            0

recyclebin                           string                 OFF

SYS@oralhr> alter system set recyclebin=on scope=spfile;

 

System altered.

 

SYS@oralhr> startup force;  ====》 Use with caution , Not recommended

ORACLE instance started.

 

Total System Global Area 3089920000 bytes

Fixed Size                  2250360 bytes

Variable Size             721422728 bytes

Database Buffers         2348810240 bytes

Redo Buffers               17436672 bytes

Database mounted.

Database opened.

SYS@oralhr>  show parameter recy

 

NAME                                 TYPE                   VALUE

------------------------------------ ---------------------- ------------------------------

buffer_pool_recycle                  string

db_recycle_cache_size                big integer            0

recyclebin                           string                 ON

 

SYS@oralhr> create table tb_20160627_lhr as select * from dual;

 

Table created.

 

SYS@oralhr> drop table tb_20160627_lhr;

 

Table dropped.

 

SYS@oralhr> select * from dba_recyclebin;

 

no rows selected

 

SYS@oralhr> create table  tb_20160627_lhr tablespace users as select * from dual;

 

Table created.

 

SYS@oralhr> drop table tb_20160627_lhr;

 

Table dropped.

 

SYS@oralhr> select * from dba_recyclebin;

 

OWNER                          OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE                      TS_NAME                        CREATETIME

------------------------------ ------------------------------ -------------------------------- --------- ------------------------- ------------------------------ -------------------

DROPTIME               DROPSCN PARTITION_NAME                   CAN CAN    RELATED BASE_OBJECT PURGE_OBJECT      SPACE

------------------- ---------- -------------------------------- --- --- ---------- ----------- ------------ ----------

SYS                            BINNjoq6PZtAGzgUxa8wKsAbA==Njoq6PZtAGzgUxa8wKsAbA==0 TB_20160627_LHR                  DROP      TABLE                     USERS                          2016-06-27:11:16:01

2016-06-27:11:16:05    7268816                                  YES YES     450051      450051       450051          8

 

 

SYS@oralhr>

explain SYSTEM Table of a table space drop It won't enter the recycle bin space .

 

 

 

Two .3.2   Experiment two :

We follow the following experimental steps :

1、 establish 10W A watch , And create indexes

2、 Open the recycle bin

3、 Delete the created table

4、 Inquire about dba_free_space View

5、 Empty the recycle bin and check again dba_free_space View

 

 

 

The experiment begins : First, we use the script to create a table 10W A watch , You can open a few more windows , Parallel table building speeds up , in addition ,10W A watch takes about users Table space 6G many , This needs attention :

wait for 10W Cancel the table creation statement when the table is finished :

[ZT1MXP11:oracle]:/oracle>ORACLE_SID=oralhr

[ZT1MXP11:oracle]:/oracle>sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jun 27 09:12:18 2016

 

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

 

SYS@oralhr> begin

  2 

  3    for cur in 1 .. 100000 loop

  4   

  5      execute immediate 'create table tb_recyclebin_' || cur ||

  6                        ' nologging tablespace users as select * from dual';

  7      execute immediate 'create index idx_recyclebin_' || cur ||

  8                        ' on tb_recyclebin_' || cur ||' (dummy) nologging tablespace users';

  9   

10    end loop;

11  end;

12  /

 

PL/SQL procedure successfully completed.

 

SYS@oralhr>

 

SYS@oralhr> SELECT count(1) FROM dba_tables d WHERE d.table_name like 'TB_RECYCLEBIN%';

 

  COUNT(1)

----------

    187796

 

SYS@oralhr> SELECT sum(d.bytes)/1024/1024 FROM dba_segments d WHERE d.segment_name like '%TB_RECYCLEBIN%';

 

SUM(D.BYTES)/1024/1024

----------------------

              11737.25

 

Open the recycle bin :

SYS@oralhr> show parameter recy

 

NAME                                 TYPE                   VALUE

------------------------------------ ---------------------- ------------------------------

buffer_pool_recycle                  string

db_recycle_cache_size                big integer            0

recyclebin                           string                 OFF

SYS@oralhr> alter system set recyclebin=on scope=spfile;

 

System altered.

 

SYS@oralhr> startup force;  ====》 Use with caution , Not recommended

ORACLE instance started.

 

Total System Global Area 3089920000 bytes

Fixed Size                  2250360 bytes

Variable Size             721422728 bytes

Database Buffers         2348810240 bytes

Redo Buffers               17436672 bytes

Database mounted.

Database opened.

SYS@oralhr>  show parameter recy

 

NAME                                 TYPE                   VALUE

------------------------------------ ---------------------- ------------------------------

buffer_pool_recycle                  string

db_recycle_cache_size                big integer            0

recyclebin                           string                 ON

 

 

Next we drop Drop the table you just created :

SYS@oralhr> WITH wt1 AS

  2   (SELECT ts.TABLESPACE_NAME,

  3           df.all_bytes,

  4           decode(df.TYPE,

  5                  'D',

  6                  nvl(fs.FREESIZ, 0),

  7                  'T',

  8                  df.all_bytes - nvl(fs.FREESIZ, 0)) FREESIZ,

  9           df.MAXSIZ,

10           ts.BLOCK_SIZE,

11           ts.LOGGING,

12           ts.FORCE_LOGGING,

13           ts.CONTENTS,

14           ts.EXTENT_MANAGEMENT,

15           ts.SEGMENT_SPACE_MANAGEMENT,

16           ts.RETENTION,

17           ts.DEF_TAB_COMPRESSION,

18           df.ts_df_count

19    FROM   dba_tablespaces ts,

20           (SELECT 'D' TYPE,

21                   TABLESPACE_NAME,

22                   COUNT(*) ts_df_count,

23                   SUM(BYTES) all_bytes,

24                   SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ

25            FROM   dba_data_files d

26            GROUP  BY TABLESPACE_NAME

27            UNION ALL

28            SELECT 'T',

29                   TABLESPACE_NAME,

30                   COUNT(*) ts_df_count,

31                   SUM(BYTES) all_bytes,

32                   SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES))

33            FROM   dba_temp_files d

34            GROUP  BY TABLESPACE_NAME) df,

35           (SELECT TABLESPACE_NAME,

36                   SUM(BYTES) FREESIZ

37            FROM   dba_free_space

38            GROUP  BY TABLESPACE_NAME

39            UNION ALL

40            SELECT tablespace_name,

41                   SUM(d.BLOCK_SIZE * a.BLOCKS) bytes

42            FROM   gv$sort_usage   a,

43                   dba_tablespaces d

44            WHERE  a.tablespace = d.tablespace_name

45            GROUP  BY tablespace_name) fs

46    WHERE  ts.TABLESPACE_NAME = df.TABLESPACE_NAME

47    AND    ts.TABLESPACE_NAME = fs.TABLESPACE_NAME(+))

48  SELECT (SELECT A.TS#

49          FROM   V$TABLESPACE A

50          WHERE  A.NAME = UPPER(t.TABLESPACE_NAME)) TS#,

51         t.TABLESPACE_NAME TS_Name,

52         round(t.all_bytes / 1024 / 1024) ts_size_M,

53         round(t.freesiz / 1024 / 1024) Free_Size_M,

54         round((t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,

55         round((t.all_bytes - t.FREESIZ) * 100 / t.all_bytes, 3) Used_per,

56         round(MAXSIZ / 1024 / 1024/1024, 3) MAX_Size_g,

57         round(decode(MAXSIZ, 0, to_number(NULL), (t.all_bytes - FREESIZ)) * 100 /

58               MAXSIZ,

59               3) USED_per_MAX,

60         round(t.BLOCK_SIZE) BLOCK_SIZE,

61         t.LOGGING,

62         t.ts_df_count

63  FROM   wt1 t

64  UNION ALL

65  SELECT to_number('') TS#,

66         'ALL TS:' TS_Name,

67         round(SUM(t.all_bytes) / 1024 / 1024, 3) ts_size_M,

68         round(SUM(t.freesiz) / 1024 / 1024) Free_Size_m,

69         round(SUM(t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,

70         round(SUM(t.all_bytes - t.FREESIZ) * 100 / SUM(t.all_bytes), 3) Used_per,

71         round(SUM(MAXSIZ) / 1024 / 1024/1024) MAX_Size,

72         to_number('') "USED,% of MAX Size",

73         to_number('') BLOCK_SIZE,

74         '' LOGGING,

75         to_number('') ts_df_count

76  FROM   wt1 t

77  order by TS#

78  ;

 

       TS# TS_NAME                         TS_SIZE_M FREE_SIZE_M USED_SIZE_M   USED_PER MAX_SIZE_G USED_PER_MAX BLOCK_SIZE LOGGING   TS_DF_COUNT

---------- ------------------------------ ---------- ----------- ----------- ---------- ---------- ------------ ---------- --------- -----------

         0 SYSTEM                               1110          10        1100     99.116         32        3.358       8192 LOGGING             1

         1 SYSAUX                                510          27         483     94.743         32        1.475       8192 LOGGING             1

         2 UNDOTBS1                              760         222         538     70.765         32        1.641       8192 LOGGING             1

         3 TEMP                                   29          25           4     13.793         32         .012       8192 NOLOGGING           1

         4 USERS                               24688        2032       22655     91.768         32       69.138       8192 LOGGING             1

           ALL TS:                           27096.5        2316       24780     91.453        160

 

6 rows selected.

 

SYS@oralhr>

SYS@oralhr> SELECT count(1) FROM dba_free_space;

 

  COUNT(1)

----------

      254

SYS@oralhr> SELECT count(1) FROM dba_recyclebin;

 

  COUNT(1)

----------

      0

 

SYS@oralhr>

SYS@oralhr>

SYS@oralhr> begin

  2    for cur in (SELECT d.table_name

  3                  FROM dba_tables d

  4                 WHERE d.table_name like 'TB_RECYCLEBIN%') loop

  5   

  6      execute immediate 'drop table ' || cur.table_name;

  7   

  8    end loop;

  9  end;

10  /

 

PL/SQL procedure successfully completed.

 

SYS@oralhr> SELECT count(1) FROM dba_recyclebin;

 

  COUNT(1)

----------

    239829

 

 

SYS@oralhr> select count(1) from dba_free_space;

 

 

 

select count(1) from dba_free_space

                     *

ERROR at line 1:

ORA-01013: user requested cancel of current operation

 

 

 

SYS@oralhr>

 

There are... In the recycle bin 239829 Data , Let's check dba_free_space The view doesn't work for a long time , The next step is to empty the recycle bin .

utilize purge dba_recyclebin Order to clean up the recycle bin :

10:39:50 SYS@oralhr> purge dba_recyclebin;

 

Separate window calculation :

SYS@oralhr> set time on

10:43:44 SYS@oralhr> SELECT count(1) FROM dba_recyclebin;

 

  COUNT(1)

----------

    234164

 

10:44:11 SYS@oralhr> SELECT count(1) FROM dba_recyclebin;

 

  COUNT(1)

----------

    227432

 

 

 

10:48:02 SYS@oralhr>  select (234164-227432)/150 from dual;

 

(234164-227432)/150

-------------------

              44.88  ====》 That's about delete per second 45 Bar record

 

10:48:05 SYS@oralhr> select 227432/45/60 from dual;

 

227432/45/60

------------

  84.2340741 ====》 Note delete 22W The data needs about 1 One and a half hours , Too slow

 

 

10:48:28 SYS@oralhr>

 

Next we use job To delete the recycle bin object :

10:51:28 SYS@oralhr> SELECT D.owner,COUNT(1) FROM dba_recyclebin D GROUP BY D.owner;

 

OWNER                                                          COUNT(1)

------------------------------------------------------------ ----------

SYS                                                              215333

 

10:53:25 SYS@oralhr> CREATE TABLE XB_recyclebin_LHR NOLOGGING AS

10:53:26   2  SELECT ROWNUM RN, 'PURGE ' || A.type || ' ' || A.owner || '."' || A.object_name || '"' EXEC_SQL

10:53:26   3    FROM dba_recyclebin A

10:53:26   4   where a.type = 'TABLE';

 

Table created.

 

10:53:41 SYS@oralhr> CREATE INDEX IDX_recyclebin_rn  on XB_recyclebin_LHR(rn) NOLOGGING ;

 

Index created.

 

10:53:55 SYS@oralhr> create table XB_SPLIT_JOB_LHR

10:54:05   2  (

10:54:05   3    startrownum NUMBER(18),

10:54:05   4    endrownum   NUMBER(18),

10:54:05   5    flag        NUMBER(1)

10:54:05   6  );

 

Table created.

 

10:54:06 SYS@oralhr> SELECT * FROM xb_split_job_lhr;

 

no rows selected

 

10:54:12 SYS@oralhr> CREATE OR REPLACE PROCEDURE pro_split_job_lhr AUTHID CURRENT_USER IS

10:54:51   2      ---------------------------------------------------------------------

10:54:51   3      -- copy on 2012/4/2 23:28:21 by lhr

10:54:51   4      --function: The save is used to separate data to create job

10:54:51   5      -- The amount of data that needs to be processed , The tables that need to be processed add rn Column , Value taking rownum,rn Columns with index

10:54:51   6 

10:54:51   7      --alter table tmp_dp_idp_lhr add rn number;

10:54:51   8      /* CREATE INDEX IDX_tmp_dp_idp_lhr_rn  on tmp_dp_idp_lhr(rn)

10:54:51   9      TABLESPACE SDH_INDEX ONLINE  NOLOGGING COMPUTE STATISTICS PARALLEL;*/

10:54:51  10 

10:54:51  11      /*  create table XB_SPLIT_JOB_LHR

10:54:51  12      (

10:54:51  13        startrownum NUMBER(18),

10:54:51  14        endrownum   NUMBER(18),

10:54:51  15        flag        NUMBER(1)

10:54:51  16      )*/

10:54:51  17      --------------------------------------------------------------------

10:54:51  18 

10:54:51  19      n               NUMBER; -- Created job Count

10:54:51  20      j               NUMBER := 0;

10:54:51  21      n_startrownum   NUMBER;

10:54:51  22      n_endrownum     NUMBER;

10:54:51  23      n_patchnum      NUMBER := 40000; -- Number of records per batch       ----modify

10:54:51  24      v_jobname       VARCHAR2(200);

10:54:51  25      v_count         NUMBER; -- The amount of table data that needs to be processed

10:54:51  26 

10:54:51  27  BEGIN

10:54:51  28   

10:54:51  29      SELECT COUNT(1) INTO v_count FROM XB_recyclebin_LHR; ----modify

10:54:51  30 

10:54:51  31      -- Need to create job Number

10:54:51  32      n := trunc(v_count / n_patchnum) + 1;

10:54:51  33 

10:54:51  34       EXECUTE IMMEDIATE 'truncate table xb_split_job_lhr';

10:54:51  35      WHILE j < n LOOP

10:54:51  36 

10:54:51  37          -- obtain rownum

10:54:51  38          n_startrownum := j * n_patchnum + 1;

10:54:51  39 

10:54:51  40          IF j = n - 1 THEN

10:54:51  41 

10:54:51  42              n_endrownum := v_count;

10:54:51  43          ELSE

10:54:51  44              n_endrownum := (j + 1) * n_patchnum;

10:54:51  45          END IF;

10:54:51  46 

10:54:51  47          INSERT INTO xb_split_job_lhr

10:54:51  48              (startrownum, endrownum)

10:54:51  49          VALUES

10:54:51  50              (n_startrownum, n_endrownum);

10:54:51  51          COMMIT;

10:54:51  52 

10:54:51  53          j := j + 1;

10:54:51  54      END LOOP;

10:54:51  55  

10:54:51  56      -- Loop creation job

10:54:51  57      j               := 0;

10:54:51  58 

10:54:51  59      FOR cur IN (SELECT * FROM xb_split_job_lhr) LOOP

10:54:51  60 

10:54:52  61          v_jobname := 'JOB_SUBJOB_SPLIT_LHR' || (j + 1);

10:54:52  62          dbms_scheduler.create_job(job_name            => v_jobname,

10:54:52  63                                    job_type            => 'STORED_PROCEDURE',

10:54:52  64                                    job_action          => 'PRO_SUB_SPLIT_LHR', --modify

10:54:52  65                                    number_of_arguments => 2,

10:54:52  66                                    start_date          => SYSDATE + 1 / 5760, -- 15 Start the job in seconds

10:54:52  67                                    repeat_interval     => NULL,

10:54:52  68                                    end_date            => NULL,

10:54:52  69                                    job_class           => 'DEFAULT_JOB_CLASS',

10:54:52  70                                    enabled             => FALSE,

10:54:52  71                                    auto_drop           => TRUE,

10:54:52  72                                    comments            => 'to split job_subjob_Split_lhr');

10:54:52  73          COMMIT;

10:54:52  74 

10:54:52  75          dbms_scheduler.set_job_argument_value(job_name          => v_jobname,

10:54:52  76                                                argument_position => 1,

10:54:52  77                                                argument_value    => cur.startrownum);

10:54:52  78          COMMIT;

10:54:52  79          dbms_scheduler.set_job_argument_value(job_name          => v_jobname,

10:54:52  80                                                argument_position => 2,

10:54:52  81                                                argument_value    => cur.endrownum);

10:54:52  82          COMMIT;

10:54:52  83          dbms_scheduler.enable(v_jobname);

10:54:52  84          j := j + 1;

10:54:52  85      END LOOP;

10:54:52  86      COMMIT;

10:54:52  87  

10:54:52  88      ----- Waiting for all the children job After execution

10:54:52  89 

10:54:52  90      LOOP

10:54:52  91 

10:54:52  92          SELECT COUNT(1)

10:54:52  93          INTO   v_count

10:54:52  94          FROM   xb_split_job_lhr t

10:54:52  95          WHERE  t.flag IS NULL;

10:54:52  96 

10:54:52  97          IF v_count = 0 THEN

10:54:52  98              EXIT;

10:54:52  99          ELSE

10:54:52 100              dbms_lock.sleep(10); --- I had a rest 10 second

10:54:52 101          END IF;

10:54:52 102 

10:54:52 103      END LOOP;

10:54:52 104     EXECUTE IMMEDIATE 'purge dba_recyclebin';

10:54:52 105  EXCEPTION

10:54:52 106      WHEN OTHERS THEN

10:54:52 107         NULL;

10:54:52 108 

10:54:52 109  END pro_split_job_lhr;

10:54:54 110  /

 

Procedure created.

 

10:55:17 SYS@oralhr> show error

No errors.

10:55:21 SYS@oralhr> create or replace procedure pro_sub_split_lhr(p_startrownum number,

10:55:24   2                                                p_endrownum   number) is

10:55:24   3  

10:55:24   4  begin

10:55:24   5 

10:55:24   6    for cur in (SELECT A.EXEC_SQL

10:55:24   7                  FROM XB_recyclebin_LHR A ---modify

10:55:24   8                 where A.rn <= p_endrownum

10:55:24   9                   and A.rn >= p_startrownum) loop

10:55:24  10      begin

10:55:24  11        EXECUTE IMMEDIATE CUR.EXEC_SQL;

10:55:24  12      exception

10:55:24  13        when others then

10:55:24  14          null;

10:55:24  15      end;

10:55:24  16    end loop;

10:55:24  17 

10:55:24  18    commit;

10:55:24  19 

10:55:24  20    -- Update logo

10:55:24  21    update xb_split_job_lhr t

10:55:24  22       set t.flag = 1

10:55:24  23     where t.startrownum = p_startrownum

10:55:24  24       and t.endrownum = p_endrownum;

10:55:24  25    commit;

10:55:24  26 

10:55:24  27  exception

10:55:24  28 

10:55:24  29    when others then

10:55:24  30   

10:55:24  31      null;

10:55:24  32   

10:55:24  33  end pro_sub_split_lhr;

10:55:25  34  /

 

Procedure created.

 

10:55:26 SYS@oralhr> show error

No errors.

10:55:29 SYS@oralhr> exec pro_split_job_lhr;

 

 

Open a separate window to recalculate the speed of emptying the recycle bin :

SYS@oralhr> set time on

11:04:38 SYS@oralhr> SELECT count(1) FROM dba_recyclebin;

 

  COUNT(1)

----------

    211055

 

11:06:00 SYS@oralhr> SELECT count(1) FROM dba_recyclebin;

 

  COUNT(1)

----------

    189105

 

11:08:00 SYS@oralhr>  select (211055-189105)/80 from dual;

 

(234164-227432)/150

-------------------

              274.375  ====》 That's about delete per second 275 Bar record

 

11:08:10 SYS@oralhr> select 189105/275/60 from dual;

 

189105/275/60

-------------

   11.4609091 ====》 Note delete 18W The data needs about 11 minute

 

11:09:10 SYS@oralhr>

 

Wait more than ten minutes to check the data :

SYS@oralhr> select * from xb_split_job_lhr;

 

STARTROWNUM  ENDROWNUM       FLAG

----------- ---------- ----------

          1      40000

      40001      80000

      80001     120000

     120001     159915

 

SYS@oralhr>

SYS@oralhr> col owner for a5

SYS@oralhr> col CPU_USED for a18

SYS@oralhr> col ELAPSED_TIME for a18

SYS@oralhr> select OWNER,JOB_NAME,CPU_USED,ELAPSED_TIME,RUNNING_INSTANCE from dba_scheduler_running_jobs;

 

OWNER JOB_NAME                       CPU_USED           ELAPSED_TIME       RUNNING_INSTANCE

----- ------------------------------ ------------------ ------------------ ----------------

SYS   JOB_SUBJOB_SPLIT_LHR1          +000 00:10:18.36   +000 00:19:15.29                  1

SYS   JOB_SUBJOB_SPLIT_LHR2          +000 00:10:14.71   +000 00:19:15.07                  1

SYS   JOB_SUBJOB_SPLIT_LHR3          +000 00:10:12.77   +000 00:19:14.95                  1

SYS   JOB_SUBJOB_SPLIT_LHR4          +000 00:10:14.70   +000 00:19:14.78                  1

 

SYS@oralhr>

 

If system CPU Strong words , The SQL It will be done soon , Inquire about dba_scheduler_running_jobs The view will have no data representation job Completed .

 

 

Two .4   Summary of the experiment

1、11.2.0.4 If there are too many objects in the recycle bin ,dba_free_space The problem of slow query has been solved

2、 The script of Experiment 2 is universal , When many operations can be performed at the same time, we can modify the program

 

 

 

The third chapter   What's used in the experiment SQL summary

 

Experiment 1 :

dbca -silent -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE \

-gdbname oralhr  -sid oralhr \

-sysPassword oracle -systemPassword lhr \

-datafileDestination 'DATA2/' -recoveryAreaDestination 'DATA2/' \

-redoLogFileSize 50 \

-storageType ASM -asmsnmpPassword lhr  -diskGroupName 'DATA2' \

-characterset AL32UTF8 -nationalCharacterSet AL16UTF16 \

-sampleSchema false \

-automaticMemoryManagement true -totalMemory 2048 \

-databaseType OLTP  \

-emConfiguration NONE

 

show parameter recy

create table tb_20160627_lhr as select * from dual;

drop table tb_20160627_lhr;

select * from dba_recyclebin;

drop table tb_20160627_lhr;

drop table tb_20160627_lhr;

select * from dba_recyclebin;

 

 

Experiment two :

begin

 

  for cur in 1 .. 100000 loop

 

    execute immediate 'create table tb_recyclebin_' || cur ||

                      ' nologging tablespace users as select * from dual';

    execute immediate 'create index idx_recyclebin_' || cur ||

                      ' on tb_recyclebin_' || cur ||' (dummy) nologging tablespace users';

 

  end loop;

end;

/

 

begin

 

  for cur in 1 .. 100000 loop

 

    execute immediate 'create table tb_recyclebin_lhr_' || cur ||

                      ' nologging tablespace users as select * from dual';

    execute immediate 'create index idx_recyclebin_lhr_' || cur ||

                      ' on tb_recyclebin_lhr_' || cur ||' (dummy) nologging tablespace users';

 

  end loop;

 

end;

/

 

begin

 

  for cur in 1 .. 100000 loop

 

    execute immediate 'create table tb_recyclebin_lhr1_' || cur ||

                      ' nologging tablespace users as select * from dual';

    execute immediate 'create index idx_recyclebin_lhr1_' || cur ||

                      ' on tb_recyclebin_lhr1_' || cur ||' (dummy) nologging tablespace users';

 

  end loop;

 

end;

 

begin

  for cur in (SELECT d.table_name

                FROM dba_tables d

               WHERE d.table_name like 'TB_RECYCLEBIN%') loop

    execute immediate 'drop table ' || cur.table_name;

  end loop;

end;

/

 

 

CREATE TABLE XB_recyclebin_LHR NOLOGGING AS

SELECT ROWNUM RN, 'PURGE ' || A.type || ' ' || A.owner || '."' || A.object_name || '"' EXEC_SQL

  FROM dba_recyclebin A

where a.type = 'TABLE';

 

CREATE INDEX IDX_recyclebin_rn  on XB_recyclebin_LHR(rn) NOLOGGING ;

 

create table XB_SPLIT_JOB_LHR

(

   startrownum NUMBER(18),

   endrownum   NUMBER(18),

   flag        NUMBER(1)

);

 

col CPU_USED for a18

col ELAPSED_TIME for a18

select OWNER,JOB_NAME,CPU_USED,ELAPSED_TIME,RUNNING_INSTANCE from dba_scheduler_running_jobs;