Problem solve Get help with specific problems with your technologies, process and projects.

Questions about procedures, packages and functions

Are procedures, packages, functions stored in the system tablespace? How can you tell? How do I prepare a database for thousands of procedures? My current database has 73% of its space in one large free section. Is that good enough? How about 9i, any changes to your answers above?

Procedures, packages, functions are stored in the Data Dictionary therefore they are, by default, stored in the SYSTEM tablespace since that is where the Data Dictionary objects reside. The source code for these objects can be found in dba_source and are owned by the user that created them.

To prepare for lots of procedures, you'll simply need to insure that you have adequate space allocated to the SYSTEM tablespace. This may require that you create a new datafile to "add room". If your datafiles were created to autoextend, you don't really have to do anything except make sure that they don't extend past the max size and if they do, add more space.

I'm not sure how to answer you about the 73% free because it depends on whether or not the 73% free is in the SYSTEM tablespace or not.

The following script will give you how much space is used in each tablespace and how much remains free:

set linesize 110
set pagesize 60

column FileNum	    format 999	    heading 'File|Num'
column FileName     format A40      heading 'File Name' 
column Tablespace   format A8       heading 'Tblspace'
column Allocated    format 99,999    heading 'Size|(MB)' 
column div1         format A3       heading ' | '
column Used         format 99,999    heading 'Used|(MB)'
column Unused       format 99,999    heading 'Unused|(MB)'
column Pct_Used     format 999.99   heading 'Used|(%)'
column div2         format A3       heading ' | ' 
column TotalFree    format 9,999    heading 'Free|(MB)'
column Pct_Free     format 999.99   heading 'Free|(%)'

compute sum of Allocated on tablespace 
compute sum of Allocated on report 
compute sum of Used      on tablespace 
compute sum of Used      on report 
compute sum of TotalFree on tablespace 
compute sum of TotalFree on report 
compute sum of Unused    on tablespace 
compute sum of Unused    on report 

break on Tablespace skip 2

select x.tablespace_name                       Tablespace,
       x.file_name                             FileName,
       x.bytes/(1024*1024)                     Allocated,
       (x.bytes-sum(y.bytes))/(1024*1024)      Used,
       x.bytes/(1024*1024) - (x.bytes-sum(y.bytes))/(1024*1024)      
       x.bytes)*100                            Pct_Used,
       sum(y.bytes)/(1024*1024)                TotalFree,
       (sum(y.bytes)/x.bytes)*100              Pct_Free
from sys.dba_data_files x , 
     sys.dba_free_space  y 
where x.file_id = y.file_id 
group by substr(to_char(x.file_id,999), 1,4),
order by 1,2,3;

For More Information

Dig Deeper on Oracle stored procedures

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.