JMU Oracle实验三
前缀
补充一下老师问的问题
- 物理结构有哪些，分别查看一下
- 逻辑结构有哪些，分别查看一下
- 还有一个关于用户的，我忘了hh
1. 简单了解Oracle数据库具有哪些数据字典和动态性能视图。
- 数据字典：数据字典是Oracle数据管理系统的核心，它存储整个Oracle数据库的所有数据定义信息，如数据库的物理存储结构和逻辑存储结构，存储空间的分配使用情况数据库内的对象及其约束，以及用户、角色、权限设置等。Oracle数据字典由以下两种对象类型组成：
- 基表：大部分数据是以加密格式存储的，用户不应该直接访问基表。
- 用户访问视图：这些视图基于数据字典基表而创建，它们汇总数据字典基表内的信息，以可读的方式提供给用户使用。
- SYS用户拥有所有数据字典基表和用户访问视图，为了便于访问，Oracle为大部分视图创建了同名的public同义词。数据字典存储在system表空间内。
- 三组常用的数据字典视图：USER_，ALL_，DBA_
SQL> desc dict # 查看数据字典的结构名称 是否为空? 类型----------------------------------------- -------- ----------------------------TABLE_NAME VARCHAR2(128)COMMENTS VARCHAR2(4000)SQL> select * from dict; # 查看数组字典中所有用户视图的名称及其描述
# 修改格式
SQL> column TABLE_NAME format a35
SQL> column COMMENTS format a35
# 很多，只截取了一点点
TABLE_NAME COMMENTS
----------------------------------- -----------------------------------ional tablesUSER_OBJECT_TABLES Description of the user’s own object tablesUSER_ALL_TABLES Description of all object and relational tables owned by the user‘sALL_TABLES Description of relational tables accessible to the user
- 动态性能视图：Oracle数据库运行过程中维护了一套动态性能视图，用于记录数据库的当前活动，管理员在进行会话管理、备份操作和性能调优时必须要使用它们。这些视图之所以被称之为动态性能视图，是因为在实例运行和数据库使用过程中，它们的内容不断地更新，而且其内容也主要与性能有关。与动态性能视图相关的对象分为以下三种:
- 基表:名称前缀为X$。它们不存储在数据库中，而是构建在0racle实例的内存结构内，所以又被称作虚拟表。普通用户不能直接访问X$表;
- 视图:基于X$表创建的动态性能视图,它们的名称前缀是V_$，又被称作V$视图。只有sys用户才能访问该视图。动态性能视图的结构定义及其基表中的数据都不能由用户修改，因此动态性能视图又被称作固定表;
- 同义词:Oracle为V$视图创建了public同义词，这些同义词的名称前缀为V$。数据库管理员和其他用户应该通过这些同义词访问V$视图，而不是直接访问V_$对象。
SQL> desc v$fixed_table # 查看动态性能视图名称 是否为空? 类型----------------------------------------- -------- ----------------------------NAME VARCHAR2(128)OBJECT_ID NUMBERTYPE VARCHAR2(5)TABLE_NUM NUMBERCON_ID NUMBERSQL> select NAME,TYPE from v$fixed_table;# where rownum <= 10;NAME TYPE
---------- ----------
X$KQFTA TABLE
X$KQFVI TABLE
X$KQFVT TABLE
X$KQFDT TABLE
X$KQFCO TABLE
X$KQFOPT TABLE
X$KYWMPCTA TABLE
BX$KYWMWRCT TABLE
ABNAME TYPE
---------- ----------X$KYWMCLTA TABLE
BX$KYWMNF TABLE
2. 使用数据字典或性能视图查询数据库的物理存储结构。
物理存储结构：控制文件，数据文件，重做日志文件。
- 控制文件：Oracle为了管理数据库的状态而维护的一个文件，记录了数据库的物理存储结构和其他控制信息。
SQL> desc v$controlfile # 查看控制文件结构信息名称 是否为空? 类型----------------------------------------- -------- ----------------------------STATUS VARCHAR2(7)NAME VARCHAR2(513)IS_RECOVERY_DEST_FILE VARCHAR2(3)BLOCK_SIZE NUMBERFILE_SIZE_BLKS NUMBERCON_ID NUMBERSQL> select name from v$controlfile;# 查看控制文件名
NAME
--------------------------------------------------------------------------------
D:\\ORACLE\\ORADATA\\ORCL\\CONTROL01.CTL
D:\\ORACLE\\ORADATA\\ORCL\\CONTROL02.CTLSQL> show parameter control_files; # 通过初始化参数查询控制文件。是show parameter的一个小技巧，可以用于模糊查询NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
control_files string D:\\ORACLE\\ORADATA\\ORCL\\CONTROL01.CTL, D:\\ORACLE\\ORADATA\\ORCL\\CONTROL02.CTL
SQL> select name from v$controlfile; # 和上面一样
NAME
----------------------------------------
D:\\ORACLE\\ORADATA\\ORCL\\CONTROL01.CTL
D:\\ORACLE\\ORADATA\\ORCL\\CONTROL02.CTL
- 数据文件：存储表和索引数据，已经排序和散列等操作的中间结果。一个数据库包含一个或多个数据文件，一个数据文件只属于一个数据库。
SQL> desc dba_data_files # 查看数据文件结构信息名称 是否为空? 类型----------------------------------------- -------- ----------------------------FILE_NAME VARCHAR2(513)FILE_ID NUMBERTABLESPACE_NAME VARCHAR2(30)BYTES NUMBERBLOCKS NUMBERSTATUS VARCHAR2(9)RELATIVE_FNO NUMBERAUTOEXTENSIBLE VARCHAR2(3)MAXBYTES NUMBERMAXBLOCKS NUMBERINCREMENT_BY NUMBERUSER_BYTES NUMBERUSER_BLOCKS NUMBERONLINE_STATUS VARCHAR2(7)LOST_WRITE_PROTECT VARCHAR2(7)SQL> select FILE_NAME from dba_data_files;# 查看数据文件名FILE_NAME
--------------------------------------------------------------------------------
D:\\ORACLE\\ORADATA\\ORCL\\SYSTEM01.DBF
D:\\ORACLE\\ORADATA\\ORCL\\SYSAUX01.DBF
D:\\ORACLE\\ORADATA\\ORCL\\UNDOTBS01.DBF
D:\\ORACLE\\ORADATA\\ORCL\\USERS01.DBF
- 重做日志文件：存储数据库的重做日志信息，记录数据库所产生的所有变化信息。
- v$log详细说明了重做日志文件的结构、属性以及动态变化情况。
- status是group的状态，current：正在使用，inactive：没有使用
- v$logfile说明了重做日志文件的物理地址和当前在线状态
- status指的是member的状态 ，blank：整除都是空，不管是否背斜，stale：备用状态
- v$log详细说明了重做日志文件的结构、属性以及动态变化情况。
SQL> desc v$log名称 是否为空? 类型----------------------------------------- -------- ----------------------------GROUP# NUMBERTHREAD# NUMBERSEQUENCE# NUMBERBYTES NUMBERBLOCKSIZE NUMBERMEMBERS NUMBERARCHIVED VARCHAR2(3)STATUS VARCHAR2(16)FIRST_CHANGE# NUMBERFIRST_TIME DATENEXT_CHANGE# NUMBERNEXT_TIME DATECON_ID NUMBERSQL> select GROUP#,MEMBERS,status from v$log;GROUP# MEMBERS STATUS
---------- ---------- --------------------------------1 1 INACTIVE2 1 INACTIVE3 1 CURRENTSQL> select GROUP#,MEMBER,status from v$logfile;GROUP# MEMBER STATUS
---------- ---------------------------------------- --------------3 D:\\ORACLE\\ORADATA\\ORCL\\REDO03.LOG2 D:\\ORACLE\\ORADATA\\ORCL\\REDO02.LOG1 D:\\ORACLE\\ORADATA\\ORCL\\REDO01.LOG
3. 用数据字典查询数据库的逻辑存储结构。
逻辑结构包括：表空间，段，区，数据块
- 表空间：它位于逻辑存储结构的顶层，用于存储数据库中的所有数据，表空间内的数据被物理存放在数据文件中，一个表空间可以包含一个或多个数据文件。
- V$_是动态性能视图,存在于controlfile中,数据库在mount状态下可以查询,
- dba_是静态视图,存在于数据库中,只能在open时查询.
SQL> desc dba_tablespaces # 查看表空间静态试图名称 是否为空? 类型----------------------------------------- -------- ----------------------------TABLESPACE_NAME NOT NULL VARCHAR2(30)BLOCK_SIZE NOT NULL NUMBERINITIAL_EXTENT NUMBERNEXT_EXTENT NUMBERMIN_EXTENTS NOT NULL NUMBERMAX_EXTENTS NUMBERMAX_SIZE NUMBERPCT_INCREASE NUMBERMIN_EXTLEN NUMBERSTATUS VARCHAR2(9)CONTENTS VARCHAR2(21)LOGGING VARCHAR2(9)FORCE_LOGGING VARCHAR2(3)EXTENT_MANAGEMENT VARCHAR2(10)ALLOCATION_TYPE VARCHAR2(9)PLUGGED_IN VARCHAR2(3)SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)DEF_TAB_COMPRESSION VARCHAR2(8)RETENTION VARCHAR2(11)BIGFILE VARCHAR2(3)PREDICATE_EVALUATION VARCHAR2(7)ENCRYPTED VARCHAR2(3)COMPRESS_FOR VARCHAR2(30)DEF_INMEMORY VARCHAR2(8)DEF_INMEMORY_PRIORITY VARCHAR2(8)DEF_INMEMORY_DISTRIBUTE VARCHAR2(15)DEF_INMEMORY_COMPRESSION VARCHAR2(17)DEF_INMEMORY_DUPLICATE VARCHAR2(13)SHARED VARCHAR2(13)DEF_INDEX_COMPRESSION VARCHAR2(8)INDEX_COMPRESS_FOR VARCHAR2(13)DEF_CELLMEMORY VARCHAR2(14)DEF_INMEMORY_SERVICE VARCHAR2(12)DEF_INMEMORY_SERVICE_NAME VARCHAR2(1000)LOST_WRITE_PROTECT VARCHAR2(7)CHUNK_TABLESPACE VARCHAR2(1)SQL> select TABLESPACE_NAME,block_size from dba_tablespaces; # 查看各表名和大小TABLESPACE_NAME BLOCK_SIZE
------------------------------------------------------------ ----------
SYSTEM 8192
SYSAUX 8192
UNDOTBS1 8192
TEMP 8192
USERS 8192SQL> desc v$tablespace # 查看表空间动态性能试图 名称 是否为空? 类型----------------------------------------- -------- ----------------------------TS# NUMBERNAME VARCHAR2(30)INCLUDED_IN_DATABASE_BACKUP VARCHAR2(3)BIGFILE VARCHAR2(3)FLASHBACK_ON VARCHAR2(3)ENCRYPT_IN_BACKUP VARCHAR2(3)CON_ID NUMBERSQL> select name from v$tablespace;NAME
------------------------------------------------------------
SYSAUX
SYSTEM
UNDOTBS1
USERS
TEMP
SYSTEM
SYSAUX
UNDOTBS1
TEMP
SYSTEM
SYSAUXNAME
------------------------------------------------------------
UNDOTBS1
TEMP
USERS已选择 14 行。
- 段：用于存储和隔离不同数据库对象的数据。Oracle数据库中的段分为表段、索引段、回滚段和临时段四种。
SQL> desc dba_segments名称 是否为空? 类型----------------------------------------- -------- ----------------------------OWNER VARCHAR2(128)SEGMENT_NAME VARCHAR2(128)PARTITION_NAME VARCHAR2(128)SEGMENT_TYPE VARCHAR2(18)SEGMENT_SUBTYPE VARCHAR2(10)TABLESPACE_NAME VARCHAR2(30)HEADER_FILE NUMBERHEADER_BLOCK NUMBERBYTES NUMBERBLOCKS NUMBEREXTENTS NUMBERINITIAL_EXTENT NUMBERNEXT_EXTENT NUMBERMIN_EXTENTS NUMBERMAX_EXTENTS NUMBERMAX_SIZE NUMBERRETENTION VARCHAR2(7)MINRETENTION NUMBERPCT_INCREASE NUMBERFREELISTS NUMBERFREELIST_GROUPS NUMBERRELATIVE_FNO NUMBERBUFFER_POOL VARCHAR2(7)FLASH_CACHE VARCHAR2(7)CELL_FLASH_CACHE VARCHAR2(7)INMEMORY VARCHAR2(8)INMEMORY_PRIORITY VARCHAR2(8)INMEMORY_DISTRIBUTE VARCHAR2(15)INMEMORY_DUPLICATE VARCHAR2(13)INMEMORY_COMPRESSION VARCHAR2(17)CELLMEMORY VARCHAR2(24)SQL> select owner,segment_name,segment_type from dba_segments where owner='SYS';
# 只获取前10行，加上条件rownum <= 10OWNER SEGMENT_NAME SEGMENT_TYPE
---------- -------------------- --------------------
SYS PROXY_DATA$ TABLE
SYS OBJ$ TABLE
SYS BOOTSTRAP$ TABLE
SYS UNDO$ TABLE
SYS CON$ TABLE
SYS FILE$ TABLE
SYS PROXY_ROLE_DATA$ TABLE
SYS OBJERROR$ TABLE
SYS OBJAUTH$ TABLE
SYS HIST_HEAD$ TABLE
- 区：Oracle数据库内存储空间最小分配单位。每个区必须是一段连续的存储空间，他可以小到只有一个数据块，也可以大到2GB的空间。
SQL> desc dba_extents名称 是否为空? 类型----------------------------------------- -------- ----------------------------OWNER VARCHAR2(128)SEGMENT_NAME VARCHAR2(128)PARTITION_NAME VARCHAR2(128)SEGMENT_TYPE VARCHAR2(18)TABLESPACE_NAME VARCHAR2(30)EXTENT_ID NUMBERFILE_ID NUMBERBLOCK_ID NUMBERBYTES NUMBERBLOCKS NUMBERRELATIVE_FNO NUMBERSQL> select extent_id from dba_extents where owner='SYS'; # and rownum <= 10;EXTENT_ID
----------0012345678已选择 10 行。
- 数据块：是Oracle数据库的I/O单位，也就是说，在读写Oracle数据库中的数据时，每次读写的数据量至少为一个数据块大小。Oracle数据库支持的数据块大小包括2KB、4KB、BKB、16KB和32KB等。Oracle 数据块有一定的标准大小，其大小被写入到初始化参数DB_BLOCK_SIZE 中。另外， Oracle 支持在同一个数据库中使用多种大小的块，与标准块大小不同的块就是非标准块。
SQL> select name,value from v$parameter where name = 'db_block_size';NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
db_block_size
8192
4. 用动态性能视图查询Oracle实例当前状态。
SQL> select status from v$instance;OPEN
5. 以scott用户登录，查询该用户所具有的数据库对象。
SQL> conn sys/1234 as sysdba
已连接。
SQL> desc user_objects名称 是否为空? 类型----------------------------------------- -------- ----------------------------OBJECT_NAME VARCHAR2(128)SUBOBJECT_NAME VARCHAR2(128)OBJECT_ID NUMBERDATA_OBJECT_ID NUMBEROBJECT_TYPE VARCHAR2(23)CREATED DATELAST_DDL_TIME DATETIMESTAMP VARCHAR2(19)STATUS VARCHAR2(7)TEMPORARY VARCHAR2(1)GENERATED VARCHAR2(1)SECONDARY VARCHAR2(1)NAMESPACE NUMBEREDITION_NAME VARCHAR2(128)SHARING VARCHAR2(18)EDITIONABLE VARCHAR2(1)ORACLE_MAINTAINED VARCHAR2(1)APPLICATION VARCHAR2(1)DEFAULT_COLLATION VARCHAR2(100)DUPLICATED VARCHAR2(1)SHARDED VARCHAR2(1)CREATED_APPID NUMBERCREATED_VSNID NUMBERMODIFIED_APPID NUMBERMODIFIED_VSNID NUMBERSQL> select OBJECT_TYPE,OBJECT_NAME from user_objects;# where cownum <= 10OBJECT_TYP OBJECT_NAME
---------- --------------------
TABLE ACCESS$
TABLE ACLMV$
VIEW ACLMV$_BASE_VIEW
VIEW ACLMV$_MVINFO
TABLE ACLMV$_REFLOG
TABLE ACLMVREFSTAT$
TABLE ACLMVSUBTBL$
TABLE ADMINAUTH$
SEQUENCE ADO_IMCSEQ$
TABLE ADO_IMPARAM$
创建表空间，分配给用户
SQL> create tablespace scott_tb_space datafile 'd:/Oracle_TableSpace/scott_tablespace.dbf' size 200M;表空间已创建。SQL> create user c##scott identified by 1234 default tablespace scott_tb_space;用户已创建。SQL> grant dba to c##scott;授权成功。SQL> exitSQL> sqlplus /nolog # 未登录开启实例
SQL> conn c##scott/1234@orcl
已连接。SQL> select username, user_id, account_status from dba_users where username like '%SCOTT%';
USERNAME
--------------------------------------------------------------------------------USER_ID ACCOUNT_ST
---------- ----------
C##SCOTT
# OPEN
