Sujeet APPS DBA

java.sql.SQLSyntaxErrorException: Access was denied to the user in MySQL 8.4.

java.sql.SQLSyntaxErrorException: Access denied for user 'SIT'@'%' to database 'SIT'

means the MySQL user SIT does not have sufficient privileges on the database SIT.

Here’s how you can fix it in MySQL 8.4:

Step 1: Log in as a privileged user

Connect with root or another admin account:

root > mysql -u root -p

Step 2: Check if the user SIT exists

mysql> SELECT user, host FROM mysql.user WHERE user='SIT';

If no row is returned, create the user:

CREATE USER 'SIT'@'%' IDENTIFIED BY 'your_password';

Step 3: Grant privileges on the database

Give the user access to the SIT database:

mysql> GRANT ALL PRIVILEGES ON SIT.* TO 'SIT'@'%';

Or if you want only read/write:

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON SIT.* TO 'SIT'@'%';

Step 4: Apply changes

mysql> FLUSH PRIVILEGES;

Step 5: Test the connection

From your Java app or CLI:

# mysql -u SIT -p -h <your_host> SIT

Query to get the package specification

In Oracle E-Business Suite R12.2.10, if you want to download (export) a package’s source code (specification and body), you can do it using a SQL query in TOAD, SQL Developer, or any other tool connected to the APPS schema.

## Query to get the package specification

sql > SELECT text

FROM all_source

WHERE name = UPPER('PACKAGE_NAME')

AND type = 'PACKAGE'

ORDER BY line;

##Query to get the package body

sql > SELECT text

FROM all_source

WHERE name = UPPER('PACKAGE_NAME')

AND type = 'PACKAGE BODY'

ORDER BY line;

In Oracle EBS R12.2.10, fetching a custom package (specification or body) is usually done directly from the APPS schema or from the Edition-Based Redefinition (EBR) filesystem if you want the file from the application tier.

# View Package Specification

SELECT text

FROM all_source

WHERE name = 'YOUR_PACKAGE_NAME'

AND type = 'PACKAGE'

AND owner = 'APPS'

ORDER BY line;

# View Package Body

SELECT text

FROM all_source

WHERE name = 'YOUR_PACKAGE_NAME'

AND type = 'PACKAGE BODY'

AND owner = 'APPS'

ORDER BY line;

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;

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