01_TABLESPLACE

CREATE_TABLESPACE.BAT

echo off
echo "***** START CREATE_TABLESPACE *****"

REM ### SET ENVIRONMENTAL VARIABLE ###
SET USER_ID=system
SET PASSWORD=123456
SET INSTANCE_NAME=CHENGYU
SET NLS_LANG=American_America.AL32UTF8

REM SQLPLUS Start
SQLPLUS %USER_ID%/%PASSWORD%@%INSTANCE_NAME% @CREATE_TABLESPACE.SQL

echo "***** END CREATE_TABLESPACE *****"
PAUSE
EXIT

CREATE_TABLESPACE.SQL

SPOOL LOG\CREATE_TABLESPACE.log

DROP TABLESPACE CHENGYU_MAIN INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE CHENGYU_MAIN_IDX INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE CHENGYU_TEMP INCLUDING CONTENTS AND DATAFILES;

CREATE TABLESPACE CHENGYU_MAIN DATAFILE 'E:\DATABASE\ORACLE19.3\ORADATA\CHENGYU\CHENGYU_MAIN01.DBF' SIZE 128M AUTOEXTEND ON;
CREATE TABLESPACE CHENGYU_MAIN_IDX DATAFILE 'E:\DATABASE\ORACLE19.3\ORADATA\CHENGYU\CHENGYU_IDX01.DBF' SIZE 128M AUTOEXTEND ON;
CREATE TEMPORARY TABLESPACE CHENGYU_TEMP TEMPFILE 'E:\DATABASE\ORACLE19.3\ORADATA\CHENGYU\CHENGYU_TEMP01.DBF' SIZE 128M AUTOEXTEND ON;

SPOOL OFF
exit

查看表空间

SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size 
FROM dba_tablespaces t, dba_data_files d 
WHERE t.tablespace_name = d.tablespace_name 
GROUP BY t.tablespace_name;

02_USER

USER_INIT.BAT

echo off
echo "***** START USER_INIT *****"

REM ### SET ENVIRONMENTAL VARIABLE ###
SET USER_ID=SYSTEM
SET PASSWORD=123456
SET INSTANCE_NAME=CHENGYU

SET LOG_DIR=%CD%\LOG
SET NLS_LANG=American_America.AL32UTF8

REM SQLPLUS Start
SQLPLUS %USER_ID%/%PASSWORD%@%INSTANCE_NAME% @USER_INIT.SQL

echo "***** END USER_INIT *****"
PAUSE
EXIT

USER_INIT.SQL

SPOOL LOG\USERDROP.log

DROP USER "YOUYOU" CASCADE;

CREATE USER "YOUYOU" IDENTIFIED BY "123456" DEFAULT TABLESPACE "CHENGYU_MAIN" TEMPORARY TABLESPACE "CHENGYU_TEMP" ACCOUNT UNLOCK;
GRANT UNLIMITED TABLESPACE TO "YOUYOU";
GRANT "CONNECT" TO "YOUYOU";
GRANT "DBA" TO "YOUYOU";

SPOOL OFF
exit

03_DMP

（1）EXPDP、IMPDP – 推荐

EXPORT.BAT

echo "***** START EXPDP *****"
DATE /T
TIME /T

REM ### SET ENVIRONMENTAL VARIABLE ###
SET USER_ID=YOUYOU
SET PASSWORD=Aa135007
SET INSTANCE_NAME=CHENGYU

SET TEMP_DIR=%CD%\TEMP
SET LOG_NAME=EXP.LOG
SET DMP_FILE_NAME=EXP.DMP
SET NLS_LANG=American_America.AL32UTF8

REM ### CREATE DIRECTORY ###
SQLPLUS %USER_ID%/%PASSWORD% @CREATE_DIRECTORY.SQL %TEMP_DIR%
IF %ERRORLEVEL%==9 EXIT

MD %TEMP_DIR%

REM ### EXPORT ###
EXPDP %USER_ID%/%PASSWORD%@%INSTANCE_NAME% DIRECTORY=CHENGYU_DUMP DUMPFILE=%DMP_FILE_NAME% LOGFILE=%LOG_NAME%

MOVE %TEMP_DIR%\%LOG_NAME% LOG\%LOG_NAME%
MOVE %TEMP_DIR%\%DMP_FILE_NAME% %DMP_FILE_NAME%
RMDIR %TEMP_DIR%

