oracle表空间创建与数据库导入导出
来源:互联网 发布:微信裂变源码 编辑:程序博客网 时间:2024/06/05 14:15
1、创建表空间(---以实例名为“zyzl”的实例为例,一般ORACLE11gR2默认实例名为“hszl”---)
CREATE SMALLFILE TABLESPACE NNC_DATA01 DATAFILE '/u01/app/oracle/oradata/orcl/NNC_DATA01.dbf' SIZE 2048M
AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE NNC_DATA02 DATAFILE '/u01/app/oracle/oradata/orcl/NNC_DATA02.dbf' SIZE 2048M
AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE NNC_DATA03 DATAFILE '/u01/app/oracle/oradata/orcl/NNC_DATA03.dbf' SIZE 2048M
AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE NNC_INDEX01 DATAFILE 'D:\app\Administrator\virtual\oradata\orcl\orclpdb\NNC_INDEX01.dbf' SIZE 1024M
AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE NNC_INDEX02 DATAFILE '/u01/app/oracle/oradata/orcl/NNC_INDEX02.dbf' SIZE 1024M
AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE NNC_INDEX03 DATAFILE '/u01/app/oracle/oradata/orcl/NNC_INDEX03.dbf' SIZE 1024M
AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE EPRK_LOB01 DATAFILE '/u01/app/oracle/oradata/orcl/EPRK_LOB01.dbf' SIZE 1024M
AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE YLS_BLOB DATAFILE '/u01/app/oracle/oradata/orcl/YLS_BLOB.dbf' SIZE 1024M
AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
--------------------------------------------------------------------------------------------------
---以用户名为“hszl3”的用户为例---
2、创建用户hszl7,给用户授权(---以用户名为“hszl3”的用户为例---)
---创建用户---
create user cimc01 identified by cimc01 default tablespace NNC_DATA01 temporary tablespace temp;
grant connect,resource,dba to cimc01;
grant read,write on directory db to cimc01;
-----------------------------------------------------------------------
---删除用户---
select select sid,serial# from v$session where username=‘hxzl';
alter system kill session 'sid,serial'; ---删除进程---
-----------------------------------------------------------
3、创建数据文件目录并将目录授权与之前创建的用户hszl3(---以目录“D:\db_dir”为例---)
select db from dba_directories;
create directory db as 'D:\oracle';
grant read,write on directory db to zhongji_1205;
------------------------------------------------------------------------------------------------------
4、导入数据文件
impdp zhongji_1205/zhongji_1205@127.0.0.1/orclpdb directory=db dumpfile=uat_20171205.dmp remap_schema=cimc01:zhongji_1205
imp cimc01/cimc01@orclpdb file=D:\oracle\uat_20171205.dmp full=y;
-------------------------------------------------------------------------------------
5、创建strcat函数,执行《创建strcat函数.sql》,提交。
6、数据库备份:
expdp zjjz_01/zjjz_01@orcl directory=db dumpfile=zjjz_0120170628.dmp logfile=zjjz_0120170628.log job_name=zjjz_0120170628_job
exp aichannel/aichannel@TESTDB2 file= d:\data\newsmgnt.dmp
CREATE SMALLFILE TABLESPACE NNC_DATA01 DATAFILE '/u01/app/oracle/oradata/orcl/NNC_DATA01.dbf' SIZE 2048M
AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE NNC_DATA02 DATAFILE '/u01/app/oracle/oradata/orcl/NNC_DATA02.dbf' SIZE 2048M
AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE NNC_DATA03 DATAFILE '/u01/app/oracle/oradata/orcl/NNC_DATA03.dbf' SIZE 2048M
AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE NNC_INDEX01 DATAFILE 'D:\app\Administrator\virtual\oradata\orcl\orclpdb\NNC_INDEX01.dbf' SIZE 1024M
AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE NNC_INDEX02 DATAFILE '/u01/app/oracle/oradata/orcl/NNC_INDEX02.dbf' SIZE 1024M
AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE NNC_INDEX03 DATAFILE '/u01/app/oracle/oradata/orcl/NNC_INDEX03.dbf' SIZE 1024M
AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE EPRK_LOB01 DATAFILE '/u01/app/oracle/oradata/orcl/EPRK_LOB01.dbf' SIZE 1024M
AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE YLS_BLOB DATAFILE '/u01/app/oracle/oradata/orcl/YLS_BLOB.dbf' SIZE 1024M
AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
--------------------------------------------------------------------------------------------------
---以用户名为“hszl3”的用户为例---
2、创建用户hszl7,给用户授权(---以用户名为“hszl3”的用户为例---)
---创建用户---
create user cimc01 identified by cimc01 default tablespace NNC_DATA01 temporary tablespace temp;
grant connect,resource,dba to cimc01;
grant read,write on directory db to cimc01;
-----------------------------------------------------------------------
---删除用户---
select select sid,serial# from v$session where username=‘hxzl';
alter system kill session 'sid,serial'; ---删除进程---
-----------------------------------------------------------
3、创建数据文件目录并将目录授权与之前创建的用户hszl3(---以目录“D:\db_dir”为例---)
select db from dba_directories;
create directory db as 'D:\oracle';
grant read,write on directory db to zhongji_1205;
------------------------------------------------------------------------------------------------------
4、导入数据文件
impdp zhongji_1205/zhongji_1205@127.0.0.1/orclpdb directory=db dumpfile=uat_20171205.dmp remap_schema=cimc01:zhongji_1205
imp cimc01/cimc01@orclpdb file=D:\oracle\uat_20171205.dmp full=y;
-------------------------------------------------------------------------------------
5、创建strcat函数,执行《创建strcat函数.sql》,提交。
6、数据库备份:
expdp zjjz_01/zjjz_01@orcl directory=db dumpfile=zjjz_0120170628.dmp logfile=zjjz_0120170628.log job_name=zjjz_0120170628_job
exp aichannel/aichannel@TESTDB2 file= d:\data\newsmgnt.dmp
阅读全文
0 0
- oracle表空间创建与数据库导入导出
- Oracle 数据库创建表空间 创建表 导入/导出
- oracle创建表空间 用户 数据库导入和导出
- 【数据库】oracle创建表空间、用户及导入导出
- oracle数据库备份(导出)、删除表空间、创建表空间、数据库恢复(导入)
- Oracle创建用户、表空间、导入导出
- oracle导入导出 创建表空间
- oracle 导入导出、创建表空间
- oracle 创建数据库 表空间 用户 授权和toad导入导出数据库
- ORACLE表空间的导入与导出
- oracle创建表空间导入/出数据库
- Oracle创建表空间,备份导入数据库
- Oracle创建用户、创建表空间、导入导出
- ORACLE创建表空间、创建用户、导入、导出等
- Oracle创建删除用户、角色、表空间、导入导出数据库命令行方式总结
- Oracle创建删除用户、角色、表空间、导入导出数据库命令行方式总结
- 倪蒙oralce学习笔记之Oracle创建删除用户、角色、表空间、导入导出数据库总结
- Oracle创建删除用户、角色、表空间、导入导出数据库命令行方式总结
- Python小说爬虫
- 排序算法
- opengl 入门教程
- GitHub的注册与安装
- 线程模型
- oracle表空间创建与数据库导入导出
- 数据结构-栈和队列面试题(下)
- J2EE学习路线图(2015版)
- struts2_day04_13_struts2常用的标签_14_struts2表单标签(一)_15_struts2表单标签(二)
- 转换 HAL 模块
- 二维数组
- Bounding box regression详解
- 12月5日项目
- iOS开发(Swift)——图片选择器