Oracle数据库实验笔记.docx

1、Oracle数据库实验笔记 Oracle数据库实验数据库移动数据文件1建立数据库的文本参数文件 一致性停库2 将所有的文件复制到目标位置3 在文本参数文件中，修改新的控制文件的的位置4 将二进制文件删除，留下文本文件5 将数据库启动mount状态6 将库中文件名重命名到目标位置 alter database rename file 旧文件 to 新文件 7 启动数据库库到OPen 状态；移库SQL create pfile from spfile;文件已创建。SQL shut immediate数据库已经关闭。已经卸载数据库。ORACLE 例程已经关闭。SQL startup mountORA

2、CLE 例程已经启动。Total System Global Area 104857600 bytesFixed Size 1247516 bytesVariable Size 62916324 bytesDatabase Buffers 33554432 bytesRedo Buffers 7139328 bytes数据库装载完毕。SQL select alter database rename file|name|to|replace(name,D:ORACLEPRODUCT10.2.0ORADATAORCL,E:TEST)|; FROM (SELECT NAME FROM V$DATAF

3、ILE);ALTERDATABASERENAMEFILE|NAME|TO|REPLACE(NAME,D:ORACLE-alter database rename fileD:ORACLEPRODUCT10.2.0ORADATAORCLSYSTEM01.DBFtoE:TESTSYSTEM01.DBF;alter database rename fileD:ORACLEPRODUCT10.2.0ORADATAORCLUNDOTBS01.DBFtoE:TESTUNDOTBS01.DBF;alter database rename fileD:ORACLEPRODUCT10.2.0ORADATAORC

4、LSYSAUX01.DBFtoE:TESTSYSAUX01.DBF;alter database rename fileD:ORACLEPRODUCT10.2.0ORADATAORCLUSERS01.DBFtoE:TESTUSERS01.DBF;ALTERDATABASERENAMEFILE|NAME|TO|REPLACE(NAME,D:ORACLE-alter database rename fileD:ORACLEPRODUCT10.2.0ORADATAORCLEXAMPLE01.DBFtoE:TESTEXAMPLE01.DBF;SQL alter database open;数据库已更改

5、。SQL select name from v$controlfile union select name from v$datafile union select member from v$logfile;NAME-D:ORACLEPRODUCT10.2.0ORADATAORCLEXAMPLE01.DBFD:ORACLEPRODUCT10.2.0ORADATAORCLREDO01.LOGD:ORACLEPRODUCT10.2.0ORADATAORCLREDO02.LOGD:ORACLEPRODUCT10.2.0ORADATAORCLREDO03.LOGD:ORACLEPRODUCT10.2

6、.0ORADATAORCLSYSAUX01.DBFD:ORACLEPRODUCT10.2.0ORADATAORCLSYSTEM01.DBFD:ORACLEPRODUCT10.2.0ORADATAORCLUNDOTBS01.DBFD:ORACLEPRODUCT10.2.0ORADATAORCLUSERS01.DBFE:TESTCONTROL01.CTLE:TESTCONTROL02.CTLE:TESTCONTROL03.CTL已选择11行。SQL位图实验步骤一.autoallocate 是自动分配的实验1.create tablespace mytest2.datafile E:test01.d

7、bf size 100M;3.create table abc tablespace mytest as select * from scott.emp;4.desc dba_extents5.select file_id,extent_id,block_id,blocks from dba_extents where segment_name=ABC;6.alter system dump datafile 6 block 37.select c.value | / | d.instance_name | _ora_ | a.spid | .trc trace from v$process

8、a,v$session b,v$parameter c,v$instance d where a.addr=b.paddr and b.audsid=userenv(sessionid) and c.name= user_dump_dest;8.打开D:ORACLEPRODUCT10.2.0ADMINORCLUDUMP/orcl_ora_2516.trc文件9. insert into abc select * from abc; 10.alter system dump datafile 6 block 3;11.select file_id,extent_id,block_id,block

9、s from dba_extents where segment_name=ABC;12.再次打开D:ORACLEPRODUCT10.2.0ADMINORCLUDUMP/orcl_ora_2516.trc文件；13.结果：End dump data blocks tsn: 7 file#: 6 minblk 3 maxblk 3* 2012-11-23 10:33:34.781Start dump data blocks tsn: 7 file#: 6 minblk 3 maxblk 3。二.uniform 就是区的大小都是一样的的实验14.CREATE TABLESPACE MYTBS DA

10、TAFILE E:mytbs01.dbf SIZE 104857600 LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL UNIFORM SEGMENT SPACE MANAGEMENT AUTO（uniform与AUTOALLOCATE 的管理区别，实验表明uniform的块的大小是一直不变的，而autoalllocate管理方式是随着增长的）15.create table abcd tablespace myTBS as select * from scott.emp;16.select file_id,exte

11、nt_id,block_id,blocks from dba_extents where segment_name=ABCD;17. insert into abcd select * from abcd; 18.alter system dump datafile 7 block 319.select c.value | / | d.instance_name | _ora_ | a.spid | .trc trace from v$process a,v$session b,v$parameter c,v$instance d where a.addr=b.paddr and b.auds

12、id=userenv(sessionid) and c.name= user_dump_dest;20.结果RelFno: 7, BeginBlock: 9, Flag: 0, First: 43, Free: 63445 FFFFFFFFFF070000 0000000000000000 0000000000000000 0000000000000000 三：空间回收(整理碎片)：1.delete from abcd where deptno=10;2.alter table abcd enable ROW MOVEMENT;3.alter table ABCD shrink space;4

13、.alter table abcd disable ROW MOVEMENT;5.select file_id,extent_id,block_id,blocks from dba_extents where segment_name=ABCD;实验结果：已选择31行数据库备份热备要求数据库处于归档状态,在业务不是很繁忙时用热备shut immediatestartup mountalter database archivelog;alter database open;启动归档模式archive log list热备:alter tablespace users begin backup;c

14、opy D:oracleproduct10.2.0oradataorcl users01.dbf e:testusers01.dbf alter tablespace users end backup实验假设users表空间存在热备份该空间有一个T2表（scott.emp）在该表产生交易update t2 set sal=sal+1;commit;alter system switch logfile;然后将USERS表空间的数据文件给它破坏掉比如用一个txt文件覆盖掉copy d:1.txt d:users01.bdf(用txt覆盖掉了users01.bdf)alter system che

15、ckpoint;select file_name,online_status from dba_data_files;产看当前数据库有哪些数据文件需要恢复取回备份文件；recover datafile 4:alter database datafile 4 online;代码Microsoft Windows XP 版本 5.1.2600(C) 版权所有 1985-2001 Microsoft Corp.C:Documents and SettingsAdministratorcopy D:oracleproduct10.2.0oradataorcl e:testusers01.dbfD:or

16、acleproduct10.2.0oradataorclCONTROL01.CTLD:oracleproduct10.2.0oradataorclCONTROL02.CTLD:oracleproduct10.2.0oradataorclCONTROL03.CTLD:oracleproduct10.2.0oradataorclEXAMPLE01.DBFD:oracleproduct10.2.0oradataorclREDO01.LOGD:oracleproduct10.2.0oradataorclREDO02.LOGD:oracleproduct10.2.0oradataorclREDO03.L

17、OGD:oracleproduct10.2.0oradataorclSYSAUX01.DBFD:oracleproduct10.2.0oradataorclSYSTEM01.DBFD:oracleproduct10.2.0oradataorclTEMP01.DBFD:oracleproduct10.2.0oradataorclUNDOTBS01.DBFD:oracleproduct10.2.0oradataorclUSERS01.DBF已复制 1 个文件。C:Documents and SettingsAdministratorcopy D:oracleproduct10.2.0oradata

18、orclusers01.dbf e:testusers01.dbf系统找不到指定的文件。C:Documents and SettingsAdministratorcopy D:oracleproduct10.2.0oradataorclusers01.dbf e:testusers01.dbf改写 e:testusers01.dbf 吗? (Yes/No/All): y已复制 1 个文件。C:Documents and SettingsAdministratorcopy D:oracleproduct10.2.0oradataorclusers01.dbf e:testusers01.dbf改

19、写 e:testusers01.dbf 吗? (Yes/No/All): y已复制 1 个文件。C:Documents and SettingsAdministratorcopy D:oracleproduct10.2.0oradata D:oracleproduct10.2.0oradataorclusers01.dbf;D:oracleproduct10.2.0oradata1.txt另一个程序正在使用此文件，进程无法访问。已复制 0 个文件。C:Documents and SettingsAdministratorcopy D:oracleproduct10.2.0oradata D:o

20、racleproduct10.2.0oradataorclusers01.dbf;D:oracleproduct10.2.0oradata1.txt另一个程序正在使用此文件，进程无法访问。已复制 0 个文件。C:Documents and SettingsAdministratorcopy D:oracleproduct10.2.0oradata1.txt D:oracleproduct10.2.0oradataorclusers01.dbf;已复制 1 个文件。C:Documents and SettingsAdministrator8888*Microsoft Windows XP 版本

21、5.1.2600(C) 版权所有 1985-2001 Microsoft Corp.C:Documents and SettingsAdministratorsqlplus/nologSQL*Plus: Release 10.2.0.1.0 - Production on 星期五 11月 30 10:37:43 2012Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL conn /as sysdba已连接。SQL shut immediate数据库已经关闭。已经卸载数据库。ORACLE 例程已经关闭。SQL startup mo

22、untORACLE 例程已经启动。Total System Global Area 289406976 bytesFixed Size 1248576 bytesVariable Size 100664000 bytesDatabase Buffers 180355072 bytesRedo Buffers 7139328 bytes数据库装载完毕。SQL alter database archivelog 2 archive log list 3 alter tablespace users begin backup 4 alter tablespace users begin backup

23、;archive log list*第 2 行出现错误:ORA-00933: SQL 命令未正确结束SQL alter database archivelog;数据库已更改。SQL alter v 2 ;alter v *第 1 行出现错误:ORA-00940: 无效的 ALTER 命令SQL alter database open 2 ;数据库已更改。SQL alter tablespace users begin backup 2 ;表空间已更改。SQL copy D:oracleproduct10.2.0oradataorcl e:testusers01.dbf用法: COPY FROM

24、 TO () USING : 数据库字符串, 例如, hr/your_passwordd:chicago-mktg : 下列关键字之一: APPEND, CREATE, INSERT 或 REPLACE: 目标表名称 : 用逗号分隔的目标列别名列表 : 任何有效的 SQL SELECT 语句缺失的 FROM 或 TO 子句使用当前的 SQL*Plus 连接SQL copy D:oracleproduct10.2.0oradataorcl e:testusers01.dbf ;用法: COPY FROM TO () USING : 数据库字符串, 例如, hr/your_passwordd:ch

25、icago-mktg : 下列关键字之一: APPEND, CREATE, INSERT 或 REPLACE: 目标表名称 : 用逗号分隔的目标列别名列表 : 任何有效的 SQL SELECT 语句缺失的 FROM 或 TO 子句使用当前的 SQL*Plus 连接SQL alter tablespace users end backup;表空间已更改。SQL create table t2 tablespace users as select * from scott.emp;表已创建。SQL alter tablespace users begin backup;表空间已更改。SQL alt

26、er tablespace users end backup;表空间已更改。SQL update t2 set sal=sal+1;已更新14行。SQL commit;提交完成。SQL alter system switch logfile;系统已更改。SQL alter system checkpoint;系统已更改。SQL select file_name,online_status from dba_data_files;FILE_NAME-ONLINE_-D:ORACLEPRODUCT10.2.0ORADATAORCLUSERS01.DBFRECOVERD:ORACLEPRODUCT1

27、0.2.0ORADATAORCLSYSAUX01.DBFONLINED:ORACLEPRODUCT10.2.0ORADATAORCLUNDOTBS01.DBFONLINEFILE_NAME-ONLINE_-D:ORACLEPRODUCT10.2.0ORADATAORCLSYSTEM01.DBFSYSTEMD:ORACLEPRODUCT10.2.0ORADATAORCLEXAMPLE01.DBFONLINESQL select * from v$recovery_log;未选定行SQL select file#,name from v$datafile; FILE#-NAME- 1D:ORACLEPRODUCT10.2.0ORADATAORCLSYSTEM01.DBF 2D:ORACLEPRODUCT10.2.0ORADATAORCLUNDOTBS01.DB