I have a tablespace with datafile of 100GB.Which contains one table of 70 GB data data is inserted or deleted in this table on monthly basis .This month purging has completed we are not going to insert data in this table for next three month. There is other three tables with total size 25 GB which are still in growing stage , hence these tables need space soon. I have to create space or can use the space from table having a huge deleted data . For cost cutting I would like to use the space occupied by delted enries .But in Oracle when we create the tables and insert data into tables extents are allocated to tables . Data stores in block(rows store in blocks) if delete the rows the deleted data are still occupying the data block .
Data blocks are not become free to insert data.
i.e
UUUUDDDDDDUUUUUUUDDDDDDUUUUDDUD
where U -Used Space
D- Deleted Space
extents are not deallocated for use so we have to make this space available in continues form .
UUUUUUUUUUUUFFFFFFFFFFFFFFFFF
F- Free space
My problem is how can I get back the unused space . Lets works on a small example
alter table TABLE_NAME move;
and now I am able to use the space .which was occupied by the deleted entries.
.when we move a table then oracle create a new table with continues blocks and insert data into that new table and drop the first one .and space is deallocated .
Second case is how can we resize the file .First we query for the used and free space in the database , as database objects used less space . Hence we need to decide to resize the file (become smaller) but sometimes oracle denies to shrink datafile.
i.e
I created the tablespace having datafile of size 500m.
SQL> create table tab1 tablespace newtbs as select * from dba_objects;
Table created.
SQL> select bytes/1024/1024 from dba_segments where segment_name='TAB1';
BYTES/1024/1024
---------------
232
SQL> create table tab2 tablespace newtbs as select * from tab1;
Table created.
SQL> select bytes/1024/1024 from dba_segments where segment_name='TAB2';
BYTES/1024/1024
---------------
232
SQL>drop table tab2;
I checked the free space in the tablespace.
SQL> select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB"
from
(select tablespace_name,
round(sum(bytes) / 1024/1024 ) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/1024/1024) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name
and tu.tablespace_name='NEWTBS'
SQL> /
Tablespace Used MB Free MB Total MB
------------------------------ ---------- ---------- ----------
NEWTBS 232 268 500
As output of the above query show used space is 232 MB. To make the space on the disk I want to resize the file .
SQL> alter database datafile '/home/oracle/oradata/mediamat/newtbs.dbf' resize 300m;
alter database datafile '/home/oracle/oradata/mediamat/newtbs.dbf' resize 300m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
If free space is available then why I am unable to resize the file.
In Oracle if we create the tables and insert the data into them extents are allocated to the tables. Once data inserted data blocks (allocate the extents to any object) are become used block ,Oracle will not allow the resize the file size below these used block sum.
Now we have to follow some steps to fulfilled our requirements we will move table from one tablespaces to other tablespace .when we move a table then oracle create a new table with continues blocks and insert data into that new table and drop the first one .and
i.e
SQL> alter table tab4 move tablespace users;
Table altered.
SQL> alter database datafile '/home/oracle/oradata/mediamat/newtbs.dbf' resize 300m;
Database altered.
You can use the script for move all tables with rebuild index.
select decode( segment_type, 'TABLE', segment_name, table_name ) decode( segment_type, 'TABLE', 1, 2 ) , 'alter ' || segment_type || ' ' || segment_name ||
decode( segment_type, 'TABLE', ' move ', ' rebuild ' )
|| ' tablespace &1 ;'
from user_segments, (select table_name, index_name from user_indexes )
where segment_type in ( 'TABLE', 'INDEX' )
and segment_name = index_name (+)
order by 1, 2
1)Alter table TBALE_NAME move tablespace TABLESPACEA_NAME;
Now we have to rebuild the index because they are unusable .
2)Alter index IINDEX_NAME REBUILD TABLESPACE MT_RPT01_INDEX;
We can find the last block used by the file as follow.
SQL> select file_id, block_id, blocks,
2 owner||'.'||segment_name "Name"
3 from sys.dba_extents
4 where tablespace_name = upper('&1')
5 UNION
6 select file_id, block_id, blocks,
7 'Free'
8 from sys.dba_free_space
9 where tablespace_name = upper('&1')
10 order by 1,2,3
11 /
FILE_ID BLOCK_ID BLOCKS Name
---------- ---------- ---------- ---------------------------------------------
2 9 8 NEWTB.ABC1
17 8 NEWTB.ABC1
25 8 NEWTB.ABC1
33 8 NEWTB.ABC1
41 8 NEWTB.ABC1
49 8 NEWTB.ABC1
57 8 NEWTB.ABC1
65 8 NEWTB.ABC1
73 8 NEWTB.ABC1
81 8 NEWTB.ABC1
2 89 8 NEWTB.ABC1
97 8 NEWTB.ABC1
105 8 NEWTB.ABC1
113 8 NEWTB.ABC1
121 8 NEWTB.ABC1
129 8 NEWTB.ABC1
137 128 NEWTB.ABC1
265 128 NEWTB.ABC1
393 128 NEWTB.ABC1
521 5880 Free
7 9 51192 Free
When should rebuild index
I) when deleted or updated entries increase and query is base on range After deleting records from tables index does not rebalanced even Oracle leaves "dead" index nodes in the index when rows are deleted
II. when a number of rows are inserted in a table then index level is increases. Hence we have to rebuild the index if we continuously inserting a huge data.
III) To set the pct increase to zero we can rebuild the index as if we set the pct increase 20% then the next allocated extend size will increase with 20 % and if extend is very large as result wastage of the space. To make extent size uniform oracle recommended pctincrease should be zero.
No comments:
Post a Comment