oracle常用操作（一）
一、用户常用操作
删除用户：drop user user_name cascade
注意：如果删除提示有用户在连接，操作顺序是停掉监听，再做删除
如果没有删除用户权限，则可以执行：
select 'drop table '||table_name||';' from cat where table_type = 'TABLE';
创建用户：create user username identified by password
创建用户并增加表空间：create user username identified by password default tablespace space_name(表空间名称) temporary tablespace space_name（临时表空间名称）
授权用户权限：grant connect，dba to username
查询所有用户：select username from dba_users
select * from all_users
注意：dba_开头的是查全库所有的,all_开头的是查当前用户可以看到的,user_开头的是查当前用户的
查看所有用户的信息
select owner, object_type, status, count(*) count# from all_objects group by owner,object_type, status;
查询用户session：select sid,serial# from v$session where username='TEST';
中断用户连接：alter system kill session 'sid,seria';
修改密码：alter user apps identified by 123456
用户解锁：alter user Scott account unlock；
查询oracle的连接数：select count(*) from v$session;
查询oracle的并发连接数：select count(*) from v$session where status='ACTIVE';
查看不同用户的连接数：6select username,count(username) from v$session where username is not null group by username;
查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：
select * from dba_sys_privs;
select * from user_sys_privs;
查看角色(只能查看登陆用户拥有的角色)所包含的权限
select * from role_sys_privs;
查看用户对象权限：
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
查看所有角色：
select * from dba_roles;
查看用户或角色所拥有的角色：
select * from dba_role_privs;
select * from user_role_privs;
查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
select * from V$PWFILE_USERS;
设置密码永不过期：
a、查看用户的proifle是哪个，一般是default：
sql>SELECT username,PROFILE FROM dba_users;
b、查看指定概要文件（如default）的密码有效期设置：
sql>SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
c、将密码有效期由默认的180天修改成“无限制”：
sql>ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
修改之后不需要重启动数据库，会立即生效。
d、修改后，还没有被提示ORA-28002警告的帐户不会再碰到同样的提示；
已经被提示的帐户必须再改一次密码
二、启停操作
- 数据库启停：
shutdown immediate
startup
- 监听启停：
lsrnctl start
lsrnctl stop
lsrnctl status
三、数据泵常用操作
准备工作：
■ 字符集确认
通过一下方式确认源端、目标端、客户端数据库字符集为一致：
数据库字符集确认方式：
col parameter for a20
col value for a20
select parameter,value from nls_database_parameters where parameter in ('NLS_CHARACTERSET','NLS_LANGUAGE','NLS_TERRITORY');
客户端字符集确认方式：
echo $NLS_LANG
■ 软件版本确认
尽可能确保源端、目标端版本为一致。
此外，目标端版本>=源端版本
■ 表空间确认
目标端数据库中包括dump文件对象涉及的所有表空间。
■ 本地磁盘大小确认
源端、目标端确保有足够的磁盘空间。
■ RAC数据库注意事项
源端数据库如果为RAC数据库，dump文件未存放于共享存储时，需指定CLUSTER=no参数。
导出：
export ORACLE_SID=c3db
■ 建立目录对象：
sqlplus / as sysdba
create directory dbdmp as '<path>';
exit
■ 执行导出
表模式
expdp "'/ as sysdba'" directory=dbdmp tables=SCOTT.EMP dumpfile=EMP_%U_`date +%Y%m%d`.dmp logfile=logfile_`date +%Y%m%d`.log
用户模式
expdp "'/ as sysdba'" directory=dbdmp SCHEMAS=SCOTT dumpfile=full_%U_`date +%Y%m%d`.dmp logfile=logfile_`date +%Y%m%d`.log
表空间模式
expdp "'/ as sysdba'" directory=dbdmp TABLESPACES=TBS01 dumpfile=full_%U_`date +%Y%m%d`.dmp logfile=logfile_`date +%Y%m%d`.log
全库模式
expdp "'/ as sysdba'" directory=dbdmp full=YES dumpfile=full_%U_`date +%Y%m%d`.dmp logfile=logfile_`date +%Y%m%d`.log
压缩模式
expdp "'/ as sysdba'" directory=dbdmp full=YES dumpfile=full_%U_`date +%Y%m%d`.dmp logfile=logfile_`date +%Y%m%d`.log COMPRESSION=ALL
并行模式
expdp "'/ as sysdba'" directory=dbdmp full=YES dumpfile=full_%U_`date +%Y%m%d`.dmp logfile=logfile_`date +%Y%m%d`.log parallel=8
导入：
export ORACLE_SID=c3db
■ 建立目录对象到存放dump文件目录
sqlplus / as sysdba
create directory dbdmp as '<path>';
exit
■ 字符集确认
通过一下方式确认源端、目标端、客户端数据库字符集为一致：
数据库字符集确认方式：
col parameter for a20
col value for a20
select parameter,value from nls_database_parameters where parameter in ('NLS_CHARACTERSET','NLS_LANGUAGE','NLS_TERRITORY');
客户端字符集确认方式：
echo $NLS_LANG
■ 执行导入
impdp "'/ as sysdba'" directory=dbdmp tables=SCOTT.EMP dumpfile=EMP_%U_`date +%Y%m%d`.dmp logfile=logfile_`date +%Y%m%d`.log
用户模式
impdp "'/ as sysdba'" directory=dbdmp SCHEMAS=SCOTT dumpfile=full_%U_`date +%Y%m%d`.dmp logfile=logfile_`date +%Y%m%d`.log
表空间模式
impdp "'/ as sysdba'" directory=dbdmp TABLESPACES=TBS01 dumpfile=full_%U_`date +%Y%m%d`.dmp logfile=logfile_`date +%Y%m%d`.log
全库模式
impdp "'/ as sysdba'" directory=dbdmp full=YES dumpfile=full_%U_`date +%Y%m%d`.dmp logfile=logfile_`date +%Y%m%d`.log
压缩模式
impdp "'/ as sysdba'" directory=dbdmp full=YES dumpfile=full_%U_`date +%Y%m%d`.dmp logfile=logfile_`date +%Y%m%d`.log COMPRESSION=ALL
并行模式
impdp "'/ as sysdba'" directory=dbdmp full=YES dumpfile=full_%U_`date +%Y%m%d`.dmp logfile=logfile_`date +%Y%m%d`.log parallel=8
补充：
select * from dba_directories;
create or replace directory oracledump as '/home/oracle/db_dmp';
impdp "'/ as sysdba'" directory=oracledump dumpfile=cretdb_20180613.dmp logfile=20180613.log schemas=CMSDB,SRCODS,OPUSER,ZGCREAD table_exists_action=replace
排除表的导出：
expdp "'/ as sysdba'" directory=IBSDUMP SCHEMAS=ICORE_IMS,ICORE_ECIDB,SRCODS,ICORE_PRDTDB dumpfile=ibsfull_%U_`date +%Y%m%d`.dmp logfile=logfile_`date +%Y%m%d`.log EXCLUDE=table:\"like \'IPAY%\'\",table:\" in \(\'TRC_LOG\',\'ECI_CIF_TRACE\',\'TRC_LOG_HST\')\"
四、表空间常用操作
查询出建立在某个表空间上的所有表名
select tablespace_name,contents from dba_tablespaces;
查询数据库数据文件信息表
select file_name from dba_data_files;
查看数据库用户默认表空间
select username,default_tablespace from user_users/dba_users;
查看数据库临时表空间
select file_name,tablespace_name,bytes from dba_temp_files;
创建表空间，自增步长为1G
create tablespace FILLER_DEFAULT datafile '/data/oradata/zgcuatdb/FILLER_DEFAULT.dbf' size 10g autoextend on next 1g;
创建临时表空间，自增步长为1G
create TEMPORARY tablespace FILLER_TEMP tempfile '/data/oradata/zgcuatdb/FILLER_TEMP.dbf' size 5g autoextend on next 1g;
删除表空间
drop tablespace AHTBSPACETEST including contents and datafiles cascade constraints
修改表空间
alter table TD_USERS move tablespace TABLESPACE_A：将表TD_USERS移至表空间tablesapc
alter index TD_USERS_ID rebuild tablespace TABLESPACE_A ：修改该表的索引的表空间
查询表空间大小
SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size FROM dba_tablespaces t, dba_data_files d WHERE t.tablespace_name = d.tablespace_name GROUP BY t.tablespace_name;
查看表空间物理名称和大小
SELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files
ORDER BY tablespace_name;
查询表空间使用情况
select sum(bytes)/(1024*1024) as free_space,tablespace_name
from dba_free_space
group by tablespace_name;
Select A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES
FREE,(B.BYTES*100)/A.BYTES % USED,(C.BYTES*100)/A.BYTES % FREE
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
Where A.TABLESPACE_NAME=B.TABLESPACE_NAME AND
A.TABLESPACE_NAME=C.TABLESPACE_NAME;
五、awr和addr操作
exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
@?/rdbms/admin/awrrpt
输入开始和结束ID，格式设置为html
@?/rdbms/admin/addmrpt.sql
六、数据信息统计
查询数据库所有数据量：select sum(num_rows) from user_tables;
查询数据库各表数据量：select u.TABLE_NAME,u.NUM_ROWS from user_tables u order by u.NUM_ROWS desc
查看数据库的创建日期和归档方式
SELECT created, log_mode, log_mode FROM v$database;
查看归档日志大小：select GROUP#,BYTES/1024/1024 size_M,STATUS,archived from V$log;
查看当前SID：select * from v$mystat where rownum = 1;或者使用进程查看：ps -ef| grep smon 或者 echo $ORACLE_SID
select count(*) from v$session where status='ACTIVE'; #并发连接数
select count(*) from v$process;
查看服务器模式：
select p.program,s.server from v$session s,v$process p where s.paddr=p.addr;
查看正在等待IO资源的sql
select * from (select s.PARSING_SCHEMA_NAME,s.DIRECT_WRITES,substr(s.SQL_TEXT,1,500),s.DISK_READS from v$sql s order by s.DISK_READS desc) where rownum<20;
查看正在等待IO资源的对象
select d.object_name,d.object_type,d.owner from v$session s,dba_objects d where event like 'db file%read' and s.ROW_WAIT_OBJ#=d.object_id;
select * from v$session_wait where wait_class <> 'Idle' order by seconds_in_wait desc;
查看并行查询的sql
select table_name from dba_tables where degree='1' or degree='DEFULT';
select instances,length(instances) from dba_tables group by instances;
select degree,length(degree) from dba_tables group by degree;
更新表统计值
EXECUTE dbms_stats.gather_table_stats(OWNNAME=>'ensemble',tabname=>'MB_TRAN_HIST',cascade=>true);
analyze table ensemble.MB_TRAN_HIST compute statistics;
查看数据库配置参数
show parameter db_block_size;
show parameter sga;
show parameter cursor_sharing;
show parameter processes;
show parameter memory_max_target;
show parameter memory_target;
show parameter sga_max_size
七、查看并修改数据库IO模式：
show parameter disk_asynch_io;
show parameter filesystemio_options;
alter system set filesystemio_options = SETALL scope=spfile;
八、导出查询信息
set head off
set pagesize 50000
spool on
spool filename
select * from logjbfp where cycflg='D' and repdat='20120201'
spool off
九、OEM使用
第一步：删除当前的 Database Control 资料档案库。命令为：emca -repos drop
第二步：创建新的 Database Control 资料档案库。命令为：emca -repos create
第三步：配置、部署数据库的 Database Control EM资料档案库。命令为：emca -config dbcontrol db
修改监听端口号后企业管理器（Enterprise Manager）对应的调整
- 修改emoms.properties属性文件中端口信息
ora10g@testdb /home/oracle$ vi$ORACLE_HOME/${HOSTNAME}_${ORACLE_SID}/sysman/config/emoms.properties
这个文件中有两行含有1521端口号的信息，将他们统统的修改为1526
oracle.sysman.eml.mntr.emdRepPort=1521
oracle.sysman.eml.mntr.emdRepConnectDescriptor=(DESCRIPTION\=(ADDRESS_LIST\=(ADDRESS\=(PROTOCOL\=TCP)(HOST\=testdb)(PORT\=1521)))(CONNECT_DATA\=(SERVICE_NAME\=ora10g)))
这两个行修改后的内容如下：
oracle.sysman.eml.mntr.emdRepPort=1526
oracle.sysman.eml.mntr.emdRepConnectDescriptor=(DESCRIPTION\=(ADDRESS_LIST\=(ADDRESS\=(PROTOCOL\=TCP)(HOST\=testdb)(PORT\=1526)))(CONNECT_DATA\=(SERVICE_NAME\=ora10g)))
- 修改targets.xml配置文件中端口信息
ora10g@testdb /home/oracle$ vi$ORACLE_HOME/${HOSTNAME}_${ORACLE_SID}/sysman/emd/targets.xml
这个文件中的1521端口号的信息统统的修改为1526
- 重启EM
ora11g@testdb /home/oracle$ emctl stopdbconsole
ora11g@testdb /home/oracle$ emctl startdbconsole
十、执行计划
- autotrace
set autotrace on开启autotrace，后面执行sql语句会自动显示sql执行结果和跟踪信息。
set autot traceonly; 仅显示跟踪信息。
set autot on explain; 仅显示跟踪的explain信息。
set autot on statistics 仅显示跟踪的统计信息。
set autotrace off关闭跟踪。
- 查看真实的sql语句执行计划
explain plan for + select * from table where ...
select sql_id,child_number from v$sql where sql_text like '%from ensemble.dtp_submitlog where bxid=%';
select * from table(dbms_xplan.display_cursor('ajvtvf2dr1rb6',0,'ALLSTATS LAST'));
注意：上面两点效果一致
十一、锁问题排查操作：
查看被锁的表
select username,lockwait,status,machine,program from v$session where sid in(select session_id from v$locked_object);
查询锁的sessionid，sid和serial#
select l.session_id sid ,s.serial#,l.locked_mode,l.oracle_username,l.os_user_name,s.machine,s.terminal,o.object_name,s.logon_time from v$locked_object l,all_objects o,v$session s where l.object_id=o.object_id and l.session_id=s.sid order by sid ,s.serial#;
查看哪个用户造成的锁，与上面的sql存在重复性
select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
SELECT sid, serial#, username, osuser FROM v$session;
杀掉锁的进程
alter system kill session'sid,serial#';
- 查行锁：
column event format a30
column sess format a20
set linesize 150
break on id1 skip 1
select decode(request,0,'Holder:',' Waiter:') || s.inst_id || ':' || s.sid||','|| s.serial# sess,
id1, id2, lmode, request, l.type, ctime, s.sql_id, s.event,s.last_call_et
from gv$lock l, gv$session s
where (id1, id2, l.type) in
(select id1, id2, type from gv$lock where request>0)
and l.sid=s.sid
and l.inst_id=s.inst_id
order by id1, ctime desc, request;
- 通过查行锁定位出来哪些sql导致的死锁：
查询导致锁表的sqlId:
select sql_text from gv$sql where sql_id='9g5813my4anbt'; 这个是查具体sql语句为死锁相关的表，通过dba权限去查看：
SELECT * FROM v$lock;
SELECT * FROM v$sqlarea;
SELECT * FROM v$session;
SELECT * FROM v$process ;
SELECT * FROM v$locked_object;
SELECT * FROM all_objects;
SELECT * FROM v$session_wait;
--查看被锁的表
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;
--查看那个用户那个进程照成死锁
select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
--查看连接的进程
SELECT sid, serial#, username, osuser FROM v$session;
- 查出锁定表的sid, serial#,os_user_name, machine_name, terminal，锁的type,mode
SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,
s.terminal, s.logon_time, l.type
FROM v$session s, v$lock l
WHERE s.sid = l.sid
AND s.username IS NOT NULL
ORDER BY sid;
这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，
任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。
--杀掉进程 sid,serial#
