- 网站资料
http://www.oracle.com/technology/global/cn/obe/11gr1_db/bidw/partition/partition.htm - 计算本星期的起始结束日期
--得到星期一的日期
select trunc(sysdate,'DD')-to_char(sysdate,'D')+2 from dual;
--得到星期天的日期
select trunc(sysdate,'DD')-to_char(sysdate,'D')+8 from dual; - 使用explain plan分析sql;
使用如下的两个命令显示查询执行的路径:
set autotrace on 命令
explain plan 命令
set autotrace on 命令返回查询数据,显示sql执行路径;
explain plan 显示sql执行路径,不执行查询操作,可见,explain plan更具实用性;
下面是我的操作记录
登录
sqlplus dev/chenli@testdb;
执行
set autotrace on;
提示错误信息:
SP2-0613: Unable to verify PLAN_TABLE format or existence
SP2-0611: Error enabling EXPLAIN report
错误信息告诉我们,没有plan_table,由于plan_table表结构在不同版本的oracle下会有所不同,所以不要随便创建这个表,在oracle的安装路径下面,提供了创建plan_table表的sql脚本,该sql脚本文件的路径是:$ORACLE_HOME/rdbms/admin/utlxplan.sql
在sqlplusq模式下,执行如下命令,创建plan_table
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
返回
Table created.
也可以把这个文件中的建表sql拷贝出来,到plsqldever中执行,创建plan_table表;
sqlplus模式下,再次执行set autotrace on;
没有错误信息返回;
这时再执行sql语句,就会在返回结果后面,显示sql执行路径,和分析结果;
用explain plan;不先运行查询sql的情况下,生成查询的执行路径;
命令格式如下:
explain plan
for
sql; - 查看组合分区建表脚本
在plsqldever中,只能看到组合分区表的表结构,看不到建表脚本,为了查看建表脚本,可以通过一个oracle自带的程序包来完成,具体操作sql如下:
SELECT DBMS_METADATA.GET_DDL('TABLE','TABLE_NAME','USER_NAME') FROM DUAL;
在上面的参数中,TABLE_NAME是被查询的表的名字,USER_NAME表归属的用户名字.
如果如上格式填写正确的参数,执行sql,就可以抓去到组合分区表的建表脚本.
如果执行如上sql的时候,出现如下错误:
ORA-31603: object "CP_EXT_PKGINFO_HIS_T" of type TABLE not found in schema
"SCHEMA"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3209
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3594
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4483
ORA-06512: at "SYS.DBMS_METADATA", line 326
ORA-06512: at "SYS.DBMS_METADATA", line 410
ORA-06512: at "SYS.DBMS_METADATA", line 449
ORA-06512: at "SYS.DBMS_METADATA", line 615
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1
那么,请检查传入的三个参数,最应该注意的是最后一个参数,他是一个数据库用户名;有些参考书上将上面描述成SELECT DBMS_METADATA.GET_DDL('TABLE','TABLE_NAME','SCHEMA') FROM DUAL;
其实SCHEMA在oracle中就是一个oracle用户 - 取消日志记录,提高性能
在海量插入数据和创建表的时候,不记录日志比记录日志在性能上有很大的提高,使用方法
insert into table_a nologging select * from table_b;
create table_a nologging as select * from table_b; - DBA的日常工作
就是每天上班分析一下日志文件。
查看其中有有错误或者警告等信息。
我的感觉，数据库如果平稳运行与平时的监控分不开的。平常经验的累积与处理突发事件的能力有很大关系。 - oracle初始化参数配置文件
oracle的配置文件init.ora,init<SID>.ora存放位置在$ORACLE_HOME/dbs/目录,可以通过find $ORACLE_HOME -name init*.ora查看具体的路径,根据自己的需求,修改OPTIMIZER_MODE等参数; - 确定oracle init.ora 文件据在位置
1.用sqlplus 以 sys 用户登陆。 连接后，用show parameter spfile可看到
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string ?/dbs/spfile@.ora
init.ora 文件就放在与 /dbs/spfile@.ora 同一个目录中
注意:我测试过,用sqlplus登录的时候,不一定需要sys用户.
2. 退出 sqlplus , 执行下面的命令
cd $ORACLE_HOME
ls 看看在这个目录下是否有 dbs 子目录
3. 如果有，进行 dbs 子目录，你就可以看到 init.ora 文件了 - 查看oracle参数1
查看oracle参数的版本有多种,我列出几个,希望大家回复补充
select * from v$nls_parameters;
SQL> show parameter db_;
SQL>show parameter db_c;
select userenv('language') from dual; - 查看oracle参数2
$ORACLE_HOME/network/admin/listener.ora文件中定义了监听端口和SID_NAME等基本参数;
可以在sqlplus上面执行show parameter service_name;查看service_name
可以根据端口号,主机,SID_NAME等参数在tnsnames.ora文件中配置到数据库的连接串,建立客户端应用程序到数据库的连接;
唯一不需要配置tnsnames.ora文件就可以建立到oracle数据库连接的只有java的thin方式连接;
其他的客户端到数据库的连接方式,哪怕是sqlplus命令都需要tnsnames.ora中的配置的连接串; - init.ora,init<sid>.ora,spfile<sid>.ora概述
spfile
9i 后缺省会使用spfile，即spfileSID.ora 或spfile.ora
SPFILE的作用是可以远程使用Alter System命令更改例程参数，而不用手工修改参数文件
可以在用Startup命令启动例程时，指定使用SPFILE或者PFILE
如:startup pfile = 'pfile.ora' 或者startup pfile = 'spfile.ora' ,根据自己的目录情况,将pfile,spfile参数指向不同文件的完整路径;
PFile是静态参数(文本文件)，SPFile是动态参数(二进制文件)，在SQLPLUS 中：
可以由PFile建立SPFile
create spfile from pfile
也可以由SPfile建立PFile
create pfile from spfile
如:create spfile from pfile='oracle_home/initSID.ora' ;'oracle_home/initSID.ora' 是具体的文本配置文件路径;
如果执行过create spfile from pfile;那么数据库启动时，先找你的pfile=oracle/oracle9i/dbs/spfile<sid>.ora。如果找不到，就找pfile=oracle/oracle9i/dbs/init<sid>.ora。如果还找不到再找init.ora。
一般配置oracle启动参数的步骤为:
1、修改initSID.ora
2、shutdown immediate
startup pfile='oracle_home/initSID.ora' --你的initSID.ora路径
看修改的参数是否合理，如果一些正常。
3、create spfile from pfile='oracle_home/initSID.ora' --你的initSID.ora路径
4、shutdown immediate
5, startup
注意:
9i和10g也可以在控制台修改
8I只能修改init.ora
总之这三个文件都是oracle的配置文件,根据自己的情况,采用其中一个来初始化oracle的启动参数;默认的查找顺序是spfile<sid>.ora,init<sid>.ora,init.ora;
查看转贴:Oracle主要的配置文件解释(转贴) - oracle统计函数rollup和CUBE
rollup和CUBE都是和group by 联合使用,对group by后的字段进行统计;
rollup是在记录最后添加一行总计记录,CUBE是按照总计的字段进行排序;
如:
select sum_item_id, count(*)
from custom_type_tm
group by sum_item_id
结果:
SUM_ITEM_ID COUNT(*)
0 4
1 7
2 5
3 5
4 5
1
---------------------------------------------------------------------
select sum_item_id,count(*) A
from custom_type_tm
group by rollup(sum_item_id);
结果为:
SUM_ITEM_ID A
0 4
1 7
2 5
3 5
4 5
1
27
----------------------------------------------------------------------
select sum_item_id,count(*) A
from custom_type_tm
group by CUBE(sum_item_id);
结果为:
SUM_ITEM_ID A
1
27
0 4
1 7
2 5
3 5
4 5
大家根据上面的结果应该就能悟出rollup和cube的用法. - 根据sql查询锁表,session等信息
在实际操作中,经常根据all_objects和V$lock_object查找不到相应的被锁对象,但是我们还可以知道一些sql的特征,可以根据sql查找相应的session,从而停止或者解锁相应的资源;
如下sql,我们可以根据sql的特征找出相应的session,然后停止session;
select a.sid,a.SERIAL#,b.sql_text
from v$session a,v$sqlarea b
where a.sql_hash_value=b.hash_value and
first_load_time ... 你执行的时间段
或者是 last_load_time ... 你执行的时间段
使用者可以根据v$session,v$sqlarea视图的一些字段找出相关的session,然后kill
相应的sql:
alter system kill session 'sid,serial#';
例如:
alter system kill session '29,5497'
通常情况下,可以根据all_objects查到object_id,然后在v$lock_object视图中找出锁住记录的session,然后kill - 查看oracle的版本和相关参数
查看版本信息:select * from v$version;
查看参数select * from v$parameter,select * from v$parameter2; - imp和exp导入到处
执行exp user/pass@dbstr进入exp的命令交互模式,进行数据库相关数据的到出;
执行imp user/pass@dbstr进入imp的命令交互模式,进行数据库相关数据的导入;
非交互模式的数据导入导出
exp user/pass@dbstr file=exp.dmp TABLES=table1,table2导出表table1,table2到exp.dmp文件
或者exp userid=user/pass@dbstr file=exp.dmp TABLES=table1,table2
imp user/pass@dbstr FULL=Y file='exp.dmp' 将exp.dmp的数据导入数据库,当然中间还可以有别的参数
imp userid=user/pass@dbstr FULL=Y file='exp.dmp'
下面给出一个具体的例子:
exp custom/linkageonly0@custom2 file=module_role.dmp TABLES=module_role
从custom/linkageonly0@custom2登录的数据库中导出表module_role,包括建表脚本到module_role.dmp文件
imp dev/chenli@testdb FULL=Y file = '/home/ora/module_role.dmp'
将module_role.dmp的数据导入数据库;
当然其中还有很多参数,可以查询相关的资料,找到你想要的参数;
在执行imp的时候,导入的目标库必须建立一个和数据导出的时同名的用户,并赋予相应的connect,resource,create session等权限;
在oracle10g以后,提供了更强大的工具dp,impdp; - svrmgr
svrmgr程序用于oracle的一些管理工作,在oracle9以前的版本中存在,在oracle9及以后的版本中取消,在svrmgr中运行的命令同样可以在sqlplus下操作,当运行svrmgr命令,提示找不到该命令的时候,请检查一下版本,然后采用sqlplus; - spool导出数据到文本文件
exp,expdp可以完成oracle数据的导出,但是如果我们需要把数据导入到别的数据库,exp和expdp导出的数据库文件将受到限制,这时候,我们需要将数据导入到文本文件,这样可以通过一些工具,方便地将数据导入各种处理库;
第一步:用sqlplus登录
第二步:执行导出的文本文件,命令spool exp.txt
第三步:执行sql,将sql查询的数据导出到第二步指定的文本文件;
第四步:关闭spool输出;查看文本文件,导出记录数是否正确;wc -l 现实文本文件的行数.
示例:
sqlplus "/ as sysdba"
SQL>spool dba_names.txt
SQL>select username from dba_users;
SQL>spool off;
SQL>quit
wc -l dba_names.txt;
可以将上面的sql命令和操作写成shell,直接在命令行执行;
------------------------------------------------------------------------------------------------------------------------------
在网上看到的一种方法：
[oracle@jumper oracle]$ more sp.sql
set heading off
set feedback off
set term off
spool a.log
@test.sql
spool off
exit
[oracle@jumper oracle]$ more test.sql
select username from dba_users;
[oracle@jumper oracle]$ sqlplus -S "/ as sysdba" @sp
[oracle@jumper oracle]$ more a.log
SYS
SYSTEM
OUTLN
SCOTT
HAWA
CSMIG
TEST
PERFSTAT
MLQIN
EYGLE
DBSNMP
WMSYS
[oracle@jumper oracle]$
这种方法用SHELL包装时，如果使用下面这种形式：
sqlplus -S "/ as sysdba" <<!
set heading off
set feedback off
set term off
spool a.log
@test.sql
spool off
exit
!
其生成的文件还不是纯数据。只有在shell脚本中使用 sqlplus -S "/ as sysdba" @s.sql (s.sql 就是两个!之间的语句。)
才会生成纯数据。不过这可以使用grep,awk,sed这些工具过滤掉。
--------------------------------------------------------------------------------------------------------------------------------------
相关资料
http://www.knowsky.com/385076.html
http://tophilxx.spaces.live.com/blog/cns!98b961a385806771!156.entry - oracle系统时间
oracle系统时间是和所在os的系统时间一致的,如果在linux/unix系统下,只要在root用户下修改了系统时间,那么oracle系统时间马上生效,如果在非root用户下用date修改时间,那么不能修改系统和oracle的系统时间,只能修改当前shell的时间. - 为什么要对进行表分析
cbo优化器执行的基础就是数据库中的统计资料
cbo是选择执行计划中代价最小的路径来执行的
而获得统计资料的方式就是对表，索引等的分析 - PCTFREE和PCTUSED的概念
PCTFREE存储参数告诉ORACLE什么时候应该将数据块从对象的空闲列表中移出。ORACLE的默认参数是PCTFREE=10；也就是说，一旦一个INSERT操作使得数据块的90%被使用，这个数据块就从空闲列表(free list)中移出。 PCTUSED存储参数告诉ORACLE什么时候将以前满的数据块加到空闲列表中。当记录从数据表中删除时，数据库的数据块就有空间接受新的记录，但只有当填充的空间降到PCTUSED值以下时，该数据块才被连接到空闲列表中，才可以往其中插入数据。PCTUSED的默认值是PCTUSED=40。 （1）PCTUSED较高意味着相对较满的数据块会被放置到空闲列表中，从而有效的重复使用数据块的空间，但会导致I/O消耗。PCTUSED低意味着在一个数据块快空的时候才被放置到空闲列表中，数据块一次能接受很多的记录，因此可以减少I/O消耗，提高性能。 （2）PCTFREE的值较大意味着数据块没有被利用多少就从空闲列表中断开连接，不利于数据块的充分使用。PCTFREE过小的结果是，在更新时可能会出现数据记录迁移(Migration)的情况。(注：数据记录迁移(Migration)是指记录在是UPDATE操作扩展了一个VARCHAR2类型的列或BLOB列后,PCTFREE参数所指定的空间不够扩展，从而记录被ORACLE强制迁移到新的数据块，发生这种情况将较严重的影响ORACLE的性能，出现更新缓慢)。 - 得到创建表空间的ddl语句
调用格式SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TABLESPACE_NAME) FROM DUAL;
示例:
SELECT DBMS_METADATA.GET_DDL('TABLESPACE','DEVBASE') FROM DUAL;
得到创建表空间DEVBASE的ddl语句; - 消除碎片的方法
是否可以将一个表空间的对象移动到另外一个表空间，然后删除表空间，然后根据表空间的建表语句重新建立表空间，这样就能得到一个没有碎片，干净的表空间.
最后将表从另外一个表空间移回重做之后的表空间，重做索引等。 - long字段问题
错误信息：ORA-00997: illegal use of LONG datatype
我在使用alter table table_name move tablespace tablespace_name;的时候，由于table_name有long类型字段，报告上面描述的错误信息。
由于oracle 9i 不支持大数据对象long等字段类型使用insert into select ,create table table_name as select 等复制操作，所以会有如上的错误信息。
由于long在很多方面的限制，使用不便，Oracle也在推出了大对象类型——LOB之后，强烈建议用户不要在使用LONG类型。使用的时候用TO_LOB将long类型转换成lob类型。在oracle数据字典中还经常看到long的使用，主要是oracle为了向后兼容。
所以alter table table_name move tablespace tablespace_name是没有办法解决long字段问题的。建议大家今后不要用long。 - 移动数据文件位置4个步骤
1)将表空间设置成脱机
alter tablespace users offline;
2)复制要移动的文件到位置
host copy d:/orant11/oradata/mis/users01.dbf d:/disk2/oradata/
将数据文件users01.dbf 移动到另外一个目录d:/disk2/oradata/
3)重新命名表空间的的数据文件名
alter tablespace users rename
datafile 'd:/orant11/oradata/mis/users01.dbf'
to 'd:/disk2/oradata/users01.dbf';
修改表空间属性，使之指向数据文件新的位置路径。
4)将表空间设置成联机
alter tablespace users online;
重新将表空间载入. - 移动普通表，索引
移动表:
alter table tab_name move tablespace tbs_name;
上面将tab_name从当前表空间移动到tbs_name表空间，由于对表做了移动，在表上创建的索引将失效，所以必须对建在表上的索引重建。外键，约束，缺省值不会失效。
重建主键和索引：
alter index index_name rebuild;
alter index pk_name rebuild;
重建移动索引：
alter index index_name rebuild tablespace tbs_name;
alter index pk_name rebuild tablespace tbs_name; - 移动分区表和分区索引
移动分区表同样会使表上索引失效，所以都必须重建索引。
分区基本语法：特别提醒注意，如果是单级分区，则使用关键字PARTITION，如果是多级分区，则使用SUBPARTITION替代PARTITION。如果分区或分区索引比较大，可以使用并行move或rebuild，PARALLEL (DEGREE 2);
如：
ALTER TABLE PART_ALARMTEXTDATA move SUBPARTITION ALARMTEXTDATA_050910_ATD01 TABLESPACE users PARALLEL (DEGREE 2);
ALTER INDEX GLOBAL_ALARMTEXTDATA REBUILD tablespace users PARALLEL (DEGREE 2);
ALTER INDEX LOCAL_ALARMTEXTDATA REBUILD SUBPARTITION ALARMTEXTDATA_050910_ATD01 TABLESPACE users PARALLEL (DEGREE 2);
移动表的某个分区：
================
ALTER TABLE tab_name move PARTITION partition_name TABLESPACE tbs_name;
重建全局索引：
================
ALTER INDEX global_index REBUILD;或
ALTER INDEX global_index REBUILD tablespace tbs_name;
重建局部索引：
================
ALTER TABLE tab_name MODIFY PARTITION partition_name REBUILD UNUSABLE LOCAL INDEXES;或
ALTER INDEX local_index_name REBUILD PARTITION partition_name TABLESPACE tbs_name; - 系统视图说明
dba_indexes,user_indexs索引视图，记录了系统中的索引信息
dba_ind_columns,user_ind_columns列索引视图,记录了表的索引，索引包含的列等信息。
user_tables,dba_tables表视图，记录了系统中的表信息，属于那个视图，表的一些参数等。
dba_free_space,user_free_space空闲表空间视图,记录了系统中有那些空闲的标空间，表空间中空闲的块信息等。
dba_tablespace,user_tablespaces查看表空间信息，如块大小，块数等。
dba_segments段信息，记录了段包含多少块，多少区段等信息。
dba_extents区段信息，记录了段包含了多少区段，区段id，文件id，字节数，块数等信息。
dba_data_files查看数据文件信息,位置，块数等，可以查看表空间拥有多少数据文件。
v$log显示数据库的重做日志配置信息
v$logfile显示数据库的重做日志的位置和状态
v$instance 显示数据库的基本信息
v$database 显示数据库的基本信息 - 关闭/启用用户（进程）追踪
ALTER session set sql_trace=false/ture - 删除表中的重复记录
如果在tab_name表中有很多在multi_field上重复的记录，要删除多于的记录，可以采取如下办法。
delete from tab_name a
where a.rowid > (select min(rowid) from tab_name b where b.multi_field=a.multi_field)
删除记录并不能释放表空间，只是把相应的块置为unused状态，只有用truncate才能释放表空间，所以在经常insert和update,delete操作的表空间上会留下很多碎片，消除碎片的方法就是将表移动表空间alter table table_name move tablespace tbs_name; - sql优化
sql优化涉及到很多问题，必须要具体问题具体分析，没有办法模拟。
拿到一个有问题的sql语句之后
先进入sqlplus命令模式，先后执行命令SET AUTOT TRACE和SET TIMING ON，然后执行问题sql，得到执行计划和统计信息，根据这些信息，你可以看到问题出在那一部。为了使被分析的sql尽快返回结果，尽量最好使用hint:/*+ FIRST_ROWS */
如果出现全表扫描，估计是没有使用索引。 - 错误ORA-01460
描述:当用plsql developer打开表进行操作的时候,会经常报告
ORA-01460: unimplemented or unreasonable conversion requested
试想,平时打开别的数据库的时候没有出什么问题,为什么打开这个数据库就出问题了呢.根据错误信息判断,这应该是字符集转换的问题,肯定是服务端,数据库的某些字符集属性的问题.
查看数据库字符集属性,对照问题所在.
SELECT * FROM NLS_DATABASE_PARAMETERS;
select * from props$;
select * from v$parameter;
select * from v$parameter2;
解决办法表props$,将NLS_CHARACTERSET=ZHS16CGB231280,更改为NLS_CHARACTERSET=ZHS16GBK,然后重启数据库即可. -
解oracle锁表问题
首先，查看ORACLE被锁的表select p.spid,c.object_name,b.session_id,b.oracle_username,b.os_user_name from v$process p,v$session a, v$locked_object b,all_objects c where p.addr=a.paddr and a.process=b.process and c.object_id=b.object_id
然后，查看连接的进程
SELECT sid, serial#, username, osuser FROM v$session;
最后，杀掉进程
alter system kill session 'sid,serial#'; -
oracle的默认日期显示格式
查看默认日期显示格式,可以通过如下sql：
select * from nls_session_parameters where parameter='NLS_DATE_FORMAT';
设置当前session默认显示格式
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'
设置数据库默认显示格式，修改参数文件$ORACLEHOME/dbs下，文件命名规则为initSID.ora
可以在里面设置默认的日期显示格式。
-
v$parameter和v$parameter2区别
两个系统视图记录看上去都差不多，v$parameter2一般比v$parameter多两条记录，执行如下sql比对：
select name,type,value from v$parameter2
minus
select name,type,value from v$parameter ;
发现多出三条控制文件参数，原来两个视图的区别就是如下两个sql查询结果的差别，其他都几乎一样。
select name,type,value from v$parameter2 where name ='control_files';
select name,type,value from v$parameter where name = 'control_files';
v$parameter2 中控制文件是三条记录，v$parameter1中控制文件是一条记录。
oracle积累
最新推荐文章于 2023-08-01 15:47:54 发布
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
