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.

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.

ORA-00059: Maximum Number Of DB_FILES Exceeded in 19C database

When I am adding datafile to my 19C database facing the below error.

SQL> alter tablespace DATA add datafile '/u01/data/data15.dbf' size 20G;
alter tablespace DATA add datafile '/u01/data/data15.dbf' size 20G
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded

SOLUTION-

Check for DB_FILES value

SQL> sho parameter db_files;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------
db_files integer 500

Check Value for Total number of datafiles in Database

SQL> select count(*) from dba_data_files;

COUNT(*)
----------
496

Set the db_files parameter to a new high-value

SQL> alter system set db_files = 1000 scope = spfile;

System altered.

Restart your database

SQL>shutdown immediate;

SQL>startup

SQL> sho parameter db_files;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------
db_files integer 1000

SQL> alter tablespace DATA add datafile '/u01/data/data15.dbf' size 20G;

issue resolved.

Active Session History, (ASH) reports.

Active Session History, (ASH) reports are one of my favorite when investigating a performance issue.

The biggest challenge I think that faces many administrators is to know when to turn to an ASH report and how to use the report to diagnose an issue.

# Generating an ASH Report on PDB database.

[oracle@ora-19c]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 6 17:49:49 2024

Version 19.21.0.0.0

Connected to:

Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production

Version 19.21.0.0.0

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB READ WRITE NO

SQL> alter session set container=PDB;

Session altered.

SQL> @$ORACLE_HOME/rdbms/admin/ashrpt.sql

Enter value for awr_location: AWR_PDB

Specify the begin time in minutes before the system date:

Enter value for begin_time: -30

In this example, 30 minutes before the current time is selected.

Enter value for begin_time: -30

Enter value for duration: <ENTER>

Enter value for report_name: ASH_report.txt

SQL> !

find same file(ASH_report.txt) in same location.

#Report Options.

You’ll notice that the SQL Details page offers you two reports that you can run- AWR SQL Report and the ASH Report.

These reports can both be found in the $ORACLE_HOME/rdbms/admin directory.

AWR SQL_ID specific report is named awrsqrpt.sql and the ASH report is named ashrpt.sql if you’re interested in running them from the command line via SQLPlus.

We are going to choose to use the ASH report instead of the SQL_ID specific AWR report OR a full AWR report for a couple reasons:

1. We are only interested in this specific SQL_ID, so the full AWR report would be overkill.

2. The specific AWR SQL_ID report wouldn’t include the session, blocked session and other wait info we are interested in.

3. We aren’t interested in execution plans, which the SQL_ID specific AWR Report includes.

4. The ASH report allows us to use samples and drill down to by time vs. snapshots, which comes in handy when we are inspecting particular set of transactions. We aren’t concerned with number of executions, which is offered in AWR, but not in samples with ASH.

SHRD0014: GLLEZL - process exiting with failure

SYMPTOMS

Journal Import completes with the following error:

Error

------

ORA-01653 : unable to extend table GL.GL_IMPORT_REFERENCES

ORA-01653 : unable to extend table GL.GL_JE_LINES

shrd0014: gllezl - process exiting with failure

gllcje- for the consolidation transfer program.

CAUSE

The tablespace must be increased at the database level.

SOLUTION

To implement the solution, please execute the following steps:

The DBA needs to increase or extend the tablespace for the tables referenced in the error at the database level.

Use this specific SQL to find the tablespace name:

SQL > select OWNER, TABLE_NAME , TABLESPACE_NAME

from dba_tables

where owner = 'GL'

and table_name like '%GL_IMPORT_REFERENCES%';

There are other columns on dba_tables that the DBA may want to query to get the extents etc.

Add a Datafile:

ALTER TABLESPACE ADD DATAFILE '' SIZE ;

If the tablespace is being used as a temporary tablespace, temporary segments may be still holding the space.

You can query this for how much free, used, total, and percentage filled space is available for each tablespace.

SQL> select tablespace_name, ROUND(bytes_free,2) free,ROUND(bytes_total-bytes_free,2) used, ROUND(bytes_total,2) total,perused

