Summary of common Oracle knowledge

Summary of common Oracle knowledge

1. I was so scared to death that I accidentally deleted all the database tables in the development database. The result shows that the following statement is restored to data 1 hour ago! Very simple.

Note: Use the Administrator to log on to the system:

Select * from table name as of timestamp sysdate-1/12 // query a table data two hours ago! Now that the data has been obtained two hours ago, let's continue to do it ..

What if the table is dropped ?? See the following:

Drop table name;

The database is restored after the table is deleted by mistake, but remember which table names are deleted.

Flashback table name to before drop;

2. query to obtain the lock in the current database and unlock it:

Lock Query

SELECT s. username,

Decode (l. type, 'TT', 'table lock ',

'Tx ', 'row lock ',

NULL) LOCK_LEVEL,

O. owner, o. object_name, o. object_type,

S. sid, s. serial #, s. terminal, s. machine, s. program, s. osuser

FROM v $ session s, v $ lock l, dba_objects o

WHERE l. sid = s. sid

AND l. id1 = o. object_id (+)

AND s. username is not null;

Unlock

Alter system kill session 'sid, serial ';

If not. Directly reverse the kill process kill-9 spid In the OS

ORA-28000: account locked

The user is automatically locked multiple times because of incorrect password input.

Solution: alter user user_name account unlock;

3. statements related to permission query for database users:

View All Users:

Select * from dba_user;

Select * from all_users;

Select * from user_users;

View user system permissions:

Select * from dba_sys_privs;

Select * from all_sys_privs;

10. select * from user_sys_privs;

View user object permissions:

Select * from dba_tab_privs;

Select * from all_tab_privs;

Select * from user_tab_privs;

View All roles:

20. select * from dba_roles;

View user roles:

Select * from dba_role_privs;

Select * from user_role_privs;

Several frequently used oracle views: note that the table name is in uppercase ....................

Query all user information in oracle

Select * from dba_user;

Query only users and passwords

Select username, password from dba_users;

Query current user information

Select * from dba_ustats;

Query view text that users can access

Select * from dba_varrays;

Query the text of all views in the database

Select * from dba_views;

Query all indexes

Select * from user_indexes;

Query all tables

Select * from user_tables;

Query all constraints

Select * from user_constraints;

Query all objects

Select * from user_objects;

View the statements being executed in the current database, and then you can continue to do many things, such as querying the execution plan.

(1) view sessions of related processes in the database

Select a. sid, a. serial #, a. program, a. status,

Substr (a. machine, 1, 20), a. terminal, B. spid

From v $ session a, v $ process B

Where a. paddr = B. addr

And B. spid = & spid;

(2). View locked objects and related sessions in the database

Select a. sid, a. serial #, a. username, a. program,

C. owner, c. object_name

From v $ session a, v $ locked_object B, all_objects c

Where a. sid = B. session_id and

C. object_id = B. object_id;

(3) view the SQL statement being executed by the relevant session

Select SQL _text from v $ sqlarea where address =

(Select SQL _address from v $ session where sid = & sid );

(1) view sessions of related processes in the database

Select a. sid, a. serial #, a. program, a. status,

Substr (a. machine, 1, 20), a. terminal, B. spid

From v $ session a, v $ process B

Where a. paddr = B. addr

And B. spid = & spid;

(2). View locked objects and related sessions in the database

Select a. sid, a. serial #, a. username, a. program,

C. owner, c. object_name

From v $ session a, v $ locked_object B, all_objects c

Where a. sid = B. session_id and

C. object_id = B. object_id;

(3) view the SQL statement being executed by the relevant session

Select SQL _text from v $ sqlarea where address =

(Select SQL _address from v $ session where sid = & sid );

Query the table structure: the table name is too large to write !!

Select t. COLUMN_NAME,

T. DATA_TYPE,

Nvl (t. DATA_PRECISION, t. DATA_LENGTH ),

Nvl (T. DATA_SCALE, 0 ),

C. comments

From all_tab_columns t, user_col_comments c

WhEre t. TABLE_NAME = c. table_name

And t. COLUMN_NAME = c. column_name

And t. TABLE_NAME = UPPER ('om _ EMPLOYEE_T ')

