Sujeet APPS DBA

How to add a datafile to a bigfile tablespace in Oracle 23ai Database.

Syntax for Bigfile Tablespace:

SQL > ALTER TABLESPACE tablespace_name

ADD DATAFILE SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;

However, bigfile tablespaces support only one datafile, but that datafile can be very large (up to 128 TB, depending on the block size). So, if your tablespace is truly a bigfile tablespace, you cannot add another datafile to it.

To check if your tablespace is a bigfile tablespace:

SQL > SELECT TABLESPACE_NAME, BIGFILE

FROM DBA_TABLESPACES

WHERE TABLESPACE_NAME = 'YOUR_TABLESPACE_NAME';

If BIGFILE = YES, it's a bigfile tablespace (only 1 datafile allowed).

If BIGFILE = NO, it's a smallfile tablespace (you can have multiple datafiles).

If it’s a smallfile tablespace and you want to add more datafiles:

SQL > ALTER TABLESPACE your_tablespace_name

ADD DATAFILE '/path/to/your/datafile02.dbf' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;

If your goal is to increase space in a bigfile tablespace, you should resize the existing datafile:

SQL > ALTER DATABASE DATAFILE '/path/to/your/datafile01.dbf' RESIZE 200G;

Or enable autoextend:

SQL > ALTER DATABASE DATAFILE '/path/to/your/datafile01.dbf'

AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

How to restore the drop table in 19c Oracle Autonomous Data Warehouse.

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 1: Flashback - Using the Recycle Bin.

1. Check the Recycle Bin for the dropped table

SQL > 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.

How to set the schema session limit in Oracle 23 AI Database.

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;

ORA-06512: at "SYS.DBMS_ISCHED"

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

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.

Oracle Database 23AI session-related scripts.

Oracle Database 23AI session-related scripts.

##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;

--------------------------

How To Find All Scheduled Requests in EBSR12.2.10

Scheduled Concurrent Program list.

SELECT request_id id,
nvl(meaning, 'UNKNOWN') status,
user_concurrent_program_name pname,
to_char(request_date, 'DD-MON-RR HH24:MI:SS') submitd,
to_char(requested_start_date, 'DD-MON-RR HH24:MI:SS') requestd
FROM fnd_concurrent_requests fcr,
fnd_lookups fl,
fnd_concurrent_programs_vl fcpv
WHERE phase_code = 'P'
AND (fcr.requested_start_date >= sysdate OR
status_code = 'P')
AND LOOKUP_TYPE = 'CP_STATUS_CODE'
AND lookup_code = status_code
AND fcr.concurrent_program_id = fcpv.concurrent_program_id
AND fcr.program_application_id = fcpv.application_id
ORDER BY request_date, request_id;

Total scheduled program count.

select 'Scheduled requests:' schedt, count(*) schedcnt

from fnd_concurrent_requests

WHERE (requested_start_date > sysdate OR

status_code = 'P')

AND phase_code = 'P';

---------

select 'Non-scheduled requests:' schedt, count(*) schedcnt

from fnd_concurrent_requests

WHERE requested_start_date <= sysdate

AND status_code != 'P'

AND phase_code = 'P';

------------

SELECT fcr.request_id ,
  fcpt.user_concurrent_program_name
  || NVL2(fcr.description, ' ('
  || fcr.description
  || ')', NULL) conc_prog ,
  fu.user_name requestor ,
  fu.description requested_by ,
  fu.email_address ,
  frt.responsibility_name requested_by_resp ,
  TRIM(fl.meaning) STATUS ,
  fcr.phase_code ,
  fcr.status_code ,
  fcr.argument_text "PARAMETERS" ,
  TO_CHAR(fcr.request_date, 'DD-MON-YYYY HH24:MI:SS') requested ,
  TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS') requested_start ,
  TO_CHAR((fcr.requested_start_date), 'HH24:MI:SS') start_time ,
  DECODE(fcr.hold_flag, 'Y', 'Yes', 'N', 'No') on_hold ,
  CASE
    WHEN fcr.hold_flag = 'Y'
    THEN SUBSTR( fu.description , 0 , 40 )
  END last_update_by ,
  CASE
    WHEN fcr.hold_flag = 'Y'
    THEN fcr.last_update_date
  END last_update_date ,
  fcr.increment_dates ,
  CASE
    WHEN fcrc.CLASS_INFO IS NULL
    THEN 'Yes: '
      || TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')
    ELSE 'n/a'
  END run_once ,
  CASE
    WHEN fcrc.class_type = 'P'
    THEN 'Repeat every '
      || SUBSTR(fcrc.class_info, 1, instr(fcrc.class_info, ':')           - 1)
      || DECODE(SUBSTR(fcrc.class_info, instr(fcrc.class_info, ':', 1, 1) + 1, 1), 'N', ' minutes', 'M', ' months', 'H', ' hours', 'D', ' days')
      || DECODE(SUBSTR(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1), 'S', ' from the start of the prior run', 'C', ' from the completion of the prior run')
    ELSE 'n/a'
  END set_days_of_week ,
  CASE
    WHEN fcrc.class_type                         = 'S'
    AND instr(SUBSTR(fcrc.class_info, 33),'1',1) > 0
    THEN 'Days of week: '
      || DECODE(SUBSTR(fcrc.class_info, 33, 1), '1', 'Sun, ')
      || DECODE(SUBSTR(fcrc.class_info, 34, 1), '1', 'Mon, ')
      || DECODE(SUBSTR(fcrc.class_info, 35, 1), '1', 'Tue, ')
      || DECODE(SUBSTR(fcrc.class_info, 36, 1), '1', 'Wed, ')
      || DECODE(SUBSTR(fcrc.class_info, 37, 1), '1', 'Thu, ')
      || DECODE(SUBSTR(fcrc.class_info, 38, 1), '1', 'Fri, ')
      || DECODE(SUBSTR(fcrc.class_info, 39, 1), '1', 'Sat ')
    ELSE 'n/a'
  END days_of_week
