Find top 10 lob of schema in oracle

Dear Team,

Please let me know SQL query in oracle 10g for below statements:

1. which objects (atleast top 10) has grown/consume space (in GBs) in last 2-3 days.

2. which Lob segments (atleast top 10) has grown/consume space (in GBs) in last 2-3 days.

For Lob segment, i have query, see below, i think it require to modify:

select * from (

select

l.owner,

l.table_name,

l.column_name,

l.segment_name,

round(s.bytes/1024/1024) as mb

from dba_lobs l , dba_segments s

where l.owner = s.owner

and l.segment_name = s.segment_name

order by s.bytes desc

) where rownum < 11;

Just a quick code snippit. I do a lot of data pumps to move schemas between different databases; for example taking a copy of a schema to an internal database to try to reproduce a problem. Some of these schemas have some very large tables. The large tables aren’t always needed to research a particular problem.

Here’s a quick bit of SQL to list the 20 largest tables by total size – including space used by indexes and LOBs. A quick search on google didn’t reveal anything similar so I just wrote something up myself. I’m pretty sure this is somewhat efficient; if there’s a better way to do it then let me know! I’m posting here so I can reference it in the future. :)

with segment_rollup as ( select owner, table_name, owner segment_owner, table_name segment_name from dba_tables

union all  

union all  

union all  

and r.owner=upper('&schema_name')  

  RANK         MB TABLE_NAME  

     1      14095 REALLYBIGTABLE_USESLOBS  
     2       6695 VERYBIG_MORELOBS  
     3       5762 VERYLARGE  
     4       5547 BIGBIG_LOTSOFINDEXES  
     5        446 MORE_REASONABLE  
     6        412 REASONABLE_TABLE_2  
     7        377 ANOTHERONE  
     8        296 TABLE1235  
     9        280 ANOTHER_MADEUP_NAME  
    10        141 I_CANT_POST_PROD_NAMES_HERE  
    11         99 SMALLERTABLE  
    12         92 NICESIZETABLE  
    13         89 ILIKETHISTABLE  
    14         62 DATATABLE  
    15         53 NODATATABLE  
    16         48 NOSQLTABLE  
    17         30 HERES_ANOTHER_TABLE  
    18         28 TINYTABLE  
    19         24 ACTUALLY_THERES_400_MORE_TABLES  
    20         19 GLAD_I_DIDNT_LIST_THEM_ALL

And just a quick reminder – the syntax to exclude a table from a data pump schema export is:

expdp ... exclude=SCHEMA_EXPORT/TABLE:[TABNAME],[TABNAME],... Hope this is useful!

Some hints about how to check the actual size of a LOB segment including the free and available space, above and below the HWM, including the commands to free the space above the HWM.

SOLUTION

The size of the LOB segment can be found by querying dba_segments, as follows:

select bytes from dba_segments where segment_name ='<lob segment name>' and owner ='<table owner>';

To get the details of the table to which this LOB segment belong to:

SELECT TABLE_NAME, COLUMN_NAME FROM DBA_LOBS WHERE OWNER = ‘<owner>’ AND SEGMENT_NAME= ‘<lob segment name>’ ;

The first thing to do would be to check the space that is actually allocated to the LOB data.

select sum(dbms_lob.getlength (<lob column name>)) from <table_name>;

Please note that the UNDO data for a LOB segment is kept within the LOB segment space. The above query result is merely the still active LOB data. The rest of allocated data is undo space. The undo space can vary quite a lot, from being very small in size (when LOBs are only inserted) to being very large (when many LOBs are deleted) and is largely depending on the PCTVERSION LOB parameter or the RETENTION parameter.

Hence, The difference between these two is free space and/or undo space. It is not possible to assess the actual empty space using the queries above alone, because of the UNDO segment size, which is virtually impossible to assess. Furthermore, even when there is free space in the LOB, this does not mean this space can be released to the tablespace, it could be under the HWM. To find the freeable space, use the UNUSED_SPACE procedure as shown below.

Check the “free” space within a LOB segment. First, remember that a lob can have 3 states: “inuse”, “deleted” and “free”. There are no statistics for the LOB segment, the DBMS_SPACE package is the only tool that could give an idea about it. As well, there is no view showing the deleted space within the LOB. The deleted space can be converted into free by rebuilding the freelist or by rebuilding the LOB segment itself, but this is not always possible. Note: LOB Partition sizing is not supported in DBMS_SPACE package until 10g. Error ORA-00600 [ktsircinfo_num1] will be generated if the procedure is run against the lob partition on versions below 10g

P.S: When using dbms_lob.getlength, the output is in characters for CLOBs and NCLOBs, and in bytes for BLOBS and BFILES.

One can get an idea about how much space is actually used and what could be deallocated as follows: 1. Determine the unused space within the LOB segment, above the HWM. using the UNUSED_SPACE procedure.

set serveroutput on

declare TOTAL_BLOCKS number; TOTAL_BYTES number; UNUSED_BLOCKS number; UNUSED_BYTES number; LAST_USED_EXTENT_FILE_ID number; LAST_USED_EXTENT_BLOCK_ID number; LAST_USED_BLOCK number;

begin dbms_space.unused_space(‘<owner>’,'<lob segment name>’,’LOB’, TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS, UNUSED_BYTES, LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID, LAST_USED_BLOCK);

dbms_output.put_line(‘SEGMENT_NAME = <LOB SEGMENT NAME>’); dbms_output.put_line(‘———————————–‘); dbms_output.put_line(‘TOTAL_BLOCKS = ‘||TOTAL_BLOCKS); dbms_output.put_line(‘TOTAL_BYTES = ‘||TOTAL_BYTES); dbms_output.put_line(‘UNUSED_BLOCKS = ‘||UNUSED_BLOCKS); dbms_output.put_line(‘UNUSED BYTES = ‘||UNUSED_BYTES); dbms_output.put_line(‘LAST_USED_EXTENT_FILE_ID = ‘||LAST_USED_EXTENT_FILE_ID); dbms_output.put_line(‘LAST_USED_EXTENT_BLOCK_ID = ‘||LAST_USED_EXTENT_BLOCK_ID); dbms_output.put_line(‘LAST_USED_BLOCK = ‘||LAST_USED_BLOCK);

end; /

This is the only space that could be deallocated using the ALTER TABLE … DEALLOCATE UNUSED command as seen below.

2.1. For tablespaces that are using free lists, the following procedures could be used to find the blocks that are on the freelists (which is not extremely useful when wanting to free up some space, as these blocks are just filled below the PCTUSED. Nevertheless, this shows there is some free space within the LOB.

2.1.1. for one freelist group:

declare free_blocks number; begin DBMS_SPACE.FREE_BLOCKS('<owner>', '<lob segment name>', 'LOB',0 ,free_blocks); dbms_output.put_line('Nb of free blocks = '||free_blocks); end; /

2.1.2. for multiple freelist groups:

variable free_blks number; declare i int; declare v_freelist_groups:=100; --- replace this with the actual number begin FOR i IN 0..v_freelist_groups-1 LOOP DBMS_SPACE.FREE_BLOCKS('<owner>','<lob segment name>','LOB', i, :free_blks); dbms_output.put_line('instance#: ' || i); dbms_output.put_line('free_blks: ' || :free_blks); dbms_output.put_line(''); END LOOP;

the above procedures would not work for ASSM tablespaces, because the free_blocks procedure does not work with them.

2.2 SPACE_USAGE procedure could be used for ASSM segments instead:

declare v_unformatted_blocks number; v_unformatted_bytes number; v_fs1_blocks number; v_fs1_bytes number; v_fs2_blocks number; v_fs2_bytes number; v_fs3_blocks number; v_fs3_bytes number; v_fs4_blocks number; v_fs4_bytes number; v_full_blocks number; v_full_bytes number; begin dbms_space.space_usage ('<owner>', '<lob segment name>', 'LOB', v_unformatted_blocks, v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes, v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes); dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks); dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks); dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks); dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks); dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks); dbms_output.put_line('Full Blocks = '||v_full_blocks); end; /

