You can immediately understand the status of the system and the status of the system through the following SQL (only to people who are used to use SQL / PLUS huh) (1) Query Oracle's physical structure 1.1. View Data file SQL> SELECT NAME FROM V $ DataFile; Name --------------------------------------------- / u05 /dbf/prod/system_01.dbf/u05/dbf/prod/system_01.dbf (system data file) /u06/dbf/PrOd/temp_01.dbf (temporary data file) /u04/dbf/Prod/Users_01.dbf (user Data file) /u09/dbf/prod/rbs_01.dbf (rollback data file) /u06/dbf/Prod/applsys_indx_01.dbf (index data file) data file is the most important physical file in Oracle
1.2. Query Control File Information SQL> Select * from V $ ControlFile1.3 View log group SQL> Select * from v $ log1.4. View specific log member file SQL> SELECT * from V $ logfile1.5 Find alarm through the following SQL To the path of the file Select Value from V $ parameter where name = 'background_dump_dest'1.6 Find the path of the user tracking files through the following SQL SELECT VALUE from V $ parameter where name =' user_dump_dest '
(2) Query security information 2.1 Display user information SQL> SELELCT * FROM DBA_USERS WHEREE Username = 'system'2.2 Display role information SQL> SELECT * FROM DBA_ROLES 2.2 Display table space limit SQL> SELECT TABLESPACE_NAME, UserName, Bytes, Max_bytes SQL> From DBA_TS_QUOTAS; 2.4 Display Information SQL> SELECT Profile, Resource_name, Limit SQL> from DBA_PrOFiles; 2.5 Display All Table SQL> Select * from Tab; 2.6 View Internal System Table SQL SELECT * FROM V $ fixed_view_definition
3 Query Oracle System Environment Variables 3.1. Find global sharing buffers (SGA) SQL> SELECT * from V $ SGA; Name Value -------------------------------------------------------------------------------------------------------------------------------------------------------- ------ Fixed Size 39816 Variable Size 259812784 Database Buffers 1.049E 09 Redo Buffers 327680 More detailed information You can refer to V $ SGASTAT, V $ buffer_pool to save the SQL statement saved in the shared pool, can be from V $ SQLText , V $ SQLAREA query
3.2 Clear Share_Pool, keep Data Buffer SQL> ALTER System Flush Shared_Pool; System Altered.
3.3. Run the following statement to get the system SQL> SELECT MAX (KTuxescnw * Power (2, 32) KTuxescnb) SCN from x $ ktuxe; SCN -------- 310143.4 Query data block available space size. Select Kvisval, kvistag, kvisdsc from sys.x $ kvis; 3.5 Check the current Oracle connection type: SELECT Server from v $ session where audisid = uernv ('sessionid'); if you output a server ---------- - Dedicated
Note The current Oracle connection is a dedicated server connection, not the MTS multi-threaded server (Shared)
3.6. View data data dictionary SQL SELECT * FROM DICT
3.7 Check the Status of the Background Thread Select Name, Description from V $ BGPROCESS WHERE PADDR <> 00 '(Process Address is not equal to' 00 ') Output results Similar: Name description ------------ --------------------------- PMON Process Cleanup DBWO DB WRITER Process 0 LGWR Redo etc Ckpt Checkpoint. . . . . . 3.8 Display Table Structure DESC TABLENAME (arbitrary table name); output results Similar: Name Null? Type ---------------------------- ---------------------------------- ID NOT NULL NUMBER (30)
I hope that this article will play about a throwing jade, I hope you can share the important or common SQL commands you think below.
