【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 200SQL> select table_name,num_rows,blocks,status,tablespace_name from user_tables;TABLE_NAME                       NUM_ROWS     BLOCKS STATUS   TABLESPACE_NAME------------------------------ ---------- ---------- -------- ------------------------------CUSTOMERS                         1000000      16087 VALID    SOESQL> col data_type for a15SQL> col nullable for a10SQL> 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 NCUSTOMERS                      CUST_FIRST_NAME                VARCHAR2                 40 YCUSTOMERS                      CUST_LAST_NAME                 VARCHAR2                 40 YCUSTOMERS                      NLS_LANGUAGE                   VARCHAR2                  3 YCUSTOMERS                      NLS_TERRITORY                  VARCHAR2                 30 YCUSTOMERS                      CREDIT_LIMIT                   NUMBER                   22 YCUSTOMERS                      CUST_EMAIL                     VARCHAR2                100 YCUSTOMERS                      ACCOUNT_MGR_ID                 NUMBER                   22 YCUSTOMERS                      CUSTOMER_SINCE                 DATE                      7 YCUSTOMERS                      CUSTOMER_CLASS                 VARCHAR2                 40 YCUSTOMERS                      SUGGESTIONS                    VARCHAR2                 40 YTABLE_NAME                     COLUMN_NAME                    DATA_TYPE       DATA_LENGTH NULLABLE------------------------------ ------------------------------ --------------- ----------- ----------CUSTOMERS                      DOB                            DATE                      7 YCUSTOMERS                      MAILSHOT                       VARCHAR2                  1 YCUSTOMERS                      PARTNER_MAILSHOT               VARCHAR2                  1 YCUSTOMERS                      PREFERRED_ADDRESS              NUMBER                   22 YCUSTOMERS                      PREFERRED_CARD                 NUMBER                   22 Y创建索引SQL> create unique index customers_pk on customers (customer_id);Index created.SQL> col index_name for a30SQL> col index_type for a15SQL> col table_name for a20SQL> col tablespace_name for a20SQL> 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    SOESQL> col column_name for a30SQL> 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 a30SQL> col constraint_type for a15SQL> col table_name for a30SQL> col index_name for a30SQL> 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                   ENABLEDSQL> col constraint_name for a30SQL> col table_name for a30SQL> col column_name for a30SQL> 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