【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