创建表
SQL> create table customers
( customer_id NUMBER(12) ,
cust_first_name VARCHAR2(40) ,
cust_last_name VARCHAR2(40) ,
nls_language VARCHAR2(3) ,
nls_territory VARCHAR2(30) ,
credit_limit NUMBER(9,2) ,
cust_email VARCHAR2(100) ,
account_mgr_id NUMBER(12),
customer_since DATE,
customer_class VARCHAR(40),
suggestions VARCHAR(40),
dob DATE,
mailshot VARCHAR(1),
partner_mailshot VARCHAR(1),
preferred_address NUMBER(12),
preferred_card NUMBER(12)
);
Table created.
导入数据
SQL> exec dbms_stats.gather_schema_stats('SOE');
PL/SQL procedure successfully completed.
SQL> set line 200
SQL> select table_name,num_rows,blocks,status,tablespace_name from user_tables;
TABLE_NAME NUM_ROWS BLOCKS STATUS TABLESPACE_NAME
------------------------------ ---------- ---------- -------- ------------------------------
CUSTOMERS 1000000 16087 VALID SOE
SQL> col data_type for a15
SQL> col nullable for a10
SQL> select table_name,column_name,data_type,data_length,nullable from user_tab_columns where table_name = 'CUSTOMERS';
TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH NULLABLE
------------------------------ ------------------------------ --------------- ----------- ----------
CUSTOMERS CUSTOMER_ID NUMBER 22 N
CUSTOMERS CUST_FIRST_NAME VARCHAR2 40 Y
CUSTOMERS CUST_LAST_NAME VARCHAR2 40 Y
CUSTOMERS NLS_LANGUAGE VARCHAR2 3 Y
CUSTOMERS NLS_TERRITORY VARCHAR2 30 Y
CUSTOMERS CREDIT_LIMIT NUMBER 22 Y
CUSTOMERS CUST_EMAIL VARCHAR2 100 Y
CUSTOMERS ACCOUNT_MGR_ID NUMBER 22 Y
CUSTOMERS CUSTOMER_SINCE DATE 7 Y
CUSTOMERS CUSTOMER_CLASS VARCHAR2 40 Y
CUSTOMERS SUGGESTIONS VARCHAR2 40 Y
TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH NULLABLE
------------------------------ ------------------------------ --------------- ----------- ----------
CUSTOMERS DOB DATE 7 Y
CUSTOMERS MAILSHOT VARCHAR2 1 Y
CUSTOMERS PARTNER_MAILSHOT VARCHAR2 1 Y
CUSTOMERS PREFERRED_ADDRESS NUMBER 22 Y
CUSTOMERS PREFERRED_CARD NUMBER 22 Y
创建索引
SQL> create unique index customers_pk on customers (customer_id);
Index created.
SQL> col index_name for a30
SQL> col index_type for a15
SQL> col table_name for a20
SQL> col tablespace_name for a20
SQL> select index_name,index_type,table_name,num_rows,distinct_keys,blevel,status,tablespace_name from user_indexes;
INDEX_NAME INDEX_TYPE TABLE_NAME NUM_ROWS DISTINCT_KEYS BLEVEL STATUS TABLESPACE_NAME
------------------------------ --------------- -------------------- ---------- ------------- ---------- -------- --------------------
CUSTOMERS_PK NORMAL CUSTOMERS 1000000 1000000 2 VALID SOE
SQL> col column_name for a30
SQL> select index_name,table_name,column_name,column_position from user_ind_columns;
INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------- ------------------------------ ---------------
CUSTOMERS_PK CUSTOMERS CUSTOMER_ID 1
创建主键约束
SQL> alter table customers add constraint customers_pk primary key (customer_id);
Table altered.
SQL> col constraint_name for a30
SQL> col constraint_type for a15
SQL> col table_name for a30
SQL> col index_name for a30
SQL> select constraint_name,constraint_type,table_name,index_name,status from user_constraints where constraint_type = 'P';
CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME INDEX_NAME STATUS
------------------------------ --------------- ------------------------------ ------------------------------ --------
CUSTOMERS_PK P CUSTOMERS CUSTOMERS_PK ENABLED
SQL> col constraint_name for a30
SQL> col table_name for a30
SQL> col column_name for a30
SQL> select constraint_name,table_name,column_name,position from user_cons_columns;
CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION
------------------------------ ------------------------------ ------------------------------ ----------
CUSTOMERS_PK CUSTOMERS CUSTOMER_ID 1
网站栏目：【OracleDatabase】数据库对象管理
链接地址：
http://bjjierui.cn/article/igojgd.html