from (select a.tablespace_name, a.bytes_free,b.bytes_total,

ROUND((100-(100*a.bytes_free/b.bytes_total)),2) perused from

(Select tablespace_name,sum(bytes)/1024/1024 bytes_free From dba_free_space Group by tablespace_name) a,

(Select tablespace_name,sum(bytes)/1024/1024 bytes_total From dba_data_files Group by tablespace_name) b

where a.tablespace_name=b.tablespace_name) where perused >= 0 ORDER BY tablespace_name;

select tablespace_name,sum(bytes)/(1024*1024) from dba_free_space

group by tablespace_name;

## ADD TABLESPACE

SQL > alter tablespace DATA add datafile '/u01/data/data13.dbf' size 10G;

## Check Temp tablespace.

SQL> select TABLESPACE_NAME, BYTES_USED/1024/1024, BYTES_FREE/1024/1024 from V$TEMP_SPACE_HEADER;

TABLESPACE_NAME BYTES_USED/1024/1024 BYTES_FREE/1024/1024

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

TEMP1 10240 10240

TEMP2 10240 10240

##Resize/add temp datafile.

SQL> alter tablespace TEMP2 add tempfile '/u01/data/temp04.dbf' size 30G;

SQL> alter tablespace TEMP1 add tempfile '/u01/data/temp05.dbf' size 30G;

SQL> ALTER DATABASE TEMPFILE '/u01/data/temp02.dbf' RESIZE 30G;

ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'

ORA-01552: cannot use system rollback segment for non-system tablespace "string"

Cause: Used the system rollback segment for non-system tablespace operations.

If this is a clone database, this will happen when attempting any data modification

outside the UNDOTBS tablespace.

Solution:

1) Switch to Automatic Undo Management

$ sqlplus / as sysdba

alter system set undo_management=auto scope=spfile;

2) Restart the database.

SQL> Shutdown immediate;

SQL> startup

ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE

Error:-

IEX: Scoring Engine Harness Error - ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE (Doc ID 2056754.1)

SYMPTOMS:-

You may have noticed that IEX_DELINQUENCIES_ALL is not getting updated with closed delinquencies or new delinquencies on past due invoices when the IEX:

Scoring Engine Harness or IEX: Delinquency Management processes run. You have confirmed that you are not putting in a value for the Maximum Number of Records parameter on the IEX: Scoring Engine Harness, however no delinquencies are being created.

On further investigation, even though IEX: Scoring Engine Harness shows successful the following message can be seen in the log -

ERROR IN CONCUR: IEX_SCORE: storeDelBuffest: UnExpected Error ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE and cannot be instatiated

CAUSE:-

The IEX_DELINQUENCIES_ALL table has run out of sequence numbers and you can no longer create new records.

Bug 21812472

SOLUTION:-

To resolve the issue, the following SQL will recreate the sequence IEX_DEL_BUFFERS_S with NO Max Value:

set serveroutput on;

declare

BEGIN

execute immediate ('drop sequence IEX.iex_del_buffers_s');

execute immediate ('create sequence IEX.IEX_DEL_BUFFERS_S START WITH 10000 '

|| 'MINVALUE 1 INCREMENT BY 1 CACHE 100 NOORDER ');

DBMS_OUTPUT.put_line('New Sequence for IEX_DEL_BUFFERS_S ' || sqlerrm);

EXCEPTION

WHEN OTHERS then

DBMS_OUTPUT.put_line('Sequence creation failed IEX_DEL_BUFFERS_S ' ||

sqlerrm);

end;

commit;

Output like below:-

New Sequence for IEX_DEL_BUFFERS_S ORA-0000: normal, successful completion

PL/SQL procedure successfully completed.

Commit complete.

-bash: dropdb: command not found in Postgresql

In PostgreSQL, the “psql command not found” error or the “psql” is not recognized as an internal or external command arises

Because of the following reasons:

- Postgres is not installed on the Machine.

- The Path for Postgres tools is not set on our system.

The stated error can be fixed either by installing PostgreSQL or by setting the environment variable for the Postgres tools.

[UAT@postgres ~]$ dropdb UATDB;

-bash: dropdb: command not found

