ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 常用语句整理

常用语句整理

原创 Linux操作系统 作者:chwang1984 时间:2011-04-11 17:33:28 0 删除 编辑
1.新建表空间、用户、赋权
  --新建表空间
  create tablespace space_name
  datafile 'F:\ORACLE\HZASA\perfstat.dbf'
  size 500M
  extent management local;
  --新建用户
  create user user_name identified by password
  default tablespace space_name
  temporary tablespace tmp_name
  --赋予权限
  grant resource,connect  to user_name
  alter user user_name default tablespace space_name;
  --赋予用户下的所有表的权限
  grant select on table1 to user_name
  grant select any table to user_name
  --删除表空间
  drop tablespace space_name including contents and datafiles;
  --删除用户
  drop user user_name cascade;
  --修改用户名(在cmd模式下)
  a.新建新的用户
  create user user_name identified by password
  default tablespace space_name
  temporary tablespace tmp_name;
  b.获取新建用户的密码的编码
  select password from user$ where name=upper('user_name');
  PASSWORD
  ------------------------------
  CA6B6E85100F4511
  c.删除该用户及其信息
  drop user user_name cascade;
  d.修改数字字典
  select user#,name,password from user$ where name = upper('old_user_name');
  USER# NAME PASSWORD
  ---------- ------------------------------ ------------------------------
  27 BNET_GX 3C5213970F4BCB25
 
  update user$ set name=upper('user_name') where user#=27;
  commit;
 
  强制写入数据文件
  alter system checkpoint;
  强制 Oracle 读取实际数据,而不是读取缓存
  alter system flush shared_pool;
 
  更新密码
  alter user bnetgx identified by values 'CA6B6E85100F4511';
 
  e.登陆后测试是否成功
  conn user_name/password
 
  select count(*) from user_objects;
2.重建临时表空间
  --启动数据库
  startup
  --新建中转临时表空间
  create temporary tablespace TEMP2
  tempfile 'F:\ORACLE\HZASA\TEMP2.dbf'
  size 500M
  reuse autoextend on next 640K maxsize unlimited
  extent management local;
  --切换到中转表空间
  alter database default temporary tablespace temp2;
  --删除原来的表空间
  drop tablespace temp including contents and datafiles;
  --创建新的临时表空间
  create temporary tablespace TEMP
  tempfile 'F:\ORACLE\HZASA\TEMP.dbf'
  size 500M
  reuse autoextend on next 640K maxsize unlimited
  extent management local;
  --重置默认表空间
  alter database default temporary tablespace temp;
  --删除中转表空间
  drop tablespace temp2 including contents and datafiles;
  --重新指定用户表空间为重建表空间
  alter user roll temporary tablespace temp;
