This shows you the differences between two versions of the page.
bd:resurse:comenzi_utile [2019/03/06 11:10] fbratiloveanu created |
bd:resurse:comenzi_utile [2019/04/25 10:33] (current) maria_luiza.serban |
||
---|---|---|---|
Line 1: | Line 1: | ||
==== Comenzi Utile ==== | ==== Comenzi Utile ==== | ||
+ | |||
+ | * Parole | ||
+ | |||
+ | <note warning> | ||
+ | **11g** | ||
+ | |||
+ | User: stud1 | ||
+ | |||
+ | Pass: student | ||
+ | |||
+ | **12c** | ||
+ | |||
+ | User: c##stud1 | ||
+ | |||
+ | Pass: student | ||
+ | |||
+ | </note> | ||
+ | * Formatare afisare | ||
+ | <code sql> | ||
+ | set lines 100 | ||
+ | set PAGES 100 | ||
+ | </code> | ||
+ | * Lista cu tabelele la care are acces userul curent: | ||
+ | <code sql> select table_name from user_tables; </code> | ||
+ | * Coloanele unui tabel: | ||
+ | <code sql> desc <table_name>; describe emp; </code> | ||
+ | * Verificarea constrangerilor existente: | ||
+ | <code sql> | ||
+ | --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; | ||
+ | </code> | ||
+ | |||
+ | * Verificarea cheilor straine | ||
+ | <code sql> | ||
+ | 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'; | ||
+ | </code> | ||
+ | * Formatare data(mai multe informatii [[https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_commands_1029.htm#OLADM780|aici]]) | ||
+ | <code sql> | ||
+ | -->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'; | ||
+ | </code> | ||
+ | |||
+ | * Verificare indecși | ||
+ | <code sql> | ||
+ | select index_name, table_name from user_indexes; | ||
+ | </code> | ||
+ | |||
+ |