Maybe Your $PATH variable doesn't contain the default paths? echo $PATH should give something like:

# echo $PATH

/usr/bin:/bin:/opt/post/bin:/opt/post/sbin

# locate dropdb

/usr/local/pgsql/bin/dropdb

You may set the PATH variable with this command

# export PATH=/usr/local/pgsql/bin:$PATH

[UAT@postgres ~]$ dropdb UATDB;

Its working now.

Oracle 19c Interview Questions and Answers.

1. What is Oracle 19c?

Oracle 19c is a relational database management system (RDBMS) from Oracle Corporation.

It is the long-term support release (LTSR) of the Oracle Database 12c Release 2 (12.2) line.

Oracle 19c is available on-premises and in the cloud.

2. Are there any specific new features that can be used in the context of a database upgrade to Oracle 19c?

Yes, there are a few new features that can be used when upgrading to Oracle 19c. One is the new Oracle Upgrade Assistant,

which can help automate the process of upgrading your database. Another is the new Oracle Database Migration Assistant,

which can help with migrating data to Oracle 19c. Finally, the new Oracle SQL Developer Migration Workbench can help with migrating database

objects and data.

3. Is there anything special about upgrading from Oracle 12c to 19c as opposed to other versions?

Yes, there are a few things to keep in mind when upgrading to Oracle 19c. First, the Oracle 19c database is only

compatible with Oracle Linux 7.3 or higher.

Second, the Oracle 19c database is only compatible with the Oracle 19c client. Finally, when upgrading from Oracle 12c to 19c,

you will need to export your data from the 12c database, then import it into the 19c database.

4. Is it possible to upgrade an existing 11gR2 or 12c non-container database (CDB) to Oracle 19c? If yes, how?

Yes, it is possible to upgrade an existing 11gR2 or 12c non-container database (CDB) to Oracle 19c.

You can do this by using the Oracle Database Upgrade Assistant (DBUA).

5. In what situations would you use a container database architecture for your production databases?

Container database architectures are becoming increasingly popular for production databases, as they offer a number of advantages.

One key advantage is that they allow for much easier management of multiple databases, as all of the databases can be managed from a single console.

This can save a lot of time and effort, particularly for larger organizations with many databases. Additionally,

container databases can offer better performance and scalability than traditional database architectures.

6. What is a pluggable database?

A pluggable database is a database that is contained within another database, known as the container database.

The pluggable database shares certain resources with the container database, but it can also be configured and administered independently.

This allows for greater flexibility and scalability when managing multiple databases.

7. Does Oracle 19c support using multiple PDBs on the same server?

Yes, Oracle 19c does support using multiple PDBs on the same server.

This allows for greater flexibility and scalability when deploying Oracle databases.

8. What are some key features of Autonomous Database Cloud Service?

Some key features of Autonomous Database Cloud Service include:

– The ability to automatically provision and manage Oracle databases in the cloud

– The ability to elastically scale compute and storage resources as needed

– The ability to patch and upgrade databases automatically

– The ability to monitor and manage databases using the Oracle Cloud Infrastructure Console

9. Can you explain what SQL Plan Management is and why we need it?

SQL Plan Management is a feature in Oracle 19c that allows the database to automatically manage SQL execution plans.

This can help improve performance by ensuring that the optimizer always has access to the most up-to-date execution plans.

SQL Plan Management can also help to prevent plan regressions by automatically reverting to a previous plan if a new one is found to be sub-optimal.

10. What are the main benefits provided by Oracle Machine Learning?

Oracle Machine Learning provides a number of benefits, including the ability to automatically generate models from data,

the ability to deploy models in a production environment, and the ability to monitor and manage models. Additionally,

Oracle Machine Learning provides a number of tools that make it easier to work with data, including data preparation,

feature selection, and model evaluation.

11. What’s the difference between Oracle Advanced Security Option and Oracle Data Redaction?

Oracle Advanced Security Option provides a comprehensive set of tools for securing data in transit and at rest, including encryption,

data masking, and access control. Oracle Data Redaction, on the other hand, is a feature that allows you to selectively redact sensitive

data from your database, making it unreadable to unauthorized users.

