oracle常用命令（工作中总结）

    Oracle默认用户  SYS   SYSTEM   SCOTT  

1-数据库中所有数据字典表和视图都存储在 SYS 模式中。SYS用户主要用来维护系统信息和管理实例。
2-SYSTEM 是默认的系统管理员，该用户拥有Oracle管理工具使用的内部表和视图。通常通过SYSTEM用户管理    数据库用户、权限和存储等
3-SCOTT用户是Oracle 数据库的一个示范帐户，在数据库安装时创建

4-超级用户权限：执行数据库实例的管理工作，使用企业管理器时只能使用这两个身份
SYSDBA：系统管理员，具有数据库服务器的最高权限
SYSOPER：系统操作员，具有打开、关闭、备份、恢复等权限
SYS：超级用户，执行数据库的管理任务，具有数据库的最高权限
连接示例：
conn sys/tiger as sysdba;
conn sys/tiger as sysoper;
conn / as sysdba;

5-创建表空间
create tablespace accpspace
datafile 'd:/oracle/oradata/ora92/accp.dbf'
[size 10m] [autoextend [off|on]] [next 1m];

--创建临时表空间
   create temporary tablespace tab_temp tempfile 'e:/aa.dbf'
 size 10M [autoextend [off|on]] [next 1m]

6-.删除表空间
  drop tablespace tbs;
  彻底删除、包括物理删除(临时表空间同样)
  drop tablespace tbs including contents and datafiles;

 truncate table t1; //删除表内所有数据 

7.创建用户
  create user accp  --用户名
  identified by accp  --密码
  default tablespace tbs1  -- 默认表空间,不指定默system表空间
  temporary tablespace temp;  --指定临时表空间

   alter user accp default tablespace tas; --指定表空间
   alter user accp temporary tablespace tase; --指定临时表空间

   create sequence student_id; --创建序列（标识列）
      插入数据时引用  student_id.nextVal   从1开始
8.授权
  grant connect to accp; --授予登陆权限
  grant resource to accp;  --授予资源使用权
  grant connect,resource to accp;--也行
  grant create session to test; --登录权限
  grant create table to test; --建表能力
  grant unlimited tablespace to test; --无限制表空间
9.回收权限
  revoke create session from test;
10.跨用户授权
  grant select on emp to accp; --授予accp select emp表的权限
  grant all on emp to accp;  --所有权限
11.切换登陆用户
  conn accp/accp;
12.修改密码
  alter user accp identified by aptech; --修改为aptech
13.查看当期用户
  show user;
  select user from dual;
14.删除用户
  drop user accp;  --删除空用户
  drop user accp cascade;--删除用户下所有的数据对象
15.跨用户授权
  grant select on emp to accp; --授予accp select emp表的权限
  grant update on emp to test;
  grant all on emp to accp;  --所有权限
14.
   select * from tab; --查看用户下所有的表

15.
   查看当前数据库名: select name from v$database; 或 show parameter db;

16.
   查看数据库实例:show parameter instance; 或 select instance_name from v$instance

17.
   数据库域名在存在于参数文件中，他的参数是db_domain.
   查询数据库域名 方法一：select value from v$parameter where name = 'db_domain';
   方法二：show parameter domain

18.
   查询数据文件 select name from v$datafile;
   查询控制文件 select name from v$controlfile;
   查询日志文件 select member from v$logfile;

19.
   //增加列
        Alter table t1 add b varchar2(20); 
   //修改数据类型、长度
 Alter table t1 modify b number(2);  
   //删除列
   Alter table t1 dorp column b;

   //设置主键
  Alter table t1 add constraint pk_t1 primary key (id);
   //设置唯一约束
 Alter table t1 add constraint uq_t1 unique(b);
   //设置检查约束
 Alter table t1 add constraint ck_t1 check(b>18);
   //设置默认值约束
 Alter table t1 modify city default ('BeiJing');
    //设置外键约束
 Alter table t1 add constraint ref_1 foreign key (rid) references t2(id);
    //给表改名
        ALTER TABLE 当前表名 RENAME TO 新表名;
    //给列改名
 ALTER TABLE 表名 RENAME COLUMN 当前列名 TO 新列名;

