Listowanie uprawnień do tabel/sekwencji
Maj 21, 2013 — 11:16
SELECT grantee, owner, table_name, select_priv, insert_priv, delete_priv, update_priv, references_priv, alter_priv, index_priv, 'revoke select on ' || table_name || ' from ' || grantee || ';'
FROM table_privileges
WHERE owner = '<owner>'
ORDER BY owner, table_name;
Dodanie pliku do tablespace
Marzec 25, 2013 — 13:15
--ile zostało
SELECT file_name, bytes, autoextensible, maxbytes FROM dba_data_files;

--dodanie kolejnego pliku, rozwiazuje problem braku miejsca
ALTER TABLESPACE USERS ADD DATAFILE 'd:\app\oracle\oradata\orcl\USERS02.DBF' SIZE 500M autoextend on next 100M;

--stworzenie oraz ustawienie nowego
CREATE TABLESPACE aps DATAFILE 'd:\app\oracle\oradata\orcl\aps01.dbf' size 500M autoextend on next 100M;

ALTER USER ap1 DEFAULT TABLESPACE aps;

select file_name, tablespace_name from dba_data_files ;

DROP TABLESPACE aps INCLUDING CONTENTS;
Oracle – wyłączenie wygasania hasła
Marzec 25, 2013 — 12:50
alter profile default limit password_life_time unlimited;
Klucze obce do tabeli
Grudzień 12, 2012 — 23:06
SELECT ac.owner,
 ac.constraint_name,
 ac.constraint_type,
 ac.table_name,
 ucc.column_name,
 ac.r_owner,
 ac.r_constraint_name
FROM user_constraints ac
JOIN user_cons_columns ucc
ON ucc.constraint_name = ac.constraint_name
AND ucc.table_name = ac.table_name
WHERE constraint_type = 'R'
AND r_constraint_name IN
 (SELECT constraint_name
 FROM all_constraints
 WHERE constraint_type IN ('P','U')
 AND table_name = upper('table_name')
 );