Error In Computing Free Space For File Oracle
Error:24: Too many open files Recently this error appeared in forms while compiling flexfields:While compiling the flexfields, the procedure wf_event.raise failed with following parameters:event_name = oracle.apps.fnd.flex.dff.compiledevent_key = ONT.OE_HEADER_ATTRIBUTESThe error raised is:ORA-00604: error occurred at recursive SQL level 1ORA-01116: error in opening database file 959ORA-01110: data file 959: $ORACLE_HOME/data/exampledbf.dbfORA-27041: unable to open fileSVR4 Error: 24: Too many open filesAdditional information: 3This error occurs when the open file descriptors is set low. We can check it with this command:ulimit -n256256 is the default value if nothing is set specifically in the kernel parameters.To increase the value without rebooting you should do the followingShutdown all app services and DB services.Login as rootulimit -n 8192su - applmgrStart all app servicesexitsu - oracleStart all DB servicesexitThe following script measures open files at the OS-level :lsof | perl -lane '$x{"$F[0]:$F[1]"}++;END { print "$x{$_}\t$_" for sort {$x{$a}<=>$x{$b}} keys %x}'Make sure lsof is in your PATHPerl can be used from opatch installation.I am revisiting this article today on August 6, 2009 where this was reported again. We have since moved to Solaris 10 where lsof doesn't work the same way it did in Solaris 8. However this time it was not due to a low ulimit. Solaris 10 employs the mechanism of /etc/project file to control resource allocation based on user and group. You can verify if an entry for your oracle user exists correctly on your box by typing this command:projects -l# projects -lsystem projid : 0 comment: "" users : (none) groups : (none) attribs:user.root projid : 1 comment: "" users : (none) groups : (none) attribs:noproject projid : 2 comment: "" users : (none) groups : (none) attribs:default projid : 3 comment: "" users : (none) groups : (none) attribs:group.staff projid : 10 comment: "" users : (none) groups : (none) attribs:user.oracle projid : 100 comment: "Oracle Database" users : oracle groups : (none) attribs: process.max-file-descriptor=(basic,2048,deny) project.max-sem-ids=(priv,4098,deny) project.max-shm-ids=(priv,2048,deny) project.max-shm-memory=(priv,51539607552,deny)The error was coming again while compiling flexfields. I checked the project file and found that the entry read like this:user.oracle11i projid : 1
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books Oracle Scripts Ion Excel-DB Don Burleson Blog
Computing Oracle percent (%) used for tablespaces Oracle Database Tips by Burleson Consulting Question: I hear that in Oracle10g it is no http://oracleappstechnology.blogspot.com/2007/10/ora-27041-svr4-error24-too-many-open.html longer necessary to compute the % usage for tablespaces and datafiles. How do you get the percent used in Oracle 10g? Answer: Oracle provides numerous DBA views to show the percent used for Oracle objects: gv$filespace_usage RAC view v$filespace_usage view dba_tablespace_usage_metrics view dba_hist_tbspc_space_usage AWR table Inside dba_tablespace_usage_metrics http://www.dba-oracle.com/t_percent_used_tablespaces.htm The most useful for computing the percent used for tablespaces ids the dba_tablespace_usage_metrics view, which has pre-computed used_space and used_percent columns: Column Name Unit Description TABLESPACE_NAME String Name of the PERMANENT, TEMPORARY, or UNDO tablespace USED_SPACE Blocks Used space, in blocks TABLESPACE_SIZE Blocks Total data file space, in blocks USED_PERCENT Percentage USED_SPACE / TABLESPACE_SIZE * 100 A query to display tablespaces with greater than 90 percent usage might look something like this: select tablespace_name, used_percent from dba_tablespace_usage_metrics where used_percent > 90; For computing space usage over time, you cannot beat the dba_hist_tbspc_space_usage table. This AWR table allows you to compute a historical trend for tablespace growth, and use the data in a linear regression to predict when your tablespaces will extend. For more details on doing time-series analysis of space requirements, see my book "Oracle Tuning: The Definitive Reference". In addition to