一、用户常用操作

删除用户：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#