| Find out which blocks included in which extent/datafile [message #674235] |
Sun, 13 January 2019 09:51  |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Hi all,
I have a database data of which is very fragmented:
2.9TB of segments/extents:
SQL>
SQL> select sum(bytes/1024/1024/1024) gb from dba_extents;
GB
----------
2952.08954
SQL> SQL>
SQL>
SQL> select sum(bytes/1024/1024/1024) gb from dba_segments;
GB
----------
2959.64288
But as much as 4.7 TB of total space take by datafiles:
select sum(bytes/1024/1024/1024) gb from dba_data_files;
I tried to minimize datafiles by resizing them according to the output of this query, by Tom Kyte:
set verify off pages 2000
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/
select file_name,
ceil( (nvl(hwm,1)*8192)/1024/1024 ) smallest,
ceil( blocks*8192/1024/1024) currsize,
ceil( blocks*8192/1024/1024) -
ceil( (nvl(hwm,1)*8192)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/
column cmd format a700 word_wrapped
col cmd for a1000
set lines 1000 pages 10000
--
select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*8192)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*8192/1024/1024) -
ceil( (nvl(hwm,1)*8192)/1024/1024 ) > 0
until it hits with the "ORA-03214: File Size specified is smaller than minimum required"
when running my queries again - nothing changes.
When I check DBA_EXTENTS I see only blocks that are allocated to a segment, but not blocks that are free.
How can I see which blocks are free, or where the blocks that have free space are locked ?
TIA
Andrey
|
|
|
|
| Re: Find out which blocks included in which extent/datafile [message #674236 is a reply to message #674235] |
Sun, 13 January 2019 10:04  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
dba_extents shows you extents allocated to objects that have not been dropped, dba_segments shows you all segments dropped or not. So it seems likely that you have a few GB of dropped stuff in the recyclebin. These dropped objects will prevent resizing datafiles. dba_free_space shows all extents available for use, including the recyclebin objects so it may be a bit misleading.
You may find that purging the recycle bin helps.
|
|
|
|
|
|
| Re: Find out which blocks included in which extent/datafile [message #674238 is a reply to message #674236] |
Sun, 13 January 2019 10:08 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
John Watson wrote on Sun, 13 January 2019 18:04dba_extents shows you extents allocated to objects that have not been dropped, dba_segments shows you all segments dropped or not. So it seems likely that you have a few GB of dropped stuff in the recyclebin. These dropped objects will prevent resizing datafiles. dba_free_space shows all extents available for use, including the recyclebin objects so it may be a bit misleading.
You may find that purging the recycle bin helps.
My recycle bin is empty:
SQL> select * from recyclebin;
no rows selected
SQL>
|
|
|
|
|
|
|
|
| Re: Find out which blocks included in which extent/datafile [message #674241 is a reply to message #674239] |
Sun, 13 January 2019 10:34 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
John Watson wrote on Sun, 13 January 2019 18:09Are not you aware that each schema has a recyclebin?
Sorry, I was not.
Indeed many objects - 2k objects to be exact... I purged them and now it's empty:
SQL> select * from dba_recyclebin;
no rows selected
I have executed the commands to resize datafiles per the script on dba_extents e.t.c - however doesn't look like situation changed, still difference I cannot put my hands on:
SELECT sum(bytes/1024/1024/1024) total_DB_segments_size from dba_segments ;
SELECT sum(bytes/1024/1024/1024) total_DB_DATAfile_size from dba_data_files;SQL> SQL>
TOTAL_DB_SEGMENTS_SIZE
----------------------
2954.17725
SQL>
TOTAL_DB_DATAFILE_SIZE
----------------------
4709.15428
|
|
|
|
|
|
| Re: Find out which blocks included in which extent/datafile [message #674243 is a reply to message #674242] |
Sun, 13 January 2019 11:32 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Michel Cadot wrote on Sun, 13 January 2019 18:36
So now you have to query DBA_FREE_SPACE.
So I did, and found this:
select tablespace_name,sum(bytes/1024/1024/1024) gb from dba_free_space
group by tablespace_name
order by 2 desc;
SQL> 2 3
TABLESPACE_NAME GB
------------------------------ ----------
MYTBS 713.588562
OTHERTBS ...
OTHERTBS2 .....
...
..
11 rows selected.
So I queried to see how much in consumes in segments:
SQL> SELECT SUM(BYTES/1024/1024/1024) GB FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'MYTBS';
GB
----------
4.62823486
SQL>
So I tried to see if there is a place coalesce here, if I have free extents that are next to one another
(took the query from AskTom => https://asktom.oracle.com/pls/apex/f%3Fp%3D100:11:0::::P11_QUESTION_ID:466020333473 )
select a.tablespace_name, a.file_id, a.block_id, a.blocks, b.block_id
SQL> 2 from dba_free_space a, dba_free_space b
3 where a.tablespace_name = 'MYTBS'
4 and b.tablespace_name = 'MYTBS'
5 and a.tablespace_name = b.tablespace_name
and a.file_id = b.file_id
6 7 and a.block_id+a.blocks = b.block_id
8 /
TABLESPACE_NAME FILE_ID BLOCK_ID BLOCKS BLOCK_ID
------------------------------ ---------- ---------- ---------- ----------
MYTAB 54 22656 485376 508032
MYTAB 54 508032 507904 1015936
MYTAB 54 1015936 507904 1523840
MYTAB 54 1647744 384000 2031744
MYTAB 54 2031744 507904 2539648
MYTAB 54 2539648 507904 3047552
....
...
..
.
156 rows selected.
SQL>
So I tried to COALESCE, and resize some of the datafiles, but it didn't work, and the query on dba_free_space still shows nearby extents that are seperated..
SQL> alter tablespace MYTBS COALESCE;
Tablespace altered.
SQL> PURGE DBA_RECYCLEBIN;
DBA Recyclebin purged.
SQL> ALTER DATABASE DATAFILE '+DG1/....MYTBS02.dbf' resize 10G;
ALTER DATABASE DATAFILE '+DG1/....MYTBS02.dbf' resize 10G
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
Even though it is a 26GB datafile , one of many, in this 0.7TB ( ! ) tablespace that has only 4GB segments...:
SQL> SELECT BYTES/1024/1024/1024 FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'MYTBS' AND FILE_ID=50;
BYTES/1024/1024/1024
--------------------
26.3837891
SQL>
Is it possible that the data in all tens of datafiles of this MYTBS tablespace are fragmented so badly that I cannot resize them ??
How can I find out which extent in a particular file is holding me back from reclaiming a nice chunk of space back ??
|
|
|
|
| Re: Find out which blocks included in which extent/datafile [message #674244 is a reply to message #674243] |
Sun, 13 January 2019 11:44 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
I tried to shrink it with
SQL> ALTER TABLESPACE MYTBS SHRINK SPACE;
ALTER TABLESPACE MYTBS SHRINK SPACE
*
ERROR at line 1:
ORA-12916: cannot shrink permanent or dictionary managed tablespace
But couldn't, since its
SQL> select tablespace_name, contents, EXTENT_MANAGEMENT from dba_tablespaces where tablespace_name='MYTBS';
TABLESPACE_NAME CONTENTS EXTENT_MAN
------------------------------ --------- ----------
MYTBS PERMANENT LOCAL
So I'm not sure what are my options... I mean obviously I have stuff leaving the watermark for my datafiles way too high, but I can't understand how to re-organize so that I can resize my datafiles and free up the space, without downtime ( like expdp-impdp would cause) , moreover when I have 99% of the space locked in 1% of data extents....
What am I missing ?
[Updated on: Sun, 13 January 2019 11:46] Report message to a moderator |
|
|
|
|
|
| Re: Find out which blocks included in which extent/datafile [message #674246 is a reply to message #674245] |
Sun, 13 January 2019 12:24 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
1.
BlackSwan wrote on Sun, 13 January 2019 20:04How many different OS files comprise MYTBS?
select count(*) from dba_data_files where tablespace_name = 'MYTBS';
SQL>
COUNT(*)
----------
73
2.
BlackSwan wrote on Sun, 13 January 2019 20:04What object exists at the "top" (highest block#) of MYTBS tablespace?
I am not sure I know how to check this for a tablespace, if I understood correctly, you mean in a datafile of this tablespace?
I tried to check datafile No. 50 and I do see a gap, and that it's inside, under the HWM which is represented by EXTENT_ID 125 ??
SQL>
select * from dba_extents where file_id = 50;SQL>
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
-------------------- -------------------- -------------------- ------------------ -------------------- ---------- ---------- ---------- ---------- ---------- ------------
S1_OWNER SOME_SEG___1 P0 TABLE PARTITION MYTBS 6 50 128 8388608 1024 50
S1_OWNER SOME_SEG___1 P0 TABLE PARTITION MYTBS 26 50 1152 8388608 1024 50
S1_OWNER SOME_SEG___1 P0 TABLE PARTITION MYTBS 46 50 2176 8388608 1024 50
S1_OWNER SOME_SEG___1 P0 TABLE PARTITION MYTBS 66 50 3200 8388608 1024 50
S1_OWNER SOME_SEG___1 P0 TABLE PARTITION MYTBS 105 50 2039936 8388608 1024 50
S1_OWNER SOME_SEG___1 P0 TABLE PARTITION MYTBS 125 50 3457152 8388608 1024 50
6 rows selected.
3.
BlackSwan wrote on Sun, 13 January 2019 20:04If or after you move this object to a different tablespace you'll be able to shrink MYTBS down to new & lower HWM.
How do I move specific extents from one tablespace to another ?
|
|
|
|
|
|
| Re: Find out which blocks included in which extent/datafile [message #674248 is a reply to message #674247] |
Sun, 13 January 2019 14:31 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
BlackSwan wrote on Sun, 13 January 2019 20:52Andrey_R wrote on Sun, 13 January 2019 10:24
3.
BlackSwan wrote on Sun, 13 January 2019 20:04If or after you move this object to a different tablespace you'll be able to shrink MYTBS down to new & lower HWM.
How do I move specific extents from one tablespace to another ?
I said to move OBJECT from MYTBS to different tablespace.
For example you can use DBMS_REDEFINTION to move a table between tablespaces.
post results from SQL below
SELECT OBJECT_TYPE, COUNT(*) FROM ALL_EXTENTS WHERE TABLESPACE_NAME = 'MYTBS' GROUP BY OBJECT_TYPE;
SQL> SELECT SEGMENT_TYPE, COUNT(*)
2 FROM dba_EXTENTS
3 WHERE TABLESPACE_NAME = 'MYTBS'
4 GROUP BY SEGMENT_TYPE;
SEGMENT_TYPE COUNT(*)
------------------ ----------
TABLE SUBPARTITION 2
TABLE PARTITION 243
SQL>
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
