| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Object names changed to BIN$pgkQ... etc
On Nov 7, 5:27 am, DA Morgan <damor..._at_psoug.org> wrote:
> Helma wrote:
> >>> Ok, last question to you: why are suddenly almost all my tables moved
> >>> to the recyclebin? I will have to look on your site to find the
> >>> answer.
>
> >> There is only one thing that moves tables to the recyclebin:
>
> >> DROP TABLE <table_name>;
> >> Takes the indexes and constraints supported by indexes there too.
> >> --
> >> Daniel A. Morgan
> >> University of Washington
> >> damor..._at_x.washington.edu (replace x with u to respond)
> >> Puget Sound Oracle Users Groupwww.psoug.org
>
> > As stated, I didn't issue a drop table command. Strange that they
> > appear in both the recyclebin AND in user_tables. The time of the last
> > DDL on the tables dates from a few days ago, when i created them.
> > Yesterday it looked ok there was no problem, but i noticed the entries
> > in the recyclebin today.
>
> > I think i'll use the windows solution: drop and recreate, and apply
> > patches first.
>
> > Thank you all very much for your help,
> > HV.
>
> Nothing can exist in both the recyclebin and user_tables.
Yes it can, in fact. When the recyclebin was first invented, it was possible to see its contents via DBA_TABLES and its cousins. That changed in a later release -I believe when 10g R2 hit the streets. Even in 10g Release 2, everything is visible in user_objects.
>What
> you are seeing is understandable though if your approach to Oracle
> is to stumble around rather than read the docs.
>
> Create a table named test: Drop it.
> Create another table named test: Drop it.
> Create a third table named test: Drop it.
A truncated test, I'm afraid. Create an index on the table, then drop the table... and then investigate the contents of the recyclebin. Now undrop the table and see what name your index now has: indexes don't get named back to their former names when they come out of the recyclebin. Which means that's another reason why, were you to query, say, DBA_OBJECTS, even in 10g Release 2, you'd see things which are completely valid objects NOT in the recycle bin but with names which makes it look like they are... which would also mean that you shouldn't leap to conclusions and *assume* that the OP is talking about objects currently in the recycle bin at all.
Thus:
FRED_at_imsd> create table t (col1 char(4));
Table created.
FRED_at_imsd> create index i on t(col1);
Index created.
FRED_at_imsd> select object_name from user_objects;
OBJECT_NAME
FRED_at_imsd> drop table t;
Table dropped.
FRED_at_imsd> select object_name from user_objects;
OBJECT_NAME
FRED_at_imsd> flashback table t to before drop;
Flashback complete.
FRED_at_imsd> select object_name from user_objects;
OBJECT_NAME
...apparently both "dropped" and undropped objects appearing in the same view.
But yes, an equally valid different test which I *think* you were alluding to, is to create table t, drop table t, create table t, drop table t, undrop table t:
FRED_at_imsd> drop table t purge;
Table dropped. (just to clear things up from before)
FRED_at_imsd> select object_name from user_objects; no rows selected (just to prove nothing exists)
FRED_at_imsd> create table t (col1 char(5)); Table created.
FRED_at_imsd> drop table t;
Table dropped.
FRED_at_imsd> create table t (col1 char(6)); Table created.
FRED_at_imsd> drop table t;
Table dropped.
FRED_at_imsd> create table t (col1 char(7)); Table created.
FRED_at_imsd> drop table t;
Table dropped.
FRED_at_imsd> flashback table t to before drop; Flashback complete.
FRED_at_imsd> select object_name from user_objects;
OBJECT_NAME
...and at that point, the table T is visible both with its "proper" name and with two recyclebin companions. Except of course, those two recyclebin objects represent different versions of the T table.
> SELECT object_name, original_name
> FROM recyclebin;
>
> To understand what is happening ... read the docs and work your
> way through the demos in my library. A usenet group is not a
> substitute for a one hour class.
I hate that attitude. A usenet group USED to be a perfectly good place to ask a question. But you've apparently decided to change that. A quick demo wouldn't have killed you, you know.
Feel free, Helma, to ask questions elsewhere (like otn.oracle.com and even www.dizwell.com)
HJR Received on Wed Nov 07 2007 - 18:45:30 CST
 |
 |