oracle常用操作

简单介绍oracle数据库的常用操作命令


//删除表空间JSPHOME
drop tablespace JSPHOME including contents and datafiles;

//创建表空间JSPHOME :
create tablespace JSPHOME
logging
datafile 'C:\oracle\product\10.2.0\oradata\orcl\JSPHOME.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local; 

//删除用户oracle:
DROP USER oracle CASCADE

//创建用户并指定表空间
create user oracle identified by pass
default tablespace JSPHOME ;

//授于oracle 用户dba,resource的权限
grant dba,resource to oracle;

//新建表TEST_PERSON，主键为code
ALTER TABLE TZZY.TEST_PERSON
DROP PRIMARY KEY CASCADE;
DROP TABLE TZZY.TEST_PERSON CASCADE CONSTRAINTS;
CREATE TABLE TZZY.TEST_PERSON
(
  CODE  VARCHAR2(20 BYTE),
  NAME  VARCHAR2(20 BYTE),
  AGE   INTEGER,
  DEPT  VARCHAR2(20 BYTE)
)
TABLESPACE TZZY
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

CREATE UNIQUE INDEX TZZY.TEST_PERSON_PK ON TZZY.TEST_PERSON
(CODE)
LOGGING
TABLESPACE TZZY
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


ALTER TABLE TZZY.TEST_PERSON ADD (
  CONSTRAINT TEST_PERSON_PK
PRIMARY KEY
(CODE)
    USING INDEX
    TABLESPACE TZZY
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

//新建表TEST_INFO，主键id自增，通过序列生成器和触发器来实现id自增
ALTER TABLE TZZY.TEST_INFO
DROP PRIMARY KEY CASCADE;

DROP TABLE TZZY.TEST_INFO CASCADE CONSTRAINTS;
CREATE TABLE TZZY.TEST_INFO
(
  ID        INTEGER                             NOT NULL,
  INFOTIME  DATE,
  INFODESC  VARCHAR2(200 BYTE),
  PERSONID  VARCHAR2(20 BYTE)
)
TABLESPACE TZZY
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

CREATE UNIQUE INDEX TZZY.TEST_INFO_PK ON TZZY.TEST_INFO
(ID)
LOGGING
TABLESPACE TZZY
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

//触发器TZZY.TEST_INFO_T 用来操作序列器TZZY.TEST_INFO_S实现id自增
CREATE OR REPLACE TRIGGER TZZY.TEST_INFO_T
BEFORE INSERT ON TZZY.TEST_INFO FOR EACH ROW
BEGIN
SELECT TZZY.TEST_INFO_S.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/

ALTER TABLE TZZY.TEST_INFO ADD (
  CONSTRAINT TEST_INFO_PK
PRIMARY KEY
(ID)
    USING INDEX
    TABLESPACE TZZY
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

//新增外键用来关联TEST_PERSON
ALTER TABLE TZZY.TEST_INFO ADD (
  CONSTRAINT FK_PERSON
FOREIGN KEY (PERSONID)
REFERENCES TZZY.TEST_PERSON
    ON DELETE CASCADE);

//新建序列器TZZY.TEST_INFO_S
DROP SEQUENCE TZZY.TEST_INFO_S;

CREATE SEQUENCE TZZY.TEST_INFO_S
  START WITH 1
  MAXVALUE 999999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;

//新建触发器TEST_PERSON_CLEAR_T，当主表TEST_PERSON删除时，与其关联的子表TEST_INFO 都将被删除

DROP TRIGGER TZZY.TEST_PERSON_CLEAR_T;
CREATE OR REPLACE TRIGGER TZZY.TEST_PERSON_CLEAR_T
AFTER DELETE ON TEST_PERSON
FOR EACH ROW
DECLARE PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   delete from TEST_INFO where PERSONID=:OLD.CODE;
   COMMIT;
END ;
/