Oracle 表操作

1、 创建表空间

create bigfile tablespace viot_data_400w1logging datafile 'E:\data\viot_data_400w1.dbf' size 20g autoextend on next 1g;

注释：

viot_data_400w1 表空间名称；

E:\data\viot_data_400w1.dbf 表空间位置（前提需创建E\data文件夹；

20g 默认的表空间大小

1g 表空间写满后，自动扩展的大小。

2、创建表，并对表指定分区

create TABLE TEMP400W("RYBH" VARCHAR2(20 BYTE),         "ZPBH"VARCHAR2(20 BYTE),         "RXZP"BLOB,         "XM"VARCHAR2(30 BYTE),         "XB"CHAR(1 BYTE),         "ZJHM"VARCHAR2(20 BYTE),    "INCCOLUM" NUMBER NOT NULL PRIMARYKEY)PARTITION BY RANGE (INCCOLUM)(   PARTITION CUS_PART1 VALUES LESS THAN (900000) TABLESPACEviot_data_400w1,   PARTITION CUS_PART2 VALUES LESS THAN (1800000) TABLESPACEviot_data_400w2,   PARTITION CUS_PART3 VALUES LESS THAN (2700000) TABLESPACEviot_data_400w3,   PARTITION CUS_PART4 VALUES LESS THAN (3600000) TABLESPACEviot_data_400w4,   PARTITION CUS_PART5 VALUES LESS THAN (4500000) TABLESPACEviot_data_400w5)

注释：创建表TEMP400W; 并且为表指定4个分区；CUS_PART1 存储0-900000的记录；CUS_PART2存储第900001-1800000的记录，以此类推；

3、 查看表分区

select * from user_tab_partitions where table_name = 'TEMP400W'

注释：查看TEMP400W的所有分区

4、 修改表操作不写日志

alter   table  temp400W   NOLOGGING

5、创建DBLink的授权给scott用户

 grant CREATE PUBLIC DATABASE LINK，DROP PUBLICDATABASE LINK to scott;

6、创建数据库链接

CREATE PUBLIC DATABASE LINK viot_dblinkCONNECT TO viot IDENTIFIED BY viot USING '(DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.12.110)(PORT = 1521))    )    (CONNECT_DATA =(SERVICE_NAME = viot)    )   )'

7、连表查询插入语句。

 insert intotemp400W(rybh,zpbh,rxzp,xm,xb,zjhm,inccolum) selectrybh,zpbh,rxzp,xm,xb,zjhm,inccolum1 from temp400W@viot_dblink

8、查看表空间的使用率

SELECT a.tablespace_name                        "表空间名",       total                                    "表空间大小",       free                                     "表空间剩余大小",       ( total - free )                         "表空间使用大小",       Round(( total - free ) / total, 4) * 100 "使用率   %"FROM   (SELECT tablespace_name,               Sum(bytes) free        FROM   DBA_FREE_SPACE        GROUP  BY tablespace_name) a,       (SELECT tablespace_name,               Sum(bytes) total        FROM   DBA_DATA_FILES        GROUP  BY tablespace_name) bWHERE  a.tablespace_name = b.tablespace_name

参考链接：

http://blog.chinaunix.net/uid-11570547-id-58989.html

http://soft.chinabyte.com/database/290/12218290.shtml

http://www.cnblogs.com/leiOOlei/archive/2012/06/08/2541306.html