Manage frequently used data dictionaries and dynamic performance views in Oracle

Manage common Oracle data dictionaries and dynamic performance views 1.v$ version query database version information

[sql] select * from v$version;  [html] BANNER  --------------------------------------------------------------------------------  Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production  PL/SQL Release 11.2.0.3.0 - Production  CORE    11.2.0.3.0  Production  TNS for Solaris: Version 11.2.0.3.0 - Production  NLSRTL Version 11.2.0.3.0 - Production  

 

2. v $ sgainfo

[sql] select name, bytes/1024/1024"Size(M)"from v$sgainfo;  [html] NAME                                Size(M)  -------------------------------- ----------  Fixed SGA Size                   2.12744903  Redo Buffers                     2.19921875  Buffer Cache Size                       300  Shared Pool Size                        216  Large Pool Size                           4  Java Pool Size                            4  Streams Pool Size                         4  Shared IO Pool Size                       0  Granule Size                              4  Maximum SGA Size                 816.328125  Startup overhead in Shared Pool  68.5404663  Free SGA Memory Available               284  

 

3. v $ parameter query initialization parameter information

[SQL] select name, valuefrom v $ parameterwhere name like '% pool %'; [html] NAME VALUE Comment comment shared_pool_size 0 large_pool_size 0 java_pool_size 0 streams_pool_size 0 limit 10905190 rows comment olap_page_pool _ Size 0 9 rows selected 4.v$ sysstat and v $ statnamev $ sysstat are statistical information, and v $ statname is used to display the statistical names parsed from v $ sesstat and v $ sysstat. For example, to determine the total transaction volume of the system, you can: [SQL] select. value as "TransactionCount" from v $ sysstat a, v $ statname B where. statistic # = B. statistic # and B. name = 'user commits '; [html] TransactionCount ---------------- 6954 5.v$ instance this v $ view shows the status of the current instance. For example, to determine the total system running time: [SQL] select (sysdate-startup_time) * 24*60*60 as seconds from v $ instance; [html] SECONDS ------- 34183 6.v$ session query session information. For example: [SQL] select username, sid, serial # from v $ session where username is not null; [html] username sid serial # ---------------------------- ---------- SYS 1 7 SYSMAN 32 165 SYSMAN 33 25 SYSMAN 35 55 SYS 37 1065 SYS 38 3205 SYSMAN 39 3 SYS 42 5039 SYSMAN 43 49 SYSMAN 44 97 SYSMAN 45 29 DBSNMP 46 95 SYSMAN 47 359 DBSNMP 48 893 SYSMAN 51 13 SYS 57 2345 7.v$ SQL query SQL-related information. As follows: [SQL] select SQL _text, parse_cils, loads, executions from v $ SQL where upper (SQL _text) like '... '; 8.v$ process and v $ bgprocess and background process information, such as: [SQL] select p. pid, p. spid, B. name, B. description from v $ process p, v $ bgprocess B where p. addr = B. paddr and B. paddr <> '00'; [html] pid spid name description ---------- ---------------------- ----- running 2 808 PMON process cleanup 3 810 PSP0 process spawner 0 4 812 VKTM Virtual Keeper of TiMe proce Ss 5 816 GEN0 generic0 6 818 DIAG diagnosibility process 7 820 DBRM DataBase Resource Manager 8 822 DIA0 diagnosibility process 0 9 824 MMAN Memory Manager 10 826 DBW0 db writer process 0 11 828 LGWR Redo etc. 12 830 CKPT checkpoint 13 832 SMON System Monitor Process 14 834 RECO distributed recovery 15 836 MMON Manageability Monitor Process 16 838 MMNL Manageability Monitor Process 2 20 852 ARC0 Rchival Process 0 21 854 ARC1 Archival Process 1 22 856 ARC2 Archival Process 2 23 858 ARC3 Archival Process 3 24 24 860 qmnc aq Coordinator pid spid name description ---------- issue ----- limit 25 874 CJQ0 Job Queue Coordinator 28 908 SMCO Space Manager Process 22 rows selected 9. dba_tablespaces [SQL] select tablespace_nam E, status, contents from dba_tablespaces; [html] TABLESPACE_NAME status contents tables ------------- system online permanent sysaux online permanent UNDOTBS1 online undo temp online temporary users online permanent example online permanent stu online permanent 7 rows selected 10. user_segments: view the segment information. For example: [SQL] select segment_name, segment_type, tablespace_name, bytes/1024 "Size (KB)", extents, blocks from user_segments where segment_name = 'dept' [html] SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME Size (KB) extents blocks blocks ------------------ tables ---------- ------------ dept table users 64 1 8 11. user_extents. For example: [SQL] select segment_name, tablespace_name, extent_id, bytes/1024 "Size (KB)", blocks from user_extents where segment_name = 'dept '; [html] SEGMENT_NAME TABLESPACE_NAME EXTENT_ID Size (KB) BLOCKS -------------------------------------------------------- ---------- ------------ dept users 0 64 8 12. dba_data_files. For example: [SQL] select file_id, file_name, tablespace_name from dba_data_files; [html] FILE_ID FILE_NAME TABLESPACE_NAME ------- invalid limit 4/u01/oradata/mike/users01.dbf USERS 3/u01/oradata/mike/Fig 2/u01/oradata/mike/SYSAUX 1 /u01/oradata/mike/system01.dbf SYSTEM 5/u01 /Oradata/mike/example01.dbf EXAMPLE 6/u01/oradata/mike/stu01.dbf STU 7/u01/oradata/mike/javasundotbs1 ows selected 13. dba_temp_files to view temporary table data file information. For example: [SQL] select file_id, file_name, tablespace_name from dba_temp_files; [html] FILE_ID FILE_NAME TABLESPACE_NAME ---------- orders ---------------------------- 1/u01/oradata/mike/temp01.dbf TEMP