SECTION5数据库管理.docx

1、SECTION5数据库管理1 传输表空间EXP有表级，用户级，表空间级，数据库级，不同的级别有不同的参数辅助使用问使用传输表空间技术将PROD数据库中OLTP_USER用户对应”OLTP”表空间迁移到EMREP数据库中参BOOKS-Administrators guied - 8managing tablespaces -Transporting tablespaces between databases参books- sql reference -create directory答： 使用/home/oracle/scripts目录下的sst.dmp，exp逻辑备份文件进行初始化1.1 初始

2、化实验环境带传输的表空间是section1创建的OLTP表空间，对应的数据文件是：/u01/app/oracle/oradata/prod/disk1/oltp_01.dbfexport ORACLE_SID=PRODsqlplus / as sysdbacreate user oltp_user identified by oltp_user default tablespace oltp;grant connect,resource to oltp_user;conn oltp_user/oltp_usercreate table t(x int) tablespace oltp;crea

3、te index i_t on t(x) tablespace oltp;insert into t values(1);commit;conn /as sysdbaSELECT tablespace_name, segment_name, segment_type FROM dba_segmentsWHERE tablespace_name =OLTP;1.2 检查是否存在违反约束问题Exec sys.dbms_tts.transport_set_check(OLTP,true);Select * from sys.transport_set_violations;1.3 置表空间为只读Al

4、ter tablespace OLTP read only;Exit;1.4 使用EXP或EXPDP的transport_tablespace=y参数导出1） 使用EXP备份方法Exp userid=/as sysdba transport_tablespace=y tablespace=oltp file=exp_oltp.dmp 2） 使用expdp备份方法Sqlplus / as sysdbaCreate directory dir_home as /home/oracle;Grant read,write on directory dir_home to public;Exit;Exp

5、dp system/oracle directory=dir_home dump_file=expdp_oltp.dmp transport_tablespaces=oltp transport_full_check=ytransport_full_check=y是两种校验，待传输和被引用的表空间都校验；=n只校验自己1.5 拷贝数据文件Sqlplus / as sysdbaSelect name ,ts# from v$tablespace;Select name from v$datafile where ts# in(11);!cp /u01/app/oracle/oradata/PRO

6、D/disk1/oltp01.dbf /u01/app/oracle/oradata/EMREP/oltp01.dbfExit1.6 实现IMP或IMPDP的传输表空间导入1.6.1 导入前准备工作（1） 创建待导入用户OLTP_USERExport ORACLE_SID=EMREPSqlplus / as sysdbaCreate user oltp_user identified by oltp_user;Grant connect,resource to oltp_user;（2） 清理环境Drop tablespace oltp including contents and dataf

7、iles;1.6.2 使用imp导入方法 Imp userid= / as sysdba FILE=/home/oracle/exp_oltp.dmp fromuser=OLTP_user touser=oltp_user transport_tablespace=y tablespaces=OLTP DATAFILES=/u01/app/oracle/ORADATA/emrep/oltp_01.dbf1.6.3 使用impdp导入方法drop tablespace oltp including contents and datafiles;!cp /u01/app/oracle/oradat

8、a/PROD/disk1/oltp_01.dbf /u01/app/oracle/oradata/EMREP/oltp_01.dbfCreate directory dir_home as /home/oracle;Grant read,write on directory dir_home to public;Exit;Impdp system/oracle/ directory=dir_home dumpfile=expdp_oltp.dmp remap_schema=(oltp_user:oltp_user) transport_datafiles=/u01/app/oracle/ora

9、data/emrep/oltp_01.dbf1.7 验证结果 Export ORACLE_SID=EMREP Sqlplus / as sysdba Select tablespace_name, segment_name,segment_type from dba_segments where tablespace_name=OLTP; Conn oltp _user/oltp_userSelect * from t1.8 最后，将PROD实例中表空间调整为可读写状态Export ORACLE_SID=PRODSqlplus / as sysdbaAlter tablespace oltp

10、read write;Select tablespace_name,status from dba_tablespaces where tablespace_name=OLTP;Exit;1.9 使用GC完成传输表空间方法1） 进入到GC传输表空间操作页面：点击“TARGETS”-点击上面的“DATABASE”栏目-选择PROD数据库，点击进入-点击“Maintenance” -Data Movement大类中的Move database files 小类，点击其中的“Transport Tablespaces”2） 输入用户名“SYS”，密码“oracle”，connect as 选择“SY

