V$SQL view has the following column and statistics data. select sql_txt from v$sql select s.username, s.sid, s.serial#,t.sql_text “Last SQL”from gv$session s, gv$sqlarea twhere s.sql_address =t.address ands.sql_hash_value =t.hash_value
SQL
Posted on:
Time Dimension – SQL Query
SELECT— DAY LEVELtime_id AS day_id,INITCAP(TO_CHAR(time_id,’fmMonth DD, YYYY’)) AS day_desc,INITCAP(TO_CHAR(time_id, ‘fmDAY’)) AS day_name,TO_NUMBER(TO_CHAR(time_id – 1, ‘D’)) AS day_of_week,TO_NUMBER(TO_CHAR(time_id, ‘DD’)) AS day_of_month,TO_NUMBER(TO_CHAR(time_id, ‘DDD’)) AS day_of_year,1 AS days_in_day, — MONTH LEVELTO_CHAR(time_id, […]
OBIEE
Posted on:
Export OBIEE reports in same excel with different sheets
To export the OBIEE report in same excel file and different sheets , create two data model and use page break in between then in the properties use […]
Uncategorized
Posted on:
Oracle DB Client Installation
Go to Oracle Linux x86-64 instant clients download page Download the matching client Install Set environment variables in your ~/.bash_profile Reload your .bash_profile by simply typing source ~/.bash_profile (suggested by jbass) or Log-out user and […]
SQL
Posted on:
Check the database user session details
v$session Using v$session you can list all sessionsidentify active sessions with STATUS=’ACTIVE’ and how long it was running LAST_CALL_ET see running sql with SQL_ID or SQL_ADDRIf session is […]
SQL
Posted on:
Exclude Days SQL Function
create or replace FUNCTION EXCLUDE_WEEKEDNDS (p_start_date IN date, p_end_date IN date)RETURN NUMBERISl_count NUMBER;BEGINl_count := 0;SELECT COUNT(1) INTO l_countFROM(WITH DATA AS(SELECT–to_date(trunc(p_start_date), ‘YYYY-MM-DD’) date1,–to_date(trunc(p_end_date), ‘YYYY-MM-DD’) date2p_start_date date1,p_end_date date2FROM dual)SELECT date1+LEVEL-1 […]
SQL
Posted on:
Oracle SQL Function with Examples
PL/SQL function is a reusable program unit stored as a schema object in the Oracle Database. The following illustrates the syntax for creating a function: CREATE [OR REPLACE] […]
LINUX
Posted on:
Opening Port in Firewall Linux
to add a new port to public zone $ sudo firewall-cmd – zone=public – permanent – add-port=9090/tcp firewall-cmd –permanent –zone=public –add-port=8080/tcp to reload the configuration for the changes […]