FROM apps.fnd_concurrent_requests fcr ,
  apps.fnd_user fu ,
  apps.fnd_concurrent_programs fcp ,
  apps.fnd_concurrent_programs_tl fcpt ,
  apps.fnd_printer_styles_tl fpst ,
  apps.fnd_conc_release_classes fcrc ,
  apps.fnd_responsibility_tl frt ,
  apps.fnd_lookups fl
WHERE fcp.application_id       = fcpt.application_id
AND fcr.requested_by           = fu.user_id
AND fcr.concurrent_program_id  = fcp.concurrent_program_id
AND fcr.program_application_id = fcp.application_id
AND fcr.concurrent_program_id  = fcpt.concurrent_program_id
AND fcr.responsibility_id      = frt.responsibility_id
AND fcr.print_style            = fpst.printer_style_name(+)
AND fcr.release_class_id       = fcrc.release_class_id(+)
AND fcr.status_code            = fl.lookup_code
AND fl.lookup_type             = 'CP_STATUS_CODE'
AND fcr.phase_code             = 'P'
AND frt.language               = 'US'
AND fpst.language              = 'US'
AND fcpt.language              = 'US'
ORDER BY Fu.Description,
  Fcr.Requested_Start_Date ASC

-------------------------------------------------------

SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, cr.resubmit_interval,
NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
) schedule_type,
DECODE (NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
),
'PERIODICALLY', 'EVERY '
|| cr.resubmit_interval
|| ' '
|| cr.resubmit_interval_unit_code
|| ' FROM '
|| cr.resubmit_interval_type_code
|| ' OF PREV RUN',
'ONCE', 'AT :'
|| TO_CHAR (cr.requested_start_date, 'DD-MON-RR HH24:MI'),
'EVERY: ' || fcr.class_info
) schedule,
fu.user_name, requested_start_date
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcr
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fcr.release_class_id(+) = cr.release_class_id
AND fcr.application_id(+) = cr.release_class_app_id;

------------------------------------------------------------------

Manage Inactive session in Oracle 23AI Database.

# CONNECT WITH PDB DATABASE(ORACLE 23AI)

[oracle@prd-23ai ~]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Sat Jan 25 06:26:57 2025

Version 23.6.0.24.10

Connected to:

Oracle Database 23ai EE High Perf Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems

Version 23.6.0.24.10

SQL>

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 PROD READ WRITE NO

SQL> alter session set container=PROD;

Session altered.

# VERIFY SESSION STATUS

SQL> select USERNAME,STATUS,SCHEMA#,SCHEMANAME,OSUSER,MACHINE,PROGRAM,EVENT from v$session;

#KILL INACTIVE SESSION

SQL> select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where status='INACTIVE';

'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||'''IMMEDIATE;'

--------------------------------------------------------------------------------

alter system kill session '16,3670' immediate;

alter system kill session '19,2265' immediate;

SQL > select count(*) from gv$session s, v$process p where p.addr=s.paddr and s.status='INACTIVE';

SQL > select LOGON_TIME from gv$session s, v$process p where p.addr=s.paddr and s.status='INACTIVE';

SQL > select count(s.status) INACTIVE_SESSIONS

from gv$session s, v$process p

where

p.addr=s.paddr and

s.status='INACTIVE' and last_call_et > 3600;

spool $file;

select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v\$session where status='INACTIVE' and program like 'JDBC%' and last_call_et > 3600;

spool off;

@file;

exit;

EOF

3600sec :- 1 hours

spool $file;

select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v\$session where status='INACTIVE' and program like 'JDBC%' and last_call_et > 3600;

spool off;

@$file;

exit;

EOF