Some useful DBA statements from Oracle

--Query the size and table space of the LOB select A.table_name, A.column_name, B.segment_name, B.segment_type, B.tablespace_n AME, round ((b.bytes/1024/1024/1024), 2) from User_lobs A, user_segments bwhere a.segment_name = b.segment_name ORDER by b.bytes desc;--query queries the size of all tables on 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 certain tables, 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 usage of the current tablespace select A.tablespace_name "tablespace name", Tota l/1024/1024 tablespace size, trunc ((free/1024/1024), 2) Table space remaining size, trunc (((total-free)/1024/1024), 2) tablespace usage size, ROUND ((total-free)/Total, 4) * 100 "Utilization%" FROM (SELECT tablespace_name, SUM (bytes) free from Dba_free_space GROUP by Tablespace_name) A, (Selec T tablespace_name, SUM (bytes) Total from Dba_data_files GROUP by Tablespace_name) b WHERE A.tablespace_na me = B.tablespace_name;? /* Query lock table case */select Sess.sid, sess.serial#, Lo.oracle_username, Lo.os_user_name, Ao.object_name, Lo.locke D_mode from V$locked_object Lo, dba_objects ao, v$session sess where ao.object_id = lo.object_id and Lo.sessio n_id = Sess.sid?/*1 Statistics Table Num_row 2 Statistical Index column Distinct_keys 3 calculates the value of Distinct_keys/num_rows, the closer to 1, the higher the column selection, the more efficient the index is. */an Alyze 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_nam E /* More stupid method, pure manual statistics, calculate */select count (*) from ScheMa.tablename; --Statistics out the total number of rows in the table select DISTINCT (ind_row) from Schema.tablename; --the distinct value of the column in which the index is counted--is then calculated purely by hand?/* Query waits for events */select event, sum (Decode (wait_time, 0, 1, 0)) "Current Wait", Sum (Decode (wai  T_time, 0, 0, 1)) "Not currently waiting", COUNT (*) "Total" from v$session_wait GROUP by event ORDER by Count (*) desc; Select A.event, COUNT (*) from v$session_wait a group by a.event, a.wait_class# ORDER by Count (*) Desc;<br><br&gt ;--query about users that occupy undo select S.username, U.name, S.sid, S.serial#from v$transaction T, V$rollstat R, V$rollname U, V$session S 　　WHERE s.taddr = T.addrand T.xidusn = R.usnand R.usn = U.usnorder by s.username;   --Lock and Unlock account statement alter user username accounts lock;   Alter user username account unlock;   --Modify user password, if encountering ORA-28001 error can also resolve alter user username identified by password; --Manually expand a data file ALTER DATABASE datafile '/DATA/DATA_FILE1.DBF ' resize 10G;?

Some useful DBA statements from Oracle