ITPub博客

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

常用SQL语句整理

原创 Linux操作系统 作者:chwang1984 时间:2009-07-27 14:34:00 0 删除 编辑
1.新建表空间、用户、赋权
  --新建表空间
  create datablespace 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;
  --删除表空间
  drop tablespace space_name including contents and datafiles;
  --删除用户
  drop user user_name cascade;
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))
 --重命名原表
 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操作。只在切换的瞬间锁表,具有很高的可用性。这种方法具有很强的灵活性,对各种不同的需要都能满足。而且,可以在切换前进行相应的授权并建立各种约束,可以做到切换完成后不再需要任何额外的管理操作。
缺点:实现上比上面两种略显复杂。
适用:各种情况。

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;

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

请登录后发表评论 登录
全部评论

注册时间:2009-03-27

  • 博文量
    96
  • 访问量
    167828