Online Table Redefinition and Privileges

[Michael42]

Hello,

In performing an Online Table Redefinition it seems I will need to apply all the same privileges to my Interim table that my Original has (plus any new ones of course).

Can you recommend a scriptlet to apply all the same privileges that one table has to another table including any dependent objects (like triggers etc.)?

Thanks,

Michael42

 

[SantaMufasa]

Michael,

Perhaps we can suggest a solution that is simpler than what you propose, if we know your business/information objective. Can you please tell us more about what you planned to do with the Interim table?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Michael42]

Santa,

Have you ever used Oracle 9i's Online Table Redefinition?

Thanks,

Michael42

 

[SantaMufasa]

I have (and found the feature to be very useful) but there are possible alternatives that may be faster/better, depending, again, upon your business/information objective(s). Specifically, was your need to:

* Defrag the original table
* Adjust storage parameters on the original table
* Adjust columns in the original table
* Convert partitioning-mode for the original table
* Making a heap/index switch
* Toggling parallel support for the table

The reason I mention "alternatives" is that in this case, depending upon your need, an alternative may be simpler than going through all of the privilege grants that you mention.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Michael42]

Santa,

You have some very nice points sir. I am using various options when moving tables when I can get a maintenance window.

In some instances I have a few tables that have partitions spread over several non-related and poorly constructed tablespaces. I want to consolidate them into one TS (in some cases no partitions, i.e. I have new hardware that made need for partitions magically disappear).

In this scenario I am using standard technique:

1. Export table.
2. Drop original table.
3. Recreate table as desired in new TS
3. Import table.

I have also used the RENAME table option as so:

1. Create a new of table in new TS with desired specs.
2. Rename orig table to orig_old.
3. Rename new table to original name.
4. INSERT INTO data from old table to new.
5. Recompile any INVALID objects.

In the instance at hand I simply want to move some tables in a 24/7 environment and it seems Online Redef may be the only option here. I wish tables in 9i had an online option like Index Rebuilds.

I must add, Santa you and other DBA's here have helped me detail the above techniques.

Can you recommend any other options to move a table online?

Thanks,

Michael42

 

[SantaMufasa]

Absolutely...my favourite is [ding, ding, ding]:

alter table <owner>.<table_name>
move parallel nologging tablespace <new_tablespace_name>;

The fact that it takes advantage of multiple processors ("parallel") and dispenses with redo logging ("nologging") makes this one of the most-remarkably fast data movements in the Oracle World...6MB/second in my experience (depending upon processor and o/s).

Because of the lightening-fast speed, I prefer this over the "...Redefinition..." technique. I also recommend it to you since you mentioned that you have a "maintenance window," which is helpful since I recommend this for a time when users are not banging against the table.

WARNING: This technique does not work on tables with LONG columns...It throws an error, but no damage done. (You must use the export/import technique that you mentioned earlier.) Also, I have not tried it on partitioned tables (and do not bet that it will work), but I recommend you try it and see if it throws an error.

Also, when you use this technique, you must also rebuild any indexes since the indexes point to block locations within the table. But, (as you probably already know) you can effect fast index rebuilds (and relocations to new tablespaces, as well) with:

alter index <owner>.<index_name> rebuild parallel tablespace <new_tsname>;

Let us know if this suits your needs.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]