Shulou(Shulou.com)06/01 Report--

1. Create a tablespace

Create bigfile tablespace viot_data_400w1logging datafile'e:\ data\ viot_data_400w1.dbf' size 20g autoextend on next 1g

Note:

Viot_data_400w1 tablespace name

E:\ data\ viot_data_400w1.dbf tablespace location (you need to create an E\ data folder

20g default tablespace size

The size that automatically expands when the 1g tablespace is full.

2. Create a table and specify a partition for the table

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)

Note: create table TEMP400W; and specify 4 partitions for the table; CUS_PART1 stores 0-900000 records; CUS_PART2 stores 900001-1800000 records, and so on

3. View the table partition

Select * from user_tab_partitions where table_name = 'TEMP400W'

Comments: view all partitions of TEMP400W

4. No log is written for table modification.

Alter table temp400W NOLOGGING

5. Create authorization for DBLink to scott users

Grant CREATE PUBLICDATABASE LINK,DROP PUBLICDATABASE LINK to scott

6. Create a database link

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. join table query insert statement.

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

8. View the utilization of tablespaces

SELECT a.tablespace_name "tablespace name", total "tablespace size", free "tablespace remaining size", (total-free) "tablespace usage size" Round ((total-free) / total, 4) * 100 "usage%" 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

Reference link:

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.