3.Oracle 分区表的建立
  分区类型:范围分区(range)、哈希分区(hash)、列表分区(list)、范围-哈希复合分区(range-hash)、范围-列表复合分区(range-list)。
  分区的有点:减少数据损坏的可能性(因数据分散到各个分区)、可单独对分区进行备份和还原、可将分区映射到不同的物理磁盘,来分散IO、提高可管理性可用性和性能。
 --利用原表重建分区表
 --新建测试表
 create table T(ID number primary key,TIME date);
 --向测试表插入数据
 insert into T select rownum,created from dba_objects;
 --利用测试表中的数据新建分区表
 create table t_new(id,time) partition by range(time)
 (partition p1 values less than(to_date('2009-1-1','yyyy-MM-dd')),
  partition p2 values less than(to_date('2009-2-1','yyyy-MM-dd')),
  partition p3 values less than(to_date('2009-3-1','yyyy-MM-dd')),
  partition p4 values less than(to_date('2009-4-1','yyyy-MM-dd')),
  partition p5 values less than(maxvalue))
  as select id,time from t;
 --重命名原表
 rename t to t_old;
 --重命名分区表
 rename t_new to t;
 --测试分区是否新建成功
 select count(*) from t partition(p1);
 select count(*) from t partition(p3);
 --利用原表重建分区表的优缺点
 优点:方法简单易用,由于采用DDL语句,不会产生UNDO,且只产生少量REDO,效率相对较高,而且建 表完成后数据已经在分布到各个分区中了。
 缺点:对于数据的一致性方面还需要额外的考虑。由于几乎没有办法通过手工锁定T表的方式保证一致性,在执行CREATE TABLE语句和RENAME T_NEW TO T语句直接的修改可能会丢失,如果要保证一致性,需要在执行完语句后对数据进行检查,而这个代价是比较大的。另外在执行两个RENAME语句之间执行的对T的访问会失败。
 适用:修改不频繁的表,在闲时进行操作,表的数据量不宜太大。
 --使用交换分区的方法
 --新建测试表
 create table T(ID number primary key,TIME date);
 --向测试表插入数据
 insert into T select rownum,created from dba_objects;
 --利用测试表中的数据新建分区表
 create table t_new(id number primary key,time date) partition by range(time)
 (partition p1 values less than(to_date('2009-4-1','yyyy-MM-dd')),
  partition p2 values less than(to_date('2009-5-1','yyyy-MM-dd')),
  partition p3 values less than(maxvalue))
 --将数据交换到一个分区中
 alter table t_new exchange partition p1 with table t;
 --重命名原表
 rename t to t_old;
 --重命名分区表
 rename t_new to t;
 --测试分区是否新建成功
 select count(*) from t partition(p1);
 select count(*) from t partition(p3);
 优点:只是对数据字典中分区和表的定义进行了修改,没有数据的修改或复制,效率最高。如果对数据在分区中的分布没有进一步要求的话,实现比较简单。在执行完rename操作后,可以检查t_old中是否存在数据,如果存在的话,直接将这些数据插入到t中,可以保证对t插入的操作不会丢失。
 缺点:存在一致性问题,交换分区之后rename t_new to t之前,查询、更新和删除会出现错误或访问不到数据。如果要求数据分布到多个分区中,则需要进行分区的split操作,会增加操作的复杂度,效率也会降低。
 适用:包含大数据量的表转到分区表中的一个分区的操作,应该尽量在闲时进行操作。
--利用在线重定义功能(9i以上)
--新建测试表
 create table T(ID number primary key,TIME date);
--向测试表插入数据
 insert into T select rownum,created from dba_objects;
--执行过程
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'T', DBMS_REDEFINITION.CONS_USE_PK);
--新建分区表
CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
(PARTITION P1 VALUES LESS THAN (TO_DATE('2009-2-1', 'YYYY-MM-DD')),
  PARTITION P2 VALUES LESS THAN (TO_DATE('2009-3-1', 'YYYY-MM-DD')),
  PARTITION P3 VALUES LESS THAN (TO_DATE('2009-4-1', 'YYYY-MM-DD')),
  PARTITION P4 VALUES LESS THAN (MAXVALUE)); 
--执行过程
exec dbms_redefinition.start_redef_table(user,'T','T_NEW',->'ID ID,TIME TIME',dbms_redefinition.cons_use_pk);
--过程执行完成
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('YANGTK', 'T', 'T_NEW');
--测试分区是否新建成功
 select count(*) from t partition(p1);
 select count(*) from t partition(p3);
