Paraméteres változó használata (SQLdeveloper)
define a = 1;
select &a from dual;
undefine a;
This is bind (bind variables store data values for SQL and PL/SQL statements executed in the RDBMS; they can hold single values or complete result sets):
var x number;
exec :x := 10;
select :x from dual;
exec select count(*) into :x from dual;
exec print x;
SELECT ROWNUM NO FROM DUAL CONNECT BY LEVEL <= 10
.?
DEFINE szam_valt = 80
select '&1' as parameter_bekeres, &szam_valt as valami from dual
------------
---- 1. féle paraméteres lekérdezés (normál futás)
define valtozo = 4321;
select 20110501 into &valtozo from dual;
select &valtozo as ez_egy_szam from dual;
select '&&valtozo' as ez_egy_szam from dual;
undefine valtozo;
---- 2. féle paraméteres lekérdezés (procesként futás)
variable myid number
exec :myid := 4321
select :myid as szam_2 from dual;
---define start_value = 4; &lala
SELECT :start_value + LEVEL -1 n
FROM dual
CONNECT BY LEVEL <= :end_value - :start_value + 1
select distinct column_value from table(sys.odcinumberlist(1,1,2,3,3,4,4,5))
select * FROM table(sys.odcivarchar2list('A','B','C','D'))
select column_value
from table(sys.dbms_debug_vc2coll('One', 'Two', 'Three', 'Four'));
select column_value
from table(sys.dbms_debug_vc2coll(1,2,3,4));
SELECT LTRIM('<===>HELLO<===>', '=<>')as e1
,RTRIM('<===>HELLO<===>', '=<>') as e2
FROM dual;
create type number_tab is table of number;
select * from table (number_tab(1,2,3,4,5,6));
SELECT * --username,opname,target_desc,sofar,totalwork,message
FROM V$SESSION_LONGOPS
select * from v$sql_plan where SQL_ID ='dsn8x9b2bb3kj'
select * from v$sqlarea
select * from v$sqltext_with_newlines
---- ORA12
SELECT EV, NAP_EVBEN
FROM DATUM_D
ORDER BY NAP_EVBEN DESC
FETCH FIRST 5 ROWS ONLY;
--- ORA11
SELECT * FROM (
SELECT EV, NAP_EVBEN
FROM DATUM_D
ORDER BY NAP_EVBEN DESC
) WHERE ROWNUM <= 5;
---- ORA12
SELECT Id, Col1
FROM TableName
ORDER BY Id
OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY;
--- ORA11
SELECT Id,
Col1
FROM (SELECT Id,
Col1,
ROW_NUMBER() over (ORDER BY Id) RowNumber
FROM TableName)
WHERE RowNumber BETWEEN 21 AND 40
---- ORA12
SELECT Id, Col1
FROM TableName
ORDER BY Id
OFFSET 5 ROWS;
--- ORA11
SELECT Id,
Col1
FROM (SELECT Id,
Col1,
ROW_NUMBER() over (ORDER BY Id) RowNumber
FROM TableName)
WHERE RowNumber > 20
WITH generator ( VALUE ) AS (
SELECT 1 FROM DUAL
UNION ALL
SELECT VALUE + 1
FROM generator
WHERE VALUE < 10
)
SELECT VALUE
FROM generator;
CREATE INDEX ord_customer_ix ON orders (customer_id);
CREATE INDEX first_name_idx ON user_data (UPPER(first_name));
CREATE BITMAP INDEX
emp_bitmap_idx
ON index_demo (gender);
INSERT /*+append*/ INTO Employees
SELECT *
FROM Employees;
SELECT /*+ PARALLEL(8) */ first_name, last_name FROM employee emp;
SELECT /*+ PARALLEL(emp,8) */ first_name, last_name FROM employee emp;
SELECT /*+use_hash(e d)*/ *
FROM Employees E
JOIN Departments D ON E.DepartmentID = D.ID
(As explained in many places, "in a HASH join, Oracle accesses one table (usually the smaller of the joined results)
and builds a hash table on the join key in memory. It then scans the other table in the join (usually the larger one)
and probes the hash table for matches to it."
It is preferred against Nested Loops method when the tables are big, no indexes are at hand, etc.)
SELECT /*+ result_cache */ NUMBER FROM main_table;
(Oracle (11g and above) allows the SQL queries to be cached in the SGA and reused to improve performance. It
queries the data from cache rather than database. Subsequent execution of same query is faster because now the
data is being pulled from cache.
)
-- DBLINK
CREATE DATABASE LINK dblink_name
CONNECT TO remote_username
IDENTIFIED BY remote_password
USING 'tns_service_name';
The remote DB will then be accessible in the following way:
SELECT * FROM MY_TABLE@dblink_name;
SELECT ROWNUM NO FROM DUAL CONNECT BY LEVEL <= 10
SELECT username,opname,target_desc,sofar,totalwork,message FROM V$SESSION_LONGOPS
select * from v$sql_plan where SQL_ID ='dsn8x9b2bb3kj'
COLUMN percent FORMAT 999.99
SELECT sid, to_char(start_time,'hh24:mi:ss') stime,
message,( sofar/totalwork)* 100 percent
FROM v$session_longops
WHERE sofar/totalwork < 1
/
WITH generator ( VALUE ) AS (
SELECT 1 FROM DUAL
UNION ALL
SELECT VALUE + 1
FROM generator
WHERE VALUE < 10
)
SELECT VALUE
FROM generator;
Last/Latest Running SQL
-----------------------
set pages 50000 lines 32767
col "Last SQL" for 100
SELECT t.inst_id,s.username, s.sid, s.serial#,t.sql_id,t.sql_text "Last SQL"
FROM gv$session s, gv$sqlarea t
WHERE s.sql_address =t.address AND
s.sql_hash_value =t.hash_value
/
Current Running SQLs
--------------------
set pages 50000 lines 32767
col HOST_NAME for a20
col EVENT for a40
col MACHINE for a30
col SQL_TEXT for a50
col USERNAME for a15
select sid,serial#,a.sql_id,a.SQL_TEXT,S.USERNAME,i.host_name,machine,S.event,S.seconds_in_wait sec_wait,
to_char(logon_time,'DD-MON-RR HH24:MI') login
from gv$session S,gV$SQLAREA A,gv$instance i
where S.username is not null
-- and S.status='ACTIVE'
AND S.sql_address=A.address
and s.inst_id=a.inst_id and i.inst_id = a.inst_id
and sql_text not like 'select S.USERNAME,S.seconds_in_wait%'
/
Current Running SQLs
--------------------
set pages 50000 lines 32767
col program format a20
col sql_text format a50
select b.sid,b.status,b.last_call_et,b.program,c.sql_id,c.sql_text
from v$session b,v$sqlarea c
where b.sql_id=c.sql_id
/
Last/Latest Running SQL
-----------------------
set pages 50000 lines 32767
select inst_id,sample_time,session_id,session_serial#,sql_id from gv$active_session_history
where sql_id is not null
order by 1 desc
/
SQLs Running from longtime
--------------------------
alter session set nls_date_format = 'dd/mm/yyyy hh24:mi';
set pages 50000 lines 32767
col target format a25
col opname format a40
select sid
,opname
,target
,round(sofar/totalwork*100,2) as percent_done
,start_time
,last_update_time
,time_remaining
from
v$session_longops
/
Active Sessions running for more than 1 hour
---------------------------------------------
set pages 50000 lines 32767
col USERNAME for a10
col MACHINE for a15
col PROGRAM for a40
SELECT USERNAME,machine,inst_id,sid,serial#,PROGRAM,
to_char(logon_time,'dd-mm-yy hh:mi:ss AM')"Logon Time",
ROUND((SYSDATE-LOGON_TIME)*(24*60),1) as MINUTES_LOGGED_ON,
ROUND(LAST_CALL_ET/60,1) as Minutes_FOR_CURRENT_SQL
From gv$session
WHERE STATUS='ACTIVE'
AND USERNAME IS NOT NULL and ROUND((SYSDATE-LOGON_TIME)*(24*60),1) > 60
ORDER BY MINUTES_LOGGED_ON DESC;
Session details associated with SID and Event waiting for
---------------------------------------------------------
set pages 50000 lines 32767
col EVENT for a40
select a.sid, a.serial#, a.status, a.program, b.event,to_char(a.logon_time, 'dd-mon-yy hh24:mi') LOGON_TIME,to_char(Sysdate, 'dd-mon-yy-hh24:mi') CURRENT_TIME, (a.last_call_et/3600) "Hrs connected" from v$session a,v$session_wait b where a.sid in(&SIDs) and a.sid=b.sid order by 8;
Session details associated with Oracle SID
-------------------------------------------
set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
'Session Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*' Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid)
/
set head on
Checking for Active Transactions SID
------------------------------------
select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr;
Session details from Session longops
-------------------------------------
select inst_id,SID,SERIAL#,OPNAME,SOFAR,TOTALWORK,START_TIME,LAST_UPDATE_TIME, username from gv$session_longops;
Session details with SPID
-------------------------
select sid, serial#, USERNAME, STATUS, OSUSER, PROCESS,
MACHINE, MODULE, ACTION, to_char(LOGON_TIME,'yyyy-mm-dd hh24:mi:ss')
from v$session where paddr in (select addr from v$process where spid = '&spid')
/
To find Undo Generated For a given session
------------------------------------------
select username,
t.used_ublk ,t.used_urec
from gv$transaction t,gv$session s
where t.addr=s.taddr and
s.sid='&sid';
To list count of connections from other machines
------------------------------------------------
select count(1),machine from gv$session where inst_id='&inst_id' group by machine;
To get total count of sessions and processes
--------------------------------------------
select count(*) from v$session;
select count(*) from v$process;
select (select count(*) from v$session) sessions, (select count(*) from v$process) processes from dual;
To find sqltext thru sqladdress
-------------------------------
select sql_address from v$session where sid=1999;
select sql_text from v$sqltext where ADDRESS='C00000027FF00AF0' order by PIECE;
To find sqltext for different sql hashvalue
-------------------------------------------
select hash_value,sql_text from v$sql where hash_value in (1937378691,1564286875,
248741712,2235840973,2787402785)
To list long running forms user sessions
----------------------------------------
select s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) from
v$session s, v$process p where round(last_call_et/3600) >4 and action like '%FRM%' and
p.addr=s.paddr ;
To list inactive Sessions respective username
---------------------------------------------
SELECT username,count(*) num_inv_sess
FROM v$session
where last_call_et > 3600
and username is not null
AND STATUS='INACTIVE'
group by username
order by num_inv_sess DESC;
SELECT count(*) FROM v$session where last_call_et > 43200 and username is not null AND
STATUS='INACTIVE';
SELECT count(*) FROM v$session where last_call_et > 3600 and username is not null AND
STATUS='INACTIVE';
To find session id with set of SPIDs
------------------------------------
select sid from v$session, v$process where addr=paddr and spid in ('11555','26265','11533');
To find Sql Text given SQLHASH & SQLADDR
----------------------------------------
select piece,sql_text from v$sqltext where HASH_VALUE = &hash and ADDRESS ='&addr' order by piece;
select piece,sql_text from v$sqltext where ADDRESS ='&addr' order by piece;
SELECT /*+ parallel(c,2) */
* FROM sh.customers c ORDER BY
cust_first_name, cust_last_name, cust_year_of_birth
----
MERGE /*+ parallel(s) parallel(u) */ INTO sales s USING sales_updates u 3 ON (s.prod_id=u.prod_id AND s.cust_id=u.cust_id AND s.time_id=u.time_id 4 AND s.channel_id=u.channel_id AND s.promo_id = u.promo_id) 5 WHEN MATCHED THEN 6 UPDATE SET s.amount_sold =u.amount_sold, 7 s.quantity_sold=u.quantity_
CREATE DATABASE LINK aramis CONNECT TO user1 IDENTIFIED BY jelszó1
USING 'aramis';
--
CREATE DATABASE LINK aramis2 CONNECT TO user1 IDENTIFIED BY jelszó1
USING 'aramis.inf.elte.hu:1521/
=>
SELECT * FROM dolgozo d, osztaly@aramis o WHERE d.oazon = o.oazon;
Select TO_CHAR(current_timestamp,'
TO_CHAR(current_timestamp+10/
from dual;
=>
TIMESTAMP TIMESTAMP_PLUS_10SEC
2020-02-18 16:05:24 2020-02-18 16:05:34
---- DATE_FROM DATE_TO generálás:
select aa.SNAPSHOT_TABLE_NAME, aa.START_TS, nvl(aa.kovetkezo_datum, date'9999-12-31') as END_TS
, nvl(aa.kovetkezo_datum -numToDSInterval( 0.000001, 'second' ), date'9999-12-31') as END_TS2
, nvl(aa.kovetkezo_datum - interval '0.000001' second, date'9999-12-31') as END_TS3
from (
select EDW_IF_PROCESS_ID, SNAPSHOT_TABLE_NAME, START_TS,
LAG( START_TS, 1) OVER (PARTITION BY SNAPSHOT_TABLE_NAME ORDER BY START_TS DESC) as kovetkezo_datum,
END_TS
from ebh_tf_edw_if.tf_edw_etl_
) aa
-----
DECLARE
v_commit NUMBER := 0;
v_new varchar2(1000) := '';
BEGIN
FOR rec IN
(
SELECT 'Lajos' as "NEVEM", aa.* FROM all_tables aa where aa.OWNER like 'HN%'
) LOOP
--
BEGIN
FOR rec2 IN
(
SELECT * from all_tables WHERE 1= 1 and table_name = rec.table_name
) LOOP
--
v_commit := v_commit + 1;
DBMS_OUTPUT.PUT_LINE(rec.OWNER ||' - '|| rec2.table_name ||' -' || to_char(v_commit));
v_new := '';
--
END LOOP;
COMMIT;
END;
--
--
END LOOP;
COMMIT;
END;
Megjegyzések
Megjegyzés küldése