Sujeet APPS DBA

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

Size of schema in Oracle database 23AI

How to find the size of schema in the 23AI ORACLE Database.

SQL> select sum(bytes)/1024/1024 "SIZE_IN_MB" from dba_segments;

SUM(BYTES)/1024/1024

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

1986.286

SQL> select sum(bytes)/1024/1024/1024 "SIZE_IN_GB" from dba_segments;

SQL> select owner,sum(bytes) from dba_segments group by owner;

SQL> select OWNER,sum(bytes)/1024/1024 "SIZE_IN_MB" from dba_segments group by owner order by owner;

SQL > select owner, sum(bytes)/1024/1024/1024 "SIZE_IN_GB" from dba_segments group by owner;

SQL > SELECT sum(bytes)/1024/1024/1024 "SIZE_IN_GB" FROM dba_segments WHERE owner = 'SYSTEM';

#How to find the size of the table within schema#

SLQ > select segment_name,segment_type, sum(bytes/1024/1024/1024) GB from dba_segments

where owner='apps' and segment_type='TABLE' group by segment_name,segment_type;

#How to find the count of the table with schema name list#

SELECT COUNT(table_name),owner FROM DBA_TABLES GROUP by owner order by owner;

MySQL Statement

MySQL Drop Database

DROP Database using MySQL Command Line Client

Using the MySQL Command Line Interface (CLI), the DROP DATABASE command is executed with the database name specified:

DROP DATABASE database_name;

Explanation: This command permanently erases the specified database, so ensure the correct database name is used to prevent unintended deletions.

Create the Database using the Command Line Interface,

Query:

CREATE DATABASE library;

Retrieve the List of Database to Confirm that the Database is Created Successfully.

SHOW DATABASES;

-Drop the Database library using the following Command.

DROP DATABASE library;

Verifying that the database library is deleted successfully from the list of databases

SHOW DATABASES;

Conclusion

In this article, we learn that the DROP DATABASE command in MySQL provides a method to permanently delete the databases. It removes the database which is no longer needed for data storing purposes. While dropping the database we need to take care while using this command is crucial, as it irreversibly erases all data within the specified database. We cannot perform a Rollback operation when the DROP DATABASE command is used, so need to use it carefully.