12. How do you ensure data security when storing sensitive information in a database like Oracle 19c?

When storing sensitive information in a database like Oracle 19c, you need to ensure that the data is encrypted. You can do this by using a

tool like Oracle Advanced Security. This will encrypt the data both at rest and in transit, making it much more difficult for unauthorized

individuals to access it.

13. What is the best way to secure passwords stored in Oracle 19c?

The best way to secure passwords stored in Oracle 19c is to use the Oracle Password Vault. The Password Vault is a secure, centralized

repository for storing and managing passwords. It provides a single point of control for managing passwords, and it ensures that all passwords

are stored in a secure, encrypted format.

14. How can you prevent unauthorized users from accessing sensitive information in Oracle 19c?

You can prevent unauthorized users from accessing sensitive information in Oracle 19c by using the Data Redaction feature. This feature allows

you to specify which columns of data should be redacted, and how the data should be redacted. You can also specify which users should have

access to the redacted data.

15. What is the importance of the Oracle wallet feature?

The Oracle wallet feature is important because it allows for the secure storage of sensitive information, such as passwords,

within the database. This means that the information is less likely to be compromised if the database is breached.

16. What is Oracle Real Application Clusters? Why should we use it?

Oracle Real Application Clusters (RAC) is a software feature that allows Oracle databases to be deployed on a cluster of servers.

This provides high availability and scalability for Oracle databases. RAC can be used to improve the performance of Oracle databases,

and it can also be used to provide failover protection in the event of a server failure.

17. What are the advantages of using Oracle Enterprise Manager?

Oracle Enterprise Manager provides a comprehensive and centralized solution for managing Oracle databases, middleware, and applications.

It offers a complete set of tools for managing and monitoring all aspects of your IT infrastructure, including:

– Provisioning and patching

– Configuration management

– Performance monitoring

– Capacity planning

– Security and compliance

– Backup and recovery

Oracle Enterprise Manager also provides a self-service portal for users, so they can request and provision new resources,

as well as monitor the status of their existing resources.

18. Do you think there will ever be a situation where a business won’t need a data warehouse?

I don’t think so. Data warehouses will always be necessary to store and analyze large amounts of data.

19. What’s your opinion on Oracle Exadata? When should it be used?

Oracle Exadata is a powerful tool that can be used to improve the performance of Oracle databases. It should be used in situations where the

database is expected to experience high demand or where the database is particularly large.

20. What are the differences between Oracle Exadata and Amazon Web Services RDS?

Oracle Exadata is a cloud-based platform that provides customers with a complete Oracle database solution,

while Amazon Web Services RDS is a cloud-based platform that provides customers with a relational database service.

Oracle Exadata includes all of the software and hardware necessary to run an Oracle database,

while Amazon Web Services RDS only provides the software necessary to run a relational database.

Oracle Exadata also provides customers with a complete set of tools for managing and monitoring their Oracle database,

while Amazon Web Services RDS only provides a limited set of tools for managing and monitoring their relational database.

Top Oracle DBA, RAC, And Performance Tuning Questions

Q #1) Why do we use the materialized view instead of a table or views?

Answer: Materialized view is a database object that holds query results. If materialized views are used instead of tables or views in complex query executions, performance gets enhanced as re-execution is not required for repeated queries.

Q #2) How is the Clustered Index different from the Non-Clustered Index?

Answer: An index is a schema object, which can search the data efficiently within the table.

Indexes can be clustered or non-clustered. Differences include:

In a clustered index, table records are sorted physically and stored in a particular order. Hence, a table can have a single clustered index only.

While in a non-clustered index, logical sorting happens which does not match the physical order of the records.

The leaf node of a clustered index holds the data pages while the non-clustered index holds the index rows.

Q #3) What are the different responsibilities of a DBA?

Answer: DBA is the database administrator who performs all administrative tasks.

Administrative tasks include:

User-level administration i.e. creates users, removes existing users, or modifies user permissions.

Maintains database security.

Manages database storage & objects.

Tunes the performance of a database.

Performs backups & recovery tasks.

Q #4) What do you mean by Database Normalization and why is it important?

