ORACLE 基本操作收录

一、     数据库篇

-----------------------------------------------

1、oracle中查看用户权限

 

1.查看所有用户：

  select * from dba_users;

  select * from all_users;

  select * from user_users;

2.查看用户或角色系统权限：

  select * from dba_sys_privs;

  select * from user_sys_privs;

3.查看用户对象权限：

  select * from dba_tab_privs;

  select * from all_tab_privs;

  select * from user_tab_privs;

4.查看所有角色：

  select * from dba_roles;

5.查看用户或角色所拥有的角色：

  select * from dba_role_privs;

  select * from user_role_privs;

 

-----------------------------------------------

2、查看系统中存在的权限

select distinct privilege from dba_sys_privs order by 1;

 

-----------------------------------------------

3、oracle 权限分类

alter any cluster 修改任意簇的权限

alter any index 修改任意索引的权限

alter any role 修改任意角色的权限

alter any sequence 修改任意序列的权限

alter any snapshot 修改任意快照的权限

alter any table 修改任意表的权限

alter any trigger 修改任意触发器的权限

alter cluster 修改拥有簇的权限

alter database 修改数据库的权限

alter procedure 修改拥有的存储过程权限

alter profile 修改资源限制简表的权限

alter resource cost 设置佳话资源开销的权限

alter rollback segment 修改回滚段的权限

alter sequence 修改拥有的序列权限

alter session 修改数据库会话的权限

alter sytem 修改数据库服务器设置的权限

alter table 修改拥有的表权限

alter tablespace 修改表空间的权限

alter user 修改用户的权限

analyze 使用analyze命令分析数据库中任意的表、索引和簇

audit any 为任意的数据库对象设置审计选项

audit system 允许系统操作审计

backup any table 备份任意表的权限

become user 切换用户状态的权限

commit any table 提交表的权限

create any cluster 为任意用户创建簇的权限

create any index 为任意用户创建索引的权限

create any procedure 为任意用户创建存储过程的权限

create any sequence 为任意用户创建序列的权限

create any snapshot 为任意用户创建快照的权限

create any synonym 为任意用户创建同义名的权限

create any table 为任意用户创建表的权限

create any trigger 为任意用户创建触发器的权限

create any view 为任意用户创建视图的权限

create cluster 为用户创建簇的权限

create database link 为用户创建的权限

create procedure 为用户创建存储过程的权限

create profile 创建资源限制简表的权限

create public database link 创建公共数据库链路的权限

create public synonym 创建公共同义名的权限

create role 创建角色的权限

create rollback segment 创建回滚段的权限

create session 创建会话的权限

create sequence 为用户创建序列的权限

create snapshot 为用户创建快照的权限

create synonym 为用户创建同义名的权限

create table 为用户创建表的权限

create tablespace 创建表空间的权限

create user 创建用户的权限

create view 为用户创建视图的权限

delete any table 删除任意表行的权限

delete any view 删除任意视图行的权限

delete snapshot 删除快照中行的权限

delete table 为用户删除表行的权限

delete view 为用户删除视图行的权限

drop any cluster 删除任意簇的权限

drop any index 删除任意索引的权限

drop any procedure 删除任意存储过程的权限

drop any role 删除任意角色的权限

drop any sequence 删除任意序列的权限

drop any snapshot 删除任意快照的权限

drop any synonym 删除任意同义名的权限

drop any table 删除任意表的权限

drop any trigger 删除任意触发器的权限

drop any view 删除任意视图的权限

drop profile 删除资源限制简表的权限

drop public cluster 删除公共簇的权限

drop public database link 删除公共数据链路的权限

drop public synonym 删除公共同义名的权限

drop rollback segment 删除回滚段的权限

drop tablespace 删除表空间的权限

drop user 删除用户的权限

execute any procedure 执行任意存储过程的权限

execute function 执行存储函数的权限

execute package 执行存储包的权限

execute procedure 执行用户存储过程的权限

force any transaction 管理未提交的任意事务的输出权限

force transaction 管理未提交的用户事务的输出权限

grant any privilege 授予任意系统特权的权限

grant any role 授予任意角色的权限

index table 给表加索引的权限

insert any table 向任意表中插入行的权限

insert snapshot 向快照中插入行的权限

insert table 向用户表中插入行的权限

insert view 向用户视图中插行的权限

lock any table 给任意表加锁的权限

manage tablespace 管理（备份可用性）表空间的权限

references table 参考表的权限

restricted session 创建有限制的数据库会话的权限

