oracle数据库创建脚本

柔光的暖阳◎ 2022-05-26 09:57 411阅读 0赞

**oracle数据库创建脚本所需文件及文件内容，将各个文件放于同一文件夹，运行bat文件即可。**

**文件一：  01\_创建表空间和用户.sql**

CREATE TABLESPACE TS_APP
        LOGGING 
        DATAFILE '&1\TS_APP.ora' SIZE 100M 
        AUTOEXTEND 
        ON NEXT  100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL 
        SEGMENT SPACE MANAGEMENT  AUTO ;
    create user summer identified by summer default tablespace TS_APP;
    grant connect,resource,unlimited tablespace to summer;
    exit;

**文件二：  02\_创建基础表.sql**

/*==============================================================*/
    /* DBMS name:      ORACLE Version 11g                           */
    /* Created on:     2016/9/26 17:40:13                           */
    /*==============================================================*/
    
     
    
    /*==============================================================*/
    /* Table: SYS_ROLE                                              */
    /*==============================================================*/
    CREATE TABLE SYS_ROLE 
    (
       ID                   VARCHAR2(36)         NOT NULL,
       ROLE_NAME            VARCHAR2(32),
       REMARK               VARCHAR2(255),
       CREATE_USER          VARCHAR2(36),
       ROLE_CODE varchar2(50),
       CONSTRAINT PK_SYS_ROLE PRIMARY KEY (ID)
    );
    
    COMMENT ON TABLE SYS_ROLE IS
    '角色信息表';
    
    COMMENT ON COLUMN SYS_ROLE.ID IS
    '角色ID';
    
    COMMENT ON COLUMN SYS_ROLE.ROLE_NAME IS
    '角色名称';
    
    COMMENT ON COLUMN SYS_ROLE.REMARK IS
    '角色描述';
    
    COMMENT ON COLUMN SYS_ROLE.CREATE_USER IS
    '创建者ID';
    
    /*==============================================================*/
    /* Table: SYS_ROLE_USER                                         */
    /*==============================================================*/
    CREATE TABLE SYS_ROLE_USER 
    (
       ID                   VARCHAR2(36)         NOT NULL,
       ROLE_ID              VARCHAR2(36),
       USER_ID              VARCHAR2(36),
       CREATE_USER          VARCHAR2(36),
       CONSTRAINT PK_SYS_ROLE_USER PRIMARY KEY (ID)
    );
    
    COMMENT ON TABLE SYS_ROLE_USER IS
    '角色用户关联表';
    
    COMMENT ON COLUMN SYS_ROLE_USER.ID IS
    '标识ID';
    
    COMMENT ON COLUMN SYS_ROLE_USER.ROLE_ID IS
    '角色ID';
    
    COMMENT ON COLUMN SYS_ROLE_USER.USER_ID IS
    '用户ID';
    
    COMMENT ON COLUMN SYS_ROLE_USER.CREATE_USER IS
    '授权者ID';
    
    
    /*==============================================================*/
    /* Table: SYS_USER                                              */
    /*==============================================================*/
    CREATE TABLE SYS_USER 
    (
       ID                   VARCHAR2(36)         NOT NULL,
       USER_CODE            VARCHAR2(32),
       USER_NAME            VARCHAR2(32),
       USER_PWD             VARCHAR2(32),
       REAL_NAME            VARCHAR2(32),
       LOG_TIMES            INTEGER,
       LAST_TIME            DATE,
       DEPT_ID              VARCHAR2(36),
       STATUS               CHAR(1),
       CREATE_TIME          DATE,
       CREATE_USER          VARCHAR2(32),
       UPDATE_TIME          DATE,
       UPDATE_USER          VARCHAR2(32),
       ADMIN                CHAR(1),
       LOCKED               CHAR(1),
       CONSTRAINT PK_SYS_USER PRIMARY KEY (ID)
    );
    
    COMMENT ON TABLE SYS_USER IS
    '用户信息表';
    
    COMMENT ON COLUMN SYS_USER.ID IS
    '用户ID';
    
    COMMENT ON COLUMN SYS_USER.USER_CODE IS
    '用户编号';
    
    COMMENT ON COLUMN SYS_USER.USER_NAME IS
    '用户名';
    
    COMMENT ON COLUMN SYS_USER.USER_PWD IS
    '用户密码';
    
    COMMENT ON COLUMN SYS_USER.REAL_NAME IS
    '真实姓名';
    
    COMMENT ON COLUMN SYS_USER.LOG_TIMES IS
    '登录次数';
    
    COMMENT ON COLUMN SYS_USER.LAST_TIME IS
    '最后登录时间';
    
    COMMENT ON COLUMN SYS_USER.DEPT_ID IS
    '部门ID';
    
    COMMENT ON COLUMN SYS_USER.STATUS IS
    '用户状态';
    
    COMMENT ON COLUMN SYS_USER.CREATE_TIME IS
    '创建时间';
    
    COMMENT ON COLUMN SYS_USER.CREATE_USER IS
    '创建人';
    
    COMMENT ON COLUMN SYS_USER.UPDATE_TIME IS
    '更新时间';
    
    COMMENT ON COLUMN SYS_USER.UPDATE_USER IS
    '更新人';
    
    
    
    exit;

