|
|
|
Re: Removing Empty space from tables without exporting/importing [message #209337 is a reply to message #209332] |
Thu, 14 December 2006 06:36 |
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 |
Frank
Messages: 7901 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 |
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 #209346 is a reply to message #209340] |
Thu, 14 December 2006 07:15 |
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 #209378 is a reply to message #209351] |
Thu, 14 December 2006 08:27 |
Frank
Messages: 7901 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 |
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 |
Frank
Messages: 7901 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'.
|
|
|