select any sequence 使用任意序列的权限

select any table 使用任意表的权限

select snapshot 使用快照的权限

select sequence 使用用户序列的权限

select table 使用用户表的权限

unlimited tablespace 对表空间大小不加限制的权限

update any table 修改任意表中行的权限

update snapshot 修改快照中行的权限

update table 修改用户表中的行的权限

update view 修改视图中行的权限

 

-----------------------------------------------------

4、查看数据库表空间使用率

select df.tablespace_name "Tablespace",totalspace "Totalspace(MB)",freespace "Freespace(MB)",round((1-freespace/totalspace)*100,2) "Use(%)" from (select tablespace_name,round(sum(bytes)/1024/1024) totalspace from dba_data_files group by tablespace_name) df, (select tablespace_name,round(sum(bytes)/1024/1024) freespace from dba_free_space group by tablespace_name) fs where df.tablespace_name=fs.tablespace_name order by 1;

 

-----------------------------------------------------

5、查表空间对应的数据文件路径

select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by 3,1;

 

select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files where tablespace_name='UMS_LOG2' order by 3,1;

 

-----------------------------------------------------

6、查临时表空间对应的数据文件路径

select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_temp_files order by tablespace_name,file_name;

 

-----------------------------------------------------

7、查看表大小

select owner,segment_name,segment_type,bytes/1024/1024 from dba_segments where segment_name in ('MSG_SYSTEM_BAK','TI_CTI_CDR_YYYYMM');\

 

select owner,segment_name,segment_type,bytes/1024/1024 from dba_segments where segment_name='MSG_SYSTEM_BAK';

 

-----------------------------------------------------

8、扩大原表空间的大小

ALTER DATABASE DATAFILE '/oradata/orc11/system01.dbf' RESIZE 4G;

 

-----------------------------------------------------

9、增加一个新的表空间文件

ALTER TABLESPACE UMS_LOG1 ADD DATAFILE '+ORADG/yxdb/datafile/ums_log1_02' SIZE 10240M;

 

-----------------------------------------------------

10、增加一个新的临时表空间文件

ALTER  TABLESPACE UMAPPM_TEMP1 ADD TEMPFILE '+ORADG/yxdb/datafile/umappm_temp02' SIZE 6144M;

 

-----------------------------------------------------

11、关闭表空间的自动增长

ALTER DATABASE DATAFILE '/usr/local/oracle/app/oracle/oradata/ora10/sysaux01.dbf' AUTOEXTEND ON/OFF;

 

-----------------------------------------------------

12、查看数据库归档情况

archive log list;

 

-----------------------------------------------------

13、查看用户权限情况

select * from dba_sys_privs where GRANTEE='BACKUP';

 

select * from dba_role_privs where GRANTEE='UMAPTEST';

 

-----------------------------------------------------

14、创建临时表空间

create temporary tablespace msec_temp tempfile '+ORADG/yxdb/datafile/msec_temp' size 2048m;

 

-----------------------------------------------------

15、创建索引表空间

create tablespace msec_index datafile '+ORADG/yxdb/datafile/msec_index01' size 5120m;

 

-----------------------------------------------------

16、创建日志表空间

create tablespace msec_log datafile '+ORADG/yxdb/datafile/ums2_log01' size 5120m;

 

-----------------------------------------------------

17、创建表空间msec

CREATE TABLESPACE msec

LOGGING

DATAFILE '+ORADG/yxdb/datafile/msec_data01' size 5120M

AUTOEXTEND OFF

EXTENT MANAGEMENT LOCAL;

 

-----------------------------------------------------

18、创建用户msec

CREATE USER msec

IDENTIFIED BY phMyfPobbD

DEFAULT TABLESPACE msec

TEMPORARY TABLESPACE msec_temp

ACCOUNT UNLOCK;

 

19、授权ums2

GRANT CONNECT TO msec;

GRANT RESOURCE TO msec;

GRANT CREATE VIEW TO msec;

GRANT CREATE TABLE TO msec;

GRANT "EXP_FULL_DATABASE" TO backup ;

GRANT "IMP_FULL_DATABASE" TO moma;

 

 

 

-----------------------------------------------------

20、oracle 10g 清理归档日志

 

20.1、RMAN数据连接

rman target system/manager@target_tnsname

or

rman target /

 

20.2、查看归档日志文件的状态：

RMAN> list archivelog all;

 

20.3、 删除操作系统中的日志文件（到操作系统级进行手工删除）；

 

20.4、 将归档日志信息进行更新；

