精华内容
下载资源
问答
  • 需求:需要将Oracle数据库中的一个表导出为CSV文件,但是表比较大,共有29488763条数据,不能通过简单的PLSQL查询在转成CSV文件的方法...如下:----Oracle中表数据行数过多导出为CSV文件的方法如下:---登录system用...

    需求:

    需要将Oracle数据库中的一个表导出为CSV文件,但是表比较大,共有29488763条数据,不能通过简单的PLSQL查询在转成CSV文件的方法,该表导出的DMP文件,约有2.3G,导出的CSV文件有3.4G。

    解决思路:

    写一个存储过程,把sql查询出来的结果使用格式转换的方式转换成CSV格式的文件。

    如下:

    ----Oracle中表数据行数过多导出为CSV文件的方法如下:

    ---登录system用户
    ---创建导出csv方法的存储过程,system用户下运行
    CREATE OR REPLACE PROCEDURE SQL_TO_CSV  
    (  
     P_QUERY IN VARCHAR2, 
     P_DIR IN VARCHAR2,
     P_FILENAME IN VARCHAR2 
     )  
     IS  
      L_OUTPUT UTL_FILE.FILE_TYPE;  
      L_THECURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;  
      L_COLUMNVALUE VARCHAR2(4000);  
      L_STATUS INTEGER;  
      L_COLCNT NUMBER := 0;  
      L_SEPARATOR VARCHAR2(1);  
      L_DESCTBL DBMS_SQL.DESC_TAB;  
      P_MAX_LINESIZE NUMBER := 32000;  
    BEGIN  
      --OPEN FILE  
      L_OUTPUT := UTL_FILE.FOPEN(P_DIR, P_FILENAME, 'W', P_MAX_LINESIZE);  
      --DEFINE DATE FORMAT  
      EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS''';  
      --OPEN CURSOR  
      DBMS_SQL.PARSE(L_THECURSOR, P_QUERY, DBMS_SQL.NATIVE);  
      DBMS_SQL.DESCRIBE_COLUMNS(L_THECURSOR, L_COLCNT, L_DESCTBL);  
      --DUMP TABLE COLUMN NAME  
      FOR I IN 1 .. L_COLCNT LOOP  
        UTL_FILE.PUT(L_OUTPUT,L_SEPARATOR || '"' || L_DESCTBL(I).COL_NAME || '"'); 
        DBMS_SQL.DEFINE_COLUMN(L_THECURSOR, I, L_COLUMNVALUE, 4000);  
        L_SEPARATOR := ',';  
      END LOOP;  
      UTL_FILE.NEW_LINE(L_OUTPUT);
      --EXECUTE THE QUERY STATEMENT  
      L_STATUS := DBMS_SQL.EXECUTE(L_THECURSOR);  
       
      --DUMP TABLE COLUMN VALUE  
      WHILE (DBMS_SQL.FETCH_ROWS(L_THECURSOR) > 0) LOOP  
        L_SEPARATOR := '';  
        FOR I IN 1 .. L_COLCNT LOOP  
          DBMS_SQL.COLUMN_VALUE(L_THECURSOR, I, L_COLUMNVALUE);  
          UTL_FILE.PUT(L_OUTPUT,  
                      L_SEPARATOR || '"' ||  
                      TRIM(BOTH ' ' FROM REPLACE(L_COLUMNVALUE, '"', '""')) || '"');  
          L_SEPARATOR := ',';  
        END LOOP;  
        UTL_FILE.NEW_LINE(L_OUTPUT);  
      END LOOP;  
      --CLOSE CURSOR  
      DBMS_SQL.CLOSE_CURSOR(L_THECURSOR);  
      --CLOSE FILE  
      UTL_FILE.FCLOSE(L_OUTPUT);  
    EXCEPTION  
      WHEN OTHERS THEN  
        RAISE;  
    END;  
    /


    ---创建导出目录,该目录为Oracle数据库所在服务器或者主机目录。


    create or replace directory OUT_PATH as 'D:\';


    ---为system用户赋予导出目录OUT_PATH的读写权限


    ---登录sys用户,或者其他dba用户
     Grant read,write on directory OUT_PATH to system;
     
    ----登录system用户,执行导出操作(总共执行约40分钟)
     EXEC SQL_TO_CSV('select * from llsj.DW_GS_SFZTXCL','OUT_PATH','DW_GS_SFZTXCL.csv');
    展开全文
  • oracle数据库迁移至mysql数据库,除了oracle数据库模型移...3、运行oracle数据库程序P_ETL_ORA_DATA,生成各表的csv数据文件,同时也生成一个导入mysql的脚本文件imp_data.sql 4、导入mysql数据,文件内容如下 load
  • Oracle连接数过多释放机制

    千次阅读 2017-12-07 11:21:32
    Oracle服务器连接数过多会当掉,把连接数过多的客户机网线拔出后,在远程Oracle上依然还会保留此用户的连接数,久久不能释放,上网查了下可以以下面方法解决。    通过profile可以对用户会话进行一定的限制,比如...

    一、设置回话和连接时间

          Oracle服务器连接数过多会当掉,把连接数过多的客户机网线拔出后,在远程Oracle上依然还会保留此用户的连接数,久久不能释放,上网查了下可以以下面方法解决。

     通过profile可以对用户会话进行一定的限制,比如IDLE时间。

     
    将IDLE超过一定时间的会话断开,可以减少数据库端的会话数量,减少资源耗用。
     
    使用这些资源限制特性,需要设置resource_limit为TRUE:

       [oracle@test126 udump]$ sqlplus "/ as sysdba"
     
      SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 13 07:58:21 2006
     
      Copyright (c) 1982, 2005, Oracle.  All rights reserved.
     
      Connected to:
     
      Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
     
      With the Partitioning and Data Mining options
     
      SQL> show parameter resource
     
      NAME                                TYPE        VALUE
     
      ------------------------------------ ----------- ------------------------------
     
      resource_limit                      boolean    TRUE
     
      resource_manager_plan                string
     
      该参数可以动态修改:
     
      SQL> alter system set resource_limit=true;
     
      System altered.
     
      数据库缺省的PROFILE设置为:
     
      SQL> SELECT * FROM DBA_PROFILES;
     
      PROFILE              RESOURCE_NAME                    RESOURCE LIMIT
     
      -------------------- -------------------------------- -------- ---------------
     
      DEFAULT              COMPOSITE_LIMIT                  KERNEL  UNLIMITED
     
      DEFAULT              SESSIONS_PER_USER                KERNEL  UNLIMITED
     
      DEFAULT              CPU_PER_SESSION                  KERNEL  UNLIMITED
     
      DEFAULT              CPU_PER_CALL                    KERNEL  UNLIMITED
     
      DEFAULT              LOGICAL_READS_PER_SESSION        KERNEL  UNLIMITED
     
      DEFAULT              LOGICAL_READS_PER_CALL          KERNEL  UNLIMITED
     
      DEFAULT              IDLE_TIME                        KERNEL  UNLIMITED
     
      DEFAULT              CONNECT_TIME                    KERNEL  UNLIMITED
     
      DEFAULT              PRIVATE_SGA                      KERNEL  UNLIMITED
     
      DEFAULT              FAILED_LOGIN_ATTEMPTS            PASSWORD 10
     
      DEFAULT              PASSWORD_LIFE_TIME              PASSWORD UNLIMITED
     
      PROFILE              RESOURCE_NAME                    RESOURCE LIMIT
     
      -------------------- -------------------------------- -------- ---------------
     
      DEFAULT              PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
     
      DEFAULT              PASSWORD_REUSE_MAX              PASSWORD UNLIMITED
     
      DEFAULT              PASSWORD_VERIFY_FUNCTION        PASSWORD NULL
     
      DEFAULT              PASSWORD_LOCK_TIME              PASSWORD UNLIMITED
     
      DEFAULT              PASSWORD_GRACE_TIME              PASSWORD UNLIMITED
     
      16 rows selected.
     
      创建一个允许3分钟IDLE时间的PROFILE:
     
      SQL> CREATE PROFILE KILLIDLE LIMIT IDLE_TIME 3;
     
      Profile created.
    新创建PROFILE的内容:
     
      SQL> col limit for a10
     
      SQL> select * from dba_profiles where profile='KILLIDLE';
     
      PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
     
      ------------------------------ -------------------------------- -------- ----------
     
      KILLIDLE                      COMPOSITE_LIMIT                  KERNEL  DEFAULT
     
      KILLIDLE                      SESSIONS_PER_USER                KERNEL  DEFAULT
     
      KILLIDLE                      CPU_PER_SESSION                  KERNEL  DEFAULT
     
      KILLIDLE                      CPU_PER_CALL                    KERNEL  DEFAULT
     
      KILLIDLE                      LOGICAL_READS_PER_SESSION        KERNEL  DEFAULT
     
      KILLIDLE                      LOGICAL_READS_PER_CALL          KERNEL  DEFAULT
     
      KILLIDLE                      IDLE_TIME                        KERNEL  3
     
      KILLIDLE                      CONNECT_TIME                    KERNEL  DEFAULT
     
      KILLIDLE                      PRIVATE_SGA                      KERNEL  DEFAULT
     
      KILLIDLE                      FAILED_LOGIN_ATTEMPTS            PASSWORD DEFAULT
     
      KILLIDLE                      PASSWORD_LIFE_TIME              PASSWORD DEFAULT
     
      PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
     
      ------------------------------ -------------------------------- -------- ----------
     
      KILLIDLE                      PASSWORD_REUSE_TIME              PASSWORD DEFAULT
     
      KILLIDLE                      PASSWORD_REUSE_MAX              PASSWORD DEFAULT
     
      KILLIDLE                      PASSWORD_VERIFY_FUNCTION        PASSWORD DEFAULT
     
      KILLIDLE                      PASSWORD_LOCK_TIME              PASSWORD DEFAULT
     
      KILLIDLE                      PASSWORD_GRACE_TIME              PASSWORD DEFAULT
     
      16 rows selected.
     
      测试用户:
     
      SQL> select username,profile from dba_users where username='EYGLE';
     
      USERNAME                      PROFILE
     
      ------------------------------ --------------------
     
      EYGLE                          DEFAULT
     
      修改eygle用户的PROFILE使用新建的PROFILE:
     
      SQL> alter user eygle profile killidle;
     
      User altered.
     
      SQL> select username,profile from dba_users where username='EYGLE';
     
      USERNAME                      PROFILE
     
      ------------------------------ --------------------
     
      EYGLE                          KILLIDLE
     
      进行连接测试:
     
      [oracle@test126 admin]$ sqlplus eygle/eygle@eygle
     
      SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 13 08:07:13 2006
     
      Copyright (c) 1982, 2005, Oracle.  All rights reserved.
     
      Connected to:
     
      Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
     
      With the Partitioning and Data Mining options
     
      SQL> select username,profile from dba_users where username='EYGLE';
     
      USERNAME                      PROFILE
     
      ------------------------------ ------------------------------
     
      EYGLE                          KILLIDLE
     
      当IDLE超过限制时间时,连接会被断开:
     
      SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
     
      TO_CHAR(SYSDATE,'YY
     
      -------------------
     
      2006-10-13 08:08:41
     
      SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
     
      select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual
     
      *
     
      ERROR at line 1:
     
      ORA-02396: exceeded maximum idle time, please connect again

     

    二、创建新的profile

    sqlplus /nolog  
    打开sqlplus   

    conn sys/orcl@orcl as sysdba
    使用具有dba权限得用户登陆oracle  
     
    show parameter resource_limit  
    显示资源限定是否开启,value为true是开启,为false是关闭  

    alter system set resource_limit=true  
    如果未开启,则使用此命令开启资源限定功能  


    create profile profileName limit connect_time 60 idle_time 30  
    创建profile文件,profileName任意起,connect_time设置连接超过多少分钟后强制释放,idle_time设置连续不活动的会话超过多少分钟后强制释放  

    alter user oracleUser profile profileName  
    将profile文件作用于指定用户 

    Oracle session连接数和inactive的问题记录oracle学习 2009-03-10 15:42:37 阅读317 评论0   字号:大中小 订阅 .

       从上周起,服务器Oracle数据库出现问题,用不到半天,就会报maxsession(150)的问题,肯定是数据库的会话超过最大数了。

      由于服务器跑的是文件传输应用,占用的请求和会话肯定很大,因此用户数不大就已经让oracle的会话数达到最大值。

      处理方式不外乎两种:扩大oracle最大session数以及清除inactive会话,当然还有,就是从数据库连接池和程序bug上面下手。

    从各处收集了一些查看当前会话的语句,记录一下:

    1.select count(*) from v$session;

      select count(*) from v$process;

      查看当前总会话数和进程数,这两个视图就是跟会话及进程有关的重要视图啦,信息都是从这里面取的。

    2.查询那些应用的连接数此时是多少

    select  b.MACHINE, b.PROGRAM , count(*) from v$process a, v$session b where a.ADDR = b.PADDR and  b.USERNAME is not null   group by  b.MACHINE  , b.PROGRAM order by count(*) desc;

    3.查询是否有死锁

    select * from v$locked_object;

    如果查询结果为no rows selected,说明数据库中没有死锁。否则说明数据库中存在死锁。

    接下来说明一下会话的状态:

    1.active 处于此状态的会话,表示正在执行,处于活动状态。

    2.killed 处于此状态的会话,表示出现了错误,正在回滚,当然,也是占用系统资源的。还有一点就是,killed的状态一般会持续较长时间,而且用windows下的工具pl/sql developer来kill掉,是不管用的,要用命令:alter system kill session 'sid,serial#' ;

    3.inactive 处于此状态的会话表示不是正在执行的,比如select语句已经完成。我一开始以为,只要是inactive状态的会话,就是该杀,为什么不释放呢。其实,inactive对数据库本身没有什么影响,但是如果程序没有及时commit,那么就会造成占用过多会话。解决inactive的方法最好的就是在oracle中直接设置超时时间,也是有两种方法,区别暂时还不清楚:

    1.修改sqlnet.ora文件,新增expire_time=x(单位是分钟)  

    我的sqlnet.ora位置在D:/oracle/ora92/network/admin

    2.通过ALTER PROFILE DEFAULT LIMIT IDLE_TIME 10; 命令修改,记得重启下oracle。

    三、修改ORACLE 中的SESSION和PROCESS

    会话sessions和进程pocesses的关系 
    一个process可以有0个、1个或者多个session,一个session也可以存在若干个process中,并行同样是一个session对应一个process,主session是coordinator session,每个parallel process同样会对应数据库里一个单独的session。可以从v$px_session和v$session中验证这点。 
    连接connects,会话sessions和进程pocesses的关系

    每个sql login称为一个连接(connection),而每个连接,可以产生一个或多个会话,如果数据库运行在专用服务器方式,一个会话对应一个服务器进程(process),如果数据库运行在共享服务器方式,一个服务器进程可以为多个会话服务。

    Oracle的sessions和processes的数量关系是:sessions=1.1 * processes + 5

    下面我们用两种方法修改PROCESS的最大值 
    一、通过Oracle Enterprise Manager Console在图形化管理器中修改 
    以系统管理员的身份登入,进入界面 数据库的例程 - 配置 - 一般信息 - 所有初始化参数,修改processes的值

    二、在SQLPLUS中修改 
    以DBA权限登录,修改PROCESS的值(SESSION的值会跟着改);创建pfile;重新启动数据库。输入的SQL命令如下,回显信息省略了 
    SQL> connect sys/sys as sysdba 
    SQL> alter system set processes=400 scope = spfile; 
    SQL> create pfile from spfile; 
    SQL> shutdown immediate; 
    SQL> startup

    四、kill session的方式来终止一个进程

    我们知道,在Oracle数据库中,可以通过kill session的方式来终止一个进程,其基本语法结构为:

    alter system kill session 'sid,serial#' ;

    被kill掉的session,状态会被标记为killed,Oracle会在该用户下一次touch时清除该进程.

    我们发现当一个session被kill掉以后,该session的paddr被修改,如果有多个session被kill,那么多个session
    的paddr都被更改为相同的进程地址:

     

    SQL> select saddr,sid,serial#,paddr,username,status from v$session where username is not null;

    SADDR           SID    SERIAL# PADDR    USERNAME                       STATUS
    -------- ---------- ---------- -------- ------------------------------ --------
    542E0E6C         11        314 542B70E8 EYGLE                          INACTIVE
    542E5044         18        662 542B6D38 SYS                            ACTIVE


    SQL> alter system kill session '11,314';

    System altered.

    SQL> select saddr,sid,serial#,paddr,username,status from v$session where username is not null;

    SADDR           SID    SERIAL# PADDR    USERNAME                       STATUS
    -------- ---------- ---------- -------- ------------------------------ --------
    542E0E6C         11        314 542D6BD4 EYGLE                          KILLED
    542E5044         18        662 542B6D38 SYS                            ACTIVE


    SQL> select saddr,sid,serial#,paddr,username,status from v$session where username is not null;

    SADDR           SID    SERIAL# PADDR    USERNAME                       STATUS
    -------- ---------- ---------- -------- ------------------------------ --------
    542E0E6C         11        314 542D6BD4 EYGLE                          KILLED
    542E2AA4         14        397 542B7498 EQSP                           INACTIVE
    542E5044         18        662 542B6D38 SYS                            ACTIVE

    SQL> alter system kill session '14,397';

    System altered.

    SQL> select saddr,sid,serial#,paddr,username,status from v$session where username is not null;

    SADDR           SID    SERIAL# PADDR    USERNAME                       STATUS
    -------- ---------- ---------- -------- ------------------------------ --------
    542E0E6C         11        314 542D6BD4 EYGLE                          KILLED
    542E2AA4         14        397 542D6BD4 EQSP                           KILLED
    542E5044         18        662 542B6D38 SYS                            ACTIVE


    在这种情况下,很多时候,资源是无法释放的,我们需要查询spid,在操作系统级来kill这些进程.

    但是由于此时v$session.paddr已经改变,我们无法通过v$session和v$process关联来获得spid

    那还可以怎么办呢?

    我们来看一下下面的查询:

       SQL> SELECT s.username,s.status,
      2  x.ADDR,x.KSLLAPSC,x.KSLLAPSN,x.KSLLASPO,x.KSLLID1R,x.KSLLRTYP,
      3  decode(bitand (x.ksuprflg,2),0,null,1)
      4  FROM x$ksupr x,v$session s
      5  WHERE s.paddr(+)=x.addr
      6  and bitand(ksspaflg,1)!=0;


    USERNAME                       STATUS   ADDR       KSLLAPSC   KSLLAPSN KSLLASPO       KSLLID1R KS D
    ------------------------------ -------- -------- ---------- ---------- ------------ ---------- -- -
                                            542B44A8          0          0                       0
                                   ACTIVE   542B4858          1         14 24069                 0    1
                                   ACTIVE   542B4C08         26         16 15901                 0    1
                                   ACTIVE   542B4FB8          7         46 24083                 0    1
                                   ACTIVE   542B5368         12         15 24081                 0    1
                                   ACTIVE   542B5718         15         46 24083                 0    1
                                   ACTIVE   542B5AC8         79          4 15923                 0    1
                                   ACTIVE   542B5E78         50         16 24085                 0    1
                                   ACTIVE   542B6228        754         15 24081                 0    1
                                   ACTIVE   542B65D8          1         14 24069                 0    1
                                   ACTIVE   542B6988          2         30 14571                 0    1

    USERNAME                       STATUS   ADDR       KSLLAPSC   KSLLAPSN KSLLASPO       KSLLID1R KS D
    ------------------------------ -------- -------- ---------- ---------- ------------ ---------- -- -
    SYS                            ACTIVE   542B6D38          2          8 24071                 0
                                            542B70E8          1         15 24081               195 EV
                                            542B7498          1         15 24081               195 EV
    SYS                            INACTIVE 542B7848          0          0                       0
    SYS                            INACTIVE 542B7BF8          1         15 24081               195 EV

    16 rows selected.
        
     我们注意,红字标出的部分就是被Kill掉的进程的进程地址.


    简化一点,其实就是如下概念:

    SQL> select p.addr from v$process p where pid <> 1  2  minus  3  select s.paddr from v$session s;ADDR
    --------
    542B70E8
    542B7498

     
     Ok,现在我们获得了进程地址,就可以在v$process中找到spid,然后可以使用Kill或者orakill在系统级来杀掉这些进程.

    实际上,我猜测:

    当在Oracle中kill session以后, Oracle只是简单的把相关session的paddr 指向同一个虚拟地址.

    此时v$process和v$session失去关联,进程就此中断.

    然后Oracle就等待PMON去清除这些Session.所以通常等待一个被标记为Killed的Session退出需要花费很长的时间.

    如果此时被Kill的process,重新尝试执行任务,那么马上会收到进程中断的提示,process退出,此时Oracle会立即启动PMON
    来清除该session.这被作为一次异常中断处理

    五、oracle profile详解

    数据库创建后,系统则存在名为DEFAULT的默认PROFILE,若不做特殊指定,创建用户时用户默认使用的PROFILE就是DEFAULT。

    使用profile

    一:创建profile

    create profile文件名limit failed_login_attempts 3 password_lock_time 2 password_life_time 10 password_grace _time 2;  
     alter user用户名 profile 文件名;

    注意:

    用户所有拥有的PROFILE中有关资源的限制与resource_limit参数的设置有关,当为TRUE时生效,当为FALSE时(默认值)设置任何值都无效。

    resource_limit默认为false.创建profile需要相应的权限,show parameter resource_limit同样需要权限.

    SQL> show parameter resource_limit
    NAME                 TYPE        VALUE
    -------------------- ----------- -------
    resource_limit       boolean     FALSE

    SQL> alter system set resource_limit=true;


    二:修改profile

      修改profile:alter profile [资源文件名] limit [资源名] unlimited;
          如:alter profile default limit failed_login_attempts 100;

    三:删除profile

        删除PROFILE:drop profile [资源文件名] [CASCADE] ;
        若创建的PROFILE已经授权给了某个用户,使用CASCADE级联收回相应的限制,收回限制信息后将以系统默认的PROFILE对该用户进行限制。

        已分配的profile,删除时必须加cascade选项。

        如果不删除profile,只是取消单个用户的profile:

    SQL>alter user dinya profile default;

    查询profile

        一:查看视图dba_profiles可找出数据库中有哪些PROFILE。

    SQL> select distinct profile from dba_profiles;
    PROFILE
    --------------------
    MONITORING_PROFILE
    DEFAULT

        二:查看所有的PROFILE

    SQL> select * from dba_profiles order by PROFILE;
    PROFILE              RESOURCE_NAME                  RESOURCE LIMIT
    -------------------- ------------------------------ -------- ----------
    DEFAULT              COMPOSITE_LIMIT                KERNEL   UNLIMITED
    DEFAULT              PASSWORD_LOCK_TIME             PASSWORD 1
    DEFAULT              PASSWORD_VERIFY_FUNCTION       PASSWORD NULL
    DEFAULT              PASSWORD_REUSE_MAX             PASSWORD UNLIMITED
    DEFAULT              PASSWORD_REUSE_TIME            PASSWORD UNLIMITED
    DEFAULT              PASSWORD_LIFE_TIME             PASSWORD 180
    DEFAULT              FAILED_LOGIN_ATTEMPTS          PASSWORD UNLIMITED
    DEFAULT              PRIVATE_SGA                    KERNEL   UNLIMITED
    DEFAULT              CONNECT_TIME                   KERNEL   UNLIMITED
    DEFAULT              IDLE_TIME                      KERNEL   UNLIMITED
    DEFAULT              LOGICAL_READS_PER_CALL         KERNEL   UNLIMITED
    DEFAULT              LOGICAL_READS_PER_SESSION      KERNEL   UNLIMITED
    DEFAULT              CPU_PER_CALL                   KERNEL   UNLIMITED
    DEFAULT              CPU_PER_SESSION                KERNEL   UNLIMITED
    DEFAULT              SESSIONS_PER_USER              KERNEL   UNLIMITED
    DEFAULT              PASSWORD_GRACE_TIME            PASSWORD 7

        三.参数解释

    1、对数据库资源做限制
    { { SESSIONS_PER_USER 每个用户名所允许的并行会话数
      | CPU_PER_SESSION   一个会话一共可以使用的CPU时间,单位是百分之一秒
      | CPU_PER_CALL      一次SQL调用(解析、执行和获取)允许使用的CPU时间
      | CONNECT_TIME      限制会话连接时间,单位是分钟
      | IDLE_TIME         允许空闲会话的时间,单位是分钟
      | LOGICAL_READS_PER_SESSION 限制会话对数据块的读取,单位是块
      | LOGICAL_READS_PER_CALL    限制SQL调用对数据块的读取,单位是块
      | COMPOSITE_LIMIT   “组合打法”
      }   { integer | UNLIMITED | DEFAULT }
      | PRIVATE_SGA   限制会话在SGA中Shared Pool中私有空间的分配  { size_clause | UNLIMITED | DEFAULT}
    }
    2、对密码做限制
    { { FAILED_LOGIN_ATTEMPTS 帐户被锁定之前可以错误尝试的次数
      | PASSWORD_LIFE_TIME    密码可以被使用的天数,单位是天,默认值180天
      | PASSWORD_REUSE_TIME   密码可重用的间隔时间(结合PASSWORD_REUSE_MAX)
      | PASSWORD_REUSE_MAX    密码的最大改变次数(结合PASSWORD_REUSE_TIME)
      | PASSWORD_LOCK_TIME    超过错误尝试次数后,用户被锁定的天数,默认1天
      | PASSWORD_GRACE_TIME   当密码过期之后还有多少天可以使用原密码
      }  { expr | UNLIMITED | DEFAULT }
      | PASSWORD_VERIFY_FUNCTION  { function | NULL | DEFAULT }
    }

    1.如果用户超过了connect_time或idle_time的会话资源限制,数据库就回滚当前事务,并结束会话。用户再次执行命令,数据库则返回一个错误,
    2.如果用户试图执行超过其他的会话资源限制的操作,数据库放弃操作,回滚当前事务并立即返回错误。用户之后可以提交或回滚当前事务,必须结束会话。
    提示:可以将一条分成多个段,如1小时(1/24天)来限制时间,可以为用户指定资源限制,但是数据库只有在参数生效后才会执行限制。

    六、常用命令

    sqlplus /nolog
    conn sys/oracle@orcl as sysdba

    su - oracle -c "sqlplus / as sysdba"

    show parameter processes;

    alter system set processes = 1000  scope=spfile; 


    select username,PROFILE FROM dba_users; 
    create profile DEFAULT limit connect_time 60 idle_time 30 

    alter profile DEFAULT limit idle_time 30;
    alter profile DEFAULT limit connect_time 60;
    select count(*) from v$process;
    select count(*) from v$session;

    select *from dba_profiles


    select * from dba_profiles s where s.profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';

    alter profile DEFAULT limit PASSWORD_LIFE_TIME unlimited; 

    shutdown immediate; 
    startup


    展开全文
  • Oracle 概要文件 Profile

    千次阅读 2017-12-26 10:12:29
    概要文件是口令限制和资源限制的命名集合,是 Oracle安全策略的重要组成部分,利用概要文件可以对数据库用户进行口令管理和资源限制。例如使用概要文可以指定口令有效期、口令校验函数、用户连接时间以及最大空闲...

    概要文件的作用
    概要文件是口令限制和资源限制的命名集合,是 Oracle安全策略的重要组成部分,利用概要文件可以对数据库用户进行口令管理和资源限制。例如使用概要文可以指定口令有效期、口令校验函数、用户连接时间以及最大空闲时间等。概要文件具有以下一些作用:
    • 限制用户执行消耗资源过度的SQL操作。
    • 自动断开空闲会话。
    • 在大而复杂的多用户数据库系统中合理分配资源。
    • 控制用户口令的使用。

    在建立数据库时, Oracle 会自动建立名称为 DEFAULT的默认概要文件,初始的DEFAULT概要文件的所有口令及资源限制选项值均为UNLIMITED,即未进行任何口令及资源限制。 当建立用户时, 如果不指定概要文件, 则Oracle会将DEFAULT概要文件分配给该用户。根据用户所承担任务的不同, DBA应该建立不同的概要文件,并将概要文件分配给相应用户。一个用户只能分配一个概要文件, 一个概要文件可以同时包含口令限制和资源限制。

    查看default概要文件的信息

    SQL> select PROFILE,RESOURCE_NAME,RESOURCE_TYPE,LIMIT from sys.dba_profiles where PROFILE='DEFAULT';

    PROFILE          RESOURCE_NAME      RESOURCE_TYPE        LIMIT
    -------------------- -------------------- -------------------- --------------------
    DEFAULT          COMPOSITE_LIMIT      KERNEL           UNLIMITED
    DEFAULT          SESSIONS_PER_USER      KERNEL           UNLIMITED
    DEFAULT          CPU_PER_SESSION      KERNEL           UNLIMITED
    DEFAULT          CPU_PER_CALL      KERNEL           UNLIMITED
    DEFAULT          LOGICAL_READS_PER_SE KERNEL           UNLIMITED
                 SSION

    DEFAULT          LOGICAL_READS_PER_CA KERNEL           UNLIMITED
                 LL

    DEFAULT          IDLE_TIME          KERNEL           UNLIMITED

    PROFILE          RESOURCE_NAME      RESOURCE_TYPE        LIMIT
    -------------------- -------------------- -------------------- --------------------
    DEFAULT          CONNECT_TIME      KERNEL           UNLIMITED
    DEFAULT          PRIVATE_SGA      KERNEL           UNLIMITED
    DEFAULT          FAILED_LOGIN_ATTEMPT PASSWORD           10
                 S

    DEFAULT          PASSWORD_LIFE_TIME   PASSWORD           180
    DEFAULT          PASSWORD_REUSE_TIME  PASSWORD           UNLIMITED
    DEFAULT          PASSWORD_REUSE_MAX   PASSWORD           UNLIMITED
    DEFAULT          PASSWORD_VERIFY_FUNC PASSWORD           NULL
                 TION


    PROFILE          RESOURCE_NAME      RESOURCE_TYPE        LIMIT
    -------------------- -------------------- -------------------- --------------------
    DEFAULT          PASSWORD_LOCK_TIME   PASSWORD           2
    DEFAULT          PASSWORD_GRACE_TIME  PASSWORD           7

    16 rows selected.

     


    修改profile文件里面的内容
    SQL> alter profile default limit IDLE_TIME 10;

    Profile altered.

     

    概要文件内容
    概要文件内容包括口令和资源的限制,下面分别介绍这两部分的有关参数。

     口令策略参数
    口令策略参数可以实现帐户锁定、口令的过期以及口令的复杂度等策略, 在概要文件中
    的口令参数可以有以下几个:
    • FAILED_LOGIN_ATTEMPTS: 该参数指定允许的输入错误口令的次数, 超过该次
    数后用户帐户被自动锁定。
    • PASSWORD_LOCK_TIME:用于指定指定账户被锁定的天数(单位:天)。
    • PASSWORD_LIFE_TIME: 指定口令的有效期(单位: 天)。 如果在达到有效期前用
    户还没有更换口令,它的口令将失效,这时必须由DBA为它重新设置新的口令。
    • PASSWORD_GRACE_TIME:用于指定口令失效的宽限期(单位:天)。

    • PASSWORD_REUSE_TIME: 指定能够重复使用一个口令前必须经过的时间(单位:
    天)。
    • PASSWORD_REUSE_MAX: 用于指定在重复使用口令之前必须对口令进行修改的
    次数。 PASSWORD_REUSE_TIME 和PASSWORD_REUSE_MAX两个参数只能设
    置一个,另一个必须为UNLIMITED。
    • PASSWORD_VERIFY_FUNCTION: 指定验证口令复杂度的函数。 Oracle提供了一
    个默认的口令校验函数, 这可以通过运行脚本utlpwdmg.sql 来建立该函数, 脚本保
    存在ORACLE_HOME\rdbms\admin目录中。DBA 也可以通过修改脚本来实现自己
    的口令校验函数。 

     

    资源限制参数
    利用概要文件,可以对以下系统资源进行限制。
    • CPU时间
    • 逻辑读
    • 用户的并发会话数
    • 空闲时间
    • 连接时间
    • 私有SGA 区
    对这些资源的限制是通过在概要文件中设置参数来实现的,参数的值可以是一个整数,
    也可以是UNLIMITED( 即不受限制), 还可以是DEFAULT( 使用DEFAULT概要文件中的
    参数设置)。下面分别介绍有关参数:
    • CPU_PER_SESSION:限制每个会话所能使用的CPU时间。参数值是一个整数,
    单位是百分之一秒。
    • SESSIONS_PER_USER:限制每个用户所允许建立的最大并发会话数。
    • CONNECT_TIME: 限制每个会话能连接到数据库的最长时间, 超过这个时间会话将自动断开。参数值是一个整数,单位是分钟。
    • IDLE_TIME:限制每个会话所允许的最长连续空闲时间,超过这个时间会话将自动断开。参数值是一个整数,单位是分钟。
    • LOGICAL_READS_PER_SESSION:限制每个会话所能读取的数据块数目。
    • PRIVATE_SGA:每个会话分配的私有SGA 区大小(以字节为单位)。该参数只对
    共享服务器模式有效。 

    • CPU_PER_CALL:用于指定每条SQL 语句可占用的最大CPU时间,单位是百分
    之一秒。
    • LOGICAL_READS_PER_CALL:用于指定每条SQL 语句最多所能读取的数据块数
    目。

    使用概要文件管理口令
    Oracle数据库中不同用户账户具有不同的口令,如何防止其他人窃取账户密码是 DBA要重点关切的问题。 为了加强用户账户的安全性, Oracle提供了概要文件来管理口令, 概要文件管理口令有锁定账户、 终止口令、 口令历史以及口令校验等四种安全保护方式。 大家需要注意,前面介绍的概要文件中共包含了7个口令管理选项, 如果仅指定某个或某几个选项,那么其他选项将自动使用DEFAULT概要文件的相应选项值。使用概要文件管理口令主要步骤是先创建概要文件, 然后设置相关口令参数, 最后把概要文件分配给用户。  建立概要文件是使用CREATE PROFILE 命令来完成的, 一般情况下建立概要文件是由DBA来完成的, 如果要以其他用户身份建立概要文件, 则要求该用户必须具有CREATEPROFILE 系统权限。
    锁定帐户
    如果用户在指定的次数内尝试登录系统失败,系统会在设置的持续时间内锁定帐户。
    • FAILED_LOGIN_ATTEMPTS 参数指定在锁定帐户前尝试登录失败的次数。
    • PASSWORD_LOCK_TIME 参数指定在经历指定的尝试登录失败次数后锁定帐户的天数。
    如果建立PROFILE 时没有提供PASSWORD_LOCK_TIME选项,将自动使用默认值(UNLIMITED),在这种情况下,需要DBA 手工解锁通过使用概要文件,可以限制登录失败次数,如果连续登录的失败次数超出一定范围,那么Oracle会自动锁定账户。
    【实例16-1】为了加强devp用户的口令安全,要限制其登录失败次数为3。
    1)以管理员身份登录
    SQL>CONNECT/ ASSYSDBA
    已连接。
    2)创建概要文件,设定相关参数
    SQL>CREATE PROFILE devp_lock LIMIT
    FAILED_LOGIN_ATTEMPTS 3;
    配置文件已创建
    3) 分配概要文件给用户devp,这既可以在建立用户时使用PROFILE 子句,也可以在
    建立用户之后使用ALTERUSER 语句修改

    SQL>ALTER USER devp PROFILE devp_lock;
    用户已更改。 

    当将概要文件分配给devp用户之后, 如果以devp连接到数据库时, 连续登录失败
    次数达到三次,那么系统会自动锁定该用户账户。此时,即使为用户 devp提供了正确的口
    令,也将无法连接到数据库,并显示错误信息。例如:
    SQL>connect devp
    请输入口令:
    ERROR:
    ORA-28000:the account is locked 

    终止口令
    使用户口令具有生存期,口令在此生存期之后会失效,而且可能已更改。
    • PASSWORD_LIFE_TIME 参数指定口令的生存期 ( 以天为单位),此时间之后,
    口令会失效。
    • PASSWORD_GRACE_TIME 参数指定口令失效后首次成功登录时用于更改
    口令的宽限期(以天为单位)。
    注: 通过使口令失效和锁定SYS、 SYSMAN 和DBSNMP 帐户, 可避免出现Enterprise
    Manager 不正常运行的情况。应用程序必须捕获“口令到期”警告消息并处理口令更
    改;否则,宽限期一到期,会在原因不明的情况下锁定用户。
    【实例16-2】强制用户devp每隔10天修改其自身口令,并设置两天宽限期。
    1)以管理员身份登录
    SQL>CONNECT/ AS SYSDBA
    已连接。
    2)创建概要文件,设定相关参数
    SQL>CREATE PROFILE devp_life LIMIT
    PASSWORD_LIFE_TIME 10
    PASSWORD_GRACE_TIME 2;
    配置文件已创建
    3) 分配概要文件给用户devp
    SQL>ALTER USER devp PROFILE devp_life;
    用户已更改。
    当将概要文件分配给用户devp之后,第10天登录会显示警告信息:
    SQL>connect devp/development
    ERROR:
    ORA_28002: the password will expire within 2 days
    已连接。
    如果第10天未改变口令, 那么第11天登录时仍然会显示类似警告信息。但用户如果第11天仍然未改变口令,那么第12天登录时系统会强制你改变口令,并显示如下信息:
    SQL>connect devp/development 

    RROR:
    ORA-28001:thepasswordhasexpired
    更改devp的口令
    新口令:
    重新键入新口令:
    口令已更改
    已连接 

     

    【实例16-3】密码180天过期设置

    查询当前几天过期

    select * from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';

    如果是180,不是UNLIMITED,就修改:

    Alter PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

     

    使用概要文件管理资源 

    概要文件不仅可用于管理用户口令, 也可用于限制用户的资源占用。概要文件既可以限制整个会话的资源占用, 也可以限制调用级(SQL语句)的资源占用。 但大家需要注意, 如果要使用概要文件管理资源,则必须要激活资源限制功能,这可以通过设置初始化参 数RESOURCE_LIMIT为TRUE做到:ALTER SYSTEMSET RESOURCE_LIMIT=TRUE;
    使用概要文件管理资源的步骤同管理口令的步骤一样, 也是先创建概要文件, 然后设置相关资源参数,最后把概要文件分配给用户。

     CREATEPROFILE"TEMPPROFILE"
    /*【一般信息】选项卡对应的配置参数*/
    LIMITCPU_PER_SESSION1000 //CPU会话
    CPU_PER_CALL1000 //CPU调用
    CONNECT_TIME30 //连接时间
    IDLE_TIMEDEFAULT //空闲时间
    SESSIONS_PER_USER10 //并行会话数
    LOGICAL_READS_PER_SESSION1000//读取数/会话
    LOGICAL_READS_PER_CALL1000//读取数/调用
    PRIVATE_SGA16K //专用SGA
    COMPOSITE_LIMIT1000000 //组合限制
    /*【口令】选项卡对应的配置参数*/
    FAILED_LOGIN_ATTEMPTS3 //允许输入的错误口令的次数
    PASSWord_LOCK_TIME5 //输入错误口令而被锁定的天数
    PASSWORD_GRACE_TIME60 //口令过期时间天数
    PASSWORD_LIFE_TIME30 //
    PASSWORD_REUSE_MAX
    DEFAULTPASSWORD_REUSE_TIME3 0
    PASSWORD_VERIFY_FUNCTIONDEFAULTV//验证用户口令复杂度的函数 

    限制会话资源
    当用户连接到数据库时, Oracle 将创建一个会话( session)。 每个会话都将消耗 Oracle
    所在计算机的 CPU 时间及内存。用户可以在会话级( session level)设置多种资源限制( resourcelimit)。如果用户超过了某一会话级资源限制, Oracle 将终止( 回滚) 当前执行的语句, 并通知用户此会话超出资源限制。此时, 当前会话内已经执行的语句不受资源限制的影响, 而用户则只能执行 COMMIT, ROLLBACK, 或断开连接( disconnect)( 如断开连接, 当前事务将被提交)。用户的其他所有操作都将导致报错。在事务被提交或回滚后,用户在当前会话内也不能执行其他任何操作。
    【实例16-5】数据库最多允许两个开发人员以devp用户登录进行应用开发,每个会话
    连接时间不能超过30min,会话空闲时间不能超过10min。
    1)以管理员身份登录
    SQL>CONNECT/ ASSYSDBA
    已连接。
    2)创建概要文件,设定参数
    SQL>CREATE PROFILE devp_session LIMIT
    CPU_PER_SESSION 5000
    SESSIONS_PER_USER 2
    CONNECT_TIME 30
    IDLE_TIME 10;
    配置文件已创建
    3) 分配概要文件给用户devp 

    SQL>ALTER USER devp PROFILE devp_session;
    用户已更改。
    因为在建立概要文件时指定SESSION_PER_USER选项为2, 即允许用户最多可以同时建立两个会话,所以如果用户devp的并发会话个数达到两个,那么当建立第三个会话时则会显示如下错误信息:
    SQL>CONNECT devp/development
    ERROR:
    ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit
    因为 IDLE_TIME选项被设置为 10,所以如果会话空闲时间超过 10min,那么 Oracle会自动断开会话。
    管理员可以限制用户并发会话数( concurrent sessions for each user)。 每个用户的并发会话数不能超过预设值。管理员可以限制一个会话的空闲时间( idle time)。 如果会话内两次 Oracle 调用间的间隔时间达到了限制值,当前事务将被回滚,会话将被终止( aborted),会话所占用的资源将被系统回收。 下次会话将得到报错信息, 提示用户已经终止了与实例的连接。 此限制的设置单位为分钟。
    当会话由于超出空闲时间限制而被终止之后,进程监视器( process monitor, PMON)
    后台进程将对被终止的会话进行清理。 在 PMON 完成此过程前, 统计用户及会话资源使用时仍将包含被终止的会话。
    管理员可以限制每个会话的连接持续时间。如果会话持续连接时间超出限制值, 当前事务将被回滚, 会话将被移除( dropped), 会话所占用的资源将被系统回收。 此限制的设置单位为分钟。
    Oracle 不会持续不断地监控会话的空闲时间及连接时间, 因为这样做将降低系统性能。Oracle 的做法是每隔数分钟检查一次。因此,在 Oracle 依据资源限制终止会话前,会话有可能已经略微超出了限制时间(例如,超出 5 分钟)。 

    限制调用资源
    SQL 语句每次运行时, Oracle 都将执行一系列操作来处理此语句。在处理过程中,不同的执行阶段需要向数据库发起不同的调用。为了防止某个调用过度地使用系统资源,Oracle 允许管理员在调用级( calllevel)设定多种资源限制( resourcelimit)。
    如果用户超过了调用级资源限制, Oracle 将停止执行语句,并进行回滚,之后向用户报错。此时,当前会话内已经执行的语句不受资源限制的影响,用户会话也将保持连接状态。
    当 Oracle 执行 SQL 语句或用户的其他各类调用( call)时,系统需要一定的 CPU 时间来处理此调用。 普通的调用所需的 CPU 时间较少。 但是需要处理大量数据的语句, 或失控的查询 ( runawayquery)可能占用大量的 CPU 时间, 这减少了其他处理任务可用的 CPU时间。
    为了防止 CPU 时间被不受控地占用,管理员可以限制会话内每个调用使用的 CPU 时间, 以及会话中所有 Oracle 调用的 CPU 时间之和。设置及衡量调用或会话可使用的 CPU 

    时间的单位为百分之一秒。
    【实例16-6】限制用户devp的调用级资源。
    1)以管理员身份登录
    SQL>CONNECT/ AS SYSDBA
    已连接。
    2)创建概要文件,设定参数
    SQL>CREATE PROFILE devp_call LIMIT
    CPU_PER_CALL200
    LOGICAL_READS_PER_CALL 3;
    配置文件已创建
    3) 分配概要文件给用户devp
    SQL>ALTER USER devp PROFILE devp_call;
    用户已更改。
    因为在建立概要文件devp_call时指定了LOGICAL_READS_PER_CALL选项为3,所
    以如果执行单条SQL语句访问的数据块总数超过3,则会显示如下错误信息:
    SQL>CONNECT devp/development
    已连接。
    SQL>select * from scott.emp;
    select * from scott.emp
    *
    ERROR 位于第 1 行:
    ORA-02395: 超出 IO 使用的调用限制
    1.1.7.3.3其他资源
    1) 逻辑读取
    输入/输出 ( Input/output, I/O)是数据库系统中开销最大的操作。I/O 操作密集的 SQL 语句可能会占用大量内存及硬盘,并与其他需要同样资源的数据库操作产生竞争。
    为了防止 I/O 被独占, Oracle 可以限制调用及会话的逻辑数据块读取 ( logicaldatablock
    read)。逻辑数据块读取包含从内存及磁盘读取数据。设置及衡量调用或会话可使用的逻辑
    读取的单位为数据块数量。
    LOGICAL_READS_PER_SESSON:用于指定会话的最大逻辑读取次数.
    LOGICAL_READS_PER_CALL:用于限制每次调用的最大逻辑I/O次数.
    2) CPU
    可以每个会话或每个调用为基础限制CPU 资源。
    “CPU/会话限制为1,000表示:
    当使用此概要文件的任一会话占用10 秒以上的CPU 时间 ( CPU 时间限制以百分之一秒为单位)时,该会话就会收到错误并被注销:
    ORA-02392:exceeded session limiton CPU usage,youare 

    beingloggedoff
    每个调用限制的作用是相同的, 但是它不是限制用户的整个会话, 而是防止任一命令占
    用过多的CPU。如果“CPU/调用”受到限制,而且用户超出了限制,命令就会中止,用户还
    会收到错误消息,例如:
    ORA-02393:exceededcalllimitonCPUusage
    CPU_PER_SESSION:用于指定每个会话可以占用的最大CPU时间.
    CPU_PER_CALL:限制每次调用(解析,执行或提取数据)可占用的最大CPU时间(单位:
    分之一秒)
    3) 网络/内存:每个数据库会话都会占用系统内存资源和网络资源(如会话来自服务
    器的非本地用户)。可以指定以下参数:
    - 连接时间:指示用户在自动注销前可以保持连接的分钟数
    - 闲置时间:指示用户会话在自动注销前可以保持闲置的分钟数。只会计算服务
    器进程的闲置时间。闲置时间中不考虑应用程序活动。 IDLE_TIME 限制不受
    长时间运行查询和其它操作的影响。
    - 并行会话:指示使用数据库用户帐户可以创建多少并行会话
    - 专用SGA:限制系统全局区域(SGA) 中执行排序、合并位图等操作占用的空间。
    此限制仅在会话使用共享服务器时才有效。(在“配置OracleNetwork 环境”
    一课中会讨论共享服务器)。
    PRIVATE_SGA:用于指定会话在共享池中可以分配的最大总计私有空间.需要注意,该选
    项只使用与共享服务器模式.
    COMPOSITE_LIMIT:用于指定会话的总计资源消耗(单位:服务单元).oracle会根据
    CPU_PER_SESSION,CONNECT_TIME,LOGICAL_READS_PER_SESSION以及
    PRIVATE_SGA的求权结果取得总计服务单元.
    SESSIONS_PER_USER:用于指定每个用户的最大并发会话个数.
    CONNECT_TIME:用于指定会话的最大连接时间.
    IDLE_TIME:用于指定会话的最大空闲时间.
    通过概要文件还可提供组合限制。组合限制以“CPU/会话”、 “读取/会话”、 “连接时
    间”和“专用SGA”的加权组合为基础。
    下面以限制帐户DEVEP会话占用CPU时间不超过50,逻辑读取次数不超过100次为
    ,说明使用 PROFILE 限制会话资源的方法.为了控制会话资源,首先应执行 CREATE
    PROFILE命令建立PROFILE,然后使用ALTERUSER 命令将PROFILE分配给用户DEVEP.
    CREATEPROFILEsession_limitLIMIT
    CPU_PER_SESSION5000LOGICAL_READS_PER_SESSION100; 
    ALTER USER devep PROFILE session_limit;

    修改概要文件
    当口令和资源限制无法满足用户的实际需求时,你就需要修改概要文件中的口令及资源
    限制参数。修改概要文件是使用ALTERPROFILE命令由DBA来完成的;如果要以其它用
    户身份修改概要文件, 则要求该用户必须具有ALTERPROFILE系统权限。 需要注意, 修改
    概要文件对已存在会话没有影响,而只对新会话起作用。
    【实例16-7】修改用户devp的口令和资源限制。
    1)以管理员身份登录
    SQL>CONNECT/ AS SYSDBA
    已连接。
    2)修改概要文件
    SQL>ALTER PROFILE devp_call LIMIT
    SESSIONS_PER_USER 2
    FAILED_LOGIN_ATTEMPTS 2
    CPU_PER_CALL 200;
    配置文件已更改修改用户devp当前的概要文件后, 当devp重新登录后, 其口令和资源限制就发生变化了,如该用户的口令失败次数只能两次。删除概要文件当某个概要文件不需要时,可以删除它。删除概要文件是使用DROPPROFILE命令由DBA来完成的,如果要以其他用户身份删除概要文件,则要求该用户必须具有 DROPROFILE系统权限。
    【实例16-8】删除概要文件devp_call
    1)以管理员身份登录
    SQL>CONNECT / AS SYSDBA
    已连接。
    2)删除概要文件
    SQL>DROPP ROFILE devp_call CASCADE;
    配置文件已丢弃
    需要注意,如果概要文件已经被分配给某个用户,那么当删除概要文件时必须带有 CASCADE选项。
    如果用户的概要文件已经被删除, Oracle将自动为用户重新指定DEFAULT概要文件。
    显示概要文件信息
    Oracle提供了几个数据字典视图查询有关概要文件的信息。下面分别介绍。
    1) 显示用户的概要文件
    当建立或修改用户时, 通过PROFILE子句可以为用户分配概要文件。 如果没有显式地
    分配概要文件, 那么Oracle会将DEFAULT概要文件自动分配给用户。那么如何确定用户的
    概要文件呢?通过查询数据字典视图DBA_USERS,可以取得用户的概要文件信息。
    【实例16-9】查询用户devp的概要文件
    1)以管理员身份登录
    SQL>CONNECT / AS SYSDBA
    已连接。
    2)查询
    SQL>SELECT profile FROM DBA_USERS WHERE username=’DEVP’;
    PROFILE
    ------------------------
    DEFAULT
    2) 显示概要文件的内容
    数据字典视图DBA_PROFILES中包含了每个概要文件的内容, 通过查询该数据字典视
    图可以取得用户的口令限制及资源限制信息。
    【实例16-10】查询概要文件devp_session的口令和资源限制。
    1)以管理员身份登录
    SQL>CONNECT/ ASSYSDBA
    已连接。
    2)查询
    SQL>SELECT *  FROM DBA_PROFILES WHERE profile=’DEVP_SESSION’;
    PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
    -------------------------------------------------------------------------------------------------
    DEVP_SESSION CONNECT_TIME KERNAL 30
    DEVP_SESSION IDLE_TIME KERNAL 10
    DEVP_SESSION SESSIONS_PER_USER KERNAL 3
    DEVP_SESSION CPU_PER_SESSION KERNAL 5000
    DEVP_SESSION PASSWORD_LIFE_TIME PASSWORD DEFAULT 

    其中:
    • PROFILE:概要文件名称。
    • RESOURCE_NAME:口令或资源选项名。
    • RESOURCE_TYPE: PASSWORD表示口令管理选项,而KERNAL则表示资源限
    制选项。
    • LIMIT:概要文件选项值 

    展开全文
  • Oracle DMP文件异常解决

    千次阅读 2016-04-15 17:18:41
    DMP文件异常解决

    “想当然”是一种思维定式,也是一种思维惯性。当我们经常处理一类问题,自认为信心满满的时候,就会形成思维定式和惯性。思维定式和惯性可以帮助我们快速定位问题,解决问题,但是也会形成“一叶遮目”。

    相同的技术,不同的场景出现问题是千差万别。特别是在第三方不确定人员的参与下,这种问题出现就更加复杂多变。我们能做到的仅是不断积累经验,提高自身阅历经验,永远本着一颗“本心”去面对我们自己的技术生涯。

    DMP文件是目前Oracle数据逻辑备份、传输和还原的最常见的方法。无论是开发人员倾向的Exp/Imp工具,还是DBA越来越接受的Data Pump,都是以.dmp作为文件载体。处理导入导出dmp文件过程中的出现各种问题,已经被大家挖掘差不多了。所以,一般水平的DBA,都可以聊几条常见错误。

    笔者一个同事在处理从第三方公司发送Dmp文件时候,处理时遇到问题。联系笔者帮忙解决,在纠结一段时间之后,问题被意想不到的定位和解决。记录下问题,权作为需要朋友不时之需。

    1、问题说明

    同事在做一个项目,从下属各公司收集数据Dmp文件进行后续处理整合。由于前期沟通有一些问题,所以并没有规定上交文件是用Exp/Imp提交还是DataPump,更不要说版本之类的信息了。所以,同事能做的只有逐步实验进行测试导入。每个文件都意味着一次实验。

    在处理过程中,一个公司提交的两个dmp文件问题显现出来。无论是Imp还是Impdp,都不能顺利处理。笔者索要了问题文件,进行初步检查和处理。

    文件自身并不是很大,只有几K,这对于Oracle DMP文件来讲是比较奇怪的。因为无论如何,Oracle在保存数据表元数据的时候,都会保存一定的信息文件。那么小的dmp文件是比较少见的。事后想,这就是重要的问题线索。

    转移到Windows平台上,调用imp和impdp命令处理文件。

    C:/Users/Administrator>impdp /"/ as sysdba/" dumpfile=i_aaa_statistical.dmp

    Import: Release 11.2.0.4.0 - Production on Thu Aug 13 17:03:24 2015

    Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit

    Production

    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    ORA-39001: invalid argument value

    ORA-39000: bad dump file specification

    ORA-31619: invalid dump file "C:/app/Administrator/admin/sicsdb/dpdump/i_aaa_statistical.dmp"

    C:/app/Administrator/admin/sicsdb/dpdump>imp /"/ as sysdba/" file=i_aaa_statistical.dmp

    Import: Release 11.2.0.4.0 - Production on Thu Aug 13 17:07:46 2015

    Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit

    Production

    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    IMP-00037: Character set marker unknown

    IMP-00000: Import terminated unsuccessfully

    调用Impdp命令,报错信息是文件格式不正确。在Oracle官方经验集合中,一般是传输过程(特别是FTP传输)中,text/binary格式数据设置错误导致文件损坏。这种错误在之前笔者也遇到过,除了找原系统管理员重新传输外没有其他办法。

    调用Imp命令,报错信息也是同样迷惑。Oracle认为字符集标记错误,无法进行解析。在之前的Blog中,我们了解到dmp文件文件头中包括字符集信息。

    两个工具的错误信息,同样是迷惑。Impdp的信息表示文件已经损坏,没有修复的可能。而Imp起码还有一个入手点。笔者打算死马当活马医,起码看看文件头内容。

    [oracle@localhost dpdump]$ cat i_aaa_statistical.dmp | od -x |head -1|awk '{print $2 $3}'|cut -c 3-6

    ef70

    SQL> select nls_charset_name(to_number('ef70','xxxx')) from dual;

    NLS_CHARSET_NAME(TO_NUMBER('EF

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

    的确是文件头错误。那么,笔者想起来Linux下的file命令,可以查看到文件的一些基础信息。想起码看看损坏文件的类型是什么样子。

    [oracle@localhost dpdump]$ file i_aaa_statistical.dmp

    i_aaa_statistical.dmp: UTF-8 Unicode (with BOM) Java program text, with CRLF line terminators

    UTF-8格式文件。根据笔者之前的经验,作为一个dmp文件,起码是一个二进制文件吧。从file命令结果看,这显然是一个与Java程序相关的文本文件,还包括换行符号。

    为了确定出一个Dmp文件究竟是什么样子,笔者分别对Exp和Expdp生成的dmp文件进行解析。

    --Expdp文件

    [oracle@localhost dpdump]$ file INT_TEST_150317.dmp

    INT_TEST_150317.dmp: DBase 3 data file (1728057941 records)

    --Exp文件

    [oracle@localhost dpdump]$ file scott.dmp

    scott.dmp: DBase 3 data file (1380929624 records)

    从结果看,起码与Database相关的文件类型。但是问题dmp文件无论如何也不能算是一个二进制文件。

    于是乎,笔者壮着胆子,用文本编辑器打开了文件。结果一切真相大白。

    prompt PL/SQL Developer import file

    prompt Created on 2015年7月01日 by xxxxxx

    set feedback off

    set define off

    prompt Creating I_aaa_stat...

    create table I_aaa_stat

    (

    (篇幅原因,有省略…..)

    )

    tablespace My_TBS

    pctfree 10

    initrans 1

    maxtrans 255

    storage

    (

    initial 64K

    next 1M

    minextents 1

    maxextents unlimited

    )

    nologging;

    prompt Disabling triggers for I_aaa_stat...

    alter table I_aaa_stat disable all triggers;

    insert into I_aaa_stat (companyname, plancode, polyr, count)

    从文件中,可以看出下属公司是利用PL/SQL Developer的某项功能将数据导出,阴差阳错的将文本文件保存成dmp文件拓展名。

    3、结论

    后续的处理似乎不需要再过多的解释。这个案例按说虽然蹊跷,但是还算简单。留给笔者最大的感受是:我们如何看待经验?经验是我们日常工作、学习中积累的宝贵财富,也是我们在专业逐步深入的水位线。

    但是同时,经验也会形成定式。让我们惯性的去做、去说、去想。当我们的经验不能应对新的问题,求知求变是必不可少的心态和过程。

    展开全文
  • Oracle 数据同步技术

    2011-09-15 19:37:30
    基于Oracle数据库的数据同步技术大体上可分为两类:Oracle自己提供的数据同步技术和第三方厂商提供的数据同步技术。Oracle自己的同步技术有DataGuard,Streams,Advanced Replication和今年刚收购的一款叫做...
  • 我们经常会遇到数据库磁盘空间爆满的问题,或由于归档日志突增、或由于数据文件过多、大导致磁盘使用紧俏。这里主要说的场景是磁盘空间本身很大,但表空间对应的数据文件初始化的时候就直接顶满了磁盘空间,导致经常...
  • Oracle数据性能优化

    2010-01-30 17:11:00
    Oracle应用系统的优化四个方面 1. 应用程序SQL语句优化; 2. ORACLE数据库参数调整; 3. 操作系统参数调整; 4. 网络性能调整. oracle应用系统的性能指标1. 数据库吞吐量; 2. 数据库用户响应时间. ORACLE...
  • Oracle数据库自动备份方案 Oracle数据库备份与恢复的三种方法 (1) ...Oracle数据库有三种标准的备份方法,它们分别是...利用Export可将数据从数据库中提取出来,利用Import则可将提取出来的数据送回到Oracle
  • ORACLE数据库数据类型

    千次阅读 2019-11-28 17:51:42
    1.2 Oracle数据类型 1.2.1 预定义数据类型 Oracle的数据类型分为标量(Scalar)类型、复合(Composite)类型、引用(Reference)类型和LOB(Large Object)类型4种类型 。标量类型没有内部组件又分为四类:数字...
  • Oracle 连接数过多的常用查看语句

    千次阅读 2017-02-14 10:49:07
    1、打开sqlplus connect /as sysdba 2、 显示资源限定是否开启,value为true是开启,为false是关闭 show parameter resource_limit; 如果未开启,则使用此命令开启资源限定功能 ...创建profile文件,profileName任意起
  • Oracle数据字典概念

    千次阅读 2006-08-22 14:05:00
    oracle数据库系统是一个复杂的软件系统。如果不了解其内部的结构原理及关系,就不可能设计和编写出高质量的应用软件系统,也不可能管理好一个复杂的应用系统。为了给以后章节的打好基础,本章简要给出 ORACLE 8 /...
  • Oracle的超大数据类型

    千次阅读 2018-08-14 17:22:53
    Oracle的超大数据类型 一 Orlce中VARCHAR2  在使用数据库的时候字符数据一般使用VARCHAR2, VARCHAR2既分PL/SQL Data Types中的变量类型,也分Oracle Database中的字段类型,不同场景的最大长度不同。在Oracle ...
  • 用户提出一个需求,即ORACLE中的一个表存储了照片信息,字段类型为BLOB,要求能导出成文件形式. 本想写个C#程序来做,后来想起ORACLE有很多包,功能很好很强大,于是网上参考了些文章完成了. 主要是用了ORACLE的两个包:UTL...
  • 查询oracle数据库数据的一款简单工具,连接上本机的数据库,就可以书写sql语句进行查询数据,查询结果是表中的所有数据数据过多会查询失败。
  • Oracle重做日志文件(转)

    千次阅读 2008-09-05 11:05:00
    通常只用于恢复,不过也可以用于以下工作:q 系统崩溃后的实例恢复q 通过备份恢复数据文件之后恢复介质q 备用(standby)数据库处理q 输入到流中,这是一个重做日志挖掘过程,用于实现信息共享(这也是一种奇特的...
  • 本文介绍JBOSS EAP 6.1中Oracle数据源的配置方式。结合之前JBOSS EAP 6.1新功能,本文初识JBOSS模块申明式容器这一特性。 模块申明式容器:JBOSS EAP不再有lib的概念,一切都是module。无论是系统调用的lib,用户...
  • Oracle中的数据类型详解

    千次阅读 2019-11-12 09:38:34
    本文就Oracle数据库,列出了比较详尽的数据类型,仅供大家参考。 分类目录 一、字符类型 二、数值类型 三、日期类型 四、long类型与lob大型对象数据类型 五、rowid & urowid类型 一、字符类型 char定长...
  • Oracle DB 概要文件

    千次阅读 2013-10-04 13:54:21
    1、概要文件和用户   一次只能向用户分配一个概要文件。 概要文件: • 控制资源消耗量 • 管理帐户状态和口令失效   注:只有将RESOURCE_LIMIT设置为TRUE,概要文件才能强制执行资源限制。         ...
  • Tomcat环境下配置oracle数据源的方法

    千次阅读 2005-04-29 15:04:00
    Tomcat环境下配置oracle数据源的方法以JB2K5下自带的tomcat jakarta-tomcat-4.1.30为例 完整版本(PDF文件)请到http://hbird.vicp.net/t-1598.html处下载 完整版本(PDF文件)请到...Tomcat4(5)提供了一个与Java ...
  • Oracle数据性能优化的若干诀窍

    千次阅读 2007-05-28 16:22:00
    如今,Oracle数据库以其高可靠性、安全性、可兼容性,得到越来越多的企业的青睐。如何使Oracle数据库保持优良性能,这是许多数据库管理员关心的问题,笔者建议不妨针对以下几个方面加以考虑。 分区 根据实际经验,在...
  • oracle数据仓库部署注意事项(OLAP)

    千次阅读 2013-08-08 19:06:02
    最近数据库升级到11G之后,出现一些...1)如果可以单独放,redo和数据文件单独划组做条带化等。物理上分开。 2)redolog如果可以单独放,就不要设置得太大,最多500M一个,因为日志太大,可能会导致实例恢复的时间很
  • 在对oracle数据库进行操作的时候,我们往往会有这样的困惑:  1、新增数据时,按照mySql的写法为什么新增不了?  2、多个数据表,通过外键关联,如何通过一条sql语句把所有需要的表关联起来并且获取到我所需要的...
  • 接下来重点讲一下,使用flume同步oracle数据至clickhouse。 安装flume 1. 下载flume wget http://www.apache.org/dist/flume/1.5.2/apache-flume-1.5.2-bin.tar.gz 2. 解压安装 tar zxvf apache-flume-...
  • 在“基于oracle的增量数据采集”一文中提出了基于[color=blue]触发器》物化视图》存储过程》java source》外部程序[/color]数据采集方案。本文初步对其进行了实现,利用maven-assembly-plugin进行打包,输出结构如下...
  • 一旦数据库出现无法启动的情况,首先需要检查system表是否为未损坏状态,通常情况而言,如果system表未被损坏那么很容易进行数据恢复,如果system表已经被损坏,那么就需要数据恢复工作人员手动对表结构进行分析核对...
  • Oracle千万级以上数据量查询速度慢问题解决,生产环境查询优化

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 32,086
精华内容 12,834
关键字:

oracle数据文件过多