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

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

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.


MySQL Statement

MySQL Drop Database DROP Database using MySQL Command Line Client Using the MySQL Command Line Interface (CLI), the DROP DATABASE command is...