read 

http://asktom.oracle.com/Misc/RolesAndProcedures.html

roles are not enabled in the stored procedure -- they are in the anonymous block.  your user has the ability to see the table via a role, not a direct grant.  Use authid current_user to make this a better general purpose solution.

We try not to bypass security in any case, so yes, the owner of the procedure does need access (it is not a simple dictionary read)

ops$tkyte@ORA920.US.ORACLE.COM> create table t ( x int ) storage (initial 200k);

Table created.

ops$tkyte@ORA920.US.ORACLE.COM> select count(*) from user_extents where
  2  segment_name = 'T';

  COUNT(*)
----------
         4

ops$tkyte@ORA920.US.ORACLE.COM> alter table t deallocate unused;

Table altered.

ops$tkyte@ORA920.US.ORACLE.COM> select count(*) from user_extents where
  2  segment_name = 'T';

  COUNT(*)
----------
         4
 

In a DMT, perhaps:

ops$tkyte@ORA817DEV> create table t ( x int ) tablespace system storage ( initial 10m );
 
Table created.
 
ops$tkyte@ORA817DEV> select blocks from dba_extents where segment_name = 'T' and owner = user;
 
    BLOCKS
----------
      1280
 
ops$tkyte@ORA817DEV> alter table t deallocate unused keep 0k;
 
Table altered.
 
ops$tkyte@ORA817DEV> select blocks from dba_extents where segment_name = 'T' and owner = user;
 
    BLOCKS
----------
         1
 
ops$tkyte@ORA817DEV>


In an LMT, you will be constrained by the uniform or system allocated extent size as all extents are the same or system managed size. 

it lets you do "invoker rights" instead of "definer rights" in the stored procedure.


</code> http://asktom.oracle.com/pls/ask/search?p_string=%22invoker+rights%22+%22definer+rights%22 <code>

no, it does not.

1) yes
2) it is entirely possible that you export and import and end up taking MORE space (as you reintroduce pctfree to all segments). It is possible that:
a) you will take MORE space
b) you will take LESS space
c) you will take SAME space

3) have you run maxshrink (search site for this), free space back right now
other than that, look at "alter table T move new_tablespace (using LMT!!! system allocated extents!!!) and alter I rebuild new_tablespace

and dropping old datafile, DO NOT USE EXPORT FOR THIS, PLEASE DON'T

you don't want the old storage, you want to WIPE OUT the storage parameters (pctincrease and the like are only meaningful during the first allocation in a locally managed tablespace - ignored after that)

alter table t
move tablespace LMT
storage ( initial 1k next 1k pctincrease 0 minextents 1 );


just use that.

the alter table T move will

a) try to use any existing free space in the tablespace to copy the existing data (the existing data will exist until the entire segment is copied)

b) extend the tablespace by growing the datafiles if it runs out of space

hard to say for definite as we don't know the entire history of this index, but...

these are all guesses

o you are rebuilding the index and reintroducing the original pctfree of 10% (by default). You are taking blocks that did 60/40 splits (so one is 40% free and the other is 60% free) and squishing them down

o you did parallel operations, they each allocate their own extents, there was lots of extra left over in each extent. when you rebuilt, you did not do parallel, hence the left over space was modest

o you over allocated the index space in the first place, then when you rebuilt, you did not.

you should read this one for sure then:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2057813314852#1343453000346173824


q1) what is the best way to do it?

that isn't a question really - are you asking

"what is the best way to reorg"?

If so, most all of the time the answer is:

"by not doing it, you don't need to"


You give almost no information here - all of the useful information is for sure missing.

So, this huge tablespace is 90% full. Why do you believe you need to reorganize anything. What is the goal, what are you HOPING to achieve and WHY do you think a reorganization of the data will accomplish that?


and bear in mind, I am very sure that after you provide that, I will have more questions and then more and then more...

they only question that I can answer definitively is q3)

q3) of course not. one of three things will happen after any change. 1) it will not affect performance at all. 2) things will go faster. 3) things will go slower.

ok, maybe two of these

q2) no, it does not. It might make somethings GROW in size. Yes, you read that right - you could take a segment - reorganize it and discover that it is larger afterwards. Why? Because you might take a very densely packed table or index - which is very full - reorganize it and re-introduce the pctfree - making the segment larger than it used to be.

