1、1实验一 Oracle 环境配置一、实验目的本实验为验证性试验，主要目的是加深对 Oracle 存储结构和内存结构的认识和理解，熟悉常用的 SQL*PLUS 命令。二、实验仪器设备1. 服务器 + Oracle 10g 服务器2. 客户端 + Oracle 10g 客户端三、预习要求1. 复习 Oracle 体系结构相关知识；2. 复习 SQL*PLUS 相关知识。四、实验内容利用课堂所学知识，完成以下实验题目，将结果写入实验报告中：1 当前数据库例程的表空间和数据文件分别有哪些？写出它们之间的对应关系。所有表空间的信息SQL select tablespace_name,contents,s
2、tatus from dba_tablespaces;TABLESPACE_NAME CONTENTS STATUS- - -SYSTEM PERMANENT ONLINEUNDOTBS1 UNDO ONLINESYSAUX PERMANENT ONLINETEMP TEMPORARY ONLINEUSERS PERMANENT ONLINE2获取数据文件的信息SQL select name from v$datafile;NAME-C:ORACLEPRODUCT10.2.0ORADATAORACLE1SYSTEM01.DBFC:ORACLEPRODUCT10.2.0ORADATAORACLE
3、1UNDOTBS01.DBFC:ORACLEPRODUCT10.2.0ORADATAORACLE1SYSAUX01.DBFC:ORACLEPRODUCT10.2.0ORADATAORACLE1USERS01.DBF简述表空间和数据文件之间的关系。 答 每一个数据文件都必须隶属于某个表空间 但一个表空间可以由多个数据文件组合而成。 tablespace 是逻辑上的概念 ,datafile 则在物理上储存了数据库的种种对象。2 举出 5 个 SCOTT 的数据段和索引段。（查询某用户的数据段：select * from user_extents;）SQL SEGMENT_NAME-PARTITIO
4、N_NAME SEGMENT_TYPE TABLESPACE_NAME- - -EXTENT_ID BYTES BLOCKS- - -DEPTTABLE USERS30 65536 8EMPTABLE USERS0 65536 8SEGMENT_NAME-PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME- - -EXTENT_ID BYTES BLOCKS- - -BONUSTABLE USERS0 65536 8SALGRADETABLE USERSSEGMENT_NAME-PARTITION_NAME SEGMENT_TYPE TABLESPACE_N
5、AME- - -EXTENT_ID BYTES BLOCKS- - -0 65536 84PLAN_TABLETABLE USERS0 65536 8PK_DEPTSEGMENT_NAME-PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME- - -EXTENT_ID BYTES BLOCKS- - -INDEX USERS0 65536 8PK_EMPINDEX USERS0 65536 8已选择 7 行。（查询某用户的索引段：select * from user_indexes;）SQL select * from user_indexes;5INDEX
6、_NAME INDEX_TYPE- -TABLE_OWNER TABLE_NAME TABLE_TYPE- - -UNIQUENES COMPRESS PREFIX_LENGTH TABLESPACE_NAME INI_TRANS- - - - -MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE- - - - - -PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOG- - - - - -BLEVEL LEAF_BL
7、OCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY- - - -AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE- - - - -LAST_ANALYZED DEGREE- -INSTANCES PAR T G S BUFFER_ USE DURATION- - - - - - - -PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME- - -PARAMETERS-6GLO DOMIDX_STATU DOMIDX FUNCIDX_ JOI IOT
8、 DRO- - - - - - -PK_EMP NORMALINDEX_NAME INDEX_TYPE- -TABLE_OWNER TABLE_NAME TABLE_TYPE- - -UNIQUENES COMPRESS PREFIX_LENGTH TABLESPACE_NAME INI_TRANS- - - - -MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE- - - - - -PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PC
9、T_FREE LOG- - - - - -BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY- - - -AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE- - - - -LAST_ANALYZED DEGREE- -INSTANCES PAR T G S BUFFER_ USE DURATION- - - - - - - -7PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME- - -PARAMETERS-GLO D
10、OMIDX_STATU DOMIDX FUNCIDX_ JOI IOT DRO- - - - - - -SCOTT EMP TABLEINDEX_NAME INDEX_TYPE- -TABLE_OWNER TABLE_NAME TABLE_TYPE- - -UNIQUENES COMPRESS PREFIX_LENGTH TABLESPACE_NAME INI_TRANS- - - - -MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE- - - - - -PCT_THRESHOLD INCLUD
11、E_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOG- - - - - -BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY- - - -AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE- - - - -8LAST_ANALYZED DEGREE- -INSTANCES PAR T G S BUFFER_ USE DURATION- - - - - - - -PCT_DIRECT_ACCESS ITYP
12、_OWNER ITYP_NAME- - -PARAMETERS-GLO DOMIDX_STATU DOMIDX FUNCIDX_ JOI IOT DRO- - - - - - -UNIQUE DISABLED USERS 2INDEX_NAME INDEX_TYPE- -TABLE_OWNER TABLE_NAME TABLE_TYPE- - -UNIQUENES COMPRESS PREFIX_LENGTH TABLESPACE_NAME INI_TRANS- - - - -MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENT
13、S PCT_INCREASE- - - - - -PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOG- - - - - -BLEVEL LEAF_BLOCKS DISTINCT_KEYS 9AVG_LEAF_BLOCKS_PER_KEY- - - -AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE- - - - -LAST_ANALYZED DEGREE- -INSTANCES PAR T G S BUFFER_ USE
14、DURATION- - - - - - - -PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME- - -PARAMETERS-GLO DOMIDX_STATU DOMIDX FUNCIDX_ JOI IOT DRO- - - - - - -255 65536 1 2147483645INDEX_NAME INDEX_TYPE- -TABLE_OWNER TABLE_NAME TABLE_TYPE- - -UNIQUENES COMPRESS PREFIX_LENGTH TABLESPACE_NAME INI_TRANS- - - - -MAX_TRANS INITI
15、AL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE- - - - - -10PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOG- - - - - -BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY- - - -AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE- - - - -LAST_ANALY
16、ZED DEGREE- -INSTANCES PAR T G S BUFFER_ USE DURATION- - - - - - - -PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME- - -PARAMETERS-GLO DOMIDX_STATU DOMIDX FUNCIDX_ JOI IOT DRO- - - - - - -10 YESINDEX_NAME INDEX_TYPE- -TABLE_OWNER TABLE_NAME TABLE_TYPE- - -UNIQUENES COMPRESS PREFIX_LENGTH TABLESPACE_NAME 11IN
17、I_TRANS- - - - -MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE- - - - - -PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOG- - - - - -BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY- - - -AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS S
18、AMPLE_SIZE- - - - -LAST_ANALYZED DEGREE- -INSTANCES PAR T G S BUFFER_ USE DURATION- - - - - - - -PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME- - -PARAMETERS-GLO DOMIDX_STATU DOMIDX FUNCIDX_ JOI IOT DRO- - - - - - -0 1 14 1INDEX_NAME INDEX_TYPE- -12TABLE_OWNER TABLE_NAME TABLE_TYPE- - -UNIQUENES COMPRESS P
19、REFIX_LENGTH TABLESPACE_NAME INI_TRANS- - - - -MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE- - - - - -PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOG- - - - - -BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY- - - -AVG_DATA_BLOCKS_PER_KEY CLUS
20、TERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE- - - - -LAST_ANALYZED DEGREE- -INSTANCES PAR T G S BUFFER_ USE DURATION- - - - - - - -PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME- - -PARAMETERS-GLO DOMIDX_STATU DOMIDX FUNCIDX_ JOI IOT DRO- - - - - - -131 1 VALID 14 14INDEX_NAME INDEX_TYPE- -TABLE_OWNER TABLE_NA
21、ME TABLE_TYPE- - -UNIQUENES COMPRESS PREFIX_LENGTH TABLESPACE_NAME INI_TRANS- - - - -MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE- - - - - -PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOG- - - - - -BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_
22、KEY- - - -AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE- - - - -LAST_ANALYZED DEGREE- -INSTANCES PAR T G S BUFFER_ USE DURATION- - - - - - - -PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME14- - -PARAMETERS-GLO DOMIDX_STATU DOMIDX FUNCIDX_ JOI IOT DRO- - - - - - -19-10 月-12 1INDEX_NAM
23、E INDEX_TYPE- -TABLE_OWNER TABLE_NAME TABLE_TYPE- - -UNIQUENES COMPRESS PREFIX_LENGTH TABLESPACE_NAME INI_TRANS- - - - -MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE- - - - - -PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOG- - - - - -BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY- - - -AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE- - -
