盘上),
减少了用户之间的I/O竞争、读写数据与写读索引的竞争(用户表空间、索引表空间也分别放在不同的物理磁盘上)
规划:
C:盘、NT系统,Oracle系统
D:盘、数据表空间1(3GB、自动扩展)、回滚表空间1(1GB、自动扩展)
E:盘、数据表空间2(3GB、自动扩展)、回滚表空间2(1GB、自动扩展)
F:盘、索引表空间1(2GB、自动扩展)、临时表空间1(0.5GB、不自动扩展)
G:盘、索引表空间2(2GB、自动扩展)、临时表空间2(0.5GB、不自动扩展)
注:这只是一个简单的规划,实际规划要依系统需求来定,尽量减少I/O竞争
实现:
1、首先查看系统有哪些回滚段及其状态。
SQL> col owner format a20
SQL> col status format a10
SQL> col segment_name format a20
SQL> col tablespace_name format a20
SQL> SELECT OWNER,SEGMENT_NAME,TABLESPACE_NAME,SUM(BYTES)/1024/1024 M
2 FROM DBA_SEGMENTS
3 WHERE SEGMENT_TYPE='ROLLBACK'
4 GROUP BY OWNER,SEGMENT_NAME,TABLESPACE_NAME
5 /
OWNER SEGMENT_NAME TABLESPACE_NAME M
-------------------- -------------------- -------------------- ---------
SYS RB1 ROLLBACK_DATA .09765625
SYS RB10 ROLLBACK_DATA .09765625
SYS RB11 ROLLBACK_DATA .09765625
SYS RB12 ROLLBACK_DATA .09765625
SYS RB13 ROLLBACK_DATA .09765625
SYS RB14 ROLLBACK_DATA .09765625
SYS RB15 ROLLBACK_DATA .09765625
SYS RB16 ROLLBACK_DATA .09765625
SYS RB2 ROLLBACK_DATA .09765625
SYS RB3 ROLLBACK_DATA .09765625
SYS RB4 ROLLBACK_DATA .09765625
SYS RB5 ROLLBACK_DATA .09765625
SYS RB6 ROLLBACK_DATA .09765625
SYS RB7 ROLLBACK_DATA .09765625
SYS RB8 ROLLBACK_DATA .09765625
SYS RB9 ROLLBACK_DATA .09765625
SYS RB_TEMP SYSTEM .24414063
SYS SYSTEM SYSTEM .1953125
查询到18记录.
SQL> SELECT SEGMENT_NAME,OWNER,
2 TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS
3 FROM DBA_ROLLBACK_SEGS
4 /
SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID FILE_ID STATUS
-------------------- -------------------- -------------------- ---------- --------- ----------
SYSTEM SYS SYSTEM 0 1 ONLINE
RB_TEMP SYS SYSTEM 1 1 OFFLINE
RB1 PUBLIC ROLLBACK_DATA 2 3 ONLINE
RB2 PUBLIC ROLLBACK_DATA 3 3 ONLINE
RB3 PUBLIC ROLLBACK_DATA 4 3 ONLINE
RB4 PUBLIC ROLLBACK_DATA 5 3 ONLINE
RB5 PUBLIC ROLLBACK_DATA 6 3 ONLINE
RB6 PUBLIC ROLLBACK_DATA 7 3 OFFLINE
RB7 PUBLIC ROLLBACK_DATA 8 3 OFFLINE
RB8 PUBLIC ROLLBACK_DATA 9 3 OFFLINE
RB9 PUBLIC ROLLBACK_DATA 10 3 OFFLINE
RB10 PUBLIC ROLLBACK_DATA 11 3 OFFLINE
RB11 PUBLIC ROLLBACK_DATA 12 3 OFFLINE
RB12 PUBLIC ROLLBACK_DATA 13 3 OFFLINE
RB13 PUBLIC ROLLBACK_DATA 14 3 OFFLINE
RB14 PUBLIC ROLLBACK_DATA 15 3 OFFLINE
RB15 PUBLIC ROLLBACK_DATA 16 3 OFFLINE
RB16 PUBLIC ROLLBACK_DATA 17 3 OFFLINE
查询到18记录.
2、修改代码如下,可把以下代码存入一.sql文件,如cg_sys.sql,然后以SQL> @cg_sys.sql调用执行。
--注意:各个硬盘上要事先建好oradata目录
--修改现有回滚段,使之失效,下线
alter rollback segment rb1 offline;
alter rollback segment rb2 offline;
alter rollback segment rb3 offline;
alter rollback segment rb4 offline;
alter rollback segment rb5 offline;
alter rollback segment rb6 offline;
alter rollback segment rb7 offline;
alter rollback segment rb8 offline;
alter rollback segment rb9 offline;
alter rollback segment rb10 offline;
alter rollback segment rb11 offline;
alter rollback segment rb12 offline;
alter rollback segment rb13 offline;
alter rollback segment rb14 offline;
alter rollback segment rb15 offline;
alter rollback segment rb16 offline;
--删除原有回滚段
drop rollback segment rb1;
drop rollback segment rb2;
drop rollback segment rb3;
drop rollback segment rb4;
drop rollback segment rb5;
drop rollback segment rb6;
drop rollback segment rb7;
drop rollback segment rb8;
drop rollback segment rb9;
drop rollback segment rb10;
drop rollback segment rb11;
drop rollback segment rb12;
drop rollback segment rb13;
drop rollback segment rb14;
drop rollback segment rb15;
drop rollback segment rb16;
--建数据表空间1
--收款、库存、订货、远程通信
create tablespace USER_DATA1 datafile
'd:\oradata\user1_1.ora' size 512M,
'd:\oradata\user1_2.ora' size 512M,
'd:\oradata\user1_3.ora' size 512M,
'd:\oradata\user1_4.ora' size 512M,
'd:\oradata\user1_5.ora' size 512M,
'd:\oradata\user1_6.ora' size 512M
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);
--initial 128K,因为,用户建在表空间上,而表建在用户里,为用户所拥有,
--用户继承数据表空间的存储参数,表继承用户的存储参数
--如果initial设的过大,如:5M,则每建一个空表就要占用5M的空间,即使一条记录也没有
--AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED,设置数据文件自动扩展,每一次扩展增加5M,最大空间不受限
--建数据表空间2
--物价、人事、结算、财务、总经理、合同、统计
create tablespace USER_DATA2 datafile
'e:\oradata\user2_1.ora' size 512M,
'e:\oradata\user2_2.ora' size 512M,
'e:\oradata\user2_3.ora' size 512M,
'e:\oradata\user2_4.ora' size 512M,
'e:\oradata\user2_5.ora' size 512M,
'e:\oradata\user2_6.ora' size 512M
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);
--建索引表空间1
create tablespace INDEX_DATA1 datafile
'f:\oradata\index1_1.ora' size 512M,
'f:\oradata\index1_2.ora' size 512M,
'f:\oradata\index1_3.ora' size 512M,
'f:\oradata\index1_4.ora' size 512M
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);
--建索引表空间2
create tablespace INDEX_DATA2 datafile
'g:\oradata\index2_1.ora' size 512M,
'g:\oradata\index2_2.ora' size 512M,
'g:\oradata\index2_3.ora' size 512M,
'g:\oradata\index2_4.ora' size 512M
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);
--建回滚表空间1
--设置初始值40M(initial 40M),则每在这个表空间中建一个回滚段,
--此回滚段自动继承此回滚表空间的存储参数,也即默认文件为40M
create tablespace ROLLBACK_DATA1 datafile
'd:\oradata\roll1_1.ora' size 512M,
'd:\oradata\roll1_2.ora' size 512M
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
default storage (initial 40M next 5M pctincrease 0);
--建回滚表空间2
create tablespace ROLLBACK_DATA2 datafile
'e:\oradata\roll2_1.ora' size 512M,
'e:\oradata\roll2_2.ora' size 512M
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
default storage (initial 40M next 5M pctincrease 0);
--建临时表空间1
create tablespace TEMPORARY_DATA1 datafile
'f:\oradata\temp1_1.ora' size 512M
default storage (initial 10M next 3M pctincrease 0);
--建临时表空间2
create tablespace TEMPORARY_DATA2 datafile
'g:\oradata\temp2_1.ora' size 512M
default storage (initial 10M next 3M pctincrease 0);
--使其真正成为临时的
alter tablespace TEMPORARY_DATA1 temporary;
alter tablespace TEMPORARY_DATA2 temporary;
--建立新的回滚段,每个都一样大,不同大小的回滚段没有什么意义,系统是随机选择的。
--建多少个,根据并发访问用户的多少,
--如果你们公司每天有50-100个人员使用Oracle系统开发的管理软件,应该20个以上
create public rollback segment rb01 tablespace rollback_data1;
create public rollback segment rb02 tablespace rollback_data1;
create public rollback segment rb03 tablespace rollback_data1;
create public rollback segment rb04 tablespace rollback_data1;
create public rollback segment rb05 tablespace rollback_data1;
create public rollback segment rb06 tablespace rollback_data1;
create public rollback segment rb07 tablespace rollback_data1;
create public rollback segment rb08 tablespace rollback_data1;
create public rollback segment rb09 tablespace rollback_data2;
create public rollback segment rb10 tablespace rollback_data2;
--前8个建在回滚表空间1中,后8个在回滚表空间2
create public rollback segment rb11 tablespace rollback_data2;
create public rollback segment rb12 tablespace rollback_data2;
create public rollback segment rb13 tablespace rollback_data2;
create public rollback segment rb14 tablespace rollback_data2;
create public rollback segment rb15 tablespace rollback_data2;
create public rollback segment rb16 tablespace rollback_data2;
create public rollback segment rb17 tablespace rollback_data2;
create public rollback segment rb18 tablespace rollback_data2;
create public rollback segment rb19 tablespace rollback_data2;
create public rollback segment rb20 tablespace rollback_data2;
--使回滚段online,即有效
alter rollback segment rb01 online;
alter rollback segment rb02 online;
alter rollback segment rb03 online;
alter rollback segment rb04 online;
alter rollback segment rb05 online;
alter rollback segment rb06 online;
alter rollback segment rb07 online;
alter rollback segment rb08 online;
alter rollback segment rb09 online;
alter rollback segment rb10 online;
alter rollback segment rb11 online;
alter rollback segment rb12 online;
alter rollback segment rb13 online;
alter rollback segment rb14 online;
alter rollback segment rb15 online;
alter rollback segment rb16 online;
alter rollback segment rb17 online;
alter rollback segment rb18 online;
alter rollback segment rb19 online;
alter rollback segment rb20 online;
--查看现有回滚段及其状态
col segment format a30
SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS;
--查看数据文件及其所在表空间、大小、状态
col file_name format a40
col tablespace_name format a20
select file_name,file_id,tablespace_name,bytes,status from dba_data_files;
至此,表空间重新规划完毕,这里讲的比较通俗,还有好多参数值得设置,能够把Oracle设置到最优的境界,
表空间设置完了,下面,就该好好的整理一下Oracle的内存区了,
Oracle很有意思,内存越大,效果越明显,所以有必要好好调整一下SGA区,也就是主要配置ininorcl.ora参数文件。
8.用户如何有效地利用数据字典
ORACLE的数据字典是数据库的重要组成部分之一,它随着数据库的产生而产生, 随着数据库的变化而变化,
体现为sys用户下的一些表和视图。数据字典名称是大写的英文字符。
数据字典里存有用户信息、用户的权限信息、所有数据对象信息、表的约束条件、统计分析数据库的视图等。
我们不能手工修改数据字典里的信息。
很多时候,一般的ORACLE用户不知道如何有效地利用它。
dictionary 全部数据字典表的名称和解释,它有一个同义词dict
dict_column 全部数据字典表里字段名称和解释
如果我们想查询跟索引有关的数据字典时,可以用下面这条SQL语句:
SQL>select * from dictionary where instr(comments,'index')>0;
如果我们想知道user_indexes表各字段名称的详细含义,可以用下面这条SQL语句:
SQL>select column_name,comments from dict_columns where table_name='USER_INDEXES';
依此类推,就可以轻松知道数据字典的详细名称和解释,不用查看ORACLE的其它文档资料了。
下面按类别列出一些ORACLE用户常用数据字典的查询使用方法。
一、用户
查看当前用户的缺省表空间
SQL>select username,default_tablespace from user_users;
查看当前用户的角色
SQL>select * from user_role_privs;
查看当前用户的系统权限和表级权限
SQL>select * from user_sys_privs;
SQL>select * from user_tab_privs;
二、表
查看用户下所有的表
SQL>select * from user_tables;
查看名称包含log字符的表
SQL>select object_name,object_id from user_objects
where instr(object_name,'LOG')>0;
查看某表的创建时间
SQL>select object_name,created from user_objects where object_name=upper('&table_name');
查看某表的大小
SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments
where segment_name=upper('&table_name');
查看放在ORACLE的内存区里的表
SQL>select table_name,cache from user_tables where instr(cache,'Y')>0;
三、索引
查看索引个数和类别
SQL>select index_name,index_type,table_name from user_indexes order by table_name;
查看索引被索引的字段
SQL>select * from user_ind_columns where index_name=upper('&index_name');
查看索引的大小
SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments
where segment_name=upper('&index_name');
四、序列号
查看序列号,last_number是当前值
SQL>select * from user_sequences;
五、视图
查看视图的名称
SQL>select view_name from user_views;
查看创建视图的select语句
SQL>set view_name,text_length from user_views;
SQL>set long 2000; 说明:可以根据视图的text_length值设定set long 的大小
SQL>select text from user_views where view_name=upper('&view_name');
六、同义词
查看同义词的名称
SQL>select * from user_synonyms;
七、约束条件
查看某表的约束条件
SQL>select constraint_name, constraint_type,search_condition, r_constraint_name
from user_constraints where table_name = upper('&table_name');
SQL>select c.constraint_name,c.constraint_type,cc.column_name
from user_constraints c,user_cons_columns cc
where c.owner = upper('&table_owner') and c.table_name = upper('&table_name')
and c.owner = cc.owner and c.constraint_name = cc.constraint_name
order by cc.position;
八、存储函数和过程
查看函数和过程的状态
SQL>select object_name,status from user_objects where object_type='FUNCTION';
SQL>select object_name,status from user_objects where object_type='PROCEDURE';
查看函数和过程的源代码
SQL>select text from all_source where owner=user and name=upper('&plsql_name');
九、触发器
查看触发器
set long 50000;
set heading off;
set pagesize 2000;
select
'create or replace trigger "' ||
trigger_name || '"' || chr(10)||
decode( substr( trigger_type, 1, 1 ),
'A', 'AFTER', 'B', 'BEFORE', 'I', 'INSTEAD OF' ) ||
chr(10) ||
triggering_event || chr(10) ||
'ON "' || table_owner || '"."' ||
table_name || '"' || chr(10) ||
decode( instr( trigger_type, 'EACH ROW' ), 0, null,
'FOR EACH ROW' ) || chr(10) ,
trigger_body
from user_triggers;
9.oracle数据库的各种文件类型和默认的存放位置这方面的资料
查询v$logfile查询oracle的重做日志文件的位置
tzxd@webora9> col member format a60 heading 'log filename'
tzxd@webora9> select * from v$logfile;
查询v$controlfile得到oracle的控制文件的位置
tzxd@webora9> col name format a60 heading 'control filename'
tzxd@webora9> select * from v$controlfile;
查询v$tempfile得到系统中临时表空间的临时表文件的位置
tzxd@webora9> col name format a50 heading 'temp filename'
tzxd@webora9> col bytes format 999999 heading 'M Bytes'
tzxd@webora9> col file# format 99
tzxd@webora9> col ts# format 99
tzxd@webora9> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
查询v$datafile得到系统中的数据文件,系统文件,回滚段文件的位置
tzxd@webora9> select file#,creation_time,ts#,status,enabled,bytes/1024/1024 bytes,name
2 from v$datafile;
使用show parameter查出用户存放trc文件的位置
tzxd@webora9> show parameter dump_dest
10. Oracle数据库的几种启动和关闭方式有以下几种启动方式:
1、startup nomount
非安装启动,这种方式启动下可执行:重建控制文件、重建数据库
读取init.ora文件,启动instance,即启动SGA和后台进程,这种启动只需要init.ora文件。
2、startup mount dbname
安装启动,这种方式启动下可执行:
数据库日志归档、
数据库介质恢复、
使数据文件联机或脱机,
重新定位数据文件、重做日志文件。
执行“nomount”,然后打开控制文件,确认数据文件和联机日志文件的位置,
但此时不对数据文件和日志文件进行校验检查。
3、startup open dbname
先执行“nomount”,然后执行“mount”,再打开包括Redo log文件在内的所有数据库文件,
这种方式下可访问数据库中的数据。
4、startup,等于以下三个命令
startup nomount
alter database mount
alter database open
5、startup restrict
约束方式启动
这种方式能够启动数据库,但只允许具有一定特权的用户访问
非特权用户访问时,会出现以下提示:
ERROR:
ORA-01035: ORACLE 只允许具有 RESTRICTED SESSION 权限的用户使用
6、startup force
强制启动方式
当不能关闭数据库时,可以用startup force来完成数据库的关闭
先关闭数据库,再执行正常启动数据库命令
7、startup pfile=参数文件名
带初始化参数文件的启动方式
先读取参数文件,再按参数文件中的设置启动数据库
例:startup pfile=E:\Oracle\admin\oradb\pfile\init.ora
8、startup EXCLUSIVE
============================================
有三种启动方式:
1、shutdown normal
正常方式关闭数据库。
2、shutdown immediate
立即方式关闭数据库。
在SVRMGRL中执行shutdown immediate,数据库并不立即关闭,
而是在Oracle执行某些清除工作后才关闭(终止会话、释放会话资源),
当使用shutdown不能关闭数据库时,shutdown immediate可以完成数据库关闭的操作。
3、shutdown abort
直接关闭数据库,正在访问数据库的会话会被突然终止,
如果数据库中有大量操作正在执行,这时执行shutdown abort后,重新启动数据库需要很长时间。
11.ORACLE数据库常见问题诊断方法---(常见错误篇)
1.ORA-12571、ORA-03113、ORA-03114、ORA-01041
特征:客户端(代理或应用服务器)有时报这类断连错误
原因:如果偶尔出现一次,则可能为网络原因或用户异常中止,如果经常出现则为客户端与服务端的字符集不一致。
措施:如果偶尔出现,可在服务端的协议配置文件PROTOCOL.ORA中增加一行TCP.NODELAY=YES;
如果经常出现,则为客户端与服务端字符集不一致或网络原因。
客户端的字符集在注册表里定义:HKEY__LOCAL__MACHINE/SOFTWARE/ORACLE/NLS__LANG
在客户端注册表中的TCP参数项中设置TCPMAXDATARETRANSMITIONS=20。
2.ORA-01000
特征:达到会话允许的最大游标数
原因:达到会话允许的最大游标数
措施:有两种解决方法:
(1)在初始化文件INIT<SID>.ORA文件中增加OPEN_CURSORS的数量,一般要求大于200。
(2)在应用级,与开发工具有关,例如设置MAXOPEN_CURSORS等。
3.ORA-01545
特征:某个回滚段不可用
原因:
(1)当使回滚段ONLINE时,但回滚段不可用,例如回滚段所在表空间OFFLINE;
(2)当使回滚段ONLINE时,但回滚段已ONLINE,例如回滚段被使用两次,典型的案例如OPS方式时,回滚段不能公有;
(3)删除回滚段时,回滚段中有活动的事务;措施:
(1)确保回滚段可
(2)从初始化文件INIT<SID>.ORA的参数ROLLBACK)SEGMENTS中删除指定的回滚段。
(3)可以将回滚段所在表空间删除,取消UNDO事务
4.ORA-0165x
特征:表空间没有足够的空间供分配
原因:表空间已满;存储参数不合理,NEXT太小;没有连续的区间
措施:如果表空间已满,则需为表空间增加文件;如果存储参数不合理,则需增加INITIAL和NEXT;如果没有连续的区间,