Sujeet APPS DBA

ERROR 1396 (HY000): Operation ALTER USER failed for 'Mysql'@'%'

This MySQL error —

ERROR 1396 (HY000): Operation ALTER USER failed for 'Mysql'@'%'

— means that MySQL cannot find the user 'Mysql'@'%' to alter, or the user definition is inconsistent in the internal privilege tables.

Possible Causes-

1. User doesn’t exist.

The user 'Mysql'@'%' may not exist in the mysql.user table.

2. User exists but has corruption or a mismatch.

Sometimes the internal data becomes inconsistent, especially after an upgrade or manual edits.

3. Privileges issue.

The currently logged-in MySQL user doesn’t have enough privileges to modify other users.

4. MySQL version difference.

The command syntax differs slightly between MySQL 5.7, 8.0, and MariaDB.

Solution-

Check if user exists

Run this:

`sql SELECT User, Host FROM mysql.user WHERE User = 'Mysql';

` If you get no rows, the user doesn’t exist

— MySQL can’t “alter” a non-existent account.

If the user doesn’t exist,

create it first

`sql CREATE USER 'Mysql'@'%' IDENTIFIED BY 'yourpasswordhere';

` Then you can alter if needed:

`sql ALTER USER 'Mysql'@'%' IDENTIFIED BY 'newpasswordhere';

` If user exists but still fails Drop and recreate the account cleanly:

`sql DROP USER 'Mysql'@'%'; CREATE USER 'Mysql'@'%' IDENTIFIED BY 'yourpasswordhere'; ` >

Make sure you can safely drop the user —

This will remove all privileges assigned to it.

Grant privileges again (if needed)

`sql GRANT ALL PRIVILEGES ON . TO 'Mysql'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES; `

Note-

• Check your MySQL version using:

`sql SELECT VERSION(); `

• In MySQL 8.0,

password updates use:

`sql ALTER USER 'Mysql'@'%' IDENTIFIED WITH mysqlnativepassword BY 'new_password';

• Make sure you’re logged in as a user with UPDATE or ALTER USER privilege, such as root` or another admin.

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.