Order by t. COLUMN_ID

Row-column interchange:

SQL code

Create an example table:

Create table t_col_row (

Id int,

C1 VARCHAR2 (10 ),

C2 VARCHAR2 (10 ),

C3 VARCHAR2 (10 ));

Insert into t_col_row VALUES (1, 'v11', 'v21', 'v31 ');

Insert into t_col_row VALUES (2, 'v12', 'v22', NULL );

Insert into t_col_row VALUES (3, 'v13', NULL, 'v33 ');

Insert into t_col_row VALUES (4, NULL, 'v24', 'v34 ');

Insert into t_col_row VALUES (5, 'v15', NULL, NULL );

Insert into t_col_row VALUES (6, NULL, NULL, 'v35 ');

Insert into t_col_row VALUES (7, NULL );

COMMIT;

The following is column-to-row: A view is created.

CREATE view v_row_col

SELECT id, 'c1 'cn, c1 cv

FROM t_col_row

UNION ALL

SELECT id, 'c2 'cn, c2 cv

FROM t_col_row

UNION ALL

SELECT id, 'c3' cn, c3 cv FROM t_col_row;

The following is a vertical table without a null value:

CREATE view v_row_col_notnull

SELECT id, 'c1 'cn, c1 cv

FROM t_col_row

Where c1 is not null

UNION ALL

SELECT id, 'c2 'cn, c2 cv

FROM t_col_row

Where c2 is not null

UNION ALL

SELECT id, 'c3' cn, c3 cv

FROM t_col_row

Where c3 is not null;

SQL code

Create an example table:

Create table t_col_row (

Id int,

C1 VARCHAR2 (10 ),

C2 VARCHAR2 (10 ),

C3 VARCHAR2 (10 ));

Insert into t_col_row VALUES (1, 'v11', 'v21', 'v31 ');

Insert into t_col_row VALUES (2, 'v12', 'v22', NULL );

Insert into t_col_row VALUES (3, 'v13', NULL, 'v33 ');

Insert into t_col_row VALUES (4, NULL, 'v24', 'v34 ');

Insert into t_col_row VALUES (5, 'v15', NULL, NULL );

Insert into t_col_row VALUES (6, NULL, NULL, 'v35 ');

Insert into t_col_row VALUES (7, NULL );

COMMIT;

The following is column-to-row: A view is created.

CREATE view v_row_col

SELECT id, 'c1 'cn, c1 cv

FROM t_col_row

UNION ALL

SELECT id, 'c2 'cn, c2 cv

FROM t_col_row

UNION ALL

SELECT id, 'c3' cn, c3 cv FROM t_col_row;

The following is a vertical table without a null value:

CREATE view v_row_col_notnull

SELECT id, 'c1 'cn, c1 cv

FROM t_col_row

Where c1 is not null

UNION ALL

SELECT id, 'c2 'cn, c2 cv

FROM t_col_row

Where c2 is not null

UNION ALL

SELECT id, 'c3' cn, c3 cv

FROM t_col_row

Where c3 is not null;

Create an example table:

Create table t_col_row (

Id int,

C1 VARCHAR2 (10 ),

C2 VARCHAR2 (10 ),

C3 VARCHAR2 (10 ));

Insert into t_col_row VALUES (1, 'v11', 'v21', 'v31 ');

Insert into t_col_row VALUES (2, 'v12', 'v22', NULL );

Insert into t_col_row VALUES (3, 'v13', NULL, 'v33 ');

Insert into t_col_row VALUES (4, NULL, 'v24', 'v34 ');

Insert into t_col_row VALUES (5, 'v15', NULL, NULL );

Insert into t_col_row VALUES (6, NULL, NULL, 'v35 ');

Insert into t_col_row VALUES (7, NULL );

COMMIT;

The following is column-to-row: A view is created.

CREATE view v_row_col

SELECT id, 'c1 'cn, c1 cv

FROM t_col_row

UNION ALL

SELECT id, 'c2 'cn, c2 cv

FROM t_col_row

UNION ALL

SELECT id, 'c3' cn, c3 cv FROM t_col_row;

The following is a vertical table without a null value:

CREATE view v_row_col_notnull

