Oracle数据库常用脚本

最常用的

　　执行  sqlplus "/as sysdba"  以系统管理员进入数据库

　　关闭数据库: shutdown immediate

　　启动数据库  startup

　　--<em>oracle</em> 常用sql

　　--查询数据库信息

　　select * from v$database;

　　--查询当前实例信息

　　select * from v$instance;

　　--查询数据库版本信息

　　select * from v$version;

　　--查询所有用户(dba权限)

　　select * from dba_users;

　　--查询共享内存中的sql信息，执行较慢

　　select hash_value from v$sqltext where sql_text like '%查询sql%';

　　select sql_text from v$sqltext where hash_value =查找的hash_value order by hash_value，piece;

　　--查询当前系统表空间存储信息

　　select a.file_id，a.file_name， a.TABLESPACE_NAME，a.bytes/1024/1024||'M' as totalspace，

　　nvl(b.freespace，0)||'M' as freespace，a.autoextensible from dba_data_files a，

　　(select sum(bytes)/1024/1024 as freespace，file_id，tablespace_name from dba_free_space

　　group by tablespace_name，file_id ) b

　　where a.file_id=b.file_id(+)

　　order  by a.tablespace_name，file_name;

　　--统计已占用的表空间

　　select sum(a.bytes/1024/1024-nvl(b.freespace，0))||'M' from dba_data_files a，

　　(select sum(bytes)/1024/1024 as freespace，file_id，tablespace_name from dba_free_space

　　group by tablespace_name，file_id ) b

　　where a.file_id=b.file_id(+) and a.tablespace_name='tablespace名称'

　　--查询当前活动session最近一次执行的sql

　　select sql_text from v$sqltext where hash_value=(

　　select   PREV_HASH_VALUE from v$session where status='ACTIVE' and username is not null ) order by piece

　　--查询相关用户的索引情况

　　select a.*，b.column_name，b.column_length from

　　(select owner，index_name，table_name，index_type，tablespace_name

　　from dba_indexes where owner ='用户名' and index_name not in

　　(select constraint_name from dba_constraints where owner='用户名' and constraint_type in ('P'，'R'))

　　)a，(select * from dba_ind_columns b where index_owner='用户名') b

　　where a.index_name=b.index_name

　　order by a.owner，a.table_name，a.index_name

　　--重建索引

　　alter index 索引名称 rebuild nologging;

　　--查看事务回滚率

　　select name，value from v$sysstat where name in('user commits'，'transaction rollbacks');

　　--sql分析优化

　　--脚本位置${oracle_home}/rdbms/admin/utlxplan.sql 创建执行计划表

　　--授权访问

　　create public synonym plan_table for plan_table;

　　grant select，update，insert，delete on plan_table to public;

　　--脚本位置${oracle_home}/sqlplus/admin/plustrce.sql 执行授权角色

　　--spfile

　　--数据库建库完成后，第一次

手工启动手工创建spfile文件，命令格式为

　　create spfile[='filename'] from pfile[='filename'];

　　--判断是采用spfile还是pfile启动数据库的，可以下面sql根据是那种方式<P>###NextPage###</P>

　　select decode(count(*)，0，'pfile'，1，'spfile') from V$spparameter where isspecified='TRUE' and rownum=1;

　　--spfile，pfile创建默认位置为$ORACLE_HOME/dbs/，$ORACLE_HOME/database/(windows)

　　--启动时公司搜索默认位置下spfile.ora，spfile$ORACLE_SID.ora，init$ORACLE_SID.ora

　　--采用指定的方式启动

　　startup pfile='filename';

　　--创建spfile，pfile可以在数据库关闭后仍然可以创建

　　--如果pfile内有spfile文件参数的设定，采用pfile方式启动后还可以修改初始参数到spfile中

　　--<em>oracle</em>9.2.0.1版本后创建数据库时系统会自动创建spfile，默认的启动方式就是采用spfile

　　--运行时间spfile文件没有锁定

　　--修改密码

　　alter user username identified by newpassword;

　　--oracle9i 验证方式有两种，可以在sqlnet.ora中配置

　　SQLNET.AUTHENTICATION_SERVICES=(NTS)   --操作系统验证，无须用户输入密码

　　SQLNET.AUTHENTICATION_SERVICES=(none) --用正确的用户和密码验证

