Home » RDBMS Server » Server Administration » Find out which blocks included in which extent/datafile (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
Find out which blocks included in which extent/datafile [message #674235] Sun, 13 January 2019 09:51 Go to next message
Andrey_R
Messages: 305
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 Go to previous messageGo to next message
John Watson
Messages: 7777
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 #674237 is a reply to message #674235] Sun, 13 January 2019 10:07 Go to previous messageGo to next message
Michel Cadot
Messages: 66205
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In addition (to dba_free_space), you'll surely have to move some objects and/or compact them, these procedures will help you:
DBMS_SPACE.SPACE_USAGE
DBMS_SPACE.UNUSED_SPACE

[Updated on: Sun, 13 January 2019 10:10]

Report message to a moderator

Re: Find out which blocks included in which extent/datafile [message #674238 is a reply to message #674236] Sun, 13 January 2019 10:08 Go to previous messageGo to next message
Andrey_R
Messages: 305
Registered: January 2012
Location: Israel
Senior Member

John Watson wrote on Sun, 13 January 2019 18:04
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.

My recycle bin is empty:

SQL> select * from recyclebin;

no rows selected

SQL>

Re: Find out which blocks included in which extent/datafile [message #674239 is a reply to message #674238] Sun, 13 January 2019 10:09 Go to previous messageGo to next message
John Watson
Messages: 7777
Registered: January 2010
Location: Global Village
Senior Member
Are not you aware that each schema has a recyclebin?
Re: Find out which blocks included in which extent/datafile [message #674240 is a reply to message #674238] Sun, 13 January 2019 10:10 Go to previous messageGo to next message
Michel Cadot
Messages: 66205
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
My recycle bin is empty:
Query DBA_recyclebin.

Re: Find out which blocks included in which extent/datafile [message #674241 is a reply to message #674239] Sun, 13 January 2019 10:34 Go to previous messageGo to next message
Andrey_R
Messages: 305
Registered: January 2012
Location: Israel
Senior Member

John Watson wrote on Sun, 13 January 2019 18:09
Are 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 #674242 is a reply to message #674241] Sun, 13 January 2019 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 66205
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So now you have to query DBA_FREE_SPACE.
Re: Find out which blocks included in which extent/datafile [message #674243 is a reply to message #674242] Sun, 13 January 2019 11:32 Go to previous messageGo to next message
Andrey_R
Messages: 305
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 Go to previous messageGo to next message
Andrey_R
Messages: 305
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 #674245 is a reply to message #674244] Sun, 13 January 2019 12:04 Go to previous messageGo to next message
BlackSwan
Messages: 26410
Registered: January 2009
Location: SoCal
Senior Member
How many different OS files comprise MYTBS?

What object exists at the "top" (highest block#) of MYTBS tablespace?

If or after you move this object to a different tablespace you'll be able to shrink MYTBS down to new & lower HWM.
Re: Find out which blocks included in which extent/datafile [message #674246 is a reply to message #674245] Sun, 13 January 2019 12:24 Go to previous messageGo to next message
Andrey_R
Messages: 305
Registered: January 2012
Location: Israel
Senior Member

1.
BlackSwan wrote on Sun, 13 January 2019 20:04
How 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:04
What 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:04
If 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 #674247 is a reply to message #674246] Sun, 13 January 2019 12:52 Go to previous messageGo to next message
BlackSwan
Messages: 26410
Registered: January 2009
Location: SoCal
Senior Member
Andrey_R wrote on Sun, 13 January 2019 10:24

3.
BlackSwan wrote on Sun, 13 January 2019 20:04
If 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;
Re: Find out which blocks included in which extent/datafile [message #674248 is a reply to message #674247] Sun, 13 January 2019 14:31 Go to previous messageGo to next message
Andrey_R
Messages: 305
Registered: January 2012
Location: Israel
Senior Member

BlackSwan wrote on Sun, 13 January 2019 20:52
Andrey_R wrote on Sun, 13 January 2019 10:24

3.
BlackSwan wrote on Sun, 13 January 2019 20:04
If 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>
Re: Find out which blocks included in which extent/datafile [message #674249 is a reply to message #674248] Sun, 13 January 2019 19:07 Go to previous message
Andrey_R
Messages: 305
Registered: January 2012
Location: Israel
Senior Member

Thank you very much BlackSwan , Michel Cadot, John Watson - Moving the partitions/subpartitions to a different tablespace indeed freed up the HWM for me to perform a successfull resize and reclaim the space.

And also, I've learned a lot! Much appreciated !

Andrey
Previous Topic: Service Name
Next Topic: ORA-01012 ORA-02063 ORA-02396 Database Link Disconnect
Goto Forum:
  


Current Time: Sat Feb 23 13:48:19 CST 2019