REM ### DROP DIRECTORY ###
SQLPLUS %USER_ID%/%PASSWORD% @DROP_DIRECTORY.SQL

DATE /T
TIME /T
echo "***** END EXPDP *****"

PAUSE
EXIT

IMPORT.BAT

echo "***** START  Import *****"

REM ### SET ENVIRONMENTAL VARIABLE ###
SET USER_ID=YOUYOU
SET PASSWORD=123456
SET INSTANCE_NAME=CHENGYU

SET DMP_FILE_NAME=EXP.DMP
SET LOG_NAME=IMP.LOG
SET TEMP_DIR=%CD%\TEMP
SET NLS_LANG=American_America.AL32UTF8

MKDIR %TEMP_DIR%
MKDIR LOG

REM ### CREATE DIRECTORY ###
SQLPLUS %USER_ID%/%PASSWORD% @CREATE_DIRECTORY.sql %TEMP_DIR%
IF %ERRORLEVEL%==9 EXIT

REM ### IMPORT COF ###
MOVE %DMP_FILE_NAME% %TEMP_DIR%\%DMP_FILE_NAME%
IMPDP %USER_ID%/%PASSWORD%@%INSTANCE_NAME% DIRECTORY=CHENGYU_DUMP DUMPFILE=%DMP_FILE_NAME% LOGFILE=%LOG_NAME% EXCLUDE=statistics EXCLUDE=USER

MOVE %TEMP_DIR%\%DMP_FILE_NAME% %DMP_FILE_NAME%
MOVE %TEMP_DIR%\%LOG_NAME% LOG\%LOG_NAME%
RMDIR %TEMP_DIR%

REM ### DROP DIRECTORY ###
SQLPLUS %USER_ID%/%PASSWORD% @DROP_DIRECTORY.sql

CREATE_DIRECTORY.SQL

SPOOL LOG\CREATE_DIRECTORY.log

WHENEVER SQLERROR EXIT 9
CREATE OR REPLACE DIRECTORY CHENGYU_DUMP AS '&1';

SPOOL OFF
EXIT;

DROP_DIRECTORY.SQL

SET PAGES 0

SPOOL LOG\DROP_DIRECTORY.log
DROP DIRECTORY CHENGYU_DUMP;
SPOOL OFF

EXIT;

（2）EXP、IMP

--导出(全部)
exp system/123456@chengyu file="D:\temp\20220415.dmp"
--导出(指定用户)
exp system/123456@chengyu file="D:\temp\20220415.dmp"  owner=(YOUYOU)
--导出个别表
exp system/123456@chengyu tables=STUDENT,TEACHER file="D:\temp\20220415.dmp"

--导入
imp youyou/123456@chengyu file="D:\temp\20220415.dmp" full=y ignore=y;
imp youyou/123456@chengyu  file="D:\temp\20220415.dmp" tables=(table1)

（3）两者区别

在10g之前，传统的导出和导入分别使用EXP工具和IMP工具，
从10g开始，不仅保留了原有的EXP和IMP工具，还提供了数据泵导出导入工具EXPDP和IMPDP。

EXPDP、IMPDP 并行操作，速度快！
EXP和IMP是客户端工具程序，既可以在客户端使用，也可以在服务端使用
EXPDP和IMPDP是服务端的工具程序，只能在ORACLE服务端使用
EXPDP和IMPDP增加了新的Option

04_其他

test.sql

-- 输出日志
SPOOL rest.log

-- 系统用户
-- 用户名密码登陆指定数据库
CONNECT SYSTEM/123456@CHENGYU

-- 创建Profile（资源限制）
@CreateProfile.sql

-- 创建数据库管理员用户和普通用户
@CreateUser.sql

-- 系统用户
-- 用户名密码登陆指定数据库
CONNECT ADMINUSER/123456@CHENGYU

-- 建表
@CreateTable.sql

-- 建序列
@CreateSequence.sql

-- 创建存储过程
@CreateProdedure.sql

-- 创建触发器
@CreateTrigger.sql

-- 普通用户的表权限设定
@Grant.sql

SPOOL OFF

CreateProfile.sql

