Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Object names changed to BIN$pgkQ... etc

Re: Object names changed to BIN$pgkQ... etc

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 07 Nov 2007 17:40:45 -0800
Message-ID: <1194486040.623051@bubbleator.drizzle.com>


hjr.pythian_at_gmail.com wrote:
> 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
> ----------------------------------------------------
> T
> I
>
> FRED_at_imsd> drop table t;
>
> Table dropped.
>
> FRED_at_imsd> select object_name from user_objects;
>
> OBJECT_NAME
> ----------------------------------------------------
> BIN$VNg6Kjj9SxG3I3Wim7Yq5g==$0
> BIN$3Bq7kQTlSbmmErOQIE1izg==$0
>
> FRED_at_imsd> flashback table t to before drop;
>
> Flashback complete.
>
> FRED_at_imsd> select object_name from user_objects;
>
> OBJECT_NAME
> ----------------------------------------------------
> T
> BIN$3Bq7kQTlSbmmErOQIE1izg==$0
>
> ...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
> -----------------------------------------------------
> BIN$RK34oLCSRvunuV6LM47zIg==$0
> BIN$HyDN6TSXTaOYxkXMqkCOvg==$0
> T
>
> ...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
Not just indexes ... constraints too:

CREATE TABLE test (
testcol VARCHAR2(20));

ALTER TABLE test
ADD CONSTRAINT pk_test
PRIMARY KEY (testcol)
USING INDEX
PCTFREE 0; INSERT INTO test VALUES ('ABC');
INSERT INTO test VALUES ('DEF');
COMMIT; SELECT * FROM test;

DROP TABLE test;

SELECT object_name, original_name
FROM recyclebin;

FLASHBACK TABLE "BIN$RfMyTuf+R7inKCbOiSay9g==$0" TO BEFORE DROP; SELECT * FROM test;

SELECT table_name, constraint_name, constraint_type FROM user_constraints
ORDER BY 1; ALTER TABLE test
RENAME CONSTRAINT "BIN$1tDo7RSYSmyca9yoqNvxPA==$0" TO pk_test;

SELECT table_name, constraint_name, constraint_type FROM user_constraints
ORDER BY 1; SELECT table_name, index_name
FROM user_indexes
ORDER BY 1; ALTER INDEX "BIN$Rnkss3raTSyTya7CVU+iZA==$0" RENAME TO pk_test;

SELECT table_name, index_name
FROM user_indexes
ORDER BY 1; DROP TABLE test PURGE;

Received on Wed Nov 07 2007 - 19:40:45 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US