Answer: The normalization technique is a set of rules that are used to organize the relational database to prevent data redundancy and dependency.

Once initial database objects are identified, normalization helps in identifying the relationships between schema objects.

Different normalization forms are as follows:

First Normal Form (1NF)

Second Normal Form (2NF)

Third Normal Form (3NF)

Boyce-Codd Normal Form (BCNF)

Fourth Normal Form (4NF)

Fifth Normal Form (5NF)

Q #5) Can you list down the different components of physical and logical database structure?

Answer: Given below is the list of different components.

The physical structure includes:

Data files, which hold all the DB objects like tables, views, indexes, etc.

Redo Log files, which maintain the records of database changes as a result of user transactions.

Control files, which maintain the database status and physical structure.

The logical structure includes:

Tablespace, which is a logical storage unit where the database object resides.

Segments are logical storage units only but within a tablespace.

Extent is the logical unit where various contiguous data blocks and extents together form a segment.

A data block is the smallest logical storage unit in the database.

Q #6) What is a SYSTEM tablespace and why do we need it?

Answer: System tablespace is created at the time of database creation. This tablespace holds all the data dictionary tables and hence it is

used for the creation of various database objects. System tablespace must stay online for the database to function smoothly.

Q #7) What do you mean by SGA and how is it different from PGA?

Answer: SGA means System Global Area is the memory area that is defined by Oracle during instance startup.

This area can be shared by the system-level processes and hence it is known as the Shared Global Area as well.

PGA is Program Global Area is memory specific to a process or session. It is created when the Oracle process gets

started and each process will have a dedicated PGA.

Q #8) What is a password file in a database and why is it required when a user can be authenticated using data dictionary tables?

Answer: Database users can be authenticated using data dictionary tables as they store the username & password.

If the password provided by a user matches the one stored in the database, then the user would be able to log in. However,

this can happen only if the database is open.

If the database is in shutdown mode, then these tables cannot be accessed and hence password file will be used by the database

administrators to log in and open the database.

Q #9) What are the different types of backups that are available in Oracle?

Answer: On a higher level, there are 2 types of backup that are available in Oracle which are physical & logical.

During physical backup, copies of physical database files (like data files, control files, redo logs & other executables)

are created and saved for the future. This can be achieved using either operating system utilities or RMAN.

In contrast, logical backup allows taking a backup of the database objects like tables, views, indexes, stored procedures, etc.

individually through the Export/Import utility provided by Oracle.

Q #10) What do we mean by hot backup & cold backup and how are they different?

Answer: Hot backup is the process of taking database backup while the database is in running mode. Hence, it is also known as Online Backup.

While cold backup can be taken only when the database is in shutdown mode and hence it is known as Offline Backup as well.

There are few websites like banking & trading ones, which are 24 hours operational and hence, cannot support bringing the database down. Hence,

DBAs need to take the backup in online mode only.

Q #11) What is the difference between restoring a database and recovering a database?

Answer: During the restoration process, backup files are copied from the hard disk, media, or tapes to the restoration location and later make

the database operational. Recovery has an additional step of updating these data files by applying redo logs so as to recover the changes which

are not backed up.

Let us understand this with the help of a scenario.

Database full backup is taken on Friday at 11 PM

The database crash happened on Saturday at 7 AM

We can restore the lost files using the 11 PM full backup which is Restoration. However, the data will be restored up till Friday at

11 PM and not till Saturday at 7 AM. In order to do the same, redo logs can be applied which will bring the database to the point of failure.

Q #12) What do you understand by Redo Log file mirroring?

Answer: Redo log is the most crucial component of database architecture that records all transactions within the database even before it goes to the data file.

Hence, the mirroring of these files is done to protect them. Redo Log file mirroring allows redo logs to be copied to different disks simultaneously.

And this can be achieved using Data Guard and other utilities.

Q #13) How is incremental backup different from differential backup?

Answer: Incremental backup is known for keeping a backup of only the changed data files since the last backup, which might be full or incremental.

For Example, An incremental/full backup is done at 10 AM on Friday and the next backup is done at 10 AM Saturday. The second incremental backup will