　　--设置密码级别 remote_login_passwordfile

　　--设置为exclusive，表示口令文件由一个数据库使用，远程客户端可以用sys登陆（如果密码文件删除后，远程无法登陆）

　　--设置为shared，表示多个数据库可以共享一个口令文件，但是只可以识别一个用户SYS，不能将sysdba权限授权给其他用户

　　--设置为none，表示没有口令文件   远程无法用sys登陆，只能通过操作系统验证方式

　　--密码文件如果丢失或损坏，系统无法启动

　　--可以手工创建<em>oracle</em>系统密码

　　orapwd file=<fname> password=<password> entries=<users>

　　entries  --sysdba链接最大数

　　--密码文件没有锁定，只是启动时的引导作用

　　V$SYSTEM_EVENT  数据库实例整个运行期间所有进程事件的等待时间、次数视图，作为系统优化的依据

　　等待事件分为三种类型 空闲等待、例行等待、资源等待

　　注意很多时候进程是空闲等待处理的，所以在该视图主要查看资源等待时间，

　　如果是进程在例行操作中等待，可以查看该事件的平均等待时间

　　--v$session_wait

　　--常见的空闲等待事件：client message、null event、pipe get、pmon/smon timer、rdbms rpc message及sql*net等；

　　--常见的非等闲事件：

　　buffer busy waits、

　　db file scattered read、

　　db file sequential read、

　　enqueue、

　　free buffer waits、

　　latch free、

　　log file sync、

　　log file paralle write

　　导

入导出建议一定要同版本的导入导出工具

　　常见错误 exp-00003

　　估算导出dmp文件大小

　　select sum(bytes) from user_segments where segment_type='TABLE';

　　这个计算结果不包含LOB， 和VARRAY， 亦不含分区表数据

　　--设置归档模式

　　startup mount;

　　alter database archivelog;

　　alter database open;

　　alter system set  log_archive_start=true scope=spfile;

　　shutdown immediate

　　startup;

　　数据库已经误删除数据文件，如何启动

　　startup mount

　　alter database datafile 'filepos'  offline drop;(noarchivelog)

　　alter database datafile 'filepos'  offline;(archivelog)

　　alter database open;

　　drop tablespace 'spacename'   including contents;

　　log_miner的使用

　　--查看联机日志信息

　　select a.group#，b.member， sequence#，first_change# from v$log a ，v$logfile b

　　where a.group#=b.group#;

　　v$rollstat三个字段说明

　　rssize 回滚段大小

　　optsize  optimal大小

　　hwmsize   你的回滚段曾经最高大小

　　--统计当前回滚段大小、最高峰大小

　　select sum(rssize)/1024/1024||'M'，sum(hwmsize/1024/1024)||'M'

　　from v$rollstat;

　　--查看事务占用的回滚段大小(事务尚未提交)

　　select b.used_ublk，b.xidusn，a.sid from v$session a，v$transaction b

　　where a.taddr=b.addr;

　　--查看操作系统硬件信息

　　prtdiag -v

　　--临时表空间为空

　　错误号ORA-25153:

　　alter tablespace 表空间名  add tempfile '文件名' size 文件大小;

　　创建临时表空间

　　create temporary tablespace 新表空间名 tempfile '文件名' size 文件大小;

　　alter  database default temporary tablespace 新表空间名;

　　drop tablespace 旧空间名 including contents;

　　rman数据文件损坏恢复脚本

　　--数据文件损坏

　　run{

　　allocate channel c1 type disk;

　　restore datafile 数据文件号;

　　recover datafile 数据文件号;

　　release channel c1;

　　}

　　一次控制文件损坏，恢复过程

　　故意修改控制文件，出现ora-00205错误

　　在rman下恢复控制文件

　　restore controlfile from file='最近的一次控制文件备份'

　　在alter database open resetlogs 出现

　　ORA 1152 file <name> was not restored from a sufficiently old backup  错误

　　不行

　　然后在rman下从自动文件恢复

　　restore controlfile from autobackup;

　　启动仍然出现该错误，

　　通过rman恢复database也不行，

　　最后没办法，采用

　　ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

　　然后根据trc文件中的指导

　　采用第二种方式也不行，只能采用第一种建立

　　在数据库nomount状态下

　　执行trc相关 脚本

　　alter dat

abase open;

　　数据库启动正常