RMAN> crosscheck archivelog all;

RMAN> delete noprompt expired archivelog all;

RMAN> exit

 

 

-----------------------------------------------------------------------------------------------------------

 

21、基于Linux的命令行环境下创建Oracle10gR2数据库

 

环境：RHEL 5.3＋Oracle10g Release 2

 

1．设置系统环境变量：

ORACLE_HOME=/usr/local/oracle/app

ORACLE_BASE=/usr/local/oracle

 

2．系统规划：

实例名称：ORACLE_SID=typtdb

数据库名称：typtdb

 

3．手工创建如下目录：

mkdir -p /usr/local/oracle/admin/typtdb/cdump

mkdir -p /usr/local/oracle/admin/typtdb/bdump

mkdir -p /usr/local/oracle/admin/typtdb/udump

mkdir -p /usr/local/oracle/admin/typtdb/pfile

 

4．建立密码文件：

/usr/local/oracle/app/bin/orapwd  file=/usr/local/oracle/app/dbs/orapwdtyptdb password=typtdb

 

5．修改参数文件：

格式：/usr/local/oracle/app/dbs/init.ora

实例：/usr/local/oracle/app/dbs/inittyptdb.ora

内容如下：

large_pool_size=4294967296

shared_pool_size=4294967296

core_dump_dest='/usr/local/oracle/admin/typtdb/cdump'

db_block_size=8192

db_file_multiblock_read_count=16

db_name='typtdb'

db_recovery_file_dest='/usr/local/oracle/flash_recovery_area'

db_recovery_file_dest_size=2147483648

dispatchers='(PROTOCOL=TCP) (SERVICE=typtdbXDB)'

job_queue_processes=1000

open_cursors=300

pga_aggregate_target=8589934592

processes=3000

undo_management='auto'

undo_tablespace='undotbs1'

user_dump_dest='/usr/local/oracle/admin/typtdb/udump'

NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS"

NLS_DATE_LANGUAGE=AMERICAN

control_files = (/usr/local/oracle/oradata/typtdb/ora_control1.ctl, /usr/local/oracle/oradata/typtdb/ora_control2.ctl,/usr/local/oracle/oradata/typtdb/ora_control3ctl)

------------------------------------------------------------------------------------------------------

或则使用如下内容

 

*.audit_file_dest='/usr/local/oracle/admin/typtdb/adump'

*.background_dump_dest='/usr/local/oracle/admin/typtdb/bdump'

*.compatible='10.2.0.3.0'

*.control_files='/usr/local/oracle/oradata/typtdb/control01.ctl','/usr/local/oracle/oradata/typtdb/control02.ctl','/usr/local/oracle/oradata/typtdb/control03.ctl'

*.core_dump_dest='/usr/local/oracle/admin/typtdb/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='typtdb'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=typtdbXDB)'

*.job_queue_processes=10

*.log_archive_dest='/usr/local/oracle/oradata/typtdb/archlog'

*.nls_language='AMERICAN'

*.open_cursors=5000

#PGA设置为(total_mem * 80%) * 20%

*.pga_aggregate_target=2748779070

*.processes=1000

*.remote_login_passwordfile='EXCLUSIVE'

#SGA设置为(total_mem * 80%) * 80%

*.sga_max_size=10995116278

*.sga_target=10995116278

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/usr/local/oracle/admin/typtdb/udump'

 

-----------------------------------------------------------------------------------------------------------------------

6．设置当前工作实例：

export ORACLE_SID=typtdb

 

7．登陆oracle：

>sqlplus  sys/typtdb as sysdba

 

8．启动实例：

SQL>startup nomount pfile="/usr/local/oracle/app/dbs/inittyptdb.ora"

 

9．创建数据库的脚本：

--创建数据库

create database typtdb

logfile group 1 '/usr/local/oracle/oradata/typtdb/redo01.log' size 128m reuse,

group 2 '/usr/local/oracle/oradata/typtdb/redo02.log' size 128m reuse,

group 3 '/usr/local/oracle/oradata/typtdb/redo03.log' size 128m reuse

datafile '/usr/local/oracle/oradata/typtdb/typtdb01.dbf' size 5120m reuse

maxdatafiles 100

archivelog

character set  ZHS16GBK

sysaux datafile '/usr/local/oracle/oradata/typtdb/sys01.dbf' size 512m 

undo tablespace undotbs1 datafile '/usr/local/oracle/oradata/typtdb/undo01.dbf' size 1024m;

--创建临时表空间