only have the transactions that occurred after Friday at 10 AM.

While Differential backup backs up the files that changed during the last full backup.

If you take a full backup on Friday at 10 AM and then differential back up on Saturday at 10 AM, it will take the backup of the files

changed since Friday, at 10 AM. Further, if the differential backup is taken on Sunday at 10 AM, it will take the backup of the files changed since

Friday, at 10 AM.

Q #14) What is a Flashback Query and when should it be used?

Answer: Oracle has introduced a flashback technology to recover the past states of database objects. It can recover the accidental changes,

which got committed as well. Recovery depends on the specified value of the UNDO_RETENTION parameter.

For Example, the UNDO_RETENTION parameter is set to 2 hours, and if a user accidentally deletes the data at 11 AM with a commit performed.

Then, using FLASHBACK QUERY, he can retrieve these rows until 1 PM only.

Q #15) How is RMAN better than the user-managed backup recovery process?

Answer: Recovery Manager (RMAN) is an Oracle built-in utility that can automate database backup & recovery processes and administrate

backup strategies as well. In user-managed backups, the user needs to perform backup manually.

RMAN backup time will be less when compared to user-managed backups as RMAN maintains all the metadata in the Central Repository and can

quickly retrieve the same.

RMAN does incremental backups rather than taking full file backups which are done by user-managed backups, which again saves time.

RMAN creates backup and recovery scripts that can be re-used and scheduled and does not need manual intervention.

RMAN can detect corrupted data blocks automatically during the backup process and recover them, whereas it doesn’t happen in user-managed backups.

Q #16) What is a Recovery Catalog?

Answer: A recovery catalog is a database schema that holds the metadata used by RMAN for restoration and recovery processes.

It basically stores information on

Data files & their backup files.

Archived Redo Logs & their backup sets.

Stored scripts

Incarnation

Backup history

The catalog gets updated once RMAN takes the backup or switches redo log or changes data file.

Q #17) How do you recover a lost control file?

Answer: If one of the control files is lost or gets corrupted, we can use another copy of the control file maintained at the OS level.

If all the copies of control files are lost or if a user is maintaining only one copy of the control file which gets lost, then a user can

Manually create a control file.

Restore it from the backup control file using the below command.

ALTER DATABASE BACKUP CONTROL FILE TO TRACE;

Restore using RMAN backup by using the below commands.

setdbid XX;

restorecontrolfile;

Q #18) What is the difference between media recovery & crash recovery?

Answer: Media recovery is the process of recovering the database from the backup whenever a disk failure is there. Physical files like data files,

control files, or server parameter files get recovered during media recovery. However, crash recovery will be performed whenever a database instance failure occurs.

Media recovery needs to be performed by DBA while crash recovery is an automated process that is taken care of SMON background process.

Q #19) What is RAC and what are the various benefits of using RAC architecture?

Answer: RAC or Real Application Cluster allows the database to be installed across multiple servers forming a cluster and sharing the storage

structure at the same time. This prevents the database from a single point of failure as one or the other instance will always stay up even if

the other fails.

Using RAC helps in

Maintaining high availability of the system.

Managing workload with the least expenses.

Scalability & agility.

Q #20) How would you differentiate between cluster and grid?

Answer: Clustering is an integral part of grid infrastructure and focuses on a specific objective.

While grid, which may or may not consist of multiple clusters, possesses a wider framework that enables sharing of storage systems, data resources,

and remaining others across different geographical locations.

A cluster will have single ownership but the grid can have multiple ownership based on the number of clusters it holds.

Q #21) What do you understand from Cache Fusion?

Answer: Cache fusion is the process of transferring data from one instance buffer cache to another at a very high speed within a cluster.

Instead of fetching data from a physical disk which is a slow process, the data block can be accessed from the cache.

For Example, Instance A wants to access a data block, owned by instance B. It will send an access request to instance B and hence can access

the same using the other instance B’s buffer cache.

Q #22) How can a single instance environment be converted into the RAC environment and how will they be different?

Answer: Single instance can be converted into RAC using one of the below methods:

Enterprise Manager

DBCA i.e. Database Configuration Assistant

RCONFIG Utility