q3) one thing you might do is search this site for maxshrink and see if you can shrink existing datafiles (if you are on 10g, you might want to purge your recycle bin first! but make sure you know what that means...)


q1) there is no best way

if there were a single best way, we would not have invented or implemented the others.


you would have to go segment by segment in these tablespaces to see the 'percentage of free space' to see if they are candidates for 'shrinking' or not.

You could use your knowledge - why do you think you will get lots of free space back? If you mostly insert and update - you won't. If you insert just a little more than you delete - you will not.

You can either use the segment advisor in Enterprise manager to find segments that might be candidates for a 'reorg'

Or you can use show_space to see how "compact" your segments are:

set define off

create or replace procedure show_space
( p_segname in varchar2,
  p_owner   in varchar2 default user,
  p_type    in varchar2 default 'TABLE',
  p_partition in varchar2 default NULL )
-- this procedure uses authid current user so it can query DBA_*
-- views using privileges from a ROLE and so it can be installed
-- once per database, instead of once per user that wanted to use it
authid current_user
as
    l_free_blks                 number;
    l_total_blocks              number;
    l_total_bytes               number;
    l_unused_blocks             number;
    l_unused_bytes              number;
    l_LastUsedExtFileId         number;
    l_LastUsedExtBlockId        number;
    l_LAST_USED_BLOCK           number;
    l_segment_space_mgmt        varchar2(255);
    l_unformatted_blocks number;
    l_unformatted_bytes number;
    l_fs1_blocks number; l_fs1_bytes number;
    l_fs2_blocks number; l_fs2_bytes number;
    l_fs3_blocks number; l_fs3_bytes number;
    l_fs4_blocks number; l_fs4_bytes number;
    l_full_blocks number; l_full_bytes number;

 -- inline procedure to print out numbers nicely formatted
 -- with a simple label
    procedure p( p_label in varchar2, p_num in number )
    is
    begin
        dbms_output.put_line( rpad(p_label,40,'.') ||
                              to_char(p_num,'999,999,999,999') );
    end;
begin
   -- this query is executed dynamically in order to allow this procedure
   -- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
   -- via a role as is customary.
   -- NOTE: at runtime, the invoker MUST have access to these two
   -- views!
   -- this query determines if the object is a ASSM object or not
   begin
      execute immediate 
          'select ts.segment_space_management
             from dba_segments seg, dba_tablespaces ts
            where seg.segment_name      = :p_segname
              and (:p_partition is null or 
                  seg.partition_name = :p_partition)
              and seg.owner = :p_owner
              and seg.tablespace_name = ts.tablespace_name'
             into l_segment_space_mgmt
            using p_segname, p_partition, p_partition, p_owner;
   exception
       when too_many_rows then
          dbms_output.put_line
          ( 'This must be a partitioned table, use p_partition => ');
          return;
   end;


   -- if the object is in an ASSM tablespace, we must use this API
   -- call to get space information, else we use the FREE_BLOCKS
   -- API for the user managed segments
   if l_segment_space_mgmt = 'AUTO'
   then
     dbms_space.space_usage 
     ( p_owner, p_segname, p_type, l_unformatted_blocks,
       l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,
       l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,
       l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);
     
     p( 'Unformatted Blocks ', l_unformatted_blocks );
     p( 'FS1 Blocks (0-25)  ', l_fs1_blocks );
     p( 'FS2 Blocks (25-50) ', l_fs2_blocks );
     p( 'FS3 Blocks (50-75) ', l_fs3_blocks );
     p( 'FS4 Blocks (75-100)', l_fs4_blocks );
     p( 'Full Blocks        ', l_full_blocks );
  else
     dbms_space.free_blocks(
       segment_owner     => p_owner,
       segment_name      => p_segname,
       segment_type      => p_type,
       freelist_group_id => 0,
       free_blks         => l_free_blks);
     
     p( 'Free Blocks', l_free_blks );
  end if;

  -- and then the unused space API call to get the rest of the 
  -- information
  dbms_space.unused_space
  ( segment_owner     => p_owner,
    segment_name      => p_segname,
    segment_type      => p_type,
    partition_name    => p_partition,
    total_blocks      => l_total_blocks,
    total_bytes       => l_total_bytes,
    unused_blocks     => l_unused_blocks,
    unused_bytes      => l_unused_bytes,
    LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
    LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
    LAST_USED_BLOCK => l_LAST_USED_BLOCK );

    p( 'Total Blocks', l_total_blocks );
    p( 'Total Bytes', l_total_bytes );
    p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
    p( 'Unused Blocks', l_unused_blocks );
    p( 'Unused Bytes', l_unused_bytes );
    p( 'Last Used Ext FileId', l_LastUsedExtFileId );
    p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
    p( 'Last Used Block', l_LAST_USED_BLOCK );