create temporary tablespace temp tempfile '/usr/local/oracle/oradata/typtdb/temp01.dbf' size 2048m;

--创建回退段表空间

create tablespace rbs datafile '/usr/local/oracle/oradata/typtdb/rbs01.dbf' size 512m;

--创建工具表空间

create tablespace tools datafile '/usr/local/oracle/oradata/typtdb/tools01.dbf' size 512m;

--创建索引表空间

create tablespace indx datafile '/usr/local/oracle/oradata/typtdb/indx01.dbf' size 512m;

--回退段设置

create public rollback segment rbs0 tablespace rbs storage (optimal 4096k) ;

create public rollback segment rbs1 tablespace rbs storage (optimal 4096k) ;

create public rollback segment rbs2 tablespace rbs storage (optimal 4096k) ;

create public rollback segment rbs3 tablespace rbs storage (optimal 4096k) ;

--启动回退段

alter rollback segment "rbs0" online ;

alter rollback segment "rbs1" online ;

alter rollback segment "rbs2" online ;

alter rollback segment "rbs3" online ;

--创建用户表空间

create tablespace userdata datafile '/usr/local/oracle/oradata/typtdb/userdata01.dbf' size 1024m ;

--创建用户

create user orauser identified by orauser default tablespace userdata temporary tablespace temp;

--给用户授权

grant connect,resource to orauser;

 

10．运行如下文件：

 

(1). 运行sql创建系统表/视图和过程:

--创建系统常用的数据字典视图和同义词

@?/rdbms/admin/catalog.sql

--运行服务器端所需要的PL/SQL脚本

@?/rdbms/admin/catproc.sql

connect system/manager

--创建PRODUCT_USER_PROFILE表，用于禁止某些用户使用SQLPLUS访问数据库

@?/sqlplus/admin/pupbld.sql

 

(2). 切换到archivelog模式

shutdown immediate

startup mount

alter database archivelog;

archive log start

alter database open;

 

(3).从pfile创建spfile

sqlplus '/as sysdba'

create spfile from pfile;

 

11．经验总结：

①inittyptdb.ora中的control_files关键字所指示的目录必需存在。

②inittyptdb.ora中undo_tablespace中的名字必需与Create Database中的相同。

③分析数据库出错可以到/usr/local/oracle/admin/typtdb/bdump目录下的alert_typtdb.log中查找。

-----------------------------------------------------------------------------------------------------------

22、oracle修改游标大小

 

这个问题最好是通过优化sql来解决，考试,大提示也可以通过修改ORACLE的参数open_cursors 来解决。

（1）oraclehome\admin\数据库名字\pfile 下面的init.ora里面有参数

#游标和库高速缓存#

open_cursors=300

（2）Oracle9i以上版本可通过

SQL>conn sys as sysdba

SQL>show parameter open_cursors;

SQL>alter system set open_cursors=4000 scope=both;

（3）重启系统使它生效。

 

-----------------------------------------------------------------------------------------------------------

23、ORACLE 中修改用户密码的方法

用OS认证方式登陆，即是CONNECT / AS SYSDBA 登陆，然后修改密码就是了

ALTER USER backup IDENTIFIED BY "bcinfobackup";

ALTER USER SYSTEM IDENTIFIED BY ****

然后你可以改变成密码文件认证。

 

 

 

-----------------------------------------------------------------------------------------------------------

24、自动清理归档日志

#!/bin/bash

#################FUNCTION#############

#

# autoclear Oracle 11g archive log

#

######################################

 

# Make user of root

 

#filename:~/clearlog.sh

 

export ORACLE_BASE=/usr/local/oracle

export ORACLE_SID=moma

export ORACLE_HOME=$ORACLE_BASE/app

export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

#export DISPLAY=10.25.110.111:0.0

export NLS_LANG=american_america.zhs16gbk

#export ORA_CRS_HOME=$ORACLE_BASE/crs

export PATH=$ORACLE_HOME/bin:$ORACLE_BASE/crs/bin:/usr/ccs/bin:$PATH:$HOME/bin:/usr/bin:.

 

# autoclear the *.dbf file in /usr/local/oracle/oradata/moma/archlog/ 31 days ago

find /usr/local/oracle/oradata/moma/archlog/ -xdev -mtime +30 -name "*.dbf" |xargs rm -rf

 

# connect database as rman

rman target / <<EOF

crosscheck archivelog all;

delete noprompt expired archivelog all;

exit

 

EOF

-----------------------------------------------------------------------------------------------------------