SELECT id, 'c1 'cn, c1 cv

FROM t_col_row

Where c1 is not null

UNION ALL

SELECT id, 'c2 'cn, c2 cv

FROM t_col_row

Where c2 is not null

UNION ALL

SELECT id, 'c3' cn, c3 cv

FROM t_col_row

Where c3 is not null;

The following may be the oracle view frequently used by DBAs. Haha

SQL code

Example: If hash_value: 3111103299 is known, query the SQL statement:

Select * from v $ sqltext

Where hashvalue = '20140901'

Order by piece

View the SQL statements that consume the most resources:

SELECT hash_value, executions, buffer_gets, disk_reads, parse_cils

From v $ SQLAREA

WHERE buffer_gets> 0000000or disk_reads> 1000000

ORDERBY buffer_gets + 100 * disk_reads DESC;

View the resource consumption of an SQL statement:

SELECT hash_value, buffer_gets, disk_reads, executions, parse_cils

From v $ SQLAREA

WHERE hash_Value = 228801498AND address = hextoraw ('cbd8e4b0 ');

Query the dynamic execution plan of an SQL statement:

First, use the following statement to find the address and hash_code of the statement in the execution plan.

SELECT SQL _text, address, hash_value FROM v $ SQL t

Where (SQL _text like '% FUNCTION_T (large table name !) % ')

Then:

SELECT operation, options, object_name, cost FROM v $ SQL _plan

WHERE address = 'c00000016bd6d248 'AND hash_value = 664376056;

Query the oracle version:

Select * from v $ version;

Query database parameters:

Select * from v $ parameter

Find your session information

Select sid, OSUSER, USERNAME, MACHINE, PROCESS

From v $ session where audsid = userenv ('sessionid ');

Search for sessions when the machine knows

Select sid, OSUSER, USERNAME, MACHINE, TERMINAL

From v $ SESSION

WHERE terminal = 'pts/tl 'AND machine = 'rgmdbs1 ';

Queries the SQL statement currently running by a specified session. Assume that sessionID is 100

Select B. SQL _text

From v $ session a, v $ sqlarea B

Where a. SQL _hashvalue = B. hash_value and a. sid = 100

SQL code

Example: If hash_value: 3111103299 is known, query the SQL statement:

Select * from v $ sqltext

Where hashvalue = '20140901'

Order by piece

View the SQL statements that consume the most resources:

SELECT hash_value, executions, buffer_gets, disk_reads, parse_cils

From v $ SQLAREA

WHERE buffer_gets> 0000000or disk_reads> 1000000

ORDERBY buffer_gets + 100 * disk_reads DESC;

View the resource consumption of an SQL statement:

SELECT hash_value, buffer_gets, disk_reads, executions, parse_cils

From v $ SQLAREA

WHERE hash_Value = 228801498AND address = hextoraw ('cbd8e4b0 ');

Query the dynamic execution plan of an SQL statement:

First, use the following statement to find the address and hash_code of the statement in the execution plan.

SELECT SQL _text, address, hash_value FROM v $ SQL t

Where (SQL _text like '% FUNCTION_T (large table name !) % ')

Then:

SELECT operation, options, object_name, cost FROM v $ SQL _plan

WHERE address = 'c00000016bd6d248 'AND hash_value = 664376056;

Query the oracle version:

Select * from v $ version;

Query database parameters:

Select * from v $ parameter

Find your session information

Select sid, OSUSER, USERNAME, MACHINE, PROCESS

From v $ session where audsid = userenv ('sessionid ');

 

Search for sessions when the machine knows

Select sid, OSUSER, USERNAME, MACHINE, TERMINAL

From v $ SESSION

WHERE terminal = 'pts/tl 'AND machine = 'rgmdbs1 ';

Queries the SQL statement currently running by a specified session. Assume that sessionID is 100

Select B. SQL _text

From v $ session a, v $ sqlarea B

Where a. SQL _hashvalue = B. hash_value and a. sid = 100

Example: If hash_value: 3111103299 is known, query the SQL statement:

Select * from v $ sqltext

Where hashvalue = '20140901'

Order by piece

View the SQL statements that consume the most resources:

SELECT hash_value, executions, buffer_gets, disk_reads, parse_cils

