Renaming a Table 3

[Michael42]

Hello,

In Oracle 9i on a test system I can use the below to rename a table if I am the Schema owner. I tried renaming a table as the user SYS but the message said I did not have privs. As SYS I used this 1st: ALTER SESSION SET CURRENT_SCHEMA = <schema>;

RENAME TABLE_A TO TABLE_B;

1. How can I rename a table as SYS (or at least a user with DBA role)?

2. I have a production table (say TABLE_A) where I want to:
A. Rename a production table to say TABLE_OLD.
B. Then rename another table (TABLE_B) to TABLE_A.
Can someone please list any special considerations in doing this in a production environment?

Thoughts:
Can a table be taken off-line first?
How can I check if anyone is using the table?

Thanks very much for your advice,

Michael42

 

[Thargy]

Michael,

1)to rename the table aaa in the test schema to bbb use

alter table test.aaa rename to bbb

2) don't do it in production, never ever!
This sort of thing should always be done in a test environment first, tested to within an inch of its life, a rollback method developed, tested and tucked away safely.

As a result of the change, at least the following will occur:-

Any and all code which refers to the now non-existent table will break.
Indexes will become useless.
Associated constraints will become invalid.
Associated triggers will decompile, and be impossible to recompile without alteration.
If the table size is significant, compared with the tablespace size, duplicating it might exceed the tablespace limit, or put the tablespace dangerously close to its limit.
Any foreign keys referencing the original data will fail, and you will produce orphan records all over the place.

Because you intend to rename another table to have the original table name, unless they have an identical structure, the above list is the minimum grief you should expect. Even if this succeeds, you'll have significant work to do, just to get things working again.

I am highly sceptical that there is a clear requirement from users, which would justify such drastic action.

Michael, could you state your business need for this alteration, so that any alternative methods can be considered.

Regards

Tharg

Grinding away at things Oracular

 

[Michael42]

thargtheslayer,

Thanks for your very lucid comments.

>> Michael, could you state your business need for this alteration, so that any alternative methods can be considered.

In production I have a table on a clustered file system. This table is in one tablespace. This table is finite and used for historical purposes. I want to improve it's maintainability by:

1. Changing the partitioning scheme to yearly instead of monthly.

2. Moving the data to a new tablespace that uses a larger block size.

3. Have it use one 50gb datafile instead of a gazillion small ones (again on a clusterd file system = high maintenance).

After improving the maintainability of a static table I would like to take the lessons learned and duplicate this for some non-static tables.

Can you offer any suggestions?

Thanks again,

Michael42

 

[gbraden]

It been a while since I had a DBA role but this (in simple terms) is what I would do using scripts: (setup/test in development first)

Export table_a and table_b

import table_a data to backup/archiving table

truncate table_a

import table_b data to table_a

setup tables in dataspaces and with sizes needed and this should work.



Visit me at

http://www.aviationartist.com

 

[carp]

I would suggest you examine the ALTER TABLE MOVE option, which would allow you to accomplish a lot of what you want to do without imports/exports, etc.

 

[gbraden]

Your right Carp... Coming from a shop that uses exports/imports for everything, I have become pretty set in my ways.




Visit me at

http://www.aviationartist.com

 

[Michael42]

Hello,

If I use the ALTER TABLE MOVE option and the table does not have any indexes (just a PK) does the PK need to be rebuilt?

Thanks,

Michael42

 

[SantaMufasa]

Michael,

If a table has a PK, then it has an index. (Oracle does not allow a declared PK or UK to exist without an index on the PK/UK column(s).)

Whenever I have done an "ALTER TABLE <name> MOVE;", I always do "ALTER INDEX <name> REBUILD;" on the related indexes, as well.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Michael42]

SantaMufasa,

>> Whenever I have done an "ALTER TABLE <name> MOVE;", I always do "ALTER INDEX <name> REBUILD;" on the related indexes, as well.

Thanks for confirming sir.

-Michael42