25、数据库备份策略

#!/bin/bash

#####################

#2009-12-09

#Liao

#####################

 

#####################

#Oracle Envirment

#####################

export ORACLE_BASE=/usr/local/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.1.0

export ORACLE_SID=qfman

export ORACLE_TERM=xterm

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib

export NLS_LANG=American_america.zhs16gbk

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

export PATH=$ORACLE_HOME/bin:$PATH

export DISPLAY=:0.0

 

#####################

#Backup Script

#####################

EXPFILE=`date '+%F_%H_%M'`

BAKPATH=/usr/local/oracle/backup

exp backup/bcinfobackup@gsdm10 file=$BAKPATH/ora.$EXPFILE compress=y buffer=16777216 full=y log=$BAKPATH/log/exp.$EXPFILE.log

gzip $BAKPATH/ora*_30

find $BAKPATH/ -xdev -mtime +4 -name "*.gz" >> $BAKPATH/log/exp.$EXPFILE.log

find $BAKPATH/ -xdev -mtime +4 -name "*.gz" |xargs rm -rf

find $BAKPATH/log/ -xdev -mtime +4 -name "*.log" |xargs rm -rf

 

-----------------------------------------------------------------------------------------------------------

26、查询当前执行IO最多的sql，并按大到小来排序

select cast(buffer_gets/decode(EXECUTions,0,10000,EXECUTions) as int) 平均IO ,

EXECUTions 执行次数,buffer_gets 总逻辑IO,disk_reads 硬盘读取, sql_text SQl语句

from v$sqlarea

where parsing_schema_name='MOMA'

and rownum<=10

order by buffer_gets desc ;

 

-----------------------------------------------------------------------------------------------------------

27、hp unix 启动监听命令

srvctl start listener -n db1

 

-----------------------------------------------------------------------------------------------------------

28、关闭Oracle快闪功能

YP >shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

YP >startup mount;

ORACLE instance started.

 

Total System Global Area  314572800 bytes

Fixed Size                  1301704 bytes

Variable Size             261890872 bytes

Database Buffers           50331648 bytes

Redo Buffers                1048576 bytes

Database mounted.

 

YP >alter database flashback off;

 

Database altered.

 

YP >alter database open;

 

Database altered.

 

YP >alter system set log_archive_dest_1='LOCATION=/usr/local/oracle/archivelog1' scope=both;

 

Database altered.

-----------------------------------------------------------------------------------------------------------

29、修改快闪区大小

alter system set db_recovery_file_dest_size=50G scope=both;

 

-----------------------------------------------------------------------------------------------------------

30、oracle11g 查看日志命令

adrci

 

-----------------------------------------------------------------------------------------------------------

31、Oracle修改日志组文件的大小

1、创建2个新的日志组

alter database add logfile group 4 ('/oradata/qfman/redo04.log') size 1G;

alter database add logfile group 5 ('/oradata/qfman/redo05.log') size 1G;

 

2、切换当前日志到新的日志组

alter system switch logfile;

或直接切换归档

alter system archive log current;

 

3、删除旧的日志组

alter database drop logfile group 1;

alter database drop logfile group 2;

alter database drop logfile group 3;

 

4、操作系统下删除原日志组1、2、3中的文件

rm -rf /oradata/qfman/redo01.log

rm -rf /oradata/qfman/redo02.log

rm -rf /oradata/qfman/redo03.log       

 

5、重建日志组1、2、3

alter database add logfile group 1 ('/oradata/qfman/redo01.log') size 1G;

alter database add logfile group 2 ('/oradata/qfman/redo02.log') size 1G;

alter database add logfile group 3 ('/oradata/qfman/redo03.log') size 1G;

 

6、切换日志组

alter system switch logfile;

 

7、删除中间过渡用的日志组4、5

alter database drop logfile group 4;

alter database drop logfile group 5;

 

8、到操作系统下删除原日志组4、5中的文件

 

-----------------------------------------------------------------------------------------------------------

32、用于查询出占用资源较高的进程的HASH值

while [ 1 ]

do

echo "SESSION EVENT WAIT LIST "

sqlplus -S '/as sysdba'  <<EOF

set pagesize 2000

set linesize 800

col event format a29

col program format a29

col sess_sql_hash for a15

col username for a11

col client for a31

select substr(a.event,1,25) event,substr(b.program,1,39) program ,b.sid||':'||decode(sql_hash_value,0,prev_hash_value,sql_hash_value) sess_sql_hash,b.username