优点:保证数据的一致性,在大部分时间内,表T都可以正常进行DML操作。只在切换的瞬间锁表,具有很高的可用性。这种方法具有很强的灵活性,对各种不同的需要都能满足。而且,可以在切换前进行相应的授权并建立各种约束,可以做到切换完成后不再需要任何额外的管理操作。
缺点:实现上比上面两种略显复杂。
适用:各种情况。
基本步骤:
第一步:利用dbms_redefinition.can_redef_table过程检查该表是否能被在线重定义。
如果这一步不抛出异常,说明该表是可以在线重定义的。
第二步:创建一个与原表类似的空表结构,用于重定义该表,这里叫做是中间表
在这里你可以定义表的新列名、新数据类型、列顺序、存储参数等。注意,为了提高效率,
在这一步不要建立索引和约束。
第三步:用dbms_redefinition.start_redef_table procedure定义重构开始
这个过程将会自动执行如下操作:
1、插入所有行从原有表到中间表
2、创建MLOG$_xxx快照与快照日志,临时存储DML语句直到完成。
第四步:调用DBMS_REDEFINITION.SYNC_INTERIM_TABLE过程同步原表与中间表的数据
这一步不是必须的,如果省略这一步,在finish_redef_table也会执行这一步骤。但我们应该把这一步
放在为中间表建立索引、约束等前面,这样可以提高效率。
第五步:与原表一致,在中间表上面创建约束,索引,触发器
与原表一致(如果需要),中间表的对象权限被授予给别的对象
注意:在中间表建立外键约束时应该加上DISABLE关键字
第六步:用dbms_redefinition.finish_redef_table过程完成表的最终重定义
该过程将自动完成
第七步:删除中间表
第八步:如果是920以上,可以利用ALTER TABLE ... RENAME CONSTRAINT ...语句来修改约束名称,
第九步:如果重组织失败,那么你就必须采取特殊的步骤来让它重新开始。由于重定义过程需要创
建表格的快照,因此为了重新开始这一过程,你必须调用DBMS_REDEFINITION.ABORT_REDEF_TABLE来
释放快照。
4.表空间限额问题
知识总结:
 表空间的大小与用户的配额大小是两种不同的概念
 表空间的大小是指实际的用户表空间的大小,而配额大小指的是用户指定使用表空间的的大小
 把表空间文件增大,还是出现这个问题,用户在使用表空间的同时使用空间的限额,如果超出 限制, 就算有空的地方,也不会让用户使用
视图:
 dba_ts_quotas  : DBA_TS_QUOTAS describes tablespace quotas for all users.
 user_ts_quotas  :USER_TS_QUOTAS describes tablespace quotas for the current user.   This view does not display the USERNAME column.
 查看用户的表空间的限额
  select * from dba_ts_quotas;
  max_bytes字段就是了
  -1是代表没有限制,其它值多少就是多少了.
用户表空间限额的创建与更改:
     1.创建用户时,指定限额
  eg:
  CREATE USER SKATE IDENTIFIED BY SKATE_PWD
  DEFAULT TABLESPACE SKATE_TS
  TEMPORARY TABLESPACE temp
  QUOTA 3M ON SKATE_TS
  PASSWORD EXPIRE;
 2.更改用户的表空间限额:
  A:不对用户做表空间限额控制:
            GRANT UNLIMITED TABLESPACE TO skate;
              这种方式是全局性的.
            或者
                  alter user skate quota unlimited on skate_ts;
               这种方式是针对特定的表空间的.
 3.可以分配自然也可以回收了:
      revoke unlimited tablespace from skate;
  或者
      alter user skate quota 0 on SKATE_TS;
 4.不用游标,实现结果集遍历
create or replace procedure PROC_TEST_TABLE
 is
  type t_table is record (
       logid TEST.LOG_ID%TYPE,
       flag TEST.FLAG%TYPE
  );
  TYPE t_type IS TABLE OF t_table INDEX BY BINARY_INTEGER;
  myRecord t_type;
  v_sql varchar2(2000);
 begin
  select log_id logid,flag flag bulk collect into myRecord from test;
  for i in myRecord.first..myRecord.last loop
      dbms_output.put_line(myRecord(i).logid || '   ' || myRecord(i).FLAG);
  end loop;
 end PROC_TEST_TABLE;
 
create or replace procedure proc_test_table
 is
   type t_type is table of test%rowtype index by binary_integer;
   myRecord t_type;
begin
  select * bulk collect into myRecord from test;
  for i in myRecord.first..myRecord.last loop
    dbms_output.put_line(myRecord(i).log_id || '   ' || myRecord(i).FLAG);
  end loop;
