张烈分享：实验驱动的Oracle数据库实战指南

Oracle

sql*plus

需积分: 9 57 浏览量更新于2024-07-31 收藏 3.15MB DOC 举报

身份认证购VIP最低享 7 折!

30元优惠券

《张烈－Oracle数据库讲义》是一本实用的教材，专为想学习Oracle数据库的人设计。作者张烈和张建中结合实际教学经验和学员需求，提炼出了一种以实验为主的高效学习方法。书中详细讲解了六个核心部分： 1. **SQL基础**：这部分涵盖了基本的SQL语法和查询技巧，强调通过实践来深化对SQL语言的理解。 2. **PL/SQL基础**：PL/SQL是Oracle数据库的编程语言，这部分内容帮助读者掌握编写存储过程、函数和触发器等高级SQL操作。 3. **数据库体系结构与包应用**：这部分介绍了Oracle数据库的内部工作原理以及如何利用内置的数据库包进行更高效的管理和操作。 4. **数据库网络配置**：涉及网络连接设置，确保数据库服务器与其他系统之间的通信顺畅。 5. **数据库备份和恢复**：数据库管理的重要环节，包括定期备份策略和在出现问题时的数据恢复技巧。 6. **数据库优化**：探讨如何提高数据库性能，包括索引设计、查询优化和资源管理等方面。作者强调，虽然Oracle有多个版本，但其核心概念是相通的，关键在于掌握正确的学习方法，理解数据库管理的本质。对于初学者来说，Sql*Plus是最推荐的管理工具，因为它能直接操作数据库，深入理解Oracle。日常数据库维护中的关键工作是备份，保持数据安全。而体系结构的学习是基石，理解了它才能更好地进行备份和优化。衡量数据库水平的标准是能否自如地使用SQL查询，以及能否理解并解决遇到的问题。图形界面在某些方面可以辅助学习，但并非必须。作者认为，真正理解数据库的工作原理比依赖图形工具更为重要。安装Oracle数据库在顺利情况下看似简单，但实际上涉及很多细节和可能出现的问题，需要具备一定的技术背景。最后，如果在实践中遇到结果与预期不符，可能是由于环境差异、错误输入或对理论理解不足等原因，强调实践中的观察和反思是学习过程中不可或缺的一部分。《张烈－Oracle数据库讲义》是一本注重实践操作和经验分享的实用教程，适合希望深入学习Oracle数据库的读者。

资源详情

资源推荐

Total System Global Area 167772160 bytes...................................................................................153

D:\ORACLE\ORADATA\ORA10\CONTROL01.CTL.................................................................153

实验 51：增加控制文件的个数....................................................................................153

System altered.................................................................................................................................154

D:\ORACLE\ORADATA\ORA10\CONTROL01.CTL.................................................................154

D:\ORACLE\ORADATA\ORA10\CONTROL02.CTL.................................................................154

SQL> host copy D:\ORACLE\ORADATA\ORA10\CONTROL01.CTL D:\ORACLE\ORADATA\

ORA10\CONTROL02.CTL............................................................................................................154

D:\ORACLE\ORADATA\ORA10\CONTROL01.CTL.................................................................155

D:\ORACLE\ORADATA\ORA10\CONTROL02.CTL.................................................................155

System altered.................................................................................................................................155

SQL> host copy D:\ORACLE\ORADATA\ORA10\CONTROL01.CTL D:\ORACLE\ORADATA\

ORA10\CONTROL03.CTL............................................................................................................155

D:\ORACLE\ORADATA\ORA10\CONTROL01.CTL.................................................................156

D:\ORACLE\ORADATA\ORA10\CONTROL02.CTL.................................................................156

日志文件.................................................................................................................................157

SQL> select to_char(FIRST_TIME,'yyyy/mm/dd') day,count(*) from.........................................158

