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”

ORA-65535: Oracle Database client cannot handle error code exceeding 65535. Actual error code and message follow:\n




Error code

ORA-65535: Oracle Database client cannot handle error code exceeding 65535. Actual error code and message follow:\n

Causes

Oracle Database clients earlier than release 12.1 could not handle error codes larger than 65535.

Solutions

Upgrade Oracle Database client library to release 12.1 or later.… Read the rest “ORA-65535: Oracle Database client cannot handle error code exceeding 65535. Actual error code and message follow:\n”

ORA-65532: cannot alter or drop automatically created indexes




Error code

ORA-65532: cannot alter or drop automatically created indexes

Causes

An attempt was made to alter or drop an automatically created index.

Solutions

Do not perform the action on an automatically created index.



References

Read the rest “ORA-65532: cannot alter or drop automatically created indexes”

ORA-65531: maximum level reached in index segment with object id string




Error code

ORA-65531: maximum level reached in index segment with object id string

Causes

The attempted operation resulted in the number of index levels exceeding the maximum allowed.

Solutions

Reduce the size of the relevant index, for example, by using compression or partitioning.



Read the rest “ORA-65531: maximum level reached in index segment with object id string”

ORA-65514: invalid LOB ENABLE STORAGE IN ROW value




Error code

ORA-65514: invalid LOB ENABLE STORAGE IN ROW value

Causes

An invalid value was specified for LOB ENABLE STORAGE IN ROW. The specified value for LOB ENABLE STORAGE IN ROW should be null, 4000, 8000, 16000 or 32000 for SECUREFILE LOBS in heap tables, … Read the rest “ORA-65514: invalid LOB ENABLE STORAGE IN ROW value”