end;
/
set define on

now if the goal is to ultimately shrink datafiles - there is no 'best way' to do that in place.

If you used alter table t shrink space/shrink space compact - you'll reorganize the tables, but you'll not free up space at the end of the datafile, rather you'll take a datafile that looked like this (each 'word' is an extent in the following string - from the first extent of a datafile to the last)


t1,t1,t2,t3,t2,t3,t1,t4,t4,t4,t5,t6,f,f,f

so, that file has extents for tables T1-T6 and three free extents at the end (assuming locally managed uniform for ease of example) If you resized the file, you could make it three extents smaller. Now, lets assume you shrank each table - and each table fit in one extent, then:

t1,f,t2,t3,f,f,f,t4,f,f,t5,t6,f,f,f

You now have nine free extents in the file, but if you shrank the file, you would only still get three - since t6 is "at the end"

So, what you would need to do to reclaim "as much as you can" would be to MOVE the segements to a new, empty tablespace - that would pack them densely and allow you to DROP the old tablespace.


The odds that this exercise is even worth considering (the entire "lets get disk space back") are so so so so very close to 0% that I would strongly encourage you to find something else to do :)

Think about it - for how many hours or days would this space be 'reclaimed' and freed up before the database just wanted it back again.

q1) alter table T move tablespace <xxxx>; alter index I rebuild tablespace <xxxx>;

q2) what is a 'custom' schema and what is a 'standard' schema

q3) no, if the tablespace names need to be the same after as before, you can alter the tablespace and rename it.

q4) it could be more or less or the same. You'd probably want to gather statistics and then you can look at them - it'll tell you how "dense" things are. Or you can use dbms_space - to review the free space in an object - procedurally

... As we can see from the above output,there are only 5 blocks on free list,so not
much space can be released,hence not beneficial if we do reorg for this table
in terms of releasing space. ...

well, my script only shows the FIRST free list, if you have multiple free lists, you'll want to tweak that code - but yes, if only five blocks were on the free lists



and you know what - what if this table said "I have 100,000 blocks on the freelist".

would that be a reason to reorg?

*probably not* - why not?

table is big
reorg will consume many resources
and the free space will probably be reused by the segment itself (meaning, you do not permanently reclaim space)

You should incorporate into your logic you KNOWLEDGE of how the tables are used, without that - you'll end up doing tons of work for nothing.



you have the code, what is on line 44 of the code you are using? For my personal copy it is:

 44        execute immediate
 45            'select ts.segment_space_management
 46               from dba_segments seg, dba_tablespaces ts
 47              where seg.segment_name      = :p_segname
 48                and (:p_partition is null or
 49                    seg.partition_name = :p_partition)
 50                and seg.owner = :p_owner
 51                and seg.tablespace_name = ts.tablespace_name'
 52               into l_segment_space_mgmt
 53              using p_segname, p_partition, p_partition, p_owner;
 54     exception
 55         when too_many_rows then
 56            dbms_output.put_line
 57            ( 'This must be a partitioned table, use p_partition => ');
 58            return;
 59     end;

if that is getting "no data found", then the object information you passed in was wrong.

query the dictionary - user_segments has a column "freelist.


You have the source code to show_space, you see the calls, you have the documentation, you have the ability to read the documentation and see what the calls I'm making do - therefore, you have everything you need to do what you want.

     dbms_space.free_blocks(
       segment_owner     => p_owner,
       segment_name      => p_segname,
       segment_type      => p_type,
       freelist_group_id => 0,
       free_blks         => l_free_blks);