Single Instance environment Vs RAC Environment

Parameters

Single Instance Environment

RAC Environment

Instance

Multiple

Memory Instance will have dedicated SGA

Every instance will have separate SGA

Access to physical files Only one instance will access data files

and control files. Data files and Control Files are shared across all instances.

Flash Recovery Log

Accessed by single instance.

Shared by multiple instances.

Redo Logs

Dedicated to single instance.

Only one instance can write at a time but others can read data during recovery or archiving process.

Q #23) How can we monitor the space allocations in a database?

Answer: We can use the below data dictionary tables to monitor the space allocations:

DBA_FREE_SPACE

DBA_SEGMENTS

DBA_DATA_FILES

Q #24) What do you understand by “Performance Tuning of DB” & what are the different areas where we can perform tuning?

Answer: It is the process of enhancing database performance by making optimal use of the available resources.

Performance can be enhanced by tuning any of the below areas:

Database design.

Memory allocation.

Disk I/Os.

Database contention.

OS level (CPU).

Q #25) What are the different tools that are provided by Oracle to assist performance monitoring?

Answer: Various tools include:

AWR(Automatic Workload Repository)

ADDM(Automated Database Diagnostics Monitor)

TKPROF

STATSPACK

OEM(Oracle Enterprise Manager)

Q #26) What are the different optimizers that are used to optimize the database?

Answer: There are two types of optimizers:

Rule-Based Optimizer (RBO): If the referenced objects don’t maintain any internal statistics, RBO is used.

Cost-Based Optimizer (CBO): If the referenced objects maintain internal statistics, CBO will check all the possible execution plans and select

the one with the lowest cost.

Q #27) What is an explained plan and how does it help in optimizing the SQL query?

Answer: An explain plan is a statement that displays the execution plan selected by the Oracle optimizer for SELECT, INSERT, UPDATE & DELETE statements.

By looking at this plan, one can figure out Oracle’s selection of the right indexes, proper joins & sorts operations, etc.

Q #28) How can we collect the statistics of different database objects?

Answer: ANALYZE statement can be used to collect the statistics of various database objects like tables, indexes, partitions, clusters,s or object

references. Using this statement we can also identify migrated as well as chained rows within a table or cluster.

Q #29) Why do we need to rebuild indexes?

Answer: Rebuilding indexes is required in order to improve the performance of an application. Due to various INSERT & DELETE operations, the index gets

fragmented & unstructured, thereby making the application slow. To reorganize data within these indexes, rebuilding is performed.

Q #30) What is TKPROF and how can we use it?

Answer: TKPROF is a tuning utility provided by Oracle which can convert SQL trace files into a readable format.

Once the trace file is generated using SQL Trace Utility, the TKPROF tool can be run against the trace file and the output can be read. It can also

generate the execution plan for SQL statements. The executable for TKPROF is located in the ORACLE HOME/bin directory.

Q #31) How can we tune a SQL query to optimize the performance of a database?

Answer: Enlisted are a few of the best practices for writing SQL queries.

Column names should be provided instead of * in SELECT statements.

Joins should be used in the place of sub-queries.

EXISTS should be used instead of IN to verify the existence of data.

UNION ALL should be used in the place of UNION.

HAVING should be used only for filtering the resulting rows from the SQL query.

Q #32) How would you identify the SHARED_POOL_SIZE parameter that needs to be adjusted?

Answer: Below is the indications for the same:

Getting an ORA-04031 error.

Degrading the performance even when all the other parameters are already optimized.

Poor library cache/data dictionary hits.

Q #33) What do you understand by Row Chaining?

Answer: When a row is too large that it cannot fit in a block, then it will end up using consequent blocks which leads to the concept of Row Chaining.

It can be avoided by updating the storage parameters to an appropriate value.

Q #34) What is table partitioning and why is it required?

Answer: It is a process of dividing a table into smaller chunks so as to make data retrieval easy and quick. Each piece will be known as a partition

and can be accessed separately. Apart from tables, indexes can also be partitioned.

Q #35) How can we identify the resources for which the sessions are waiting?

Answer: We can find it out using v$session_waits and v$ system _waits.