11、SDBA”，点击“LOGIN”3） 保持默认的“Generate a transportable tablespace set”选择不变，输入Host credentials username是“oracle”密码是oracle，点击“continue”4） 按照每一步骤提示完成数据的导出5） 如上同样的操作进入到EMREP数据库，到第8步骤时选择“Integreate an existing transportable tablespace set”选项6） 按照每一步骤提示完成传输表空间的导入。非归档是DROP OFFLINE，强制脱机，而且必须MOUNT状态做,脱机状态归档的是DROP2

12、 内存设置2.1 创建一个能够存储16K大小块的缓冲区 Alter system set db_16k_cache_size=60m;2.2 HR用户下的departments经常被使用，因此将此表缓存到内存中 Select sement_name from dba_segments where buffer_pool=KEEP; Alter system set db_keep_cache_size=12m; Alter table t1 storage(buffer_pool keep); Select sement_name from dba_segments where buffer_

13、pool=KEEP;2.3 有一张不经常被访问的大表XX，使其不被保留在内存中，用完就换出 Select segment_name from dba_segments where buffer_pool=RECYCLE; Alter system set db_recycle_cache_size=12m; Alter table t2 storage (buffer_pool recycle); Select segment_name from dba_segments where buffer_pool=RECYCLE;3 创建分区表及分区索引1、只truncate某一分区2、分区交换技术

14、：它的UNDO只是数据字典里生成的UNDO，代价非常小3、所查询的落在某一分区上了，这样遍历的范围降低了，这样可以提升性能，分区键值用在WHERE子句内，所查询所有的数据放在某一分区内的，给DBA最大的优点是可管理性，可用性索引的热点块比较表的热点块更严重 ，索引是ROWID+KEY，BLOCK8K，索引缓存的数据要比表缓存的数据更多，所以块中的索引条目就越多，这样就会产生索引块复合分区：检索的时候一定会含有表分区的键值，然后在查找子分区的键值，才能扫描到其子分区分区不能放同一磁盘上，充分利用读性能和写性能块越大，UPDATE代价太大2K，4K块的I/O代价太大问：按照要求在PROD数据库中创

15、建5个表空间用于存放分区数据Create 5 new tablespace in the PROD database as follows:1、 use the names data01,data02,data03,data04,data052、 spread the datafiles across different disk devices;3、 each file should be 256mb in size4、 use uniform extents of 4mb5、 block size shall be 16kbnote :there will be used to store

16、 partitioned data参:books-sql reference-create tablespace答：3.1 设置db_16k_cache_size参数alter system set db_16k_cache_size=60m;3.2 创建表空间Create tablespace data01 datafile /u01/app/oracle/oradata/PROD/disk1/data01_01.dbf size 256m extent management localuniform size 4m blocksize 16k;Create tablespace data0

17、2 datafile /u01/app/oracle/oradata/PROD/disk2/data01_02.dbf size 256m extent management localuniform size 4m blocksize 16k;Create tablespace data03 datafile /u01/app/oracle/oradata/PROD/disk3/data01_03.dbf size 256m extent management localuniform size 4m blocksize 16k;Create tablespace data04 datafi

18、le /u01/app/oracle/oradata/PROD/disk4/data01_04.dbf size 256m extent management localuniform size 4m blocksize 16k;Create tablespace data05 datafile /u01/app/oracle/oradata/PROD/disk5/data01_05.dbf size 256m extent management localuniform size 4m blocksize 16k;问：在PROD数据库的SH用户下按照要求创建分区表SALES_HISTORYC

19、reate a partitioned table named SALES_HISTORY in the SH schema in the PROD database .use the following specification:1、 the column names and definitions will be the same as the OLTP_USER.SALES table2、 Partiion P1 will contain data for 1998 and should be placed in the DATA01 tablespace3、 Partiion P2

20、will contain data for 1999 and should be placed in the DATA02 tablespace4、 Partiion P3 will contain data for 2000 and should be placed in the DATA03 tablespace5、 Partiion P4 will contain data for 2001 and should be placed in the DATA04 tablespace6、 Partiion P5 will contain data for 2002 and should b