**文件三：  03\_创建基础数据.sql    **

-- 初始化用户表
    INSERT INTO sys_user (id, user_code, user_name, user_pwd, real_name, log_times, last_time, dept_id, status, create_time, create_user, update_time, update_user, admin, locked) VALUES ('1', 'admin', 'admin', 'e10adc3949ba59abbe56e057f20f883e', '系统管理员', 0, NULL, NULL, '1', NULL, 'fff', NULL, '系统管理员', '1', '0');
    commit;
    exit;

**文件四：  04\_删除表空间和用户.sql**

DROP TABLESPACE TS_APP INCLUDING CONTENTS AND DATAFILES;
    drop user  summer cascade;
    commit;
    exit;

**文件五：  05\_删除基础表.sql**

drop table SYS_ROLE cascade constraints;
    
    drop table SYS_ROLE_USER cascade constraints;
    
    drop table SYS_USER cascade constraints;
    
    commit;
    exit;

**文件六：  数据库初始化脚本.bat**

set createtablespace=@"%~dp001_创建表空间和用户.sql"
    set createtable=@"%~dp002_创建基础表.sql"
    set initdata=@"%~dp003_创建基础数据.sql"
    set droptablespace=@"%~dp004_删除表空间和用户.sql"
    set droptable=@"%~dp005_删除基础表.sql"
    set logTime=%DATE:~0,4%%DATE:~5,2%%DATE:~8,2%%TIME:~0,2%%TIME:~3,2%%TIME:~6,2%
    set logFile=%logTime%.log
    @echo off
    
    title Summer 's ORACLE数据库初始化脚本
    
    :menu
    cls
    color 0A
    
    :cho
    echo.
    echo.
    echo *********请选择进行操作的编号，按下回车*********
    echo A  初始化ORACLE版本            B  重置
    echo ==============================================
    set /p choice=          请选择:
    if /i "%choice%"=="A"  goto create
    if /i "%choice%"=="B"  goto drop
    echo 选择无效，请重新输入
    echo.
    goto cho
    
    :create
    set /p location=请输入表空间存储路径：
    md %location%
    @echo off
    set /p user=请输入数据库管理员账号：
    set /p pass=请输入数据库管理员密码：
    set /p instance=请输入数据库客户端服务名：
    set /p continue=请确认以上信息输入正确，是否继续Y/N:
    if /i "%continue%"=="n" goto cho
    sqlplus user_sys/user_sys@%instance% as sysdba %createtablespace% '%location%' 
    sqlplus user_sys/user_sys@%instance%  %createtable% '%location%' 
    sqlplus user_sys/user_sys@%instance%  %initdata% '%location%' 
    goto cho
    pause
     
     
    :drop
    set /p user=请输入数据库管理员账号：
    set /p pass=请输入数据库管理员密码：
    set /p instance=请输入数据库实例名：
    set /p continue=请确认以上信息输入正确，是否继续Y/N:
    if /i "%continue%"=="n" goto cho
    sqlplus %user%/%pass%@%instance% as sysdba %droptablespace% 'location'
    echo 表空间和用户删除成功
    if errorlevel==1 then
    echo %errorlevel%
    goto cho
    pause
     
    
    
    
    :quit
    exit