ORACLE 12C 生产脚本规范

2017-01-05 11:30:35来源:CSDN作者:baidu_36415076人点击


-------创建kdbase-----------------------------------------sqlplus / as sysdba;alter session set container=PDBORCL;  drop user ktest cascade;drop tablespace TS_KTEST including contents and datafiles cascade constraints;--2.创建表空间create tablespace TS_KTEST datafile 'C:/app/oracle/oradata/orcl/ktest.dbf' size 32M autoextend on next 100M maxsize 15000M extent management local;--3.重启OracleServiceOrcl服务exitlsnrctl stopsqlplus / as sysdbaalter session set container=PDBORCL; shu immediatestartupexitlsnrctl startsqlplus / as sysdbaalter session set container=PDBORCL; --4.创建数据库用户create user ktest identified by ktest default tablespace TS_KTEST;GRANT   CREATE SESSION, CREATE ANY TABLE, CREATE ANY VIEW ,CREATE ANY INDEX, CREATE ANY PROCEDURE,  ALTER ANY TABLE, ALTER ANY PROCEDURE,  DROP ANY TABLE, DROP ANY VIEW, DROP ANY INDEX, DROP ANY PROCEDURE,  SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE  TO ktest;GRANT connect,resource TO ktest;alter user ktest quota unlimited on TS_KTEST;--解决PLSQL无法连接的问题grant dba to ktest;  



--存在就删除函数(函数中使用execute immediate不能执行DDL语句删除表结构。)declare tb_num number :=0;BEGIN  select count(1) into tb_num from user_tables where table_name = upper('TABLE_NAME') ;    if tb_num > 0 then        execute immediate 'drop table TABLE_NAME cascade constraints';    end if;END;--创建表create table TABLE_NAME (   RECORD_SNO           INTEGER     default SEQ_TABLE_NAME.NEXTVAL ,   CUST_CODE            VARCHAR2(32),   YY_CODE             VARCHAR2(32),   UP_DATE              VARCHAR2(10),   CREATE_DATE          VARCHAR2(10),   CREATE_TIME          VARCHAR2(10),   CREATE_STA           VARCHAR2(1),   REMARK               VARCHAR2(256),   constraint PK_TABLE_NAME primary key (RECORD_SNO));comment on table TABLE_NAME is'一码通任务流水表';comment on column TABLE_NAME.RECORD_SNO is'记录号';comment on column TABLE_NAME.CUST_CODE is'客户代码';comment on column TABLE_NAME.YY_CODE is'测试代码';comment on column TABLE_NAME.UP_DATE is'更新日期';comment on column TABLE_NAME.CREATE_DATE is'生成日期';comment on column TABLE_NAME.CREATE_TIME is'生成时间';comment on column TABLE_NAME.CREATE_STA is'生成状态';comment on column TABLE_NAME.REMARK is'备注';


--BEGIN END之间用execute immediate 执行DDL语句。declare sq_num number :=0;BEGIN    select count(1) into sq_num from user_sequences where sequence_name = upper('SEQ_NAME') ;    if sq_num > 0 then        execute immediate 'drop sequence SEQ_NAME';    end if;END;/create sequence SEQ_NAMEminvalue 1maxvalue 9999999999999999999999999999start with 100000increment by 1cache 20;