Syntax to Reference the Tablespace

[cfFran]

Is it possible to delete a table by identifying the tablespace, owner, and table name?

We have several accounts set up. Most of them have tables with the same name. Can I drop a table by identifying which tablespace it is in before identifying which account owns the table?

Maybe I am way off the mark here. Is it even possible for a person to create more than one table under AccountA with the same name, if the tables are located in different tablespaces?

These questions all rise from our efforts to clean up our Oracle accounts and tablespaces. We currently have more than one project in some tablespaces. If I move a set of tables to its correct tablespace by doing an EXPORT followed by an IMPORT using the approach suggested in thread186-1182717, do I have one or two copies when I am done?

Thanks

 

[SantaMufasa]

cfFran,

>>> Is it possible to delete a table by identifying the tablespace, owner, and table name?

No. When working with tables, the only time you can/may specify a tablespace name is when you create the table.

>>> Can I drop a table by identifying which tablespace it is in before identifying which account owns the table?

No...See above.

>>> Is it even possible for a person to create more than one table under AccountA with the same name, if the tables are located in different tablespaces?

No. A single Oracle user can create only one object amongst that user's tables, indexes, synonyms, et cetera, with a specific spelling.

The reason that you have many tables with the same name existing in even the same tablespace is because they are uniquely qualified by the owner of the table.

>>> If I move a set of tables to its correct tablespace by doing an EXPORT followed by an IMPORT using the approach suggested in thread186-1182717, do I have one or two copies when I am done?

Move implies delete from source; copy to target. Export and Import don't ever, in and of themselves, do any deleting. If you import a dump file into the same Oracle User, by default you will receive an error that fails the import for a table which already exists. You can, however, specify "IGNORE=Y" as an import parameter that means, if the table already exists, don't throw an error...just append the rows into the existing table structure.

If you use the Oracle "imp" "fromuser=<user 1> touser=<user 2>" parameters, the tables in the dump file will duplicate into the target user's schema (provided the table name does not already exist there, per explanation, above).

Does this answer your questions?





Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Beilstwh]

If all you want to do is to move a table to another tablespace, but keep it in the same schema then simply perform an

ALTER TABLE my_table MOVE TABLESPACE new_tablespace_name;

Then rebuild the indexes by using

ALTER INDEX index_name REBUILD;


Bill
Oracle DBA/Developer
New York State, USA