1. linux下创建oracle表空间
/*linux下登陆sqlplus*/
sqlplus / as sysdba
--或者
sqlplus
用户名:sys
口令:change_on_install as sysdba
/*创建临时表空间 test_temp */
CREATE TEMPORARY TABLESPACE test_temp
tempfile '/home/oracle/tablespace/test_temp.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
/*创建数据表空间 test*/
create tablespace test
logging
datafile '/home/oracle/tablespace/test.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
/*删除表空间test*/
DROP TABLESPACE test INCLUDING CONTENTS AND DATAFILES;
/*增加表空间大小20G*/
alter tablespace test add datafile '/home/oracle/tablespace/test_add.dbf' size 20480m;
2. linux下创建oracle表空间的用户(同上,在sqlplus内操作)
/*查询所有用户*/
select * from all_users;
/*创建用户*/
CREATE USER 用户名 IDENTIFIED BY 密码
default tablespace test
temporary tablespace test_temp ;
/*用户权限*/
GRANT CONNECT,RESOURCE,DBA TO 用户名;
/*删除用户*/
drop user 用户名 cascade;
/*查看用户对应表空间*/
SELECT username AS 数据库用户名,default_tablespace AS 默认表空间 FROM dba_users;
/*退出sqlplus*/
quit
3.导出dmp文件
/*在linux下 exp dmp*/
exp 用户名/密码@10.141.234.135:1521/orcl file=/home/oracle/oracleBackup/20180728.dmp owner=用户名 log=/home/oracle/oracleBackup/20180728.log
4.导入dmp文件
/*在linux下 imp dmp*/
imp 用户名/密码@192.168.90.36:1521/orcl file=/home/oracle/backup/test.dmp full=y ignore=y log=/home/oracle/backup/imp.log
5.表字段注释中文乱码问题
在exp和imp之前都执行下面的命令
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
6.压缩zip
zip -m 目录/压缩包.zip 目录/file1.dmp 目录/file2.log
zip -m /home/oracle/oracleBackup/test.zip /home/oracle/oracleBackup/test.dmp /home/oracle/oracleBackup/test.log