Shulou(Shulou.com)06/01 Report--

View information about tablespaces

SELECT TABLESPACE_NAME, BLOCK_SIZE,-- db_block_size size INITIAL_EXTENT FROM DBA_TABLESPACES

two。 Tablespace corresponds to file name and size

SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, ROUND (BYTES / (1024 * 1024), 0) TOTAL_SPACE FROM DBA_DATA_FILES ORDER BY TABLESPACE_NAME

3. View tablespace size and usage

SELECT UPPER (F.TABLESPACE_NAME) AS TABLESPACE_NAME,-- tablespace name D.TOTAL_BYTES AS TOTAL_BYTES,-- tablespace size M F.FREE_BYTES AS FREE_BYTES,-- idle size M D.TOTAL_BYTES-F.FREE_BYTES AS USEAGE_BYTES -- use size M TO_CHAR (ROUND ((D.TOTAL_BYTES-F.FREE_BYTES) / D.TOTAL_BYTES * 100,2), '990.99') AS USEAGE_PERCENT,-- use proportion F.MAX_BYTES AS BLOCK_MAX_BYTES-- largest BLOCK block FROM (SELECT TABLESPACE_NAME) ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_BYTES, ROUND (MAX (BYTES) / (1024 * 1024), 2) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT DD.TABLESPACE_NAME, ROUND (SUM (DD.BYTES) / (1024 * 1024)) 2) TOTAL_BYTES FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 4 DESC

4. View database version related information

SELECT PRODUCT,VERSION,STATUS FROM PRODUCT_COMPONENT_VERSION

5. View table size

Analyze table tablename compute statistics; SELECT NUM_ROWS * AVG_ROW_LEN FROM USER_TABLES WHERE TABLE_NAME = 'tablename'

6. View the SQL being executed

SELECT b.sid oracleID, b.username, b.machine, b.PROGRAM, b.serializations, spid, STATUS, paddr, sql_text, c.CPU_TIMEFROM v$process a, v$session b, v$sqlarea cWHERE a.addr = b.paddr AND b.sql_hash_value = c.hash_value

7. View the current table locked object

SELECT SESS.SID, SESS.SERIAL#, LO.ORACLE_USERNAME, LO.OS_USER_NAME, AO.OBJECT_NAME, LO.LOCKED_MODE, SESS.MACHINE, SESS.TERMINAL, SESS.LOGON_TIME FROM V$LOCKED_OBJECT LO, DBA_OBJECTS AO, V$SESSION SESS WHERE AO.OBJECT_ID = LO.OBJECT_ID AND LO.SESSION_ID = SESS.SID; ALTER SYSTEM KILL SESSION 'SID,SERIAL#';-kill the table locking process

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.