Posts Tagged ‘OracleDBA’

« Older Entries

ORA-32001: Write To SPFILE Requested But No SPFILE Is In Use

June 3rd, 2026, posted in Oracle Queries
Share

PROBLEM :

While altering parameter in parameter file got below error :

ALTER SYSTEM SET processes = 800 SCOPE=SPFILE;
ALTER SYSTEM SET processes = 800 SCOPE=SPFILE
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE is in use

SOLUTION :

This error comes, if the database is running with pfile instead of spfile.

1. Check whether DB is running with pfile or spfile:

SQL> show parameter pfile
NAME TYPE VALUE
------ -------- ------------------------------
spfile string

Here value is showing BLANK, Means database is running with pfile. So with pfile , if we are trying to alter any init parameter in database, it will throw error.
To fix it, create an spfile from the pfile and restart the database.

2. Create spfile from pfile ;

create spfile from pfile;

3. Check whether spfile has been created in $ORACLE_HOME/dbs location :

cd $ORACLE_HOME/dbs
ls -ltr spfile*

4. Restart the database :

shutdown immediate;
startup

When we start the database, if both pfile(init$ORACLE_SID.ora) and spfile(spfile$ORACLE_SID.ora) are present at dbs location, Then bydefault spfile will be used for db startup.

5. Check whether spfile is used or not :

show parameter pfile :

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/app/oracle/product/12chome/spfiledbaclass.ora

6. Now run alter statement :

ALTER SYSTEM SET processes = 800 SCOPE=SPFILE
System altered.
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

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

Restore The Drop Table In 19c Oracle Autonomous Data Warehouse

October 12th, 2025, posted in Oracle Queries
Share

In Oracle Autonomous Data Warehouse (ADW) 19c, if you have accidentally dropped a table and want to restore it, there are a few recovery options available depending on how the table was dropped and the time elapsed since then.

STEPS : Flashback – Using the Recycle Bin

1. Check the Recycle Bin for the dropped table

QL > SHOW RECYCLEBIN;
SQL > select * from DBA_RECYCLEBIN where ORIGINAL_NAME='TABLE_NAME';

2. Flashback the table (use the original table name)

SQL > FLASHBACK TABLE your_table_name TO BEFORE DROP;
SQL > FLASHBACK TABLE TABLE_NAME TO BEFORE DROP;

Flashback succeeded.

3. Verify the drop table data.

SQL > select * from TABLE_NAME;

Notes :

Recycle Bin is enabled by default in ADW.
You cannot use RMAN or traditional tablespace restore in ADW – it’s fully managed.
ADW automatically takes backups every 60 minutes and retains them for 60 days; however, point-in-time table-level recovery from these backups is not user-managed.

Share
read more no comments
« Older Entries