Archive for the ‘Oracle’ Category

« Older Entries

ORA-00059: Maximum Number Of DB_FILES Exceeded

December 8th, 2025, posted in Oracle Queries
Share

ORA-00059: Maximum Number Of DB_FILES Exceeded in 19C database

When I am adding datafile to my 19C database facing the below error.

SQL> alter tablespace DATA  add datafile '/u01/data/data15.dbf' size 20G;
alter tablespace DATA add datafile '/u01/data/data15.dbf' size 20G
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded

 SOLUTION :

Check for DB_FILES value :

SQL> sho parameter db_files;
 NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_files                             integer    500

Check Value for Total number of datafiles in Database :

 SQL> select count(*) from dba_data_files;
COUNT(*)
----------
        496

Set the db_files parameter to a new high-value :

SQL> alter system set db_files = 1000 scope = spfile;
System altered.

Restart your database :

SQL>shutdown immediate;
SQL>startup
SQL> sho parameter db_files;
 NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_files                             integer            1000
SQL> alter tablespace DATA  add datafile '/u01/data/data15.dbf' size 20G;

Issue resolved !!

Share
read more no comments

Oracle Database 23AI Session Related Scripts.

December 1st, 2025, posted in Oracle Queries
Share

Columns Name Details

sid – session identifier
serial# – session serial number
osuser – operating system client user name
machine – operating system machine name
program – operating system program name
module – name of the currently executing module as set by calling the DBMS_APPLICATION_INFO.SET_MODULE procedure

——————————

select * from v$session;

——————————

select SID,SERIAL#,USERNAME,STATUS,SCHEMANAME,OSUSER,MACHINE from v$session where USERNAME=’SYSTEM’;

—————

select SID,SERIAL#,USERNAME,STATUS,SCHEMANAME,OSUSER,MACHINE from v$session where STATUS=’ACTIVE’;

———————

select sid,serial#,osuser,machine,program,module from v$session ;

———————–

Note : Sometimes need to find session details. 
You can change the last line to search for sessions that are running specific queries. 
Replace ALTER TABLE%SHRINK% with another command that you want to search.

SELECT SES.SID,SES.SERIAL# SER#,SES.PROCESS OS_ID,SES.STATUS,SQL.SQL_FULLTEXT
FROM 
   V$SESSION SES,V$SQL SQL,V$PROCESS PRC
WHERE
   SES.SQL_ID=SQL.SQL_ID AND SES.SQL_HASH_VALUE=SQL.HASH_VALUE AND SES.PADDR=PRC.ADDR AND UPPER(SQL.SQL_FULLTEXT) LIKE UPPER(‘ALTER TABLE%SHRINK%’);

   ————————–

How to find sql text and session information history during a week.

select a.SAMPLE_TIME, a.SQL_OPNAME, a.SQL_EXEC_START, a.program, a.client_id, b.SQL_TEXT
from DBA_HIST_ACTIVE_SESS_HISTORY a, dba_hist_sqltext b –v$sqltext b
where a.SQL_ID = b.SQL_ID order by a.SQL_EXEC_START asc;

Share
read more no comments

Oracle EBS R12.1.1 File Locations

November 16th, 2025, posted in Oracle EBS Application
Share

Autoconfig log file :

Apps:

$INST_TOP/apps/$CONTEXT_NAME/admin/log/$MMDDHHMM/adconfig.log.

Database:

$ORACLE_HOME/appsutil/log/$CONTEXT_NAME//adconfig.log $ORACLE_HOME/appsutil/log/$CONTEXT_NAME//NetServiceHandler.log

Startup/Shutdown Log files:

$INST_TOP/logs/appl/admin/log

Apache, OC4J and OPMN:

$LOG_HOME/ora/10.1.3/Apache $LOG_HOME/ora/10.1.3/j2ee $LOG_HOME/ora/10.1.3/opmn

Patch log:

$APPL_TOP/admin/$SID/log/

Concurrent log:

$INST_TOP/apps/$CONTEXT_NAME/logs/appl/conc/log

Clone log :
Preclone log files in source instance 

Apps:

$INST_TOP/apps/$CONTEXT_NAME/admin/log/ (StageAppsTier_MMDDHHMM.log) 

Database:

$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/(StageDBTier_MMDDHHMM.log) 

Clone log files in target instance :

Apps :

$INST_TOP/apps/$CONTEXT_NAME/admin/log/ApplyAppsTier_.log 

Database:

$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ApplyDBTier_.log

Alert Log File:

$ORACLE_HOME/admin/$CONTEXT_NAME/bdump/alert_$SID.log

APPL_TOP

[applmgr@gayatri10 ~]$ cd /oracle/PROD/apps/apps_st/appl

