Sujeet APPS DBA

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.

What Is MySQL Database.

What Is MySQL Database?

MySQL is an open-source database management system. Databases are the essential data repositories for all software applications. MySQL is a SQL-based relational database designed to store and manage structured data.

As a relational database system, MySQL stores data in rows and columns defined by schemas.

MySQL derives part of its name from the SQL language, which is used for managing and querying data in databases.

MySQL offers full ACID transactions and can handle a high volume of concurrent connections.

MySQL Database Definition.

MySQL is an open-source RDBMS that uses SQL to create and manage databases. As a relational database, MySQL stores data in tables of rows and columns organized into schemas. A schema defines how data is organized and stored and describes the relationship among various tables. With this format, developers can easily store, retrieve, and analyze many data types, including simple text, numbers, dates, times, and, more recently, JSON and vectors. Because MySQL is open source, it includes numerous features developed in close cooperation with a community of users over almost 30 years. Two capabilities that developers rely on are MySQL’s support for ACID transactions and MySQL’s ability to scale. ACID stands for “atomicity, consistency, isolation, and durability,” the four properties that ensure database transactions are processed dependably and accurately. With ACID transactions, MySQL can guarantee that all data modifications are made consistently and reliably, even in the event of a system failure. MySQL can be scaled out to support very large databases, and it can handle a high volume of concurrent connections. MySQL’s performance, ease of use, and low cost combined with its ability to reliably scale as a business grows have made it the world’s most popular open-source database.

MySQL: Distinguishing It from SQL.

The acronym “SQL” stands for Structured Query Language, a type of programming language that’s used for manipulating data in a database. MySQL uses the SQL language to manage and query data in databases and, hence, uses the acronym as part of its name. If you’ve got data stored in a MySQL RDBMS, then you can write simple SQL prompts to add, search, analyze, and retrieve data.

Understanding MySQL: Features and Popularity.

MySQL’s ability to efficiently store and analyze vast quantities of data means it can help with tasks as varied as informing complex business decisions and finding a local restaurant for a date night. Here’s a look at the top functionality that makes MySQL so pervasive in today’s tech landscape. A Comprehensive Relational Database System MySQL is known as a flexible, easy-to-use database management system. You’ll find it used by lone developers grabbing an open-source database for a small project all the way up to the world’s most visited websites and applications. MySQL has been evolving to keep up with demand for nearly 30 years and offers ACID transactions that ensure data modifications are made invariably—even when supporting a high volume of concurrent connections.

The Open Source Advantage of MySQL Database.

MySQL is open source, which means anyone can download MySQL software from the internet and use it without cost. Organizations can also change their source code to suit their needs. MySQL software uses the GNU General Public License (GPL), which is a common set of rules for defining what may or may not be done with or to the software in various situations. If an organization feels uncomfortable with the GNU GPL or wishes to embed MySQL code into a commercial application, it can buy a commercially licensed version. See the MySQL Legal Policies page for more information about licensing.

Why Developers Prefer MySQL Database Performance and Flexibility.

MySQL is known for being easy to set up and use, yet reliable and scalable enough for organizations with very large data sets and vast numbers of users. MySQL’s native replication architecture enables organizations such as Facebook to scale applications to support billions of users. Other key factors in MySQL’s popularity include abundant learning resources and the software’s vibrant global community.

How Does MySQL Work?

Each software application needs a repository to store data so the information can be accessed, updated, and analyzed in the future. A relational database such as MySQL stores data in separate tables rather than putting all the data in one big storeroom. The database structure is organized into files optimized so data can be accessed quickly. This logical data model, with objects such as data tables, views, rows, and columns, offers developers and database administrators a flexible programming environment. They can set up rules governing the relationships between different data fields, such as one-to-one, one-to-many, unique, required, or optional, and add “pointers” among different tables. The system enforces these rules so that, with a well-designed database, an application never sees data that are inconsistent, duplicated, orphaned, or out of date. MySQL Database is a client/server system that consists of a multithreaded SQL server that supports different back ends, several client programs and libraries, a choice of administrative tools, and a wide variety of application programming interfaces (APIs). MySQL is available as an embedded multithreaded library that developers can link into applications to get a smaller, faster, easier-to-manage standalone product. SQL is the most common standardized programming language used to access databases. Depending on the programming environment, a developer might enter SQL directly—for example, to generate reports. It’s also possible to embed SQL statements into code written in another programming language or use a language-specific API that hides the SQL syntax.

MySQL Important point?

MySQL is important because of its ubiquitousness and the fundamental role of databases as the amount of data both grows exponentially and fuels AI. MySQL underpins a vast array of websites and applications and helps businesses worldwide organize, analyze, and protect their data. Other factors also help maintain MySQL’s enduring popularity.

Open source with strong community support

