Oracle 常用命令

oracle 常用命令 
 
1.增加主键
   alter table TABLE_NAME add constraint KEY_NAME primary key (TABLE_COLUMN);
2.增加外键
   alter table TABLE_NAME add constraint FK_NAME  foreign key (TABLE_COLUMN) references KEY_TABLE_NAME;
3.使主键或外键失效、生效
   alter table TABLE_NAME  disable(enable) constraint KEY_NAME;
4、查看各种约束
  select constraint_name,table_name,constraint_type,status from user_constraints;
5、删除主键或外键
  alter table TABLE_NAME  drop constraint KEY_NAME;
6、在initorcl.ora中加入rollback_segments=(rb0,rb1,...)
  其中rb0,rb1为自己定义的回滚段，可使这些回滚段在启动时生效
7、svgmgr>show sga 显示全局区
8、copy命令
   格式:   copy from <db> to <db> <opt> <table>
           {(<cols>)}  using <sel>
   其中:<db>:database string ；e.g:scott/tiger@oracle
9、客户端注册表修改
   HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE
   NLS_LANG_BAK  ----语言字符集
   简体中文:SIMPLIFIED CHINESE_CHINA.ZHS16CGB231280
   美国英文:AMERICA.WE8ISO8859P1
10、在win95/win98中在注册表中:
 HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE
 填加一个字符串键值
    local="oracle"  （可根据情况而定,指数据库别名)
    即可以在sql*plus中不用输入连接串
    或在autoexec.bat 中添加"set local=alias_name"
11、在sqlplus快捷方式中，在属性窗口中，在目标中，在"d:\orawin95\bin\plus33w.exe"


后面加空格和"scott/tiger@oracle"即可快速进入


12、修改数据库的字符集
   在表props$中
   update props$ set value$='ZHS16CGB231280'
13、oracle 安全与审计
   user_sys_privs,user_tab_privs;
配置文件
   主要参数
   session_per_user  每个用户可同时进行几个会话
   cpu_per_session   每个用户可用多少个(cpu的)百分之一秒
   cpu_per_call      语法分析、执行、取数可用多少个百分之一秒
   connect_time      用户连接数据库的时间(分钟)
   idle_time         用户不调用数据库的时间(分钟)
   logical_reads_per_session  整个会话过程中用户访问oracle的块数
   logical_reads_per_call     一次调用用户访问oracle的块数
   private_SGA       一个用户进程所用SGA的内存数量
   composite_limit   复合限制数
   failed_login_attempts 连续多次注册失败引起一个帐户死锁
   password_life_time    一个口令在其终止前可用的天数
   password_reuse_time    一个口令在其n天才能重新使用
   password_reuse_max    一个口令在重新使用之前必须改变多少次
   password_lock_time    一个口令帐户被锁住的天数
14、管理初始化文件
   show parameters
   经常修改的项目有  v$parameter
   shared_pool_size  分配给共享的字节数
   rollback_segments 回滚段的个数
   sessions          会话个数
   processes         进程个数
15、管理控制文件
    控制文件保存文件有关数据库创建时间、数据库名以及数据库运行时使用的所有文件位置和名称。
    增加控制文件，在initorcl.ora中，找到control_file项,增加一项即可
    删除控制文件，在initorcl.ora中去掉，然后删除物理文件
    建立新的控制文件
    create controlfile  [reuse] [set] database  数据库名  
        logfile [group 整数] 文件名 [,[group 整数] 文件名],...
    对于现有的数据库，可以间接地通过
    alter database backup  controlfile  to trace命令生成控制文件，即可在\orant\rmb7\trace
    下有ora00289.trc文件，其内容为文本
16、管理回滚段:
    存放事务的恢复信息
    建立回滚段
      create public  rollback segment SEG_NAME  tabelspace TABLESPACE_NAME;
      alter   rollback segment SEG_NAME  online;
    删除回滚段
      首先改变为offline状态
    直接使用回滚段
    sql>set transaction  use  rollback  segment  SEG_NAME;
17、管理日志
    建立日志组
    sql>select * from v$logfile;
    sql>alter database add logfile group 3
        ('f:\orant\database\log1_g3.ora'
         'f:\orant\database\log2_g3.ora') size 100k;
    sql>select * from v$logfile;
    删除日志组
      alter database  drop logfile group 1;
      但是其物理文件并没有被删除掉
        系统至少需要2个日志组，如果只有2个，就不能删除
        不能删除正活动的日志组
    手工归档
      通过alter system 的archive log 子句来实现
          archive  log [thread 整数]
          [seq 整数][change 整数][current][group 整数]
          [logfile '文件名'][next][all][start][to '位置']
