在使用ORACLE时常用到的命令和脚本

在使用ORACLE时常用到的命令和脚本，希望大家能用上

一、数据字典
1、查数据块大小
select name,value fromv$parameter where name='db_block_size';
2、查数据文件的大小
select bytes/1024/1024 "sizeinMB" fromv$datafile where name='&actual_file_name';
3、如何查出前台正在发出的sql语句;先查出正在运行的前台程序的sid;然后根据上面得到的sid,可查出正在运行的前台程序发出的sql语句:
sql>select sid,serial#,username,program from v$session where status='ACTIVE';
sql>select user_name,sql_text fromv$open_cursor wheresid=xx;

二、逻辑备份
1、full方式下的完全导出（增量导出与积累只能在全数据库方式－－FULL＝Y下使用）
exp test/test file=c:/testcom.dmp full=Y inctype=complete log=c:/testcom.log
2、full方式下的增量导出
exp test/test file=c:/testinc.dmp full=Y inctype=incremental log=c:/testinc.log
3、full方式下的积累导出
exps mart/test file=c:/testcum.dmp full=Y inctype=cumulative log=c:/testcum.log
4、导出成三个文件，每个文件的大小设成1.5G
exp test/test file=c:/exp01.dmp,exp02.dmp,exp03.dmp filesize=1500M buffer=204800 owner=test grants=Y compress=Y

---

5、将三个DMP文件导入到LC用户下
imp lc/lc file=c:/exp01.dmp,exp02.dmp,exp03.dmp log=c:/implog.txt fromuser=test touser=lc
6、建立一个与现存数据库相同，但不包含数据的空库；对全库作Export或Import时，使用参数ROWS=Y
exp system/manager full=Y rows=N file=full.dmp
imp system/manager full=Y rows=N file=full.dmp
7、在oracle7和oracle8之间export/import数据；必须先在oracle8数据库上以internal或sys用户运行catexp7.sql
svrmgr>connect internal
svrmgr>@?/rdbms/admin/catexp7.sql

---

完全增量导入A
imp system/manager inctype=restore full=y file=A
累计增量导入E
imp system/manager inctype=restore full=y file=E
最近增量导入F
imp system/manager inctype=restore full=y file=F

---

三、数据库管理

1、为远程用户设置"internal"的口令；先在数据库端的init<sid>.ora文件中设置,并且为oracle owner设置环境变量
remote_login_passwordfile=exclusive
ORA_<SID>_PWFILE=orapw<sid>.pwd
然后以oracleowner运行以下命令
$cd $ORACLE_HOME/dbs
$orapwd file=orapw<sid>.pwd password=<passwd> entries=5

2、设置redo log文件的大小。设置redo log文件的原则就是要保证30分钟之内进行logswitch.logswitch的信息被记录在alertSID.log中。
-------------------------------------------------------
Mon May 513:53:311997
Thread 1 advanced to log sequence 68
Current log#2seq#68mem#0:/u05/dbs/log2ween.dbf-------------------------------------------------------

如果两次switch的时间是10分钟，你需要增加redolog文件到三倍的尺寸。
如果两次switch的时间是15分钟，你需要增加redolog文件到两倍的尺寸。
如果两次switch的时间超过30分钟，你不需要需要增加redolog文件的尺寸。
同时设置CHECK_POINT_INTERVAL大于redolog文件的大小。

3、检查是否一个数据库用户被授予sysdba,sysoper的权限;注册到sys用户下，查询数据字典v$pwfile_users.

4、移动数据文件的位置;
　　1.正常关闭数据库；
　　2.拷贝数据文件到新位置；
　　 $cp $ORACLE_HOME/dbs/dbsNEW.dbf/usr3/oracle/dbsNEW.dbf
　　3.svrmgrl
　　 connect internal;
　　 startup mount;
　　 alter database rename file '<$ORACLE_HOME>/dbs/dbsNEW.dbf' to '/usr3/oracle/dbsNEW.dbf';
　　 alter database open.
4.修改数据文件物理大小
alter database datafile 'C:/ORACLE/ORADATA/testAA/UNDOTBS01.DBF' resize 150M;
　　
5、移动LOG文件的位置;可以先添加新的LOG文件，再删除原来的LOG文件。
svrmgrl>connect internal;
alter database add logfile '<$ORACLE_HOME>/usr3/oracle/logNEW.dbf';
alter database drop logfile '<$ORACLE_HOME>/dbs/logNEW.dbf';
增加日志成员到日志组3：alter database add logfile member 'c:/oracle/oradata/test/redo03a.log' to group 3;