During MySQL’s nearly three decades as the leading open-source RDBMS, a vibrant global community has grown up around it. That’s important because the community provides a wealth of expertise and resources, such as tutorials, tips in forums, and more. By testing the software in multiple use case scenarios, the community also has helped discover and fix bugs, making MySQL highly reliable. The open-source community’s knowledge-sharing, problem-solving, and continuous innovation keep MySQL users at the forefront of technological advancements. High performance and reliability.

MySQL is home to many environments, including individual developer projects and mission-critical applications demanding unwavering stability. The open-source RDBMS can handle high volumes of data and concurrent connections and provide uninterrupted operations under demanding circumstances. This is partly due to MySQL’s robust replication and failover mechanisms, which help minimize the risk of data loss. Ease of use and compatibility MySQL is often praised for being easy to use and for offering broad compatibility with technology platforms and programming languages, including Java, Python, PHP, and JavaScript. MySQL also supports replication from one release to the next, so an application running MySQL 5.7 can easily replicate to MySQL 8.0. In addition, MySQL offers flexibility in developing both traditional SQL and NoSQL schema-free database applications. This means developers can mix and match relational data and JSON documents in the same database and application.

Cost-effectiveness and scalability Because MySQL is open source, it’s freely available to use at no cost, beyond the on-premises hardware it runs on and training on how to use it. For the latter, a global community of MySQL users provides cost-effective access to learning resources and troubleshooting expertise. Oracle also offers a wide range of training courses. When it’s time to scale out,

MySQL supports multithreading to handle large amounts of data efficiently. Automated failover features help reduce the potential costs of unplanned downtime.

Benefits of MySQL MySQL is fast, reliable, scalable, and easy to use. It was originally developed to handle large databases quickly and has been used in highly demanding production environments for many years.

MySQL offers a rich and useful set of functions, and it’s under constant development by Oracle, so it keeps up with new technological and business demands. MySQL’s connectivity, speed, and security make it highly suited for accessing databases on the internet.

MySQL’s key benefits include the following:

Ease of use. Developers can install MySQL in minutes, and the database is easy to manage.

Reliability.

MySQL is one of the most mature and widely used databases. It has been tested in a wide variety of scenarios for nearly 30 years, including by many of the world’s largest companies. Organizations depend on MySQL to run business-critical applications because of its reliability.

Scalability.

MySQL scales to meet the demands of the most accessed applications. MySQL’s native replication architecture enables organizations, including Facebook, Netflix, and Uber, to scale applications to support tens of millions of users or more.

Performance.

MySQL is a proven high-performance, zero-administration database system that comes in various editions to meet nearly any demand. Cloud-based HeatWave MySQL provides unmatched performance and price performance, according to industry benchmarks including TPC-H, TPC-DS, and CH-benCHmark.

High availability.

MySQL delivers a complete set of native, fully integrated replication technologies for high availability and disaster recovery. For business-critical applications and service level agreement commitments, customers can achieve recovery point objective zero (zero data loss) and recovery time objective zero seconds (automatic failover).

Security.

Data security entails both data protection and compliance with industry and government regulations, including the European Union General Data Protection Regulation, the Payment Card Industry Data Security Standard, the Health Insurance Portability and Accountability Act, and the Defense Information Systems Agency’s Security Technical Implementation Guides. MySQL Enterprise Edition provides advanced security features, including authentication/authorization, transparent data encryption, auditing, data masking, and a database firewall.

Flexibility.

The MySQL Document Store gives users maximum flexibility in developing traditional SQL and NoSQL schema-free database applications. Developers can mix and match relational data and JSON documents in the same database and application.

What Is HeatWave MySQL?

HeatWave is an in-memory query accelerator for MySQL Database. HeatWave MySQL is the only MySQL cloud database service that offers such acceleration and that combines transactions with real-time analytics, eliminating the complexity, latency, cost, and risk of extract, transform, and load (ETL) duplication. As a result, users can see orders-of-magnitude increases in MySQL performance for analytics and mixed workloads. In addition, HeatWave AutoML lets developers and data analysts build, train, deploy, and explain the outputs of machine learning models within HeatWave MySQL in a fully automated way. They can also benefit from integrated and automated generative AI using HeatWave GenAI. Key Features of HeatWave MySQL HeatWave MySQL can help improve MySQL query performance significantly and enables organizations to access real-time analytics on their transactional data without moving it to a separate analytics database. You can enhance data security and deploy HeatWave MySQL-powered applications in Oracle Cloud Infrastructure (OCI), Amazon Web Services (AWS), or Microsoft Azure. HeatWave MySQL is the only MySQL cloud service integrating HeatWave, an in-memory, massively parallel, hybrid columnar query-processing engine. It’s also the only MySQL cloud service built on MySQL Enterprise Edition. Advanced features provide additional security measures to help companies protect data throughout its lifecycle and address regulatory requirements. Additionally, the built-in HeatWave Autopilot automatically helps improve MySQL performance and reduce costs with machine learning-powered automation, without requiring database tuning expertise. Autopilot can help increase the productivity of developers and DBAs and help reduce human error.