• Parole

• Formatare afisare

SET LINES 100
SET PAGES 100

• Lista cu tabelele la care are acces userul curent:

 SELECT TABLE_NAME FROM user_tables; 

• Coloanele unui tabel:

 DESC <table_name>; DESCRIBE emp; 

• Verificarea constrangerilor existente:

--Method 1
SELECT * FROM user_constraints;
 
--Method 2
SELECT constraint_name, constraint_type, TABLE_NAME 
FROM user_constraints 
WHERE TABLE_NAME = 'EMP';
 
--Method 3
SELECT constraint_name, constraint_type, TABLE_NAME 
FROM user_constraints 
WHERE REGEXP_LIKE(TABLE_NAME, 'EMP|DEPT|SALGRADE');
 
--Method 4
SELECT * FROM user_cons_columns;

• Verificarea cheilor straine

SELECT
--current fk
CAST(a.TABLE_NAME AS VARCHAR(20)) TABLE_NAME, 
CAST(a.column_name AS VARCHAR(20)) COLUMN_NAME, 
CAST(a.constraint_name AS VARCHAR(20)) CONSTRAINT_NAME, 
CAST(c.owner AS VARCHAR(20)) OWNER, 
-- referenced pk
CAST(c.r_owner AS VARCHAR(20)) R_OWNER, 
CAST(c_pk.TABLE_NAME AS VARCHAR(20)) R_TABLE_NAME,
CAST(c_pk.constraint_name AS VARCHAR(20)) R_PK
FROM user_cons_columns a
JOIN user_constraints c ON a.owner = c.owner
                       AND a.constraint_name = c.constraint_name
JOIN user_constraints c_pk ON c.r_owner = c_pk.owner
                          AND c.r_constraint_name = c_pk.constraint_name
WHERE c.constraint_type = 'R'
AND a.TABLE_NAME = 'EMP';

• Formatare data(mai multe informatii aici)

-->verificare format
--a. Method 1
define _DATE
--b. Method 2
SELECT sysdate FROM dual;
 
-->modificare format
ALTER SESSION SET nls_date_format='DD-MON-YYYY';

• Verificare indecși

SELECT index_name, TABLE_NAME FROM user_indexes;