6、查看SGA区剩余可用内存；
SQL> col OBJECT_NAME format a20
SQL> col 自由空间百分比(%) format 90.99　　　--格式化，把小数点对齐为两位

SQL> select name,
　　　　　　 sgasize/1024/1024　　　　　　　　"Allocated(M)",
　　　　　　 bytes/1024　　　　　　　　　　　 "自由空间(K)",
　　　　　　 round(bytes/sgasize*100, 2)　　　"自由空间百分比(%)"
　　from (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f
　　wheref.name = 'free memory';

以下为查看sys.v_$sgastat相关

SQL> desc sys.v_$sgastat
SQL> select sum(bytes) sgasize from sys.v_$sgastat; --SGA区总和
SQL> select * from sys.v_$sgastat;

7、如何查看什么时间有哪些数据库对象结构被修改过？说明：
SQL> col OBJECT_NAME format a20
SQL> SELECT OBJECT_NAME,　　　　　　　　　　　　　　　　　　　　　　　--对象名
　　　　　 　OBJECT_TYPE,　　　　　　　　　　　　　　　　　　　　　　　　--对象类型
　　　　　 　TO_CHAR(CREATED, 'YYYY-Mon-DD HH24:MI') CREATE_TIME,　--创建时间
　　　　　　 　TO_CHAR(LAST_DDL_TIME, 'YYYY-Mon-DD HH24:MI') MOD_TIME,　　 --修改时间
　　　　　　 　TIMESTAMP,　　　　　　　　　　　　　　　　　　　　　　　　　--时间戳
　　　　　　 　STATUS　　　　　　　　　　　　　　　　　　　　　　　　　　　--状态
　　 FROM USER_OBJECTS
　 　 WHEREto_char(LAST_DDL_TIME,'yyyymmdd')>'&日期变量';

8、如何查看Oracle版本及安装了哪些选项？
A、查看数据库版本
SQL> select banner from sys.v_$version;

B、查看安装了哪些选项
SQL> col PARAMETER format a60
SQL> col VALUE format a10
SQL> select * from sys.v_$option;
SQL> set head off feed off pages 0 serveroutput on
SQL> begin
2dbms_output.put_line('Port String: '||dbms_utility.port_string);
3end;
4/
Port String: IBMPC/WIN_NT-8.1.0

9、如何查看数据文件是否自动扩展？
SQL> col FILE_NAME format a40
SQL> col TABLESPACE_NAME format a20
SQL> select file_id,file_name,tablespace_name,autoextensible from dba_data_files order by file_id;
说明：TEST表空间用以下语句创建的一个实验表空间，没有指定 AUTOEXTEND ON 参数，所以不是自动扩展。
SQL> create tablespace test datafile
2'd:/test.dbf' size 5M
3default storage (initial 1M next 1M pctincrease 0)
4/

10、怎样识别IO竞争和负载平衡？
使用系统表：
v$datafile：存储数据库中数据文件的信息
v$filestat：存储系统中访问数据文件的统计信息

SQL语句：

col 文件名 format a35
select
df.name 文件名,
fs.phyrds 读次数,
fs.phywrts 写次数,
(fs.readtim/decode(fs.phyrds,0,-1,fs.phyrds)) 读时间,
(fs.writetim/decode(fs.phywrts,0,-1,fs.phywrts)) 写时间
from
v$datafile df,
v$filestat fs
where df.file#=fs.file#
order by df.name
/
其中：ORADB为数据库名，因为本例中数据库使默认安装，没有进行过优化、调整，
所以，一直在system表空间上做操作，导致system表空间所在的数据文件SYSTEM01.DBF被读写的次数最多，
这也说明了，尽量不要在system表空间做与系统无关的操作，应给各个用户建立单独的表空间。

11、如何查看数据库的字符集？
实现方法：
SQL> conn sys/change_on_install
SQL> desc props$
SQL> set arraysize 1
SQL> col value$ format a40 --格式化value$的输出为40个字符宽
SQL> select name,value$ from props$ where name='NLS_CHARACTERSET';
SQL> conn system/manager
SQL> select * from nls_database_parameters;
SQL> select * from V$NLS_PARAMETERS;
SQL> select * from nls_database_parameters;
SQL> select * from V$NLS_PARAMETERS;

12、查看数据库中所有数据文件与表空间的名称与文件名称及物理位置
select t.name Tablespace,f.name Datafile from v$tablespace t,v$datafile f where t.ts# = f.ts# order by t.name；

13、查看控制文件信息：
先用alter database backup controlfile to trace;
这样就会产生文本文件，用UE来打开产生的这个trace就可看了。