OraFAQ Forum: Server Administration » Table re-organisation using CLOB datatype column

Home » RDBMS Server » Server Administration » Table re-organisation using CLOB datatype column

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Table re-organisation using CLOB datatype column [message #224163]

Tue, 13 March 2007 05:47

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

I need to move some tables from the current tablespace(schema) to another tablespace. I also need to poupalte these data back to original tablespace as a part of table re-oragnisation using

ALTER TABLE tablename MOVE TABLESPACE

It may be re-oranised in the same tablepsace or another.

But one of my table contains CLOB datatype. I wanted to know this type of data type would be handled using Oracle 8.1.7.

I wish to follow steps, if CLOB datatype handelded

A. Create a new tablespace TEMP_DATA with autoextend option of datafile

B. Move all the tables of schema (tablespace) in the new tablespace, moving the data we want to save over temporarily

C. Truncate all the tables from schema

D. Resize the current tablespace, if required.

E. Move the data from TEMP_DATA back to the current
tablespace

F. Re-build all the concern unusable or invalidated indexes

G. Drop the TEMP_DATA tablespace

Report message to a moderator

Re: Table re-organisation using CLOB datatype column [message #227241 is a reply to message #224163]

Tue, 27 March 2007 12:15

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

I want just to know the step C Truncate all the tables from schema is required when I re-organise all the tables.

Your feedback would be appreciated.

Report message to a moderator

Re: Table re-organisation using CLOB datatype column [message #227250 is a reply to message #227241]

Tue, 27 March 2007 13:07

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

First, why do you want to do re-org?
>>I want just to know the step C Truncate all the tables from schema is required when I re-organise all the tables.
By doing this,
>>ALTER TABLE tablename MOVE TABLESPACE
You already moved the table to another tablespace. Your "re-org" is done~.
>>C. Truncate all the tables from schema
What table are you talking about? You already moved it another tablespace. Table is just ***moved*** to another tablespace and NOT ***copied*** (Only one copy of table is maintained).
If you still have access to same table, you might be truncating the table you moved!!.

  1* select table_name,tablespace_name from user_tables where table_name='DEPT'
SQL> /

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DEPT                           SYSTEM

SQL> alter table dept move tablespace apps;

Table altered.

SQL> select table_name,tablespace_name from user_tables where table_name='DEPT';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DEPT                           APPS

SQL> truncate table dept;

Table truncated.

SQL> select * from dept;

no rows selected

Report message to a moderator

Re: Table re-organisation using CLOB datatype column [message #227764 is a reply to message #227250]

Thu, 29 March 2007 06:02

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

I wanted to remove intra-block fragmentation from the larger tables and improving performance (access to table faster) so that I need to move table from one tablespace to another. I can also perform this task using the same tablespace name. As per you reply, I undestand that it does not require to truncate table after moving table from one tablespace to another and back as below.

1) alter table dept move tablespace apps

2) TRUNCATE TABLE DEPT ( NEED TO REMOVE THIS STEP, IF NOT WRONG)

3) alter table dept move tablespace SYSTEM

ALTERNATIVELY, I can perform as below

alter table dept move tablespace apps

It would remove intra-block fragementation. I wanted to know is theer any advantages of performing this job and re-building indexes.

Thanks for your clarification. Your further answer would be highly appreciated.

Report message to a moderator

Re: Table re-organisation using CLOB datatype column [message #227766 is a reply to message #227764]

Thu, 29 March 2007 06:14

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>any advantages of performing this job and re-building indexes
Usually,Rebuilding indexes may or may not help.
Whatever it is, do not rebuild indexes as a scheduled job.
In this case you need to rebuild the indexes (after an alter table move).
>> to remove intra-block fragmentation
You might find it, those tables will be in same state after a while.
So you would be doing this all your life:) with regular downtime!.
Ideal solution would be switching to LMT (local managed tablespaces) with uniform extent size. THis will almost fix any intra-block fragmentation (still inter-block fragmentation will exist and you cannot completetly eradicate it).
If you are still in DMT, use pctincrease=0 and initial=next.

De-Fragmentation is a dead concept (well, almost).
Design your tablespaces properly.
This very old Oracle white paper talks about it.
http://www.oracle.com/technology/deploy/availability/pdf/defrag.pdf

Report message to a moderator

Re: Table re-organisation using CLOB datatype column [message #230574 is a reply to message #227766]

Thu, 12 April 2007 04:07

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

Thanks for your valuable feedback for my queries. I would like to verify again one thing as below

1)It is really required to having downtime to re-organise table and re-build index while only the records are appended in the table. Please give your opinion

2) Can I create new table in another schema B using the old table from A schema. please note that this table is continously appending records through job(synronising from the live server).

Is there any issue of locking,latch or any negative impact on the DB while it is running

Please advise me.

Report message to a moderator

Re: Table re-organisation using CLOB datatype column [message #230674 is a reply to message #227766]

Thu, 12 April 2007 10:12

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

I would like to specify the initial,next and pctincrease for DMT based tablespace as below

20480 20480 0

please make your comment on it.

Report message to a moderator

Re: Table re-organisation using CLOB datatype column [message #230751 is a reply to message #227764]

Thu, 12 April 2007 12:55

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

jaydba wrote on Thu, 29 March 2007 07:02

3) alter table dept move tablespace SYSTEM

Jiminy Crickets, you're not doing that, are you?

Report message to a moderator

Re: Table re-organisation using CLOB datatype column [message #231007 is a reply to message #230751]

Fri, 13 April 2007 12:32

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

HI,
First i don't why you want RE-ORGANIZE system tbs. ...
Please don't use system tbs for normal operation.

regards
Taj

Report message to a moderator

Previous Topic:	Uninstallation of Oracle 9i
Next Topic:	SGA and PGA Parts

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 07 00:23:35 CST 2024