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 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”

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”

Oracle DECODE statement failing on RHEL but works on HPUX




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




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”

SQL Conditional SELECT with COALESCE (possible issue with grouping or table joins)




SQL Conditional SELECT with COALESCE (possible issue with grouping or table joins)

Apologies in advance for the length of the question:
I am working on a query to display data that will pull from different tables depending on the facility that is requesting the data.… Read the rest “SQL Conditional SELECT with COALESCE (possible issue with grouping or table joins)”

ORA-12899 value too large error while trying to drop a NUMBER column




ORA-12899 value too large error while trying to drop a NUMBER column

I am trying to drop a column from a table in Oracle using a statement like this:
ALTER TABLE “MY_TABLE” DROP COLUMN “ENABLED”;

but I am getting this error:
ORA-12899: value too large … Read the rest “ORA-12899 value too large error while trying to drop a NUMBER column”

select value from same row using case on table oracle




select value from same row using case on table oracle

I have a table like that in oracle:

Then I want to select customer_type and my new column named ‘customer_id’. If customer_type is ‘CORPORATE’ then ‘customer_id’ column will filled with shipper_id else if customer_type is … Read the rest “select value from same row using case on table oracle”