How to show all configuration in Oracle database

Showing all configuration in Oracle databases can be done in several ways.

Using v$parameter system table

Logging in as SYS or any account with sufficient privileges and run this command.

SELECT * FROM v$parameter;

To search specific parameter

SELECT * FROM v$parameter WHERE NAME LIKE 
Read the rest “How to show all configuration in Oracle database”

How to limit number of sessions per user in Oracle database

Limiting sessions per user (connection per user) is an important task of DBA so we can make sure that one arbitrary application with a specific credential can not overkill whole database.

Step 1: Enable database resource limit

By default, Oracle disable resource limit. You need … Read the rest “How to limit number of sessions per user in Oracle database”

Understanding Oracle Session vs Connection vs Process

The relationship between Oracle Session vs Connection and Process may be the most embarrassing to professional or season dba. But its really an important article, as a DBA, you should understand that clearly.

A connection is a physical circuit between you and the database.

A … Read the rest “Understanding Oracle Session vs Connection vs Process”

Understanding Hibernate database connection pool and fix SQLTransientConnectionException: HikariPool-1 – Connection is not available, request timed out after 30000ms.

It’s quite a long title, isn’t it? I recently has a question from some dev about SQLTransientConnectionException: HikariPool-1 – Connection is not available, request timed out after 30000ms. error that their application sometimes has had. After taking a few time with them to check their … Read the rest “Understanding Hibernate database connection pool and fix SQLTransientConnectionException: HikariPool-1 – Connection is not available, request timed out after 30000ms.”

How to check number of sessions of a user in Oracle

To calculate number of session per username with status and detail source address, original OS user in Oracle database, you can use this command.

SELECT USERNAME, STATUS, OSUSER|| '@' || MACHINE AS SOURCEINFO, COUNT(*) AS NUMOFCONN 
 FROM v$session 
 WHERE TYPE = 'USER'
 GROUP BY USERNAME, 
Read the rest “How to check number of sessions of a user in Oracle”

How to allow remote root access MariaDB 10.4 on Centos 7




By default, MariaDB not allow you access as root from anywhere but localhost. In order to allow remote root access to MariaDB, follow these steps.

Step 1: Accessing MariaDB on local

Use this command

sudo mysql -u root

Step 2: Grant access for root remotely

Read the rest “How to allow remote root access MariaDB 10.4 on Centos 7”

How to disable Oracle listener log.xml

It can become very big and it’s a trouble to your database. Here is how to check listener logging configuration and disable this setting.

$ lsnrctl
LSNRCTLshow log_status
 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
 LISTENER parameter "log_status" set to ON
 The command completed successfully

Here you can … Read the rest “How to disable Oracle listener log.xml”

ORA-03114: Not connect to Oracle while startup solutions

Recently, one of my Oracle databases can start mount but unable to open.

sqlstartup
...
Total System Global Area                              7629732 bytes Fixed Size                                              60324 bytes Variable Size                                         6627328 bytes Database Buffers                                       409600 bytes Redo Buffers                                           532480 bytes 
...
ORA-03114: Not connect to Oracle

The … Read the rest “ORA-03114: Not connect to Oracle while startup solutions”

How to add partitions to an existing table in Oracle database

When the data in the table becomes bigger, we’ll need partitioning in order to make our SQL command faster. But the thing is we already have data in that table. These queries will help you do that with just one execution step.

--create new partitioned 
Read the rest “How to add partitions to an existing table in Oracle database”

Oracle: “CREATE TABLE AS SELECT” (CTAS) with PARTITIONING and Column Default Value

This article guides you to write Oracle PL/SQL command for creating new table as select from other tables/views with partitioning and column default value.

First of all, this is the syntax of a simple CTAS:

CREATE TABLE TBLA AS SELECT * FROM TBLB;

Please note … Read the rest “Oracle: “CREATE TABLE AS SELECT” (CTAS) with PARTITIONING and Column Default Value”