INST_TOP

[applmgr@gayatri10 ~]$ cd /oracle/PROD/inst/apps/context_name

COMMAN_TOP

[applmgr@gayatri10 ~]$ cd /oracle/PROD/apps/apps_st/comn

JAVA_TOP  

[applmgr@gayatri10 ~]$ cd /oracle/PROD/apps/apps_st/comn/java

Database_top  

[applmgr@gayatri10 ~]$ cd /oracle/PROD/db/tech_st/10.2.0/appsutil/scripts/PROD_gayatri10/ 

  
Context file:-(.xml)

[applmgr@gayatri10 ~]$ cd /oracle/PROD/inst/apps/PROD_gayatri10/appl/admin

Defaults file location:-(.txt)

[applmgr@gayatri10 ~]$ cd  /oracle/PROD/apps/app_st/appl/admin/PROD/adalldefault.txt
[applmgr@gayatri10 ~]$ cd APPL_TOP/admin/<SID>  


Autoconfig log file :

[applmgr@gayatri10 ~]$ cd /oracle/PROD/inst/apps/PROD_gayatri10/appl/admin/log

Share
read more no comments

Set The Schema Session Limit In Oracle 23 AI Database

November 9th, 2025, posted in Oracle Queries
Share

In Oracle, to set a session limit per schema (user) – i.e., control how many concurrent sessions a specific user can have. You use profiles with the SESSIONS_PER_USER parameter.

Step-by-Step Guide to Set Session Limit per Schema.

1. Create a Profile with a Session Limit

If the profile doesn’t exist already, create one :

SQL > CREATE PROFILE limited_sessions_profile LIMIT SESSIONS_PER_USER 40;

This limits users assigned to this profile to 40 concurrent sessions.

2. Assign the Profile to a User (Schema)

SQL > ALTER USER target_user PROFILE limited_sessions_profile;

Replace target_user with the username (schema) you want to restrict. 

3. Check the Profile Assignment and Limit

To verify the profile assigned to the user :

SQL > SELECT username, profile FROM dba_users WHERE username = ‘TARGET_USER’;

4. To check session limits in the profile:

SQL > SELECT * FROM dba_profiles WHERE profile = ‘LIMITED_SESSIONS_PROFILE’ AND resource_name = ‘SESSIONS_PER_USER’;

5. Optional: Modify an Existing Profile

If the user already has a profile, and you want to add a session limit to it:

SQL > ALTER PROFILE existing_profile LIMIT SESSIONS_PER_USER 3;

Important Notes :

This setting does not immediately terminate sessions if they’re over the limit. It prevents new logins once the session count limit is reached. You can check current session counts with :

SQL > SELECT username,COUNT(*) FROM v$session WHERE username IS NOT NULL GROUP BY username;

Share
read more no comments

ORA-06512 : at “SYS.DBMS_ISCHED”

November 2nd, 2025, posted in Oracle Queries
Share

Database – Oracle Database 23AI

Cause :
An attempt was made to perform a scheduler operation without the required privileges.

Action :
Ask a sufficiently privileged user to perform the requested operation, or grant the required privileges to the proper user(s).

ERROR :

BEGIN
    DBMS_SCHEDULER.create_job (
        job_name        => ‘REFRESH’,
        job_type        => ‘PLSQL_BLOCK’,
        job_action      => ‘BEGIN DBMS_RESH(”QA”, ”F”); END;’,
        start_date      => SYSTIMESTAMP,
        repeat_interval => ‘FREQ=SECONDLY; INTERVAL=30’,
        enabled         => TRUE
    );
END;
Error report –
ORA-27486: insufficient privileges
ORA-06512: at “SYS.DBMS_ISCHED”, line 191
ORA-06512: at “SYS.DBMS_SCHEDULER”, line 332
ORA-06512: at line 2
27486. 00000 –  “insufficient privileges”

*Cause:    An attempt was made to perform a scheduler operation without the required privileges.
*Action:   Ask a sufficiently privileged user to perform the requested operation, or grant the required privileges to the proper user(s).

Solution :

This error is related to the user privileges.  Grant the privileges for the related user as follows.

[oracle@ora23ai-db ~]$ sqlplus sys/*****@SIT as sysdba
SQL*Plus: Release 23.0.0.0.0 – Production on Thu Mar 13 13:40:06 2025
Version 23.4.0.24.05
Copyright (c) 1982, 2024, Oracle.  All rights reserved.
Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 – Production
Version 23.4.0.24.05
SQL> grant execute on DBMS_SCHEDULER to SCHEMA_NAME;
Grant succeeded.
SQL> grant create job to SCHEMA_NAME;
Grant succeeded.

Share
read more no comments
« Older Entries