From v $ SQLAREA

WHERE buffer_gets> 0000000or disk_reads> 1000000

ORDERBY buffer_gets + 100 * disk_reads DESC;

View the resource consumption of an SQL statement:

SELECT hash_value, buffer_gets, disk_reads, executions, parse_cils

From v $ SQLAREA

WHERE hash_Value = 228801498AND address = hextoraw ('cbd8e4b0 ');

Query the dynamic execution plan of an SQL statement:

First, use the following statement to find the address and hash_code of the statement in the execution plan.

SELECT SQL _text, address, hash_value FROM v $ SQL t

Where (SQL _text like '% FUNCTION_T (large table name !) % ')

Then:

SELECT operation, options, object_name, cost FROM v $ SQL _plan

WHERE address = 'c00000016bd6d248 'AND hash_value = 664376056;

Query the oracle version:

Select * from v $ version;

Query database parameters:

Select * from v $ parameter

Find your session information

Select sid, OSUSER, USERNAME, MACHINE, PROCESS

From v $ session where audsid = userenv ('sessionid ');

Search for sessions when the machine knows

Select sid, OSUSER, USERNAME, MACHINE, TERMINAL

From v $ SESSION

WHERE terminal = 'pts/tl 'AND machine = 'rgmdbs1 ';

Queries the SQL statement currently running by a specified session. Assume that sessionID is 100

Select B. SQL _text

From v $ session a, v $ sqlarea B

Where a. SQL _hashvalue = B. hash_value and a. sid = 100

Tree Structure connect by sorting:

SQL code

Query the tree data structure and sort the data in a layer.

SELECT last_name, employee_id, manager_id, LEVEL

FROM employees

Start with employee_id = 100

Connect by prior employee_id = manager_id

Order siblings by last_name;

The following are the query results.

LAST_NAME EMPLOYEE_ID MANAGER_ID LEVEL

--------------------------------------------------------

King 100 1

Cambrault 148 100 2

Batelasticsearch 172 148 3

Bloom 169 148 3

Fox 170 148 3

Kumar 173 148 3

Ozer 168 148 3

Smith 171 148 3

De Haan 102 100 2

Hunold 103 102 3

Austin 105 103 4

Ernst 104 103 4

Lorentz 107 103 4

Pataballa 106 103 4

Errazuriz 147 100 2

Ande 166 147 3

Banda 167 147 3

SQL code

Query the tree data structure and sort the data in a layer.

SELECT last_name, employee_id, manager_id, LEVEL

FROM employees

Start with employee_id = 100

Connect by prior employee_id = manager_id

Order siblings by last_name;

The following are the query results.

LAST_NAME EMPLOYEE_ID MANAGER_ID LEVEL

--------------------------------------------------------

King 100 1

Cambrault 148 100 2

Batelasticsearch 172 148 3

Bloom 169 148 3

Fox 170 148 3

Kumar 173 148 3

Ozer 168 148 3

Smith 171 148 3

De Haan 102 100 2

Hunold 103 102 3

Austin 105 103 4

Ernst 104 103 4

Lorentz 107 103 4

Pataballa 106 103 4

Errazuriz 147 100 2

Ande 166 147 3

Banda 167 147 3

Query the tree data structure and sort the data in a layer.

SELECT last_name, employee_id, manager_id, LEVEL

FROM employees

Start with employee_id = 100

Connect by prior employee_id = manager_id

Order siblings by last_name;

The following are the query results.

LAST_NAME EMPLOYEE_ID MANAGER_ID LEVEL

--------------------------------------------------------

King 100 1

Cambrault 148 100 2

Batelasticsearch 172 148 3

Bloom 169 148 3

Fox 170 148 3

Kumar 173 148 3

Ozer 168 148 3

Smith 171 148 3

De Haan 102 100 2

Hunold 103 102 3

Austin 105 103 4

Ernst 104 103 4

Lorentz 107 103 4

Pataballa 106 103 4

Errazuriz 147 100 2

Ande 166 147 3

Banda 167 147 3

Sometimes when I write more things, I forget the most basic SQL syntax. All the following are written. The basic oracle statements can be found here. Is a basic statement!

SQL code