Determine ASSM tablespaces or ASSM residing LOBs with:

select tablespace_name,EXTENT_MANAGEMENT,allocation_type,segment_space_management from dba_tablespaces where segment_space_management=’AUTO’ and tablespace_name NOT LIKE ‘%UNDO%’ and tablespace_name = ‘<name of tablespace>’ /

–Find LOBs that reside in ASSM tablespaces:

col Table format a24 col Tablespace format a22 col partitioned format a11 col column_name format a24

select column_name “Column”,table_name “Table”,tablespace_name “Tablespace”,partitioned from DBA_LOBS where tablespace_name in (select tablespace_name from DBA_TABLESPACES where segment_space_management=’AUTO’ and tablespace_name NOT LIKE ‘%UNDO%’ and owner NOT IN (‘SYS’,’SYSTEM’,’CTXSYS’,’MDSYS’,’ORDSYS’,’DBSNMP’, ‘SYSMAN’,’XDB’,’MDSYS’,’ORDSYS’,’EXFSYS’,’DMSYS’,’WMSYS’)) order by tablespace_name /

3. The command used to deallocate the lob free space is:

alter table <table name> modify lob (<lob column name>) (deallocate unused);

This is not very useful in most circumstances. There is probably very little space above the high water mark. On top of this, the deleted space from inside the the lob segment is not even shown by the procedures above. This is the expected behaviour and, unfortunately, currently there is no procedure/view to show the deleted space. Having such an option is the current subject of an enhancement request.

However, the deleted space can be turned into free space and, when this happens, the procedure in 2.2 would show this free space. To turn the deleted space into free space, one has to rebuild the freepools. The command used to do this is:

alter table <table name> modify lob(<lob column name>) (freepools <number of free pools>);

The free pools number can be taken from the dba_lobs view. When this value is null, the command can be run with a freepools number of 1. This procedure will not release the free space to the tablespace.

If one wants to release the space, – for versions below 10.2, one has to rebuild the LOB segment using the MOVE command:

alter table <table name> move lob (<lob column name>) store as (tablespace <tablespace name>);

– 10.2 introduced an even better option, it extended the SHRINK SPACE command to LOBs. As such, one can remove the deleted and free space altogether from the LOB segment and LOB index:

How to get top 10 rows in Oracle?

Get the top-N rows per group.

Statement 1. Get the 10 most recent orders select * from co.orders order by order_datetime desc fetch first 10 rows only. ... .

Statement 2. Add a row number for each customer select o.*, row_number () over ( partition by customer_id order by order_datetime desc ) rn from co.orders o. ... .

Statement 3..

How to find top 10 tables in Oracle?

How to Find the Largest Tables in your Database by Row Count?.

Select the root of the tables in the treeview to make this feature available..

Click on 'Table Row Count' in the navigation bar. ... .

To limit the number of tables displayed, click the 'wrench' icon to open the macro options window..

How to check the LOB size in Oracle?

The first thing to do would be to check the space that is actually allocated to the LOB data. select sum(dbms_lob. getlength (<lob column name>)) from <table_name>; Please note that the UNDO data for a LOB segment is kept within the LOB segment space.

What is the index of a LOB in Oracle?

The contents of a LOB are often specific to the application, so an index on the LOB column will usually deal with application logic. You can create a function-based or a domain index on a LOB column to improve the performance of queries accessing data stored in LOB columns.