常用SQL语句整理

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17179887/viewspace-610527/