21、e placed in the DATA05 tablespace参 books- Administrators Guide - 17Managing Partitioned Tables and Indexes参 books-Data Warehousing Guide - 5 Partitioning in Data Warehouses答3.3 模拟创建OLTP_USER.SALES表Conn oltp_user/oltp_userDrop table sales purge;create table sales (orderid number(10),name varchar2(20)

22、,s_date date);Create index i_sales on sales (orderid);Insert into sales values(1,sec1,to_date(1981-02-15,yyyy-mm-dd);Insert into sales values(2,sec2,to_date(1998-10-11,yyyy-mm-dd);Insert into sales values(3,sec3,to_date(1999-02-17,yyyy-mm-dd);Insert into sales values(4,sec4,to_date(2000-09-06,yyyy-m

23、m-dd);Insert into sales values(5,sec5,to_date(2000-05-15,yyyy-mm-dd);Insert into sales values(6,sec6,to_date(2001-03-17,yyyy-mm-dd);Insert into sales values(7,sec7,to_date(2001-04-12,yyyy-mm-dd);Insert into sales values(8,sec8,to_date(2002-12-17,yyyy-mm-dd);Insert into sales values(9,sec9,to_date(20

24、02-06-13,yyyy-mm-dd);Insert into sales values(10,sec10,to_date(1981-05-08,yyyy-mm-dd);Commit;Select * from sales;3.4 使用CTAS方式在SH用户下创建分区表Conn sh/shDrop table sales_history purge;Create table sales_history Partition by range(s_date)(partition p1 values less than (to_date(1999-01-01,yyyy-mm-dd) tablesp

25、ace data01,partition p2 values less than (to_date(2000-01-01,yyyy-mm-dd) tablespace data02,partition p3 values less than (to_date(2001-01-01,yyyy-mm-dd) tablespace data03,partition p4 values less than (to_date(2002-01-01,yyyy-mm-dd) tablespace data04,partition p5 values less than (to_date(2003-01-01

26、,yyyy-mm-dd) tablespace data05,partition others values less than (maxvalue) tablespace data05)As select * from oltp_user.sales;3.5 查看分区表不同分区中的数据Alter session set nls_date_format=yyyy-mm-dd hh24:mi:ss;Select * from sales_history partition(p1);Select * from sales_history partition(p2);Select * from sa

27、les_history partition(p3);Select * from sales_history partition(p4);Select * from sales_history partition(p5);Select * from sales_history partition(others);问执行给定的脚本Run the populate_sales_hist.sql script located in the /home/oracle/scripts directory to populate the SALES_HISTORY table with data答：按照要求

28、执行相关脚本/home/oracle/scripts/populate_sales_hist.sqlBecause of the unevently distributed data in the DEPARTMENT_ID column of the EMPLOYEES table of the HR schema,you need to supply more information to the optimizer to allow for more efficient use of indexes.Regenerate statistics on the EMPLOYEES table

29、 to solve this problem问：创建HASH全局分区索引不管是哪种分区索引，全局索引必须是分区键，而且是索引的前列分区索引列，第一列必须是索引键值Create a unique index named SALES_HISTORY_PK in the SH schema of PROD database on the SALES_HISTORY table.Partition the index into 4 partitons with each partition containing approximately the same amount of entries foll

30、owing specification1、 create the index to include the ORDERID column.2、 Create the index in the INDX tablespace3、 Create the index with parallelism degree 4参BOOKS-SQL Reference - CREATE TABLE 和CREATE INDEX答注：为防止出现“ORA-14038：GLOBAL partitioned index must be prefixed”错误，使用全局分区索引时，索引键值必须包含分区键值，并且分区键值位于

31、索引键值的最前面，此谓“前缀索引”。ORACLE不支持非前缀的全局分区索引，如果需要建立非前缀分区索引，索引必须建成本地索引。3.6 第一种创建方法Drop index SALES_HISTORY_PK;CREATE UNIQUE INDEX SALES_HISTORY_PK ON SALES_HISTORY (name, orderid) GLOBAL PARTITION BY HASH (name) PARTITIONS 4 STORE IN (INDX) TABLESPACE INDX PARALLEL 4;select dbms_metadata.get_ddl(INDEX,SALES_HISTORY_PK,SH) from dual;3.7 第二种创建方法Drop index SALES_HISTORY_PK;CREATE UNIQUE INDEX SALES_HISTORY