oracle：关于数据表customers

[-droidcoffee-]

在oracle的一些教科书里，经常看到讲解用的实例表是 customers

请问一下 ，oracle 里自带这个表吗？

怎么可以获取这个表的建表脚本呢？

[ngx20080110]

在oe用戶下有這個表

[ngx20080110]

ALTER TABLE OE.CUSTOMERS
 DROP PRIMARY KEY CASCADE;

DROP TABLE OE.CUSTOMERS CASCADE CONSTRAINTS;

CREATE TABLE OE.CUSTOMERS
(
  CUSTOMER_ID        NUMBER(6),
  CUST_FIRST_NAME    VARCHAR2(20 BYTE) CONSTRAINT CUST_FNAME_NN NOT NULL,
  CUST_LAST_NAME     VARCHAR2(20 BYTE) CONSTRAINT CUST_LNAME_NN NOT NULL,
  CUST_ADDRESS       OE.CUST_ADDRESS_TYP,
  PHONE_NUMBERS      OE.PHONE_LIST_TYP,
  NLS_LANGUAGE       VARCHAR2(3 BYTE),
  NLS_TERRITORY      VARCHAR2(30 BYTE),
  CREDIT_LIMIT       NUMBER(9,2),
  CUST_EMAIL         VARCHAR2(30 BYTE),
  ACCOUNT_MGR_ID     NUMBER(6),
  CUST_GEO_LOCATION  MDSYS.SDO_GEOMETRY,
  DATE_OF_BIRTH      DATE,
  MARITAL_STATUS     VARCHAR2(20 BYTE),
  GENDER             VARCHAR2(1 BYTE),
  INCOME_LEVEL       VARCHAR2(20 BYTE)
)
COLUMN CUST_ADDRESS NOT SUBSTITUTABLE AT ALL LEVELS
COLUMN CUST_GEO_LOCATION NOT SUBSTITUTABLE AT ALL LEVELS
TABLESPACE EXAMPLE
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOLOGGING 
NOCOMPRESS 
VARRAY "CUST_GEO_LOCATION"."SDO_ORDINATES" STORE AS LOB (
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  RETENTION
  CACHE
  INDEX       (
        TABLESPACE EXAMPLE
        STORAGE    (
                    INITIAL          64K
                    NEXT             1M
                    MINEXTENTS       1
                    MAXEXTENTS       UNLIMITED
                    PCTINCREASE      0
                    BUFFER_POOL      DEFAULT
                   ))
      STORAGE    (
                  INITIAL          64K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                 ))
VARRAY "CUST_GEO_LOCATION"."SDO_ELEM_INFO" STORE AS LOB (
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  RETENTION
  CACHE
  INDEX       (
        TABLESPACE EXAMPLE
        STORAGE    (
                    INITIAL          64K
                    NEXT             1M
                    MINEXTENTS       1
                    MAXEXTENTS       UNLIMITED
                    PCTINCREASE      0
                    BUFFER_POOL      DEFAULT
                   ))
      STORAGE    (
                  INITIAL          64K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                 ))
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE OE.CUSTOMERS IS 'Contains customers data either entered by an employee or by the customer
him/herself over the Web.';

COMMENT ON COLUMN OE.CUSTOMERS.ACCOUNT_MGR_ID IS 'References hr.employees.employee_id.';

COMMENT ON COLUMN OE.CUSTOMERS.CUST_GEO_LOCATION IS 'SDO (spatial) column.';

COMMENT ON COLUMN OE.CUSTOMERS.CUSTOMER_ID IS 'Primary key column.';

COMMENT ON COLUMN OE.CUSTOMERS.CUST_FIRST_NAME IS 'NOT NULL constraint.';

COMMENT ON COLUMN OE.CUSTOMERS.CUST_LAST_NAME IS 'NOT NULL constraint.';

COMMENT ON COLUMN OE.CUSTOMERS.CUST_ADDRESS IS 'Object column of type address_typ.';

COMMENT ON COLUMN OE.CUSTOMERS.PHONE_NUMBERS IS 'Varray column of type phone_list_typ';

COMMENT ON COLUMN OE.CUSTOMERS.CREDIT_LIMIT IS 'Check constraint.';


CREATE UNIQUE INDEX OE.CUSTOMERS_PK ON OE.CUSTOMERS
(CUSTOMER_ID)
NOLOGGING
TABLESPACE EXAMPLE
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE INDEX OE.CUST_ACCOUNT_MANAGER_IX ON OE.CUSTOMERS
(ACCOUNT_MGR_ID)
NOLOGGING
TABLESPACE EXAMPLE
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE INDEX OE.CUST_EMAIL_IX ON OE.CUSTOMERS
(CUST_EMAIL)
NOLOGGING
TABLESPACE EXAMPLE
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE INDEX OE.CUST_LNAME_IX ON OE.CUSTOMERS
(CUST_LAST_NAME)
NOLOGGING
TABLESPACE EXAMPLE
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE INDEX OE.CUST_UPPER_NAME_IX ON OE.CUSTOMERS
(UPPER("CUST_LAST_NAME"), UPPER("CUST_FIRST_NAME"))
NOLOGGING
TABLESPACE EXAMPLE
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


ALTER TABLE OE.CUSTOMERS ADD (
  CONSTRAINT CUSTOMER_CREDIT_LIMIT_MAX
 CHECK (credit_limit <= 5000),
  CONSTRAINT CUSTOMER_ID_MIN
 CHECK (customer_id > 0),
  CONSTRAINT CUSTOMERS_PK
 PRIMARY KEY
 (CUSTOMER_ID)
    USING INDEX 
    TABLESPACE EXAMPLE
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                NEXT             1M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

ALTER TABLE OE.CUSTOMERS ADD (
  CONSTRAINT CUSTOMERS_ACCOUNT_MANAGER_FK 
 FOREIGN KEY (ACCOUNT_MGR_ID) 
 REFERENCES HR.EMPLOYEES (EMPLOYEE_ID)
    ON DELETE SET NULL);

GRANT SELECT ON OE.CUSTOMERS TO BI;

GRANT SELECT ON OE.CUSTOMERS TO PM;

[-droidcoffee-]

请问一下 2楼， oe 用户在 11g 中也存在么？

默认密码是啥？

谢谢

[ngx20080110]

沒試過，不好說，不過你可以用sys或system用戶修改它的密碼
alter user oe identified by your_password;

[-droidcoffee-]

果真不存在， 很感谢 nGX20080110

请问一下哪个版本的数据库里有 默认存在 oe 这个用户啊？

[ngx20080110]

10g我正在用，有oe用戶

[-droidcoffee-]

[Quote=引用 6 楼 ngx20080110 的回复:]

10g我正在用，有oe用戶
[/Quote]

哦， 知道了。 多谢。