20.
     利用现有的表创建表
          CREATE TABLE <new_table_name> AS
          SELECT column_names FROM <old_table_name>;

 CREATE TABLE newitemfile2
      AS SELECT * FROM itemfile
      WHERE 1 = 2;             --只有表结构

21.
     插入来自其它表中的记录（表必须先存在）
        INSERT INTO <table_name> [(cloumn_list)]
           SELECT column_names FROM <other_table_name>;

22.
 Oracle事务（transaction）管理
 一个事务包含一个或多个SQL语句，是逻辑管理的工作单元（原子单元）[班级->项目组]
 一个事务开始于第一次执行的SQL语句，结束于Commit 或 Rollback 或 DDL语句。
 注意：其中Commit, Rollback是显示的提交事务，而DDL语句是隐式的提交事务的。
 DDL语句的操作是没有办法回滚的。
 事务的特点：原子性、一致性、隔离性、持久性。
 事务结束的地方有：
 1>. 执行Commit, Rollback， 没有使用savepoint.
 2>. 执行DDL操作如：create , drop, rename, alter
 3>. 断开与Oracle的连接，事务将自动提交。
 4>. 用户进程异常终止，当前事务回滚。
 注意：应用程序与Oracle连接的情况，在应用程序终止前必须显示的提交(Commit)或回滚Rollback

 事务
 SQL>  UPDATE order_master SET del_date = ‘30-8月-05’WHERE orderno <= 'o002';
 
 SQL>  savepoint mark1;  --标记事务中可以回滚的点
 SQL>  DELETE FROM order_master WHERE orderno = ‘o002’;
 SQL>  savepoint mark2; 
 SQL>  rollback to savepoint mark1;   --撤消
 SQL>  commit;   --提交
23.
     集合操作符
      union  联合两个表的数据，去掉了重复值
      union all   保留重复值
      intersect   返回两个查询中的公共行
      minus   返回第一个查询结果并排除第二个查询中出现的公共行

24.
   同义词是现有对象的一个别名。
     1.简化SQL语句
     2.隐藏对象的名称和所有者
     3.提供对对象的公共访问
   同义词共有两种类型：
 公有同义词可被所有的数据库用户访问。
     create public synonym emp_syn for scott.emp ;
 私有同义词只能在其模式内访问，且不能与当前模式的对象同名。
     create synonym emp2 for scott.emp;
 创建或替换现有的同义词
     create or replace synonym emp2 for dept;
 删除同义词
     drop synonym emp2;
     drop public synonym emp_syn;
25.
   序列是用于生成唯一、连续序号的对象
   序列可以是升序的，也可以是降序的
   使用create sequence语句创建序列
 CREATE sequence toys_seq
 start with 10    --起始值为10
 increment by 10   --增长量为10
 MAXVALUE 2000
 MINVALUE 10  --可以为负数
 nocycle      --不循环
 cache 10;    --指定内存中预先分配的序号数

 nextval 返回序列的下一个值
 currval 返回序列的当前值
 修改
 alter sequence id_seq maxvalue 2000 cycle;不能更改序列的start with 参数
 删除
 drop sequence id_seq;
26.
    视图可以视为“虚拟表”或“存储的查询”
 create [or replace] [force] view
     view_name [(alias[, alias]...)]
   as select_statement
   [with check option]
   [with read only];

 drop view view_name
 
 在视图上也可以使用修改数据的DML语句，如INSERT、UPDATE和DELETE
 视图上的DML语句有如下限制：
   1.只能修改一个底层的基表
   2.如果修改违反了基表的约束条件，则无法更新视图
   3.如果视图包含连接操作符、DISTINCT 关键字、集合操作符、聚合函数或GROUP BY 子句，则将             无法更新视图
   4.如果视图包含伪列或表达式，则将无法更新视图

 可以通过查询USER_VIEWS数据字典视图来获得用户所创建的视图信息。
  select * from user_views;

