【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