后端开发数据库技术

Oracle常用命令（一）

一些常用的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';

查看不同用户的连接数：select 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;

 

设置密码永不过期：

  •    查看用户的proifle是哪个，一般是default：select username,PROFILE from dba_users;

  •    查看指定概要文件（如default）的密码有效期设置：

          SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

  •    将密码有效期由默认的180天修改成“无限制”：（修改之后不需要重启动数据库，会立即生效）

          ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

  •    修改后，还没有被提示ORA-28002警告的帐户不会再碰到同样的提示："已经被提示的帐户必须再改一次密码"

启停操作

  • 数据库启停：

关停：shutdown immediate

启动：startup

  • 监听启停：

启动：lsrnctl start

关停：lsrnctl stop

状态查询：lsrnctl status

数据泵常用操作

准备工作：

  • vzf 字符集确认

通过以下方式确认源端、目标端、客户端数据库字符集为一致：

数据库字符集确认方式：

            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

排除表的导出：

      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# ;

杀掉锁的进程：

alter system kill session'sid,serial#';

注：一定要核对仔细是不是自己锁的表，看准了再kill；

OS级别kill（如果上面的步骤无法杀掉，使用此方法）：

select spid, osuser, s.program
from v$session s,v$process p
where s.paddr=p.addr and s.sid=30（30是上面的sid）

查行锁：

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='96fndms7';

一下为死锁相关的表，通过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语句产生的锁

Comment