18 系统控制
   alter system ...   
   alter system enable restricted session;  只允许具有restricted系统特权的用户登录
   alter system flush  shared_pool  清除共享池
   alter system checkpoint   执行一 个检查点
   alter system set license_max_session=64,license_session_warning=54 会话限制为64，会话的警界为54
   alter system set license_max_session=0 会话数为无限制
   alter system set license_max_users=300 用户限制为300个
   alter system switch logfile  强制执行日志转换
19 会话控制
   alter session    
   alter session set sql_trace=true  当前会话具有sql跟踪功能
   alter session set NLS_language=French 出错信息设为法语
   alter session set NLS_date_format='YYYY MM DD HH24:MI:SS';缺省日期格式
   alter session set optimizier_goal=first_row改变优化方法为基于开销方法，具有快速响应速度
   update student@teach set sold=sold+1 where sno='98010';
   commit;
   alter session close database link teach; 关闭远程链路
20、封锁机制
   数据封锁:保护表数据，在多个用户并行存取数据时候，保证数据的完整性。
   DML操作又在两个级别获取数据封锁：指定记录封锁和表封锁
   表封锁可以有下列方式：行共享（RS），行排他（RX），共享封锁（S），共享行排他（SPX)和排他 封锁(X) 行共享表封锁(RS),允许其他事务并行查询、插入，修改和删除及再行封锁
   select ...from  表名  ...  for update of ...;
   lock table 表名  in row share mode;
   行排他表封锁(RX) 对该行有独占权利
   insert into 表名 ...;
   update 表名 ...;
   delete from 表名 ...;
   lock table 表名 in row exclusive mode;
   允许并行查询、插入、删除或封锁其他行，但禁止其他事务使用下列命令进行并发封锁：
     lock table 表名 in share mode;
     lock table 表名 in share exclusive mode;
     lock table 表名 in exclusive mode;
   共享表封锁(S)
     lock table 表名 in share mode;
     允许其他事务可在该表上做查询和再实现共享表操作，但不能修改该表，同时也不能做如下封锁：
       lock table 表名 in share row exclusive mode;
       lock table 表名 in  exclusive  mode;
       lock table 表名 in row exclusive mode;
   共享排他表封锁(SRX)
      lock table 表名 in share row exclusive mode;
   排他表封锁(SRX)
      lock table 表名 in  exclusive mode;   
21、通常oracle需要启动OracleServiceORCL,OracleStartORCL,OracleTNSListener 任务
    在NT上至少要启动两个服务
       oraclestartID和oracleserverID
22、每个数据库都有一个系统标识符(SID)，典型安装的数据库使用的系统标识符是ORCL
23、删除带约束的表
    Drop table 表名 cascade  constraints;
24、设置事务
   set transaction [read only][read write][use rollback segment 回滚段名]