TO_NUMBER('FFFFFFFFFFFF','XXXX.....................................................................................160

ALTER SYSTEM DUMP LOGFILE 'FileName';..........................................................................161

实验 52：日志文件管理和 nologging 的实现..............................................................161

Table dropped..................................................................................................................................163

SQL> drop table t2 purge;...............................................................................................................163

Table dropped..................................................................................................................................163

SQL> create table t1 as select * from emp;....................................................................................163

Table created....................................................................................................................................163

SQL> insert into t1 select * from t1;..............................................................................................163

SQL> /.............................................................................................................................................163

SQL> commit;.................................................................................................................................163

SQL> set autotrace trace stat...........................................................................................................163

Statistics...........................................................................................................................................163

SQL> rollback;................................................................................................................................163

Rollback complete...........................................................................................................................164

Statistics...........................................................................................................................................164

SQL> rollback;................................................................................................................................164

Rollback complete...........................................................................................................................164

SQL> insert into t2 select * from t1 nologging;.............................................................................164

Statistics...........................................................................................................................................164

SQL> rollback;................................................................................................................................164

Rollback complete...........................................................................................................................164

SQL> insert /*+ append */ into t2 select * from t1 nologging;......................................................164

FORCE_..........................................................................................................................................165

YES..................................................................................................................................................165

Database altered..............................................................................................................................165

SQL> select FORCE_LOGGING from v$database;......................................................................165

FORCE_..........................................................................................................................................165

NO...................................................................................................................................................165

SQL> conn scott/tiger.....................................................................................................................165

Statistics...........................................................................................................................................165

SQL> insert into t2 select * from t1 nologging;.............................................................................166

Statistics...........................................................................................................................................166

SQL> rollback;................................................................................................................................166

Rollback complete...........................................................................................................................166

Statistics...........................................................................................................................................166

数据文件.................................................................................................................................167

实验 53：建立新的表空间............................................................................................167

Tablespace altered...........................................................................................................................168

SQL> select tablespace_name,file_name,ceil(bytes/1024/1024) mb.............................................168

Offline 表空间.................................................................................................................................169

实验 54：更改表空间的名称，更改数据文件的名称...............................................170

SQL> host copy D:\ORACLE\ORADATA\ORA10\TS1.1 D:\ORACLE\ORADATA\ORA10\

TS1.dbf............................................................................................................................................171

SQL> host copy D:\ORACLE\ORADATA\ORA10\TS1.2 D:\ORACLE\ORADATA\ORA10\

TS1_2.dbf........................................................................................................................................172

SQL> alter database rename file 'D:\ORACLE\ORADATA\ORA10\TS1.2'................................172

Database altered..............................................................................................................................172

DUMP OF DATA FILES: 8 files in database.................................................................................173

DATA FILE #1:...............................................................................................................................173

表空间.....................................................................................................................................173

System 表空间................................................................................................................................173

实验 55：建立临时表空间............................................................................................174

TABLESPACE_NAME FILE_NAME...........................................................................................175

实验 56：大文件表空间和表空间的管理模式...........................................................176

Bigfile 大文件表空间（10g 新特性）..........................................................................................176

数据库的逻辑结构.................................................................................................................178

Block（块）最基本的存储单元...................................................................................................178

实验 57：建立表，描述表的存储属性........................................................................180

SQL> select BLOCKS,EMPTY_BLOCKS,AVG_SPACE,NUM_FREELIST_BLOCKS...........180

2 from user_tables where table_name='E3';..................................................................................180

BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS................................180

SQL> ANALYZE TABLE E3 COMPUTE STATISTICS;.............................................................180

BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS................................181

SQL> ANALYZE TABLE E3 COMPUTE STATISTICS;.............................................................181

BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS................................181

SQL> ANALYZE TABLE E3 COMPUTE STATISTICS;.............................................................181

Table analyzed.................................................................................................................................181

SQL> select BLOCKS,EMPTY_BLOCKS,AVG_SPACE,NUM_FREELIST_BLOCKS...........181

BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS................................181

SQL> commit;.................................................................................................................................181

Commit complete............................................................................................................................181

SQL> ANALYZE TABLE E3 COMPUTE STATISTICS;.............................................................181

Table analyzed.................................................................................................................................182

SQL> select BLOCKS,EMPTY_BLOCKS,AVG_SPACE,NUM_FREELIST_BLOCKS...........182

BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS................................182

SQL> insert into e3 select * from e3;.............................................................................................182

SQL> commit;.................................................................................................................................182

Commit complete............................................................................................................................182

SQL> ANALYZE TABLE E3 COMPUTE STATISTICS;.............................................................182