– ORACLE tömeges tábla kiajánlás:
— előzetes lekérdetés
select
‘GRANT SELECT ON klajosw.’||table_name||’ TO vendeg, uj_hasznalo GRANT OPTION;’
from user_tables
;
=>
set serveroutput on
spool ..\result\&1
—
GRANT SELECT ON klajosw.tbl_elso TO vendeg, uj_hasznalo WITH GRANT OPTION;
…
spool off
exit
–//Eredmény sql filebe irányitása és megfuttatás.
———————
– ORACLE saját tábla használó/elérő userek listázása:
select * from USER_TAB_PRIVS where owner=’KECSKEMETIL’;
=>
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY
vendeg klajosw INPUT_V1 klajosw DEBUG NO NO
———————-
/*=======================
Adatbázis linkek eldobása/törlése
=======================*/
declare v_script varchar2(32767);
begin
for i in(select db_link
from user_db_links
)
loop
v_script := ‘drop database link ‘||i.db_link;
execute immediate v_script;
end loop;
end
;
/
disconnect
;
exit
————————-
/*========================================================
Komlex minta
========================================================*/
drop table tt_0002743_sp purge
;
drop sequence tt_0002743_sp_seq
;
create table tt_0002743_sp
(tt_0002743_sp_id number(14)
,div2_termek_rk varchar2(30)
,div2_termek_id varchar2(30)
,div2_termek_nm varchar2(255)
,crt_usr varchar2(30)
,crt_dt date
,lstmd_usr varchar2(30)
,lstmd_dt date
) compress
;
create unique index indx_tt_0002743_sp_t_id
on tt_0002743_sp(div2_termek_id asc
)
;
grant all on tt_0002743_sp to public
;
create sequence tt_0002743_sp_seq
start with 1 –select nvl(max(tt_0002743_sp_id),1) from tt_0002743_sp
increment by 1
nocache
nocycle
;
— drop trigger t_0002743_sp_chngs
create or replace trigger t_0002743_sp_chngs
before insert or update
on mkr_0002743_sp
for each row
declare
begin
:new.lstmd_dt := sysdate;
:new.lstmd_usr := user;
if :old.crt_dt is null
then :new.crt_dt := sysdate;
:new.crt_usr := user;
end if;
end;
/
exit
—————–