SECTION5数据库管理.docx
《SECTION5数据库管理.docx》由会员分享，可在线阅读，更多相关《SECTION5数据库管理.docx（30页珍藏版）》请在冰豆网上搜索。
SECTION5数据库管理
1传输表空间
EXP有表级，用户级，表空间级，数据库级，不同的级别有不同的参数辅助使用
问使用传输表空间技术将PROD数据库中OLTP_USER用户对应”OLTP”表空间迁移到EMREP数据库中
参BOOKS->Administrator’sguied->8managingtablespaces->Transportingtablespacesbetweendatabases
参books->sqlreference->createdirectory
答：
使用/home/oracle/scripts目录下的sst.dmp，exp逻辑备份文件进行初始化
1.1初始化实验环境
带传输的表空间是section1创建的OLTP表空间，对应的数据文件是：
/u01/app/oracle/oradata/prod/disk1/oltp_01.dbf
exportORACLE_SID=PROD
sqlplus/assysdba
createuseroltp_useridentifiedbyoltp_userdefaulttablespaceoltp;
grantconnect,resourcetooltp_user;
connoltp_user/oltp_user
createtablet（xint）tablespaceoltp;
createindexi_tont（x）tablespaceoltp;
insertintotvalues
（1）;
commit;
conn/assysdba
SELECTtablespace_name,segment_name,segment_type
FROMdba_segments
WHEREtablespace_name='OLTP';
1.2检查是否存在违反约束问题
Execsys.dbms_tts.transport_set_check（'OLTP',true）;
Select*fromsys.transport_set_violations;
1.3置表空间为只读
AltertablespaceOLTPreadonly;
Exit;
1.4使用EXP或EXPDP的transport_tablespace=y参数导出
1）使用EXP备份方法
Expuserid=\'/assysdba\'transport_tablespace=ytablespace=oltpfile=exp_oltp.dmp
2）使用expdp备份方法
Sqlplus/assysdba
Createdirectorydir_homeas'/home/oracle';
Grantread,writeondirectorydir_hometopublic;
Exit;
Expdpsystem/oracledirectory=dir_homedump_file=expdp_oltp.dmptransport_tablespaces=oltptransport_full_check=y
transport_full_check=y是两种校验，待传输和被引用的表空间都校验；=n只校验自己
1.5拷贝数据文件
Sqlplus/assysdba
Selectname,ts#fromv$tablespace;
Selectnamefromv$datafilewherets#in（11）;
!
cp/u01/app/oracle/oradata/PROD/disk1/oltp01.dbf/u01/app/oracle/oradata/EMREP/oltp01.dbf
Exit
1.6实现IMP或IMPDP的传输表空间导入
1.6.1导入前准备工作
（1）创建待导入用户OLTP_USER
ExportORACLE_SID=EMREP
Sqlplus/assysdba
Createuseroltp_useridentifiedbyoltp_user;
Grantconnect,resourcetooltp_user;
（2）清理环境
Droptablespaceoltpincludingcontentsanddatafiles;
1.6.2使用imp导入方法
Impuserid=\'/assysdba\'FILE=/home/oracle/exp_oltp.dmpfromuser=OLTP_usertouser=oltp_usertransport_tablespace=ytablespaces=OLTPDATAFILES=/u01/app/oracle/ORADATA/emrep/oltp_01.dbf
1.6.3使用impdp导入方法
droptablespaceoltpincludingcontentsanddatafiles;
!
cp/u01/app/oracle/oradata/PROD/disk1/oltp_01.dbf/u01/app/oracle/oradata/EMREP/oltp_01.dbf
Createdirectorydir_homeas'/home/oracle';
Grantread,writeondirectorydir_hometopublic;
Exit;
Impdpsystem/oracle/directory=dir_homedumpfile=expdp_oltp.dmpremap_schema=（oltp_user:
oltp_user）transport_datafiles=/u01/app/oracle/oradata/emrep/oltp_01.dbf
1.7验证结果
ExportORACLE_SID=EMREP
Sqlplus/assysdba
Selecttablespace_name,segment_name,segment_typefromdba_segmentswheretablespace_name='OLTP';
Connoltp_user/oltp_user
Select*fromt
1.8最后，将PROD实例中表空间调整为可读写状态
ExportORACLE_SID=PROD
Sqlplus/assysdba
Altertablespaceoltpreadwrite;
Selecttablespace_name,statusfromdba_tablespaceswheretablespace_name='OLTP';
Exit;
1.9使用GC完成传输表空间方法
1）进入到GC传输表空间操作页面：
点击“TARGETS”->点击上面的“DATABASE”栏目->选择PROD数据库，点击进入->点击“Maintenance”->DataMovement大类中的Movedatabasefiles小类，点击其中的“TransportTablespaces”
2）输入用户名“SYS”，密码“oracle”，connectas选择“SYSDBA”，点击“LOGIN”
3）保持默认的“Generateatransportabletablespaceset”选择不变，输入Hostcredentialsusername是“oracle”密码是oracle，点击“continue”
4）按照每一步骤提示完成数据的导出
5）如上同样的操作进入到EMREP数据库，到第8步骤时选择“Integreateanexistingtransportabletablespaceset”选项
6）按照每一步骤提示完成传输表空间的导入。
非归档是DROPOFFLINE，强制脱机，而且必须MOUNT状态做,脱机状态
归档的是DROP
2内存设置
2.1创建一个能够存储16K大小块的缓冲区
Altersystemsetdb_16k_cache_size=60m;
2.2HR用户下的departments经常被使用，因此将此表缓存到内存中
Selectsement_namefromdba_segmentswherebuffer_pool='KEEP';
Altersystemsetdb_keep_cache_size=12m;
Altertablet1storage（buffer_poolkeep）;
Selectsement_namefromdba_segmentswherebuffer_pool='KEEP';
2.3有一张不经常被访问的大表XX，使其不被保留在内存中，用完就换出
Selectsegment_namefromdba_segmentswherebuffer_pool='RECYCLE';
Altersystemsetdb_recycle_cache_size=12m;
Altertablet2storage（buffer_poolrecycle）;
Selectsegment_namefromdba_segmentswherebuffer_pool='RECYCLE';
3创建分区表及分区索引
1、只truncate某一分区
2、分区交换技术：
它的UNDO只是数据字典里生成的UNDO，代价非常小
3、所查询的落在某一分区上了，这样遍历的范围降低了，这样可以提升性能，分区键值用在WHERE子句内，所查询所有的数据放在某一分区内的，
给DBA最大的优点是可管理性，可用性
索引的热点块比较表的热点块更严重，索引是ROWID+KEY，BLOCK8K，索引缓存的数据要比表缓存的数据更多，所以块中的索引条目就越多，这样就会产生索引块
复合分区：
检索的时候一定会含有表分区的键值，然后在查找子分区的键值，才能扫描到其子分区
分区不能放同一磁盘上，充分利用读性能和写性能
块越大，UPDATE代价太大
2K，4K块的I/O代价太大
问：
按照要求在PROD数据库中创建5个表空间用于存放分区数据
Create5newtablespaceinthePRODdatabaseasfollows:
1、usethenamesdata01,data02,data03,data04,data05
2、spreadthedatafilesacrossdifferentdiskdevices;
3、eachfileshouldbe256mbinsize
4、useuniformextentsof4mb
5、blocksizeshallbe16kb
note:
therewillbeusedtostorepartitioneddata
参:
books->sqlreference->createtablespace
答：
3.1设置db_16k_cache_size参数
altersystemsetdb_16k_cache_size=60m;
3.2创建表空间
Createtablespacedata01
datafile'/u01/app/oracle/oradata/PROD/disk1/data01_01.dbf'size256m
extentmanagementlocal
uniformsize4mblocksize16k;
Createtablespacedata02
datafile'/u01/app/oracle/oradata/PROD/disk2/data01_02.dbf'size256m
extentmanagementlocal
uniformsize4mblocksize16k;
Createtablespacedata03
datafile'/u01/app/oracle/oradata/PROD/disk3/data01_03.dbf'size256m
extentmanagementlocal
uniformsize4mblocksize16k;
Createtablespacedata04
datafile'/u01/app/oracle/oradata/PROD/disk4/data01_04.dbf'size256m
extentmanagementlocal
uniformsize4mblocksize16k;
Createtablespacedata05
datafile'/u01/app/oracle/oradata/PROD/disk5/data01_05.dbf'size256m
extentmanagementlocal
uniformsize4mblocksize16k;
问：
在PROD数据库的SH用户下按照要求创建分区表SALES_HISTORY
CreateapartitionedtablenamedSALES_HISTORYintheSHschemainthePRODdatabase.usethefollowingspecification:
1、thecolumnnamesanddefinitionswillbethesameastheOLTP_USER.SALEStable
2、PartiionP1willcontaindatafor1998andshouldbeplacedintheDATA01tablespace
3、PartiionP2willcontaindatafor1999andshouldbeplacedintheDATA02tablespace
4、PartiionP3willcontaindatafor2000andshouldbeplacedintheDATA03tablespace
5、PartiionP4willcontaindatafor2001andshouldbeplacedintheDATA04tablespace
6、PartiionP5willcontaindatafor2002andshouldbeplacedintheDATA05tablespace
参books->Administrator’sGuide->17ManagingPartitionedTablesandIndexes
参books->DataWarehousingGuide->5PartitioninginDataWarehouses
答
3.3模拟创建OLTP_USER.SALES表
Connoltp_user/oltp_user
Droptablesalespurge;
createtablesales（orderidnumber（10）,namevarchar2（20）,s_datedate）;
Createindexi_salesonsales（orderid）;
Insertintosalesvalues（1,'sec1',to_date（'1981-02-15','yyyy-mm-dd'））;
Insertintosalesvalues（2,'sec2',to_date（'1998-10-11','yyyy-mm-dd'））;
Insertintosalesvalues（3,'sec3',to_date（'1999-02-17','yyyy-mm-dd'））;
Insertintosalesvalues（4,'sec4',to_date（'2000-09-06','yyyy-mm-dd'））;
Insertintosalesvalues（5,'sec5',to_date（'2000-05-15','yyyy-mm-dd'））;
Insertintosalesvalues（6,'sec6',to_date（'2001-03-17','yyyy-mm-dd'））;
Insertintosalesvalues（7,'sec7',to_date（'2001-04-12','yyyy-mm-dd'））;
Insertintosalesvalues（8,'sec8',to_date（'2002-12-17','yyyy-mm-dd'））;
Insertintosalesvalues（9,'sec9',to_date（'2002-06-13','yyyy-mm-dd'））;
Insertintosalesvalues（10,'sec10',to_date（'1981-05-08','yyyy-mm-dd'））;
Commit;
Select*fromsales;
3.4使用CTAS方式在SH用户下创建分区表
Connsh/sh
Droptablesales_historypurge;
Createtablesales_history
Partitionbyrange（s_date）
（
partitionp1valueslessthan（to_date（'1999-01-01','yyyy-mm-dd'））tablespacedata01,
partitionp2valueslessthan（to_date（'2000-01-01','yyyy-mm-dd'））tablespacedata02,
partitionp3valueslessthan（to_date（'2001-01-01','yyyy-mm-dd'））tablespacedata03,
partitionp4valueslessthan（to_date（'2002-01-01','yyyy-mm-dd'））tablespacedata04,
partitionp5valueslessthan（to_date（'2003-01-01','yyyy-mm-dd'））tablespacedata05,
partitionothersvalueslessthan（maxvalue）tablespacedata05
）
Asselect*fromoltp_user.sales;
3.5查看分区表不同分区中的数据
Altersessionsetnls_date_format='yyyy-mm-ddhh24:
mi:
ss';
Select*fromsales_historypartition（p1）;
Select*fromsales_historypartition（p2）;
Select*fromsales_historypartition（p3）;
Select*fromsales_historypartition（p4）;
Select*fromsales_historypartition（p5）;
Select*fromsales_historypartition（others）;
问执行给定的脚本
Runthepopulate_sales_hist.sqlscriptlocatedinthe/home/oracle/scriptsdirectorytopopulatetheSALES_HISTORYtablewithdata
答：
按照要求执行相关脚本
@/home/oracle/scripts/populate_sales_hist.sql
BecauseoftheuneventlydistributeddataintheDEPARTMENT_IDcolumnoftheEMPLOYEEStableoftheHRschema,youneedtosupplymoreinformationtotheoptimizertoallowformoreefficientuseofindexes.RegeneratestatisticsontheEMPLOYEEStabletosolvethisproblem
问：
创建HASH全局分区索引
不管是哪种分区索引，全局索引必须是分区键，而且是索引的前列
分区索引列，第一列必须是索引键值
CreateauniqueindexnamedSALES_HISTORY_PKintheSHschemaofPRODdatabaseontheSALES_HISTORYtable.Partitiontheindexinto4partitonswitheachpartitioncontainingapproximatelythesameamountofentriesfollowingspecification
1、createtheindextoincludetheORDERIDcolumn.
2、CreatetheindexintheINDXtablespace
3、Createtheindexwithparallelismdegree4
参BOOKS->SQLReference->CREATETABLE和CREATEINDEX
答
注：
为防止出现“ORA-14038：
GLOBALpartitionedindexmustbeprefixed”错误，使用全局分区索引时，索引键值必须包含分区键值，并且分区键值位于索引键值的最前面，此谓“前缀索引”。
ORACLE不支持非前缀的全局分区索引，如果需要建立非前缀分区索引，索引必须建成本地索引。
3.6第一种创建方法
DropindexSALES_HISTORY_PK;
CREATEUNIQUEINDEXSALES_HISTORY_PK
ONSALES_HISTORY（name,orderid）
GLOBALPARTITIONBYHASH（name）
PARTITIONS4
STOREIN（INDX）
TABLESPACEINDX
PARALLEL4;
selectdbms_metadata.get_ddl（'‘INDEX','SALES_HISTORY_PK','SH'）fromdual;
3.7第二种创建方法
DropindexSALES_HISTORY_PK;
CREATEUNIQUEINDEXSALES_HISTORY
升级会员 