SQL statements commonly used in DBA Inspection

1. Check how many process there are in the current database

Select count (1) from v$process

two。 Check how many session (session=process*1.1) there are in the current database

Select count (1) from v$session

3. View the currently executed SQL statement

Select a.programmatic b.spidrec. Sqlgramtextauthorc.sqlplayid from v$session a _

Select a.* from v$sql a where a.sqabc8sgs2'-686nqabc8sgs2 is the sql_id of the above statement

4. View tablespace name and size

Select t.tablespace_name, round (SUM (bytes/ (1024 * 1024)), 0) ts_size from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name

-- displays the tablespace name and tablespace size, and how much M.

5. Calculate the usage of tablespace data files

Select a.tablespace_name, total, free, total-free as used from (select tablespace_name, sum (bytes) / 1024 as total from dba_data_files group by tablespace_name) a, (select tablespace_name, sum (bytes) / 1024 as free from dba_free_space group by tablespace_name) b where a.tablespace_name = b.tablespace_name

6. View the utilization of temporary tablespace

Select * from v$temp_space_header

7. View the number of sessions for the current oracle user

Select username,count (username) from v$session where username is not null group by username

8. Query which data file a row in the table is in

Select a _ | |'_'| | dbms_rowid.rowid_block_number (rowid) | |'_'| | dbms_rowid.rowid_row_number (rowid) as f_b_n from t where astat4.

A ROWID F_B_N

4 AAAUCYAABAAAXEpAAB 1 "94505" 1-1 represents the first data file, 94505 represents more or less blocks, and 1 indicates every 2 rows of data in the table, because each row is 0.

9. Find the id value of the user through sql_id

Select distinct USER_ID from dba_hist_active_sess_history where sql_id='44u0yksh46aq2'

10. Find users through user_id

Select user_id,username from dba_users where user_id=98