end;
create or replace procedure PROC_TEST_TABLE
is
  type t_table is record (
       logid TEST.LOG_ID%TYPE,
       flag TEST.FLAG%TYPE
  );
  TYPE t_type IS TABLE OF t_table INDEX BY BINARY_INTEGER;
  myRecord t_type;
  type t_cursor is ref cursor;
  v_cursor t_cursor;
  v_sql varchar2(2000);
begin
  v_sql:='select log_id logid,flag flag from test';
  open v_cursor for v_sql;
    fetch v_cursor bulk collect into myRecord;
  CLOSE v_cursor;
 
  for i in 1..myRecord.COUNT
  LOOP
      dbms_output.put_line(myRecord(i).logid || '   ' || myRecord(i).FLAG);
  END LOOP;
end PROC_TEST_TABLE;
 
create or replace procedure PROC_TEST_TABLE
is
  type t_table is record (
       logid TEST.LOG_ID%TYPE,
       flag TEST.FLAG%TYPE
  );
  TYPE t_type IS TABLE OF t_table INDEX BY BINARY_INTEGER;
  myRecord t_type;
  type t_cursor is ref cursor;
  v_cursor t_cursor;
  v_sql varchar2(2000);
begin
  v_sql:='select log_id logid,flag flag from test';
  open v_cursor for v_sql;
    fetch v_cursor bulk collect into myRecord;
  CLOSE v_cursor;
 
  for i in 1..myRecord.COUNT
  LOOP
      dbms_output.put_line(myRecord(i).logid || '   ' || myRecord(i).FLAG);
  END LOOP;
end PROC_TEST_TABLE;
create or replace procedure PROC_TEST_TABLE
is
  type t_table is record (
       logid TEST.LOG_ID%TYPE,
       flag TEST.FLAG%TYPE
  );
  myRecord t_table;
  CURSOR v_cursor is select log_id logid,flag flag from test;
begin
  open v_cursor;
  loop
      fetch v_cursor into myRecord;
       exit when v_cursor%NOTFOUND OR v_cursor%NOTFOUND is null;
      dbms_output.put_line(myRecord.logid || '   ' || myRecord.FLAG);
  end loop;
  close v_cursor;
end PROC_TEST_TABLE;
5.利用Oracle9iR2以上的新特性,实现误删数据的回复
6.查看数据库的归档模式
 -》首先查看数据库现有模式可使用以下语句
    select name,log_mode from v$database;
 -》也可以用下面的语句
    archive log list;(该方法需要as sysdba)
 对于非归档模式的数据库该为归档模式(主要以Oracle 10g为参考)使用以下步骤:
   1.  SQL> alter system set log_archive_dest_1            ='location=/oracle/oracle10g/log/archive_log';
    该语句含义是确定归档日志的路径,实际上Oracle 10g可以生成多份一样的日志,保存多个位置,以防不测
    例如再添加一个日志位置可使用以下语句
       SQL>alter system set log_archive_dest_2='location=/oracle/oracle10g/log2/archive_log';
   2.关闭数据库
     SQL> shutdown immediate
   3.启动数据mount状态:
     SQL> startup mount;
   4、修改数据库为归档模式:
     SQL> alter database archivelog;
   5、打开数据库,查询:
     SQL> alter database open;
    修改日志文件命名格式:
     SQL> alter system set log_archive_max_processes = 5;
     SQL> alter system set log_archive_format = "archive_%t_%s_%r.log"  scope=spfile;
    修改完成后可以查看日志模式是否修改成功!
    特别指出的是在Oracle 9i中还要修改参数log_archive_start = true才能生效,oracle 10g中已经废除了该参数,所以不需要设置该参数。
    alter system set log_archive_start=true scope=spfile
 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17179887/viewspace-692239/,如需转载,请注明出处,否则将追究法律责任。

上一篇: expdp的常用用法
下一篇: 常用指令
请登录后发表评论 登录
全部评论

注册时间:2009-03-27

  • 博文量
    96
  • 访问量
    169925