dbms_space.free_space[17]

questions  august 30, 2004
reviewer:  a reader

1. why do you have the 'exception when others then null' in your code above? you 
are very much against hiding exceptions, arent you?

2. i understand the authid current_user part so that it can be installed once 
per database and each calling user doesnt get any extra privileges due to it 
being definer's right.

but how does authid current_user  address "this procedure uses authid current 
user so it can query dba_* views using privileges from a role"

3. "-- 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"

instead, why not simply use the all_* views so that the execute immediate is not 
needed at all? every user calling this proc automatically sees what they are 
supposed to see i.e. the all_* views do all the heavy lifting instead of us?

4. what benefit is pipelined giving us in the 9ir2 version of this utility?

5. why is the pragma autonomous_transaction needed?

thanks 

1) because here i have a simple report, rather then blow up part way thru on an 
object that cannot be "free space reported" on i skip it.

2) 

    l_sql := 'select owner, segment_name, segment_type, partition_name
                from dba_segments ';

    add_predicate( 'segment_name', p_segname );
    add_predicate( 'owner', p_owner );
    add_predicate( 'segment_type', p_type );
    add_predicate( 'partition', p_partition );

    execute immediate 'alter session set cursor_sharing=force';
    open l_cursor for l_sql;
    execute immediate 'alter session set cursor_sharing=exact';

i used dynamic sql -- so the creator of the procedure need not have dba_* 
access, just the invoker of the procedure.  and they can use the privs from a 
role since roles can be in place during the execution of an invokers rights 
routine.

3) because this was written to be a dba_ utility?  dba_ views have no filters on 
them.

4) more efficient, search for pipelined for many examples

5) it isn't, don't know what i was thinking leaving it in. 

free space (table space)  august 31, 2004
reviewer:  nazmul hoque  from dhaka - bangladesh

** do you have a dba you can work with?  
i am the programmer and dba both !!!


**** you control the size of temp, that is entirely up to you.  if you don't 
like temp being so large, drop it and recreate it.

please advise me how i drop and rrcreate, temp is created by the stystem, if i 
dtop that, is it create any problem ?

*** search this site for maxshrink, it'll tell you if you can shrink your 
users01.dbf file.
please write with details.....

*** it is normal for a database to consume space.  you have indexes, you have 
free space in there.  3gig is tiny. 

in my server now only 400 mb is free on that drive.

please help me.... 

thanks
nazmul hoque 

dba and programmer both is going to be a problem.  this is straight forward dba 
stuff really.  

to downsize your temp, create new:

ops$tkyte@ora9ir2> create temporary tablespace temp tempfile size 100m;
tablespace created.

and create new
 
ops$tkyte@ora9ir2> drop tablespace temp2;
tablespace dropped.
 

the details on maxshrink are as follows:

search this site for maxshrink, read the first couple of pages you find.  


perhaps you need "more disk".   

define  september 02, 2004
reviewer:  abc

tom

please provide single line definitio of hwm 

high water mark -- the biggest the table ever was, hwm's are pushed "up" if you 
fill up a table with 1,000 blocks of data and then delete all of the data -- 
there will still be 1,000 blocks of "data" there that would be full scanned. 

temp tablespace create and drop  september 02, 2004
reviewer:  nazmul hoque  from bangladesh, dhaka

dba and programmer both is going to be a problem.  this is straight forward dba 
stuff really.  
-- no other option

to downsize your temp, create new:

ops$tkyte@ora9ir2> create temporary tablespace temp tempfile size 100m;
tablespace created.
and create new
 
--- getting below message :
sql> create temporary tablespace temp tempfile size 100m;
create temporary tablespace temp tempfile size 100m
                                          *
error at line 1:
ora-02236: invalid file name

**** please help me ****

ops$tkyte@ora9ir2> drop tablespace temp2;
tablespace dropped.
 

the details on maxshrink are as follows:

search this site for maxshrink, read the first couple of pages you find.  


perhaps you need "more disk".   
*** we are using server compaq proliant 1600, now a days it so difficault to add 
hdd at my end.

**** dear tom,
i know how create table space and drop tabel space from sql and from dba studio 
both, my problem is temp tablespace, i never create temp tablespace or drop 
before. so far i have 
seen it is created by the system at time of installation. if i drop and recreate 
temp with 100 mb space my problem will be solved, please give some more trips to 
drop and creat temp tablespace. please please please..

thanks
nazmul hoque 

you'll need to refer to your sql reference, i cannot (will not) be your dba.

you are using 817, you didn't have oracle managed files way back then.  you need 
to name your tempfile.  the sql reference (available on otn.oracle.com) will 
give you the syntax.