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 ;
/