本文主要向大家介绍了Oracle数据库之Oracle DBA 必须掌握的 查询脚本,通过具体的内容向大家展现,希望对大家学习Oracle数据库有所帮助。
1 ----通过 v$parameter数据字典来查询oracle标准数据块的大小。
2 SYS@orcl> startup
3 ORACLE instance started.
4
5 Total System Global Area 1221992448 bytes
6 Fixed Size 1344596 bytes
7 Variable Size 771754924 bytes
8 Database Buffers 436207616 bytes
9 Redo Buffers 12685312 bytes
10 Database mounted.
11 Database opened.
12 SYS@orcl> col name format a30;
13 SYS@orcl> col value format a20;
14 SYS@orcl> select name,value from v$parameter where name=‘db_block_size‘;
15
16 NAME VALUE
17 ------------------------------ --------------------
18 db_block_size 8192
19
20 SYS@orcl> show parameter db_block
21
22 NAME TYPE VALUE
23 ------------------------------------ ----------- ------------------------------
24 db_block_buffers integer 0
25 db_block_checking string FALSE
26 db_block_checksum string TYPICAL
27 db_block_size integer 81922:通过 dict 查看数据库中数据字典的信息 1 SYS@orcl> col table_name for a30;
2 SYS@orcl> col comments for a30;
3 SYS@orcl> select * from dict;
4
5 TABLE_NAME COMMENTS
6 ------------------------------ ------------------------------
7 DBA_CONS_COLUMNS Information about accessible c
8 olumns in constraint definitio
9 ns
10
11 DBA_LOG_GROUP_COLUMNS Information about columns in l
12 og group definitions
13
14 DBA_LOBS Description of LOBs contained
15 in all tables
16
17 DBA_CATALOG All database Tables, Views, Sy3 : 通过 v$fixed_view_definition 查看数据库中内部系统表的信息 1 SYS@orcl> col view_name format a15;
2 SYS@orcl> col view_definition format a30000;
3 SYS@orcl> select * from v$fixed_view_definition where rownum<=10;
4
5 VIEW_NAME VIEW_DEFINITION
6 ----------------------------------------------------------------------------------------------
7 GV$WAITSTAT select inst_id,decode(indx,1,‘data block‘,2,‘sort block‘,3,‘save undo block‘, 4,
8 ‘segment header‘,5,‘save undo header‘,6,‘free list‘,7,‘extent map‘, 8,‘1st level
9 bmb‘,9,‘2nd level bmb‘,10,‘3rd level bmb‘, 11,‘bitmap block‘,12,‘bitmap index b
10 lock‘,13,‘file header block‘,14,‘unused‘, 15,‘system undo header‘,16,‘system und
11 o block‘, 17,‘undo header‘,18,‘undo block‘), count,time from x$kcbwait where ind
12 x!=04:通过查询 dba_data_files 数据来了解Oracle系统的数据文件信息 1 [oracle@localhost ~]$ sqlplus / as sysdba;
2
3 SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 8 23:27:12 2016
4
5 Copyright (c) 1982, 2011, Oracle. All rights reserved.
6
7
8 Connected to:
9 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
10 With the Partitioning, OLAP, Data Mining and Real Application Testing options
11
12 SYS@orcl> col file_name format a50;
13 SYS@orcl> set linesize3000;
14 SYS@orcl> select file_name,tablespace_name from dba_data_files where rownum<=10;
15
16 FILE_NAME TABLESPACE_NAME
17 -------------------------------------------------- ------------------------------
18 /u01/app/oracle/oradata/orcl/users01.dbf USERS
19 /u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1
20 /u01/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX
21 /u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
22 /u01/app/oracle/oradata/orcl/example01.dbf EXAMPLE
23
24 SYS@orcl>
本文由职坐标整理并发布,希望对同学们学习Oracle有所帮助,更多内容请关注职坐标数据库Oracle数据库频道!