Environment: win2008server + oracle11g

Front has several big history table, I rebuilt after direct drop table, over a period of time I found the table space or is full, the execution of purge recyclebin and found the table space is still not released, query user_segment table found BIN $opening paragraph:
SEGMENT_NAME SUM (BYTES/1024/1024)
BIN $0 pzthetfticg6ob9noo + ZA==$0
87667.5BIN $MWC/GsbqTHSAqPoSdAnM8A==$0, 24475
BIN $7 txajpq4eivojwhpifdw==$0 P/81418.75

I don't know for what reason? How to recycle these table space?

CodePudding user response:

 drop table mytable purge; Not into the recycle bin 

Purge recyclebin. To empty the recycle bin 

CodePudding user response:

The drop table: execute drop table xx statement
Drop after the table was laid in the recycle bin (user_recyclebin), rather than directly deleted, so recycling bins table information can be recovered, or wipe out,
By querying the recycle bin user_recyclebin dropped table information, and then use the statement
Flashback table & lt; User_recyclebin. Object_name or user_recyclebin. Original_name & gt; To before drop [rename to & lt; new_table_name & gt;] ;
The restoration of recycling bins in the table for the original name or specify a new name, table data will not be lost,
If you want to delete table, use the statement: drop table & lt; Table_name & gt; Purge;
Clear recycling bins information
Remove specified tables: purge table & lt; Table_name & gt;;
Remove the current user's recycle bin: purge recyclebin;
Remove all users of the recycle bin: purge dba_recyclebin;
Not into the recycle bin, delete directly is: drop table xx purge;

CodePudding user response:

Purge recyclebin can not clear about it,

Select * from user_recyclebin query for the current user in the recycle bin contents, look at there, is what

CodePudding user response:

How to confirm has not been released? If wrong

CodePudding user response:

No wrong, is indeed exist table space no recycling:
The select segment_name, sum (bytes/1024/1024) from user_segments; Get
SEGMENT_NAME SUM (BYTES/1024/1024)
ATS_EVENT_IDX 5630
BIN $0 pzthetfticg6ob9noo + ZA==$0
87667.5BIN $MWC/GsbqTHSAqPoSdAnM8A==$0, 24475

But performs the select object_name original_name ts_name, droptime from user_recyclebin; By not return records,
Execute the query space utilization situation of statements, the space take up almost 100%, show BIN $space is not release,


Consult everybody a great god! Why is this?

CodePudding user response:

 
- see what things in the recycle bin 
Select * from user_recyclebin 

CodePudding user response:

Select * from user_recyclebin is empty, nothing,

CodePudding user response:

Supplement:
Drop table is made of before, there were no execution purge recyclebin empty the recycle BIN, then drop table after reconstruction of the original a few tables directly, now look at view user_segment table, found out the BIN $at the beginning of paragraphs, and now perform purge recyclebin the BIN $period still exist,

CodePudding user response:

Estimation is triggered the oracle a bug, if data volume is not big, just delete this user, reconstruction;

PS: clearing up the rubbish that live, not dry

CodePudding user response:

Q say 212646490

CodePudding user response:

This is the online operation system, can't delete the user reconstruction, now I am only constantly enlarge the table space, I want to the recycle BIN space,

CodePudding user response:

 - try this command, can see clear 
Purge table "BIN $MWC/GsbqTHSAqPoSdAnM8A==$0" 

CodePudding user response:

SQL> Purge table "BIN $MWC/GsbqTHSAqPoSdAnM8A==$0"; According to table does not exist,


But
SQL> Select * from user_catalog where table_name like 'BIN %'; Can look up to the table,

CodePudding user response:

May be a BUG, restart the library to see if there is

CodePudding user response:

Now the problem solved? If not, can consider to tables and indexes in the table space that the content of the class object moving to another table space;

After deleting the table space, reconstruction;

The drop in tablespace xx o contents - add this option

CodePudding user response:

This is 7 * 24 hours of production environment, the new data already has dozens of G, data migration?