【Oracle Database】 數據庫對象管理

創建表
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