2012. május 11., péntek

Oracle SQL és PLSQL használata

--- kiajanlás
grant select tabla to public;

--- analizállás
Analyze Table tmp Estimate Statistics Sample 33 Percent;

--- táblák és mezök listája
select * from ALL_TAB_COLUMNS where column_name like '%SAJAT%'

--- SQL történet
select * from  dba_hist_sqlstat
select * from  dba_hist_snapshot


---hosszú futás idő
select * from   v$session_longops

----- select kikérése
select * from  v$sql

SELECT * FROM  v$sqlarea

select * from  v$sqltext
select * from  v$sqltext_with_newlines


select sesion.sid,
       sesion.username,
       optimizer_mode,
       hash_value,
       address,
       cpu_time,
       elapsed_time,
       sql_text
  from v$sqlarea sqlarea, v$session sesion
 where sesion.sql_hash_value = sqlarea.hash_value
   and sesion.sql_address    = sqlarea.address
   and sesion.username is not null
   and sesion.username like 'K%'
  
   SELECT SQL_FULLTEXT FROM GV$SQLAREA
   SELECT * FROM GV$SQLAREA where sql_text like 'Select%sajat%'


---- adatbázis használat / várható futásidő
SELECT   
username,
OPNAME,
message,
TO_CHAR (a.start_time, 'yyyy.mm.dd hh24:mi:ss') as started,
ROUND ((a.sofar / a.totalwork) * 100, 2) as complete,
TO_CHAR (TRUNC (a.time_remaining / 3600))
         || ':'
         || TO_CHAR (TRUNC (MOD (a.time_remaining, 3600) / 60), 'fm00')
         || ':'
         || TO_CHAR (TRUNC (MOD (MOD (a.time_remaining, 3600), 60)), 'fm00')
as timeremaining
FROM v$session_longops a
WHERE (sofar / totalwork) * 100 != 100
and totalwork != 0
and username like 'K%'
ORDER BY started DESC

  Párhuzaqmosítás

-- Futtatandó parancsban:
SELECT /*+PARALLEL(kl 3)*/ * FROM tabla_1 kl;

-- Táblák használat beállítása:
ALTER TABLE tabla_1 PARALLEL 3;

-- Munkamenet:
ALTER SESSION FORCE PARALLEL QUERY;
ALTER SESSION ENABLE PARALLEL DML;

-- Lekérdezésa beállításnál
SELECT name, value FROM v$parameter WHERE name IN ('parallel_max_servers', 'parallel_min_servers');

-- Függvény törzs/tartalom lekérdezés
select
  case when line=1 then 'create or replace ' else null end
||regexp_replace(text,'['||chr(10)||chr(13)||']+$') as SOROK
from all_source
where 1=1
and type='FUNCTION'
and owner='USER_NEV'
and name='F_USER_PRIVATE_FUNCTION'
order by line;

-- Jelszó csere 
alter user <username> identified by <new password>  replace <old password>

-- Szerkezet/leírás kikérése
describe tabla_1;

-- Különleges karakterek
SELECT ASCIISTR(CHR(128) || 'Udv' || CHR(255)) FROM dual;
SELECT ASCII('Z') FROM dual;
SELECT 'ol' || COMPOSE('e' || UNISTR('\0301')) FROM dual;  --- (Unicode szöveggé alakítás : COMPOSE)
SELECT CONVERT('Ä E Í O O A B C D E','US7ASCII','WE8ISO8859P1') FROM dual;
SELECT DECOMPOSE('ol' || COMPOSE('e' || UNISTR('\0301'))) FROM dual;
SELECT ASCII(SUBSTR('Valami szöveg',1,1)) FROM dual;

-- Formázás
SELECT TO_CHAR(SYSDATE, 'YYYY.MM.DD HH:MI:SS') FROM dual;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY.MON.DD HH24:MI:SS';
ALTER SESSION SET NLS_TERRITORY = 'AMERICA';
ALTER SESSION SET TIME_ZONE = '2:0';



set echo off
set feedback off
set heading off
set linesize 500
set numwidth 18
set pagesize 0
set trimspool on
set verify off

--set termout OFF
--ALTER SESSION SET NLS_DATE_FORMAT="DD-MON-YY";
alter session set NLS_DATE_FORMAT='YYYY.MM.DD HH24:MI:SS Dy'
ALTER SESSION SET NLS_LANGUAGE='AMERICAN';
ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,';
/
ALTER SESSION SET recyclebin = OFF;
/
insert into munk_log (sorszam,kezdes) values ('09',sysdate);
commit;
/


set serveroutput on size 30000;
exec DBMS_OUTPUT.PUT_LINE('Kezdődik most:');
begin
for rec in (select * from CALENDAR where YYYYMM ='202001')  loop
---    ebh_iwfs.iwfs_campaign_pkg.delete_campaign(day_num => rec.day_num);
   DBMS_OUTPUT.PUT_LINE('napok' || rec.day_num);
end loop;
end;
SET SERVEROUTPUT Off;

spool c:\tmp\kp_igeny.txt
/
spool off


select sysdate,
trunc(sysdate),
add_months(date'2020-01-01', -2),
months_between(date'2020-01-01', date'2020-05-05')
from dual


BEGIN
   dbms_output.put_line  (user || ' Tables in the database:');
   FOR t IN (SELECT table_name FROM user_tables)
   LOOP
      dbms_output.put_line(t.table_name);
   END LOOP;
END;
/
commit;
/
exit    ;