Some practical DBA statements (continuous learning and continuous updating)

-- Query the lob size and tablespace
 

 Select A. table_name,
A. column_name,
B. segment_name,
B. segment_type,
B. tablespace_name,
 Round (B. bytes /   1024   /   1024   /   1024 ), 2 )
From User_lobs A, user_segments B
 Where A. segment_name = B. segment_name
 Order   By B. bytes Desc ;

-- Query the size of all tables in a tablespace
 Select Us. segment_name, US. segment_type, US. tablespace_name,
Us. tablespace_name,
 Round (US. bytes / 1024  /  1024  /  1024 ), 2 )
 From User_segments us
 Where Us. tablespace_name =   '' ;

-- Query the size of a specific table. You can change the query keyword.
 Select Segment_name,
Alt. Owner
Tablespace_name,
Segment_type,
 Round (Bytes /  1024  /  1024  /  1024 ), 2 ) As "Size (GB )"
 From User_segments, all_tables alt
 Where Segment_type=   '  Table  ' 
 And Segment_name Like   '  %  ' 
 And Alt. table_name = Segment_name
 Order   By Tablespace_name, bytes Desc ;

-- Query the usage of the current tablespace
 Select A. tablespace_name "tablespace name ",
Total /  1024  /  1024 Tablespace size,
Trunc (free /  1024  /  1024 ), 2 ) Remaining tablespace size,
Trunc (total - Free) /  1024  /  1024 ), 2 ) Tablespace size,
 Round (Total - Free) / Total, 4 ) *   100 "Usage % "
From ( Select Tablespace_name, Sum (Bytes) free
 From Dba_free_space
 Group   By Tablespace_name),
( Select Tablespace_name, Sum (Bytes) Total
 From Dba_data_files
 Group   By Tablespace_name) B
 Where A. tablespace_name = B. tablespace_name;

 
/* Query the lock table */select sess. sid, sess. serial #, Lo. oracle_username, Lo. OS _user_name, AO. object_name, Lo. locked_mode from V $ locked_object Lo, dba_objects AO, V $ session sess where AO. object_id = lo. object_id and Lo. session_id = sess. sid
 

/* 1 calculate the distinct_keys 3 of the index column in num_row 2 of the statistical data table to calculate the value of distinct_keys/num_rows. The closer this value is to 1, the higher the column selection degree, the efficiency of indexes is higher */analyze table schema. tablename compute statistics for all indexes for all columns; -- collect the latest information select ut. num_rows, UI. distinct_keys, round (UI. distinct_keys/UT. num_rows), 2) from user_indexes UI, user_tables ut where UI. table_name = upper ('tablename') and UI. index_name = upper ('indexname') and UT. table_name = UI. table_name;/* relatively stupid method, manual statistics, calculation */select count (*) from schema. tablename; -- calculates the total number of rows in the Table. Select distinct (ind_row) from schema. tablename; -- count the distinct value of the column where the index is located -- the result is calculated manually.
 

 
/* Query wait events */select event, sum (decode (wait_time, 0, 1, 0) "Current wait", sum (decode (wait_time, 0, 0, 1) "not waiting yet", count (*) "Total" from V $ session_wait group by event order by count (*) DESC; select. event, count (*) from V $ session_wait a group by. event,. wait_class # order by count (*) DESC;

 

--Query undo user informationSelectS. username, U. Name, S. Sid, S. Serial #FromV $TransactionT, V $ rollstat R, V $ rollname u, v $ session sWhereS. taddr=T. ADDRAndT. xidusn=R. USNAndR. USN=U. USNOrder ByS. Username;