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;
- Note: Your recyclebin names will be different.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Wed Nov 07 2007 - 19:40:45 CST
