Shulou(Shulou.com)06/01 Report--

Operating system version:

[oracle@oracle trace] $uname-a

Linux oracle.example.com 2.6.32-431.el6.x86_64 # 1 SMP Sun Nov 10 22:19:54 EST 2013 x86 "64 GNU/Linux

[oracle@oracle trace] $lsb_release-a

LSB Version:: base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch

Distributor ID: RedHatEnterpriseServer

Description: Red Hat Enterprise Linux Server release 6.5 (Santiago)

Release: 6.5

Codename: Santiago

Database version:

SYS@proc > select * from v$version where rownum=1

BANNER

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

Environmental construction:

SYS@proc > create table t (id int)

Table created.

SYS@proc > insert into t values (1)

1 row created.

SYS@proc > insert into t select * from t

1 row created.

SYS@proc > /

2 rows created.

... Omit the same steps.

SYS@proc > /

1048576 rows created.

SYS@proc > commit

Commit complete.

SYS@proc > select BYTES/1024/1024 mb from dba_segments where owner='SYS' and segment_name='T'

MB

-

twenty-five

SYS@proc > create tablespace abc datafile'/ u01ActionAccording to procc01.dbf' size 26m

Tablespace created.

SYS@proc > create user km identified by oracle account unlock

User created.

SYS@proc > grant connect,create table to km

Grant succeeded.

SYS@proc > alter user km quota 26m on abc

User altered.

SYS@proc > select AUTOEXTENSIBLE from dba_data_files where tablespace_name='ABC'

AUT

-

NO

SYS@proc > create table km.t tablespace abc as select * from t

Create table km.t tablespace abc as select * from t

*

ERROR at line 1:

ORA-01652: unable to extend temp segment by 128 in tablespace ABC

SYS@proc > col file_name for A50

SYS@proc > select file_name from dba_data_files

FILE_NAME

/ u01/app/oracle/oradata/proc/test01.dbf

/ u01/app/oracle/oradata/proc/example01.dbf

/ u01/app/oracle/oradata/proc/users01.dbf

/ u01/app/oracle/oradata/proc/abc01.dbf

/ u01/app/oracle/oradata/proc/sysaux01.dbf

/ u01/app/oracle/oradata/proc/system01.dbf

/ u01/app/oracle/oradata/proc/undotbs2_1.dbf

/ u01/app/oracle/oradata/proc/undotbs2_2.dbf

8 rows selected.

SYS@proc > alter database datafile'/ u01 resize

Database altered.

SYS@proc > create table km.t tablespace abc as select * from t

Table created.

SYS@proc > conn km/oracle

Connected.

KM@proc > drop table t

Table dropped.

KM@proc > show recycle

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

-

T BIN$Q38bmJwFDYXgU28cqMDtew==$0 TABLE 2016-12-13 purl 07 purl 03purl 11

KM@proc > conn / as sysdba

Connected.

SYS@proc > delete from recyclebin$;-irregular operation. You should execute purge table t or purge table "BIN$Q38bmJwFDYXgU28cqMDtew==$0" under km users correctly.

1 row deleted.

SYS@proc > commit

Commit complete.

SYS@proc > conn km/oracle

Connected.

KM@proc > show recycle

KM@proc > conn / as sysdba

Connected.

SYS@proc > create table km.t tablespace abc as select * from t

Create table km.t tablespace abc as select * from t

*

ERROR at line 1:

ORA-01536: space quota exceeded for tablespace 'ABC'

SYS@proc > alter user km quota unlimited on abc

User altered.

SYS@proc > create table km.t tablespace abc as select * from t

Create table km.t tablespace abc as select * from t

*

ERROR at line 1:

ORA-01652: unable to extend temp segment by 128 in tablespace ABC-you can see here that although the show recycle executed by the km user is empty, the real space has not been released.

SYS@proc > drop tablespace abc including contents and datafiles

Drop tablespace abc including contents and datafiles

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-38301: can not perform DDL/DML over objects in Recycle Bin

SYS@proc > drop user km cascade

Drop user km cascade

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-38301: can not perform DDL/DML over objects in Recycle Bin

Formally clean the table of the Recycle Bin:

1. Use the original_table_name here in PURGE TABLE original_table_name; to indicate the name before drop.

2. Use the recyclebin_object_name here in PURGE TABLE recyclebin_object_name; to represent the object name in the Recycle Bin.

3. Use PURGE TABLESPACE tablespace_name to clear all discarded objects from the specified table space

4. Use PURGE TABLESPACE tablespace_name USER user_name to clear all discarded objects belonging to a particular user from the Recycle Bin.

5. DROP USER user_name cascade directly deletes the specified user and all the objects to which it belongs, that is, the DROP USER command will directly delete it by bypassing the Recycle Bin.

6. Use the PURGE RECYCLEBIN command to clear the user's own recycle bin

7. PURGE DBA_RECYCLEBIN clears all objects from all users' recycle bin

If this happens, how should we solve it?

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.