27.
    索引：是与表相关的一个可选结构，用于提高SQL语句执行的性能。
 标准索引 ：create index item_index on itemfile(itemcode) tablespace index_tbs;
  重建索引：alter index item_index rebuile;
  删除索引：drop index item_index;

 唯一索引：确保列中没有重复值。Oracle自动在表的主键列上创建唯一索引
  create unique index item_index on itemfile(itemcode);

 组合索引：是在表的多个列上创建的索引。索引中列的顺序是任意的。如果SQL语句的where子句中                    引用了组合索引的所有列或大多数列，则可以提高检索速度。
  create index comp_index on itemfile(p_category,itemrate);

 反向键索引：反转索引列键值的每个字节。通常建立在值是连续增长的列上，使数据均匀地分布在       整个索引上。
  create index rev_index on itemfile(itemcode) reveres;

 位图索引：适合创建在重复值比较多的列上。不直接存储rowid，而是存储字节位到rowid的映射。
   减少响应时间。节省空间占用。
  create bitmap index bit_index on order_master(orderno);

 基于函数的索引
  1：（基于函数）
   create index vn_index on vendor_master(upper(venname));
  2：（基于表达式）
   create index vn_index on itemtb(qty_num * itemrate);
  3：（应用）
   select * from vendor_master where upper(venname)='SMALL';
  要创建基于函数或表达式的索引，必须具有query rewrite系统权限。

 ————————————————————
 与索引有关的数据字典视图有：
  user_indexes － 用户创建的索引的信息
  user_ind_partitions － 用户创建的分区索引的信息
  user_ind_columns － 与索引相关的表列的信息
---------------------------------------------------------------------------------
28.
 PL/SQL 分为三个部分，声明部分、可执行部分和异常处理部分
    [DECLARE   --声明
     declarations]
     BEGIN
      executable statements   --可执行部分
     [EXCEPTION     --异常处理
      handlers]
    END;

 1.声明变量和常量的语法：
 identifier [constant] datatype [NOT NULL] [:= | default expr];
     ----v3 constant varchar2(20):='常量定义';
  dbms_output.put_line(v3);  --输出语句
 2.给变量赋值有两种方法：
  使用赋值语句 :=
  使用 SELECT INTO 语句

 3.记录类型变量
  declare
  type myrecord is record(
   myrecordnumber int,
   mycurrentdate date);
  srecord myrecord;
  begin
   select empno,hiredate into srecord from scott.emp where sal=1300;
   dbms_output.put_line(srecord.mycurrentdate);
  end;

 4.定义一维数组
  type 数组名 is table of 元素类型 index by 索引类型;
  type array_ is table of varchar2(20) index by binary_integer;
  定义数组变量:Arr array;
  定义一维数组(注意索引,从1开始)
   TYPE sarr IS TABLE OF BINARY_INTEGER; 
    aarr sarr := sarr();  --初始化
    aarr.EXTEND(2);       --分配空间大小
  或者:
   aarr sarr := sarr(1, 2);
   aarr(1) := 11; aarr(2) := 22;
  数组元素的个数: count
 
 5.执行动态 SQL 的语法：
    EXECUTE IMMEDIATE dynamic_sql_string
        [INTO  define_variable_list]
       [USING bind_argument_list];

 6、
 PL/SQL有丰富的数据类型
 PL/SQL有结构化的程序控制结构
     分支结构：if else，when case
     循环结构：loop，  while 条件表达式 loop，
                     for 变量 in 集合 loop
     程序跳转：goto，<<标示符>>
 动态SQL的执行： EXECUTE IMMEDIATE  SQL语句;
 使用Exception块处理异常

 

 

00.
   查询一些参数的表
 select * from v$nls_parameters

  将当前用户下的所有表的日期格式改成'yyyy-mm-dd' 
  alter session set nls_date_format='yyyy-mm-dd';
  返回
  alter session set nls_date_format='dd-mon-rr';

   Alter session set nls_date_language=’american’; 将日期语文改成美国的
   例： Insert into books values(1,’04-jul-06’);  正确的。
    Alter session set nls_date_language=’simplified chinese’;  改回中国的

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