Query constraints in data dictionary:

SELECT constraint_name, constraint_type, search_condition

FROM user_constraints WHERE table_name = 'ployees ';

// The table names here are all uppercase!

2. Describe the table structure:

Desc Tablename

3. View tables under the user

Select table_name from user_tables;

4. Check if the constraint is created on that column:

SELECT constraint_name, column_name

FROM user_cons_columns

WHERE table_name = 'ployees ';

10. Search for the column names associated with the constraints in a specific table using the variables:

Select constraint_name, column_name from user_cons_columns where table_name = '& tablename'

12. query the data dictionary to view the intermediate elements:

SELECT object_name, object_type

FROM user_objects

WHERE object_name LIKE 'emp' %'

OR object_name LIKE 'dept %'

14 query object type:

Select distinct object_type FROM user_objects;

17 Change Object Name: (Table Name, view, sequence)

Rename emp to emp_newTable

18 Add a table comment:

Comment on table employees IS 'employee information ';

20. view the view structure:

Describe view_name

23 view information in the data dictionary:

Select viewe_name, text from user_views

25. view the sequence in the data dictionary:

Select * from user_sequences

33. Get the names of all time zones:

Select * from v $ timezone_names

34. display the time zone offset of 'us/Eastern '.

Select TZ_OFFSET ('us/Eastern ') from DUAL -- dual'

Display the current date and time in the current session time zone:

Alter session set NLS_DATE_FORMAT = 'dd-MON-YYYY HH24: MI: ss'; -- modify the setting of the display time Method

Alter session set TIME_ZONE = '-'; -- modify the time zone

Select sessiontimezone, CURRENT_DATE from dual; -- a really useful statement!

SELECT CURRENT_TIMESTAMP from dual; -- the returned time is the current date and time, containing the time zone

SELECT CURRENT_TIMESTAMP from dual; -- the returned time is the current date and time, excluding the time zone !!!

35. display the values of the database time zone and session time zone:

Select datimezone, sessiontimezone from dual;

13 common table creation statements:

Create table dept

(Deptno NUMBER (2 ),

Dname VARCHAR2 (14 ),

Loc VARCHAR2 (13 ));

15 create a table using a subquery:

Create table dept80

As select employee_id, last_name,

Salary * 12 ANNSAL,

Hire_date FROM employees WHERE department_id = 80;

6. add a column: // alter table EMP add column (dept_id number (7); error !!

Alter table EMP add (dept_id number (7 ));

7. delete a column:

Alter table emp drop column dept_id;

8. Add both column names and constraints:

Alter table EMP add (dept_id number (7)

Constraint my_emp_dept_id_fk references dept (ID ));

9. Change the column: // note that the constraint cannot be modified !!

Alter table dept80 modify (last_name varchar2 (30); // here we use modify instead of alter!

24 add a line:

Insert into table_name values ();

5. Add a primary key:

Alter Table EMP add constraint my_emp_id_pk primary key (ID );

11 Add a new column with check constraints:

Alter table EMP

Add (COMMISSION number (2) constraint emp_commission_ck check (commission> 0 ))

16. delete a table:

Drop table emp;

19 create a view:

Create view empvu80

As select employee_id, last_name, salary

FROM employees WHERE department_id = 80;

21. Delete A View:

Drop view view_name

22. Find the top 5 employees. (Top-n Analysis) (In-row view)

Select rownum, employee_id from (select employee_id, salary from

Employees order by salary desc)

Where rownum <5;

26 create Synonyms:

Create synonym name for original name

Or create public synonym name for the original name

27 create a sequence: (Note that the sequence in the table is not shown here !!)

Create sequence dept_deptid_seq

Increment by 10

Start with 120

Max value 9999

NOCACHE

NOCYCLE

28 use sequence:

Insert into dept (ID, NAME) values (DEPT_ID_SEQ.nextval, 'admin ');

29 create an index: // by default, the index is a nonunique index, unless the Keyword: unique is used.

Create index emp_last_name_idx ON employees (last_name );

30. Create a user: (errors may occur. For more information, see help)

Create user username (user name)

Identified by oracle (password)

Default tablespace data01 (the tablespace name // exists in the system tablespace by default)