,substr(b.osuser||'@'||b.machine||'@'||b.process||'@'||c.spid,1,31) client,to_char(b.logon_time,'mm-dd hh24:mi') logon_time

from v\$session_wait a,v\$session b,v\$process c

where a.sid=b.sid  and b.paddr=c.addr

and a.event not like '%SQL%'

and a.event not like '%message%'

and a.event not like '%time%'

and a.event not like 'PX Deq:%'

and a.event not like 'jobq slave%'

/

exit

EOF

sleep 5

done

 

-----------------------------------------------------------------------------------------------------------

33、用于根据HASH值查询相应的SQL语句

sqlplus -S '/as sysdba' <<EOF

variable v_hash_value Number;

BEGIN

:v_hash_value :=$1;

END;

/

set pages 0

set lines 1000

set feedback off

set term on

select to_char(sysdate,'yyyymmdd hh24:mi:ss') as curtime,hash_value,sql_text from v\$sqltext where hashvalue=:v_hash_value order by piece;

/

exit

EOF

 

---------------------------------------------------------------------------------------------------------------------------------34、查看数据库是否存在死锁

select /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */ 'Wait' "Status", a.username, a.machine, a.sid, a.serial#, a.last_call_et "Seconds", b.id1, c.sql_text "SQL"

from v$session a, v$lock b, v$sqltext c

where a.username is not null

and a.lockwait = b.kaddr

and c.hash_value =a.sql_hash_value

union

select /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */ 'Lock' "Status", a.username, a.machine, a.sid, a.serial#, a.last_call_et "Seconds", b.id1, c.sql_text "SQL"

from v$session a, v$lock b, v$sqltext c

where b.id1 in

(select /*+ NO_MERGE(d) NO_MERGE(e) */ distinct e.id1

from v$session d, v$lock e

where d.lockwait = e.kaddr)

and a.username is not null

and a.sid = b.sid

and b.request=0

and c.hash_value =a.sql_hash_value;

 

 

 

select t2.username||'   '||t2.sid||'   '||t2.serial#||'   '||t2.logon_time||'   '||t3.sql_text

      from v$locked_object t1,v$session t2,v$sqltext t3

      where t1.session_id=t2.sid

      and t2.sql_address=t3.address

      order by t2.logon_time;

 

 

SELECT /*+ rule */ s.username,

decode(l.type,'TM','TABLE LOCK',

'TX','ROW LOCK',

NULL) LOCK_LEVEL,

o.owner,o.object_name,o.object_type,

s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser

FROM v$session s,v$lock l,dba_objects o

WHERE l.sid = s.sid

AND l.id1 = o.object_id(+)

AND s.username is NOT NULL;

 

 

select distinct s.SID,s.SERIAL#,a.owner,a.object_name,q.SQL_TEXT,s.paddr,p.SPID

        from v$locked_object l,all_objects a,v$session s,v$sql q,v$process p

        where l.OBJECT_ID=a.object_id

              and s.SID=l.SESSION_ID

              and q.ADDRESS=s.PREV_SQL_ADDR

              and s.PADDR=p.ADDR

 

 

----------------------------------------------------------------------------------------------------

35、silence模式安装oracle 10g过程

 

1.Swap partition created

dd if=/dev/zero of=/swapfile bs=1M count=2048;

mkswap /swapfile

swapon /swapfile

vim /etc/fstab

/swapfile swap default 0 0

 

2.groupadd oninstall

groupadd dba

useradd -g oinstall -G dba oracle -d /home/oracle oracle

passwd oracle

mkdir -p /u01/app/oracle/product/10.2.0/db1

chown -R oracle.oinstall /u01/app/

 

3.vim /etc/hosts

 

4.vim /etc/sysctl.conf

