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”
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”
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”
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.”
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
WHERE TYPE = 'USER'
GROUP BY USERNAME,
… Read the rest “How to check number of sessions of a user in Oracle”
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.
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”
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”
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”
Oracle DECODE statement failing on RHEL but works on HPUX
I’ve isolated a very specific code piece that works on our HP-UX Oracle 11.2 environment, but fails on our RHEL 7.1 Oracle 11.2 environment. Any pointers as to why this would happen?
Everything else (except … Read the rest “Oracle DECODE statement failing on RHEL but works on HPUX”
SQL insert into select from – insert the id instead of the data
I need to populate my fact table with data from lds_placement table. I have selected the records and here is what it looks like:
fk1_account_id | fk3_job_role_id | salary | no_of_placements | … Read the rest “SQL insert into select from – insert the id instead of the data”