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 126.96.36.199 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) Unused, ((x.bytes-sum(y.bytes))/ 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), x.file_name, x.tablespace_name, x.bytes, x.status order by 1,2,3;
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.