that would be the API you'd be interested in, see the freelist_group_id?


in fact, read about dbms_space if you have access to AWR and such (separately licensed packs from enterprise manager), there is a API in there that pretty much does this

1) this is a hugely complex question.

In general:

a) if you index access this, none really - a reorg would do nothing - IN GENERAL. The reason - you read an index to get a rowid, you take the rowid and read that file/block. It matters not really where that file/block are.

b) if you full scan this, and given that you said "we would not make the segment smaller", a reorg would not make the full scan faster.

(close your eyes, envision the MECHANICS - what has to happen - then you can many times answer these sorts of questions yourself...)


Now, to make the answer more complex.....

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8764517459743#737753700346729628

there a reorg of the disorganized table, using dbms_redefinition with the "order_by" parameter to sort the table by object_name into the organized table - would help THAT particular query (where object_name like ?). If you place data that is queried together on as few blocks as possible, you'll decrease the work we need to perform to retrieve that data.

However, remember - if you sort the table by OBJECT_NAME, you have probably "un-sorted" the table by CREATE_DATE. A table can be sorted one way really - so a reorg to help Peter might seriously and utterly damage Paul.



2) it completely depends. How fast is your IO, are there other users contending for this IO. How are you set for CPU. Can you use parallel? Is nologging an option.

And then - once you figure out all of the parameters - you benchmark. That is the only way I know to do it.

you probably do not want uniform sizes

https://www.oracle.com/technetwork/issue-archive/2007/07-may/o37asktom-101781.html

you probably want system allocated extents and the power of extent trimming

and please do not loose sleep over having lots of extents - it is really "ok"

why is this perceived to be a problem to you?

your inserts will use this space the next time you insert.

3,000 is just a number, it means nothing without context. If your table is 3,010 blocks - 3,000 is big. If your table is 300,000 blocks, 3,000 is tiny.

And do you actually full scan this table frequently?


and since you are using ASSM (automatic segment space management) I would not consider alter table t move at all - that would require index rebuilds and would be offline. alter table t shrink space compact; alter table t shrink space - maybe that, but only if there was a ACTUAL PROBLEM that needed to be solved.

... what will be the best way to
find out that the blocks get used by inserts, ...

you know how big the table is today.
come back next month, next year, whatever.
is the table noticeable larger?
if not, it must be using the space.

but you still haven't even told us if 3,000 is

a) teeny tiny, noise, nothing to think about
b) big, huge, 90% of the existing table size

well, say they are about 20% full - so, you'll turn 4/5 blocks into one (say four into one). So, you'll go from 10k + 3k to 10k + 3k/4 or 10,750 blocks.

You'll be about 80% of original size.

So, you would

a) measure before shrink
b) gather information about the queries that full scan this table (number of IOs, CPU used)

c) shrink

d) measure after shrink (to verify)
e) gather information about the queries that full scan (to verify they do significantly less work)

f) repeat d and e for days on end to see if how OFTEN you want to do (c) - if at all (d & e might show to you "not worth it", or they might show to you "it goes right back where it was after 12 hours, not worth it)

a delete will never give space back for use for other segments. A delete simply frees up space in a segment for further reuse in that segment.


If you just deleted a large percentage of your table

And you want to be able to use that space for another segment (table/index/etc)

THEN - you can consider a reorg

ELSE - you probably do not need to consider a reorg (unless you full scan that segment frequently)



there is no estimate for the time to complete the alter table t shrink space - what you probably want to do is alter table t shrink space COMPACT - that'll be 100% online (no blocking) - so it doesn't really matter how long it takes (it will purely be a function of the size of the table - how long to full scan it - plus the number of rows that actually need to move from the bottom to the top).

After you do the compact, the shrink space command is very fast (very little to do) and will only lock the table for the short duration it needs to move the high water mark.

this will NOT affect the size of an export (data pump or otherwise) by any amount. Export/datapump read rows and write rows to a file. if you have a table that has 1000gb allocated to it - but zero rows - it will consume no space in an export.


And it would not affect the imported table structure either, importing loads rows.


this would only make sense if you were TRANSPORTING the data and you could shrink the datafiles (not just the table)

In other words, it doesn't make sense for you.