25、建外键
    单字段时：create table 表名 (col1  char(8),
                                 cno   char(4)  REFERENCE course);
    多个字段时,在最后加上 Foreign Key (字段名) REFERENCE 表名(字段)
    连带删除选项 (on delete cascade
       当指定时，如果父表中的记录被删除，则依赖于父表的记录也被删除
             REFERENCE 表名（） on delete cascade;
26、启动关闭数据库
  关闭：
   svrmgr>connect internal/oracle
         >shutdown   --正常关闭数据库
   svrmgr>shutdown  immediate  --立即关闭数据库
   svrmgr>shutdown abort  --一种最直接的关闭数据库的方式，执行之后，重新启动需要花6-8小时
  启动：
       $>svrmgrl
   svrmgr>startup  --正常启动
         --等价于:startup nomount;
                  alter database  mount;
                  alter database  open;
   svrmgr>startup mount; --安装启动：用于改变数据库的归档或执行恢复状态
   svrmgr>startup nomount;  --用于重建控制文件或重建数据库
   svrmgr>startup  restrict; --约束启动，能启动数据库，但只允许具有一定特权的用户访问
   如果希望改变这种状态，连接成功后
     alter system disable restricted session;
   svrmgr>startup force;当不能关闭数据库时，可采用强制启动数据库来完成数据库的关闭操作。
   svrmgr>startup pfile=d:\orant\database\initorcl.ora  --带初始化参数文件的启动
27、缺省用户和密码
        <1>. Oracle安裝完成后的初始口令? 
  　internal/oracle 
　　sys/change_on_install 
　　system/manager 
　　scott/tiger 
　　sysman/oem_temp
        <2>. ORACLE9IAS WEB CACHE的初始默认用户和密码？ 
   administrator/administrator


28、对公共授予访问权
    grant select on 表名 to public;
    create public synonym 同义词名  for 表名;
29、填加注释
    comment on table 表名 is  '注释';
    comment on column 表名.列名 is '注释';
30 oracle  loader  
   控制文件的格式
   load data
   infile '数据文件名'
   into table 表名
   (first_name position(01:14) char,
    surname    position(15:28) char,
    clssn      position(29:36) char,
    hire_data  position(37:42) date 'YYMMDD')    
31、程序中报错:maxinum cursor exceed!
   应该调整数据库初始化文件
   加如一项  open_cursors=200
32、生成用户时指定缺省表空间
   create user 用户名 identified by 口令  default 
      tablespace 表空间名;
33、重新指定用户的缺省表空间
    alter user 用户名 default tablespace 表空间名
34、约束条件
    create table employee
     (empno  number(10)  primary key,
      name   varchar2(40) not null,
      deptno  number(2)  default 10,
      salary  number(7,2)  check  salary<10000,
      birth_date date,
      soc_see_num  char(9)  unique,
      foreign key(deptno) references dept.deptno)
      tablespace users;      
    关键字(primary key)必须是非空,表中记录的唯一性
    not null  非空约束
    default   缺省值约束
    check     检查约束,使列的值符合一定的标准范围
    unqiue  唯一性约束
    foreign key 外部键约束
35、分布式数据库
    create [public] database link LINKNAME 
       [connect to USERNAME identified by PASSWORD]
       [using 'CONNECT_STRING']
    可以在服务器端，也可以在客户端建立，但必须注意，两台服务器之间
    数据库必须可以互访，必须各有各自的别名数据库
36、alter user语句的quota子句限制用户的磁盘空间
   如:alter user jf  quota 10M  on system;
37   create table a
      storage(
              initial 1M   /*第一次创建时分配空间*/
              next 1M      /*第一次分配的存储空间用完时在分配*/
              )
              as  select * from b;
38、确定可用空间
    select tablespace_name,sum(blocks),sum(bytes) from sys.dba_free_space group by tablespace_name;
39、创建序列
    select * from user_sequences;
    create  sequence SEQ_NAME  start with 1000 
         maxvalue  1000 increment by 1;
    alter sequence  SEQ_NAME minvalue 50 maxvalue 100;
40、删除重复行
    update a set aa=null where aa is not null;    
    delete from a where rowid!=
        (select max(rowid) from a  b where  a.aa=b.aa);
41、删除同其他表相同的行
    delete from a  where exits
      (select 'X' from b where b.no=a.no);
    或
      delete from a  where no in (select no from b);
42、查看数据库链路
    select * from  all_db_links;
    select * from user_db_links;
    查询  select * from TABLENAME@DBLNKNAME;
    创建远程数据库同义词
       create synonym  for TABLENAME@DBLNKNAME;
    操纵远程数据库记录
      insert into TABLENAME@DBLNKNAME (a,b)  values (va,vb);
      update    TABLENAME@DBLNKNAME  set a='this';
      delete from TABLENAME@DBLNKNAME;
43、快照
    create snapshot SNAPSHOT_NAME 
       [storage (storage parameter)]
       [tablespace  TABLESPACE_NAME]
       [refresh  [fast\complete\force]
       [start with  START_DATE next NEXT_DATE]
       as QUERY;   
   create snapshot snapshot_to_study as select * from TABLE_NAME@to_study;
   创建角色
     create role aa identified by aaa;
   授权  grant create snapshot,alter snapshot to aaa;
         grant  aaa to emp;
   create snapshot SNAPSHOT_TO_HTML refresh  complete start with sysdate next        sysdate+5/(24*60*60) as  select * from a@to_html;
   删除  drop snapshot snap_to_html
   手工刷新快照,(调用DBMS_SNAPSHOT包中的refresh过程)DBMS_SNAPSHOT.refresh(snapshot_name,refresh_type);
       begin
          DBMS_SNAPSHOT.REFRESH('snap_to_html','c');
       end;
   对所有快照进行刷新
       begin
          DBMS_SNAPSHOT.REFRESH_ALL;
       end;
   怎样执行远程的内嵌过程
       begin
         otherdbpro@to_html(参数);
       end;
44、包
   包说明(package specification)，包头,存放关于包的内容的信息，定义包的用户可见的过程、
   函数，数据类型和变量
     create or replace package  tt_aa  as 
        v1  varchar2(10);
        v2  varchar2(10);
        v3  number;
        v4  boolean;
        procedure proc1(x  number);
        procedure proc2(y varchar2);
        procedure proc3(z  number);
        function my_add(x number,y number) return number;
      end;
   包主体（package body)是可选的
     create or replace package  body  tt_aa as 
       procedure proc1(x number) as 
         begin
           v1:=to_char(x);
         end;
       procedure proc2(y varchar2) as 
         begin
           v2:=y;
         end;
       procedure proc3(z number) as 
         begin
           v1:=z;
         end;
       procedure proc4(x number,y number) return number as 
         begin
           return x+y;
         end;
     end;
        调用
     begin  
         tt_aa.proc1(6);
         dbms_output.put_line(to_char(tt_aa.my_add(1,3));
     end;
45、调度程序  DBMS_JOB
       broken       中止一个任务调度
    change       修改任务的属性
    internal     改变间隔
    submit       任务发送到任务队列中去
    next_date    改变任务的运行时间
    remove       删除一个任务
    run          立即执行一个任务
    submit       提交一个任务
    user_export  任务说明
    what         改变任务运行的程序
查询 
   select * from user_job;
   建立一存储过程
   create or replace procedure log_proc  as 
     begin
      insert into test(aa) values(sysdate);
      commit;
     end;
    提交一个任务
     declare 
       job_num  number;
     begin
       dbms_job.submit(job_num,'log_proc;',sysdate,sysdate+5/(24*60*60),false);
       dbms_output.put_line('Job numer='||to_char(job_num));
     end;
    移走任务
     begin
       dbms_job.remove(1);
     end;
    中止任务
      begin
        dbms_job.broken(1,true);
      end;
46、动态sql
    create or replace procedure my_execute(sql_string in varchar2) as 
      v_cursor  number;
      v_numrows  interger;
    begin
       v_cursor:=dbms_sql.open_cursor;
       dbms_sql.parse(v_cursor,sql_string,dbms_sql.v7);
       v_numrows:=dbms_sql.execute(v_cursor);
       dbms_sql.close_cursor(v_cursor);
    end;     
    则可以
     sql>exec  my_execute('select * from tab');
     sql>exec  my_execute('insert into test value'||'('||'''ddd'''||')');
     sql>exec  my_execute('commit');
       对于查询方面的可以如下方式：
  比如想用游标查询一个表，但是这个表是分月的，每个月可能表名都会改变。
  create or replace procedure proc_test as
    v_curid  integer;
    v_result integer;
    v_strSql varchar2(255);
    v_userid okcai.userid%type;
    v_username okcai.username%type;
  begin
     v_strSql := 'select * from okcai_'||to_char(sysdate,'yyyymm');
     v_curid := dbms_sql.open_cursor;
     dbms_sql.parse(v_curid,v_strSql,dbms_sql.v7);
     dbms_sql.define_column(v_curid,1,v_userid);
     dbms_sql.define_column(v_curid,2,v_username,10);  --必须指定大小
     v_result := dbms_sql.execute(v_curid);
     loop
        if dbms_sql.fetch_rows(v_curid) = 0 then
            exit; --没有了 ，退出循环
        end if;
        dbms_sql.column_value(v_curid,1,v_userid);
        dbms_sql.column_value(v_curid,2,v_username);
        dbms_output.put_line(v_userid);
        dbms_output.put_line(v_username);
     end loop;
     dbms_sql.close(v_curid);
  end;


46.1 用EXECUTE IMMEDIATE
    <1>. 在PL/SQL运行DDL语句
    begin
      execute immediate 'set role all';
    end;
    <2>. 给动态语句传值(USING 子句)
    declare
       l_depnam varchar2(20) := 'testing';
       l_loc    varchar2(10) := 'Dubai';
    begin
      execute immediate 'insert into dept values  (:1, :2, :3)'
              using 50, l_depnam, l_loc;
      commit;
    end;
   <3>. 从动态语句检索值(INTO子句)
   declare
      l_cnt    varchar2(20);
   begin
      execute immediate 'select count(1) from emp'
          into l_cnt;
      dbms_output.put_line(l_cnt);
  end;
   <4>. 动态调用例程.例程中用到的绑定变量参数必须指定参数类型.黓认为IN类型,其它类型必须显式指定
        declare
                  l_routin   varchar2(100) := 'gen2161.get_rowcnt';
                l_tblnam   varchar2(20) := 'emp';
                  l_cnt      number;
                  l_status   varchar2(200);
        begin
                  execute immediate 'begin ' || l_routin || '(:2, :3, :4); end;'
                            using in l_tblnam, out l_cnt, in out l_status;


                  if l_status != 'OK' then
                             dbms_output.put_line('error');
                  end if;
        end;


<5>. 将返回值传递到PL/SQL记录类型;同样也可用%rowtype变量
         declare
                  type empdtlrec is record (empno  number(4),
                           ename  varchar2(20),
                           deptno  number(2));
                  empdtl empdtlrec;
         begin
                  execute immediate 'select empno, ename, deptno ' ||
                   'from emp where empno = 7934'
                    into empdtl;
         end;
   <6>. 传递并检索值.INTO子句用在USING子句前
         declare
                  l_dept    pls_integer := 20;
                  l_nam     varchar2(20);
                  l_loc     varchar2(20);
         begin
                execute immediate 'select dname, loc from dept where deptno = :1'
                            into l_nam, l_loc
                            using l_dept ;
         end;
   <7>. 多行查