CREATE PROFILE USERROFILE LIMIT
    PASSWORD_LIFE_TIME UNLIMITED --同一密码無期限
    PASSWORD_GRACE_TIME UNLIMITED -- 数据库发出警告到登陆失效前的天数
    PASSWORD_REUSE_MAX UNLIMITED -- 多少次之内不许设置重复密码
    PASSWORD_REUSE_TIME UNLIMITED --密码不能重用前的天数
    PASSWORD_VERIFY_FUNCTION NULL --不使用密码验证功能
    FAILED_LOGIN_ATTEMPTS UNLIMITED --许尝试登陆的的最大次数
    PASSWORD_LOCK_TIME UNLIMITED; --登陆失败锁定天数

CreateUser.sql

-- 创建管理员
CREATE USER ADMINUSER PROFILE USERROFILE
    IDENTIFIED BY 123456 DEFAULT TABLESPACE CHENGYUTBS
    ACCOUNT UNLOCK;
GRANT ALTER ANY INDEX TO ADMINUSER;
GRANT ALTER ANY TABLE TO ADMINUSER;
GRANT CREATE ANY INDEX TO ADMINUSER;
GRANT CREATE ANY SYNONYM TO ADMINUSER;
GRANT CREATE ANY TABLE TO ADMINUSER;
GRANT CREATE ANY VIEW TO ADMINUSER;
GRANT DROP ANY INDEX TO ADMINUSER;
GRANT DROP ANY SYNONYM TO ADMINUSER;
GRANT DROP ANY TABLE TO ADMINUSER;
GRANT DROP ANY VIEW TO ADMINUSER;
GRANT GRANT ANY PRIVILEGE TO ADMINUSER;
GRANT UNLIMITED TABLESPACE TO ADMINUSER;
GRANT CONNECT TO ADMINUSER;
GRANT DBA TO ADMINUSER;

-- 创建普通用户
CREATE USER STAFFUSER  PROFILE USERROFILE
    IDENTIFIED BY 123456 DEFAULT TABLESPACE CHENGYUTBS
    ACCOUNT UNLOCK;
GRANT CREATE ANY TABLE TO STAFFUSER;
GRANT DROP ANY TABLE TO STAFFUSER;
GRANT UNLIMITED TABLESPACE TO STAFFUSER;
GRANT CONNECT TO STAFFUSER;

@CreateTable.sql

DROP TABLE CNAME_MANUAL CASCADE CONSTRAINTS;
DROP PUBLIC SYNONYM CNAME_MANUAL; -- SYNONYM 同义词删除

CREATE TABLE CNAME_MANUAL (
       MANUAL_ID            CHAR(6) NOT NULL,
       FILE_NAME            VARCHAR2(256 CHAR) NOT NULL,
       MANUAL_NM            VARCHAR2(256 CHAR) NOT NULL,
       UPD_TIMESTAMP        DATE NOT NULL,
       UPD_HOST_IP          VARCHAR2(36 CHAR) NOT NULL,
       UPD_USER_ID          VARCHAR2(20 CHAR) NOT NULL
)
       PCTFREE 10
       PCTUSED 75
       TABLESPACE CANO_DATA
;

CREATE UNIQUE INDEX XPKCNAME_MANUAL ON CNAME_MANUAL
(
       MANUAL_ID
)
       PCTFREE 5
       TABLESPACE CANO_INDEX
;

ALTER TABLE CNAME_MANUAL
       ADD  ( PRIMARY KEY (MANUAL_ID)
       USING INDEX
              PCTFREE 5
              TABLESPACE CANO_INDEX ) ;

CREATE PUBLIC SYNONYM CNAME_MANUAL FOR CNAME_MANUAL; -- 同义词创建

CreateSequence.sql

DROP SEQUENCE SEQ_SEQUENCE_ID;
DROP PUBLIC SYNONYM SEQ_SEQUENCE_ID;
CREATE SEQUENCE SEQ_SEQUENCE_ID INCREMENT BY 1 START WITH 0 MAXVALUE 9999999999 MINVALUE 0 CYCLE  NOCACHE;
CREATE PUBLIC SYNONYM SEQ_SEQUENCE_ID FOR SEQ_SEQUENCE_ID;

@Grant.sql

-- 表
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE1 TO STAFFUSER;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE2 TO STAFFUSER;
-- 序列
GRANT SELECT ON SEQ_SEQUENCE_ID TO STAFFUSER;

创建DBLink，访问不同数据库的表

CREATE PUBLIC DATABASE LINK "LINK_CHENGYU"
    CONNECT TO "STAFFUSER"
    IDENTIFIED BY "123456"
    USING 'CHENGYU';

原文链接：https://blog.csdn.net/chengyu0726/article/details/124197270