OraFAQ Forum: Performance Tuning » what are the steps to follow to coalesce

Home » RDBMS Server » Performance Tuning » what are the steps to follow to coalesce

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

what are the steps to follow to coalesce [message #160798]

Tue, 28 February 2006 06:59

pnvani
Messages: 8
Registered: February 2006

Junior Member

Hi!

I am working on a project where oracle 7.3.4 is used. I have taken up some tables and deleted some millions of records from those tables as part of database purging.

But when i compare the freespace before deleting and after deleting records, there is no difference.

Then I learned that we can do coalesce to reclaim tablespace.

So did ( when database was up and running )

alter tablespace tablespace_name coalesce;

Then i ran freespace script to get freespace report.

Still I don't see any change in the freespace report. It shows the same as before deleting the records.

Do i need to rebuild indexes and other stuff to see the change?

Pl. tell me what i need to do?
Any help is appreciated. Mad

Mad

Report message to a moderator

Re: what are the steps to follow to coalesce [message #160800 is a reply to message #160798]

Tue, 28 February 2006 07:04

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

Senior Member
Account Moderator

Search the forum for HWM.
One such is here.
Delete will NOT reclaim the space.
You need to truncate
http://www.orafaq.com/forum/t/8612/0/
http://www.orafaq.com/forum/m/110688/42800/?srch=tbs_usage#msg_110688

With higher versions you may find alter table move tablespace very handy.
In 7.3, export/import may help ( export with compress=n).
Or
create table another_table /*+ APPEND */ as select * from mytable;
drop table mytable;
rename another_table to mytable;

[Updated on: Tue, 28 February 2006 07:07]

Report message to a moderator

Re: what are the steps to follow to coalesce [message #160809 is a reply to message #160798]

Tue, 28 February 2006 07:41

pnvani
Messages: 8
Registered: February 2006

Junior Member

Mahesh,

Thanks.

So if i do

create table another_table /*+ APPEND */ as select * from mytable;
drop table mytable;
rename another_table to mytable;

then i will see some change in the freespace report ?

if so then i shouldn't be doing delete rather i can do as you say above, right?

Then what will happen to the constraints involved? Also will my entire database be in sync, i mean it will not be hampered.

Pl. reply

Report message to a moderator

Re: what are the steps to follow to coalesce [message #160811 is a reply to message #160809]

Tue, 28 February 2006 07:55

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

Senior Member
Account Moderator

>>then i will see some change in the freespace report ?
Try it!. Test it. Then do it in your production database.
this only for one table and constraints / indexes are not dealt. So you need to rebuild the constraints/indexes.
You need to do this for all tables ( a simple scripting would do).
Probably for you, export/import is the easiest way.

[Updated on: Tue, 28 February 2006 07:55]

Report message to a moderator

Re: what are the steps to follow to coalesce [message #160822 is a reply to message #160798]

Tue, 28 February 2006 09:10

pnvani
Messages: 8
Registered: February 2006

Junior Member

Mahesh,

Thanks a lot for your quick reply. I still need some exact info.
I have indexes and constraints on the tables. if i do

create index
create anoter_table as select * from my_table
drop my_table
rename my_table to another_table
drop index

in a script then this table will retain the relationship with other tables in the DB as before ?

i want to try but i want to know before that.

Also what are the steps for export/import stuff ? How that helps in deleting old data and reclaiming space?

Pl. explain

Neela

Report message to a moderator

Re: what are the steps to follow to coalesce [message #160826 is a reply to message #160822]

Tue, 28 February 2006 09:40

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

Senior Member
Account Moderator

>> Also what are the steps for export/import stuff ? How that helps in deleting old data and reclaiming space?
http://www.orafaq.com/faq/import_export
More information in Oracle documentation.
Long story short, with export/import ,You are rebuilding your database/concerned schema.

>>create index
>>create anoter_table as select * from my_table
>>drop my_table
>>rename my_table to another_table
>>drop index

>>in a script then this table will retain the relationship with other tables in the DB as before ?
You need to understand more.
Above has nothing to do with relationships.
When you drop the table, all the related indexes/constraints are dropped.
When you create another_table with CTAS ( Create table as select) indexes/constraints are not recreated. You can see here another_emp table as not been recreated with indexes/constraints by default. You need to recreate them manually.

scott@9i > select table_name,index_name from user_indexes;

TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
DEPT                           SYS_C001862
EMP                            SYS_C001863

scott@9i > select table_name,constraint_name,constraint_type,r_constraint_name from user_constraints;

TABLE_NAME                     CONSTRAINT_NAME                C R_CONSTRAINT_NAME
------------------------------ ------------------------------ - ------------------------------
DEPT                           SYS_C001862                    P
EMP                            SYS_C001863                    P
EMP                            SYS_C001864                    R SYS_C001862

scott@9i > create table another_emp as select * from emp;

Table created.

scott@9i > select table_name,index_name from user_indexes;

TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
DEPT                           SYS_C001862
EMP                            SYS_C001863

scott@9i > select table_name,constraint_name,constraint_type,r_constraint_name from user_constraints;

TABLE_NAME                     CONSTRAINT_NAME                C R_CONSTRAINT_NAME
------------------------------ ------------------------------ - ------------------------------
DEPT                           SYS_C001862                    P
EMP                            SYS_C001863                    P
EMP                            SYS_C001864                    R SYS_C001862

Other option is to just re-insert the data back ( without dropping the original table)

scott@9i > drop table another_emp;

Table dropped.

scott@9i > create table anothe_emp as select * from emp;

Table created.

scott@9i > truncate table emp;

Table truncated.

scott@9i > insert into emp /*+ APPEND */ select * from anothe_emp;

14 rows created.

scott@9i > commit;

Commit complete.

Drop drop the duplicate / dummy table.
You have to do this for every table.
Instead do an export/import.

Report message to a moderator

Re: what are the steps to follow to coalesce [message #160956 is a reply to message #160826]

Wed, 01 March 2006 03:05

pnvani
Messages: 8
Registered: February 2006

Junior Member

Mahesh,

Thanks a lot for the clarification. I am aware of export/import stuff but what i did was i have selected some tables which has millions of data and did a delete. I have created and dropped indexes on those tables in a script. But i am still not seeing any change in the freespace report.

You are suggesting that instead of delete, i should be creating another table, push the no. of records to be deleted in to it, truncate original table, then insert into original table from another table and commit. I will try this for another table and let you know.

so you mean to say that i should go for truncate rather than delete?

Now i have deleted, what steps should i do for seeing a change in the freespace report ? i did coalesce, but it didn't do. At this point what export/import should i do so that i will not get back deleted data but also i should see a change in the freespace report.

Once again thanks in advance
Neela

Report message to a moderator

Re: what are the steps to follow to coalesce [message #160974 is a reply to message #160956]

Wed, 01 March 2006 04:07

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

Senior Member
Account Moderator

>> Now i have deleted, what steps should i do for seeing a change in the freespace report ?
There is no point in continuing this. What do you mean by "freespace report"? Only when you truncate/drop the table,the HWM is reset.
Post what you have done (.i,e exact snapshots/sessions/code).
see here
http://www.orafaq.com/forum/m/110688/42800/?srch=tbs_usage#msg_110688
You can see the "freespace" is better after truncate.

You rebuilt only the indexes. NOT the tables. So it may not make a difference.
And what do you mean by "freespace" report?
You have tablespace DATA with datafile DATA size 10m;
The table size is 8m;
after resetting the HWM , table size may be around say,5m.
Still the datafile will be 10m. It is only the space withing the datafile that is been released.

Report message to a moderator

Re: what are the steps to follow to coalesce [message #161002 is a reply to message #160798]

Wed, 01 March 2006 06:33

pnvani
Messages: 8
Registered: February 2006

Junior Member

Mahesh,

what i did was as i told you i deleted some records from some tables. I ran freespace.sh script before and after deleting records. i didn't see any change. The freespace looks like this :

Connected to database cdis2, ORACLE_SID=CR1, userid=

OSQL-> OSQL-> DB TableSpace FreeSpace Summary (SID=CR1)
==================================================
TableSpace Total ext # free Total free Percent largest
Name size (Kb) extent space (Kb) available extent (Kb)
------------ ----------- -------- ------------ --------------
AUTOIND1 1022976 1 752272 73.54 752272
AUTOIND2 2558976 4 1640648 64.11 1115848
CLRTBL1 1125376 7 585392 52.02 447744
CLRTBL2 818176 4 493328 60.30 489608

I am seeing the total free space. No change.

What should i do now so that i will see some change in the freespace ?

This is my point.

Thanks
Neela

Report message to a moderator

Re: what are the steps to follow to coalesce [message #161003 is a reply to message #161002]

Wed, 01 March 2006 06:37

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

Senior Member
Account Moderator

>> u i deleted some records from some tables.
Deletion will not release the free space.

Report message to a moderator

Re: what are the steps to follow to coalesce [message #161005 is a reply to message #161003]

Wed, 01 March 2006 06:43

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

Senior Member
Account Moderator

I do not have an oracle 7.3 around.
But the concept is almost the same.

scott@9i > get size
  1  column segment_name format a15
  2* Select segment_name,bytes  size_in_bytes  from user_segments where segment_name='&SEGMENT_NAME';
scott@9i > @size
Enter value for segment_name: EMP
old   1: Select segment_name,bytes  size_in_bytes  from user_segments where segment_name='&SEGMENT_NAME'
new   1: Select segment_name,bytes  size_in_bytes  from user_segments where segment_name='EMP'

SEGMENT_NAME    SIZE_IN_BYTES
--------------- -------------
EMP                  23068672

scott@9i > select count(*) from emp;

  COUNT(*)
----------
    458752

scott@9i > delete from emp;

458752 rows deleted.

scott@9i > commit;

Commit complete.

scott@9i > @size
Enter value for segment_name: EMP
old   1: Select segment_name,bytes  size_in_bytes  from user_segments where segment_name='&SEGMENT_NAME'
new   1: Select segment_name,bytes  size_in_bytes  from user_segments where segment_name='EMP'

SEGMENT_NAME    SIZE_IN_BYTES
--------------- -------------
EMP                  23068672

scott@9i > truncate table emp;

Table truncated.

scott@9i > @size
Enter value for segment_name: EMP
old   1: Select segment_name,bytes  size_in_bytes  from user_segments where segment_name='&SEGMENT_NAME'
new   1: Select segment_name,bytes  size_in_bytes  from user_segments where segment_name='EMP'

SEGMENT_NAME    SIZE_IN_BYTES
--------------- -------------
EMP                     65536

Report message to a moderator

Re: what are the steps to follow to coalesce [message #161007 is a reply to message #160798]

Wed, 01 March 2006 06:58

pnvani
Messages: 8
Registered: February 2006

Junior Member

Mahesh,

Thanks a lot for clarifying.

Now can i have another script where in i will do truncate of those tables ? then creating and dropping indexes is required or not ? Meanwhile i will try

Thanks
Neela

Report message to a moderator

Re: what are the steps to follow to coalesce [message #161008 is a reply to message #161007]

Wed, 01 March 2006 07:04

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

Senior Member
Account Moderator

Please read my first response.
I am saying the same thingy again and again and again.
Create a backup of data (using CTAS).
truncate the table
Insert back the data.

You may want to drop the indexes before loading the data (just to speed the process. If you are not worried about, just leave it.) and create it again. In 7.3 versions, it is better to recreate the indexes, collect statistics again.

Report message to a moderator

Re: what are the steps to follow to coalesce [message #161013 is a reply to message #160798]

Wed, 01 March 2006 07:24

pnvani
Messages: 8
Registered: February 2006

Junior Member

mahesh,

I am worried about your last line. Its indicating me to recreate all indexes on the tables and get the statistics. You mean i have to recreate all indexes that the table was having before truncating ? Then i will have lot of indexes on lot of tables. Then its tedious. Also what statistics should i see and how ?

You know i don't want to disturb the current schema with tables, indexes,constraints etc. its very sensitive. Thats why i didn't attempt truncate.

Also you indicated me export/import. I have deleted records. At this point how should i do export/import so as to get the same db after deleting records.

I am very sorry to bother you but i am helpless.

Report message to a moderator

Re: what are the steps to follow to coalesce [message #161022 is a reply to message #161013]

Wed, 01 March 2006 07:58

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

Senior Member
Account Moderator

>> Also what statistics should i see and how ?
I mean, the statistics on tables/indexes.

sql> Analyze table < tname > compute/estimate statistics.

>>Also you indicated me export/import. I have deleted records. At this point how should i do export/import so as to get the same db after deleting records.
Export / import is the same as dropping tables and recreating all the objects. You do not have many options with oracle 7.3 ( does oracle 7.3 support rebuild index online? i have no idea).
>> Then its tedious.
Yes it is. Oracle 7.3 is stone age version of oracle.
Long story short,
Unless you rebuild your tables/truncate the data/move tables(not possible in 7.3), HWM will not be reset. And if you are not even collecting statistics on tables/indexes, thats painful.

Report message to a moderator

Previous Topic:	Visual Explain
Next Topic:	Statistic

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 15:06:10 CST 2024