Home » SQL & PL/SQL » SQL & PL/SQL » Removing Empty space from tables without exporting/importing
icon8.gif  Removing Empty space from tables without exporting/importing [message #209332] Thu, 14 December 2006 06:04 Go to next message
apjone
Messages: 3
Registered: December 2006
Location: Wrexham
Junior Member

Hello, this is my first post so sorry if its not formatted correctly.

I have a Oracle database (set up way before my time with the company) which each month clears itself down using the DELETE command. However this leaves empty space in the table. Is there a way to remove this empty space with out exporting and importing the table.

I am great full for any help you can give me.

Tank you

Anthony
Re: Removing Empty space from tables without exporting/importing [message #209334 is a reply to message #209332] Thu, 14 December 2006 06:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can use TRUNCATE, but if you're going to re-add data to the tables, then there's no point, as you'll just end up re-creating the extents that you had previously.
Re: Removing Empty space from tables without exporting/importing [message #209335 is a reply to message #209332] Thu, 14 December 2006 06:09 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You can move the table and rebuild the indexes seperately.
sql>alter table table_name move tablespace same_tabelspace_as_before;

If want to do it online (without a downtine), look into dbms_redefinition (please search this forum/google/docs).

Edit:
And JRowBottom was faster and wiser Smile

[Updated on: Thu, 14 December 2006 06:12]

Report message to a moderator

Re: Removing Empty space from tables without exporting/importing [message #209337 is a reply to message #209332] Thu, 14 December 2006 06:36 Go to previous messageGo to next message
apjone
Messages: 3
Registered: December 2006
Location: Wrexham
Junior Member

Thx, the problem with just truncating is that i need to keep two months worth of data while getting shot of the white space.

Mahesh Rajendran could you please explain this a bit more as I am still a nebie.

sql>alter table table_name move tablespace same_tabelspace_as_before;

is tablespace a predefined within oracle? And what would the 'same_tablespace_as_before' vaule be if my table name was tbl_mytable.

thank you

Anthony

ps. thank you for your speedy replies earlier.

[Updated on: Thu, 14 December 2006 06:37]

Report message to a moderator

Re: Removing Empty space from tables without exporting/importing [message #209338 is a reply to message #209337] Thu, 14 December 2006 06:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I think you really should take JRowbottom's advice into consideration.
There is no point in reclaiming the non-used space if you know it will be used again at the end of the period. This will only mean more work for both you and the database, whereas you cannot even use the freed space in the meantime, because that would cause problems at the end of the period.
Re: Removing Empty space from tables without exporting/importing [message #209339 is a reply to message #209337] Thu, 14 December 2006 06:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Tablespaces are the Oracles internal representation of the physical space on disc in which data is stored.

Tables live in tablespaces.
Tablespaces are composed of one or more data files
Each data file is one physical file on disc.

To find out which tablespace your table lives in:
SELECT table_name,tablespace_name
FROM user_tables
WHERE table_name = <your table name>;
Re: Removing Empty space from tables without exporting/importing [message #209340 is a reply to message #209332] Thu, 14 December 2006 06:46 Go to previous messageGo to next message
apjone
Messages: 3
Registered: December 2006
Location: Wrexham
Junior Member

thank for your help guys.

Anthony
Re: Removing Empty space from tables without exporting/importing [message #209342 is a reply to message #209339] Thu, 14 December 2006 06:48 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I am really slow today Smile

[Updated on: Thu, 14 December 2006 06:51]

Report message to a moderator

Re: Removing Empty space from tables without exporting/importing [message #209346 is a reply to message #209340] Thu, 14 December 2006 07:15 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi,

1 create table scott.tab as select * from all_objects
2 union all
3 select * from all_objects
4 union all
5 select * from all_objects
6 union all
7 select * from all_objects
8 union all
9* select * from all_objects
SQL> /

Table created.

Elapsed: 00:00:11.29

SQL> create index scott.tabindex on scott.tab(object_id);

Index created.

Elapsed: 00:00:02.76

SQL> select index_name,status
2 from dba_indexes
3 where index_name = 'TABINDEX'
4 and owner = 'SCOTT';

INDEX_NAME STATUS
------------------------------ --------
TABINDEX VALID

Elapsed: 00:00:00.21

SQL> select bytes/1024/1024 "MB", blocks
2 from dba_segments
3 where segment_name = 'TAB'
4 and owner = 'SCOTT';

MB BLOCKS
---------- ----------
26 3328

Elapsed: 00:00:00.29

SQL> select count(*) from scott.tab;

COUNT(*)
----------
236950

Elapsed: 00:00:01.40

Note : select statement take "01.40". time.
Now i am delete 236000 rows in the table ( i am perfom DML operation so it is not reclaim ur space ( no reset HWM High Water Mark).


SQL> delete scott.tab
2 where rownum <= 236000;

236000 rows deleted.

Elapsed: 00:01:07.50
SQL> select index_name,status
2 from dba_indexes
3 where index_name = 'TABINDEX'
4 and owner = 'SCOTT';

INDEX_NAME STATUS
------------------------------ --------
TABINDEX VALID

Elapsed: 00:00:00.20


SQL> select count(*) from scott.tab;

COUNT(*)
----------
950

Elapsed: 00:00:00.81

SQL> set autot traceonly stat

SQL> select count(*) from scott.tab;

Elapsed: 00:00:00.68

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3263 consistent gets
3006 physical reads
0 redo size
394 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

After 236000 rows delete but oracle also take .81 sec time or 3263 to consistant get to collect rows.
( here full table scan bcoz space doesn't claim )

For set HWM mark we can used export or import or alter table move option.

SQL> alter table scott.tab move tablespace oracle;

Table altered.

Elapsed: 00:00:04.23

SQL> select count(*) from scott.tab;

Elapsed: 00:00:00.06
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
37 consistent gets
14 physical reads
116 redo size
394 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

after table reorganize result connect in 0.6 sec or 37 cosistent get. after alter table move all index are unusable you have to rebuild.

SQL> set autotrace off
SQL> select index_name,status
2 from dba_indexes
3 where index_name = 'TABINDEX'
4 and owner = 'SCOTT';

INDEX_NAME STATUS
------------------------------ --------
TABINDEX UNUSABLE
Elapsed: 00:00:00.20
SQL> alter index scott.tabindex rebuild;

Index altered.

Elapsed: 00:00:00.20
SQL>

SQL> select bytes/1024/1024 "MB", blocks
2 from dba_segments
3 where segment_name = 'TAB'
4 and owner = 'SCOTT';

MB BLOCKS
---------- ----------
.125 16

Elapsed: 00:00:00.00
SQL>
[/code]

regards
Taj













[Updated on: Thu, 14 December 2006 07:24]

Report message to a moderator

Re: Removing Empty space from tables without exporting/importing [message #209349 is a reply to message #209346] Thu, 14 December 2006 07:22 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
seems ECHO was set to ON today. Smile
Re: Removing Empty space from tables without exporting/importing [message #209351 is a reply to message #209332] Thu, 14 December 2006 07:24 Go to previous messageGo to next message
orafaqer
Messages: 48
Registered: July 2006
Member
In 10g you you are able to shrink table.
http://www.oracle.com/technology/oramag/oracle/05-may/o35tuning.html
Re: Removing Empty space from tables without exporting/importing [message #209378 is a reply to message #209351] Thu, 14 December 2006 08:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
@user52:
I can come up with practically the same example, proving that a reorganize has no effect:
SQL> create table faq
  2  as
  3  select rownum id
  4  ,      obj.*
  5  from   (select * from all_objects
  6          union all
  7          select * from all_objects
  8          union all
  9          select * from all_objects
 10          union all
 11          select * from all_objects
 12          union all
 13          select * from all_objects
 14         ) obj
 15  /

Table created.

Elapsed: 00:00:15.35
SQL> alter table faq add constraint faq_pk
  2  primary key (id)
  3  /

Table altered.

Elapsed: 00:00:00.40
SQL> exec dbms_stats.gather_table_stats(user, 'FAQ', cascade => true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.61
SQL>
SQL> set timing on
SQL>
SQL> select count(*)
  2  from   faq;

  COUNT(*)
----------
     67235

Elapsed: 00:00:00.01
SQL>
SQL> delete faq where rownum <= 67000
  2  /

67000 rows deleted.

Elapsed: 00:00:03.46
SQL> exec dbms_stats.gather_table_stats(user, 'FAQ', cascade => true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.36
SQL>
SQL> select count(*)
  2  from   faq;

  COUNT(*)
----------
       235

Elapsed: 00:00:00.01

Since all tables should have a primary key, and all tables should be analyzed on a regular base, my example is more 'real-world' than yours...

[Edit: misspelled name]

[Updated on: Thu, 14 December 2006 08:31]

Report message to a moderator

Re: Removing Empty space from tables without exporting/importing [message #209399 is a reply to message #209378] Thu, 14 December 2006 10:42 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

proving that a reorganize has no effect:
but where you did reorganize table.

dbms_stats package is used for Statistics. not for table reorganization.

In my example my point is RESET HWM. not timing.

SQL> ed
Wrote file afiedt.buf

1 create table faq_2
2 as
3 select rownum id
4 , obj.*
5 from (select * from all_objects
6 union all
7 select * from all_objects
8 union all
9 select * from all_objects
10 union all
11 select * from all_objects
12 union all
13 select * from all_objects
14* ) obj
15 /

Table created.

Elapsed: 00:00:16.32
SQL> alter table faq_2 add constraint faqpk primary key(id);

Table altered.

Elapsed: 00:00:04.40

SQL> set timing off

SQL> exec dbms_stats.gather_table_stats(user,'FAQ_2' , cascade => true);

PL/SQL procedure successfully completed.

SQL> set timing on

SQL> select count(*) from faq_2;

COUNT(*)
----------
237010

Elapsed: 00:00:00.20

SQL> select bytes/1024/1024 "MB", blocks
2 from user_segments
3 where segment_name = 'FAQ_2';

MB BLOCKS
---------- ----------
27 3456

Elapsed: 00:00:00.21

SQL> delete faq_2 where rownum <= 236550;

236550 rows deleted.

Elapsed: 00:01:00.65

SQL> exec dbms_stats.gather_table_stats(user,'FAQ_2' , cascade => true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.48

SQL> select count(*) from faq_2;

COUNT(*)
----------
460

Elapsed: 00:00:00.18

SQL> select bytes/1024/1024 "MB", blocks
2 from user_segments
3 where segment_name = 'FAQ_2';

MB BLOCKS
---------- ----------
27 3456

Elapsed: 00:00:00.18

SQL> set autotrace traceonly statistics

SQL> select count(*) from faq_2;

Elapsed: 00:00:00.04

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
501 consistent gets
0 physical reads
0 redo size
394 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> alter table faq_2 move ;

Table altered.

Elapsed: 00:00:01.37
SQL> set autotrace off
SQL> select bytes/1024/1024 "MB", blocks
2 from user_segments
3 where segment_name = 'FAQ_2';

MB BLOCKS
---------- ----------
.125 16
Elapsed: 00:00:00.20


SQL> alter index faqpk rebuild;

Index altered.

Elapsed: 00:00:00.20
SQL> exec dbms_stats.gather_table_stats(user,'FAQ_2' , cascade => true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.20
SQL> select count(*) from faq_2;

COUNT(*)
----------
460

regards
Taj
Re: Removing Empty space from tables without exporting/importing [message #209460 is a reply to message #209399] Fri, 15 December 2006 00:23 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I showed that a reorg is not necessary.
If your point is to show the reset of the HWM instead of timing, then why do you execute a "select count"?
My point is that in most cases it is just a waste of effort to reorganize the table if you are going to fill it again.
If you have the proper indexes, and you analyze it regularly, you will not even notice the 'empty space'.
Previous Topic: Procedure Help Needed. (SQL, PL/SQL)
Next Topic: date difference
Goto Forum:
  


Current Time: Wed Dec 07 08:51:12 CST 2016

Total time taken to generate the page: 0.15734 seconds