kernel.shmmax = 1000000000(#0.5*nG*1024*1024*1024)

kernel.shmmni = 4096

kernel.shmall = 2097152

kernel.sem = 250 32000 100 128

net.ipv4.ip_local_port_range = 1024 65000

net.core.rmem_default=1048576

net.core.rmem_max=1048576

net.core.wmem_default=262144

net.core.wmem_max=262144

fs.file-max = 65536

sysctl -p

 

5.vim /home/oracle/.bash_profile

export TMP=/tmp

export TMPDIR=$TMP

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db1

export ORACLE_SID=db1

export ORACLE_TERM=xterm

export PATH=$PATH:$ORACLE_HOME/bin

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

export DISPLAY="127.0.0.1:0.0"

export LC_CTYPE=en_US.UTF-8

 

6.apt-get install make gcc g libc6-dev gnome xorg glibc-common gnome-libs libstdc libstdc -devel

 

7.vim /etc/redhat-release

Red Hat Enterprise Linux AS release 4 (Taroon)

 

8.apt-get install unzip

unzip -x 10201_database_linux-32

mv database /u01/app/

chown -R oracle oinstall /u01/app/database

cp /u01/app/database/response/enterprise.rsp /u01/app/db1.rsp

vim /u01/app/db1.rsp

n_configurationOption=3

UNIX_GROUP_NAME="oinstall"

ORACLE_HOME="/opt/u01/app/oracle/product/10g"

ORACLE_HOME_NAME="OraDbHome1"

ORACLE_HOSTNAME="$hostname"

s_nameForDBAGrp="dba"

s_nameForOPERGrp="dba"

 

9.cd /u01app/database

ls

./runInstaller -silent -responsefile /u01/app/db1.rsp -ignoreSysPrereqs

 

10.cd /u01/app/oracle/admin/db1

touch adump bdump cdump dpdump udump

 

11.vim /u01/app/oracle/db1.sql

CREATE DATABASE db1

USER SYS IDENTIFIED BY sys

USER SYSTEM IDENTIFIED BY sys

LOGFILE GROUP 1 ('/u01/app/oracle/oradata/db1/redo01.log') SIZE 50M,

GROUP 2 ('/u01/app/oracle/oradata/db1/redo02.log') SIZE 50M,

GROUP 3 ('/u01/app/oracle/oradata/db1/redo03.log') SIZE 50M

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXLOGHISTORY 1

MAXDATAFILES 100

MAXINSTANCES 1

CHARACTER SET AL32UTF8

NATIONAL CHARACTER SET AL16UTF16

DATAFILE '/u01/app/oracle/oradata/db1/system01.dbf' SIZE 325M REUSE

EXTENT MANAGEMENT LOCAL

SYSAUX DATAFILE '/u01/app/oracle/oradata/db1/sysaux01.dbf' SIZE 325M REUSE

DEFAULT TABLESPACE usertbs DATAFILE '/u01/app/oracle/oradata/db1/usertbs.dbf' SIZE 10M REUSE

DEFAULT TEMPORARY TABLESPACE tempts1

TEMPFILE '/u01/app/oracle/oradata/db1/temp01.dbf'

SIZE 20M REUSE

UNDO TABLESPACE undotbs

DATAFILE '/u01/app/oracle/oradata/db1/undotbs01.dbf'

SIZE 20M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

 

12.vim $ORACLE_HOME/dbs/initdb1.ora

db_name=db1

shared_pool_size=120000000

undo_tablespace=undotbs

undo_management=AUTO

 

13. $ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapwdb1 password=123

 

14.sqlplus / as sysdba

@/u01/app/oracle/db1.sql

@$ORACLE_HOME/rdbms/admin/catalog.sql

@$ORACLE_HOME/rdbms/admin/catproc.sql

conn system/manage

@$ORACLE_HOME/sqlplus/admin/pupbld.sql

 

---------------------------------------------------------------------------------------------------------------------------

36、使用RAC的Oracle软件安装数据库时，出现如下情况时

ORA-29702: error occurred in Cluster Group Service operation

需关闭RAC的动态链接

 

关闭

cd $ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk rac_off

make -f ins_rdbms.mk ioracle

 

打开

cd $ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk rac_on

make -f ins_rdbms.mk ioracle

------------------------------------------------------

37、impdp/expdp使用实例

 

一、创建逻辑目录，该命令不会在操作系统创建真正的目录，最好以system等管理员创建。

create directory dpdata1 as 'd:\test\dump';

 

二、查看管理理员目录（同时查看操作系统是否存在，因为Oracle并不关心该目录是否存在，如果不存在，则出错）

select * from dba_directories;

 

三、给scott用户赋予在指定目录的操作权限，最好以system等管理员赋予。

grant read,write on directory dpdata1 to scott;

 

四、导出数据

1)按用户导

expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1;

2)并行进程parallel

expdp scott/tiger@orcl directory=dpdata1 dumpfile=scott3.dmp parallel=40 job_name=scott3

3)按表名导

expdp scott/tiger@orcl TABLES=emp,dept dumpfile=expdp.dmp DIRECTORY=dpdata1;

4)按查询条件导

expdp scott/tiger@orcl directory=dpdata1 dumpfile=expdp.dmp Tables=emp query='WHERE deptno=20';

5)按表空间导

expdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=temp,example;

6)导整个数据库

expdp system/manager DIRECTORY=dpdata1 DUMPFILE=full.dmp FULL=y;

 

五、还原数据

1)导到指定用户下

impdp scott/tiger DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=scott;

2)改变表的owner

impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp TABLES=scott.dept REMAP_SCHEMA=scott:system;

3)导入表空间

impdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=example;

4)导入数据库

impdb system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y;

5)追加数据

impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION=append;

 

如果导出和导入的用户名和表空间名不一样，则使用如下命令

impdp noep/xbwesrU3tN directory=dump_dir dumpfile=noep_test.dmp logfile=impdp_noep.log remap_schema=noep_test:noep remap_tablespace=noep_test:noep_data

 

impdp的remap使用说明

总结：impdp导入到目标数据库中，需要确保目标数据库中存在源数据库中的用户和用户所属表空间都存在，否则需要使用remap参数。

 

如：

情景一：

源数据库：create user test identified by test default tablespace ts_test;

目标数据库：create user test identified by test default tablespace ts_test1;（表空间不同）

这样必须加remap_tablespace=ts_test:ts_test1

 

情景二：

源数据库：create user test identified by test default tablespace ts_test;

目标数据库：create user test2 identified by test default tablespace ts_test1;(用户和表空间都不同)

这样必须加remap_schema=test:test2 remap_tablespace=ts_test:ts_test1

 

 

------------------------------------------------------------------------------------------------------------

38、修改RAC数据库的归档的默认路径：

SQL> select inst_id,instance_name,version,archiver,status from gv$instance;

 

   INST_ID INSTANCE_NAME    VERSION           ARCHIVE STATUS

---------- ---------------- ----------------- ------- ------------

         1 pushdb1          10.2.0.4.0        STARTED OPEN

         2 pushdb2          10.2.0.4.0        STARTED OPEN

 

SQL> alter system set log_archive_dest_1='location=/oracle/product/archivelog1' sid='pushdb1';

System altered.

 

SQL> alter system set log_archive_dest_1='location=/oracle/product/archivelog2' sid='pushdb2';

System altered.

 

SQL> show parameter log_archive

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_config                   string

log_archive_dest                     string

log_archive_dest_1                   string      location=/oracle/product/archivelog1

log_archive_dest_10                  string

... ....

 

 

39、CRS及相关服务的常用命令的演示

查看状态的命令

# su – oracle --先切换到oracle用户

$ crs_stat -t --查看crs及所有的service的状态

$ crsctl check crs --用crsctl命令，检查crs相关服务的状态

$ srvctl status nodeapps -n db01 (db02) --使用srvctl查看所有节点上的服务是否正常

$ srvctl status database -d db --使用srvctl查看数据库状态

 

启动和关闭相关服务的命令

# su – oracle

$ crs_start –all --启动所有的crs服务

$ crs_stop –all --停止所有的crs服务

$ crsctl start crs --启动crs服务

$ crsctl stop crs --停止crs服务

$ srvctl start nodeapps -n db01(db02) --启动某节点上所有的应用

$ srvctl stop nodeapps -n db01(db02) --停止某节点上所有的应用

$ srvctl start listener -n db01(db02) --启动某个节点的listener

$ srvctl stop listener -n db01(db02) --停止某个节点的listener

$ srvctl start instance –d db –i db01(db02) --启动某个instance

$ srvctl stop instance –d db –i db01(db02) --停止某个instance

$ srvctl start database –d db --启动数据库

$ srvctl stop database –d db --停止数据库

 

------------------------------------------------------------------------------------------

40、Oracle RAC停开机器顺序标准做法

开机顺序：

 

srvctl start nodeapps -n <node1 hostname>

srvctl start nodeapps -n <node2 hostname>

srvctl start asm -n <node1 hostname>

srvctl start asm -n <node2 hostname>

srvctl start database -d <database name>

srvctl start service -d <database name> -s <service name>

 

关闭顺序：　

 

srvctl stop service -d <database name> -s <service name>

srvctl stop database -d <database name>

srvctl stop asm -n <node1 hostname>

srvctl stop asm -n <node2 hostname>

srvctl stop nodeapps -n <node1 hostname>

srvctl stop nodeapps -n <node2 hostname>

 

如果某些服务没有启动

 

手工SHUTDOWN 每个实例

然后CRS_STOP -ALL，关闭其他服务。

启动的时候可以通过CRS_START -ALL启动