Prerequisites An understanding of Autonomous Database and Database Actions Page. After you have successfully connected to MSSQL Server using SQL Developer, you can open the query window and execute commands.
Uncategorized
Posted on:
SQL History
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, […]
SQL
Posted on:
Update a column with sequential number
To get sequence number use the below query. select ROW_NUMBER() OVER (ORDER BY COLUMN_NAME) AS seq,A.* from TABLE_NAME A To Update sequential number in column MERGE INTO TABLE_NAME […]
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 […]