ubuntu下oracle数据的导入,在ubuntu下将dmp数据导入Oracle的常用命令

0.导入文件很大时需要加一个undo表空间(如果不大，可跳过)

1.这一句是创建一个临时表空间，表空间的名称就是bigdata2temp

CREATE BIGFILE TEMPORARY TABLESPACE bigdata2temp TEMPFILE '/home/yanqilong/Oracle/oradata/orcldb/bigdata2temp.dbf' SIZE 512M AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

2.这一句是创建一个表空间，表空间的名称就是bigdata2

CREATE BIGFILE TABLESPACE bigdata2 DATAFILE '/home/yanqilong/Oracle/oradata/orcldb/bigdata2.dbf' SIZE 512M AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

3.创建用户并指定默认空间和临时表空间

create user BIGDATA2

(用户名) identified by bigdata2 (密码) default tablespace bigdata2 (默认表空间) temporary tablespace bigdata2temp(临时表空间);

4.这个是给 BIGDATA2用户授权会话

GRANT connect,exp_full_database,imp_full_database,resource to BIGDATA2;

5.给用户BIGDATA2授权读写

grant read,write on directory data_dir to BIGDATA2;

6.将导入或导出的文件放在这个目录下

create directory dump_dir as '/mnt/dev_sdc1/Data_Oracle';

7.impdp导入则用该语句：

impdp BIGDATA/mima_test dumpfile=c27-10.dmp logfile=impdp_BIGDATA.log schemas=BIGDATA directory=data_dir

8.imp语句则用该语句，注意：如果出现未知参数则删掉，原因是数据导出时未加

impdp并行导入大文件(100G以上)

impdp BIGDATA1/bigdata1 dumpfile=EXPLTD_%U.DMP logfile=impdp_BIGDATA11.log remap_schema=bigdata:bigdata1 directory=dump_dir parallel=4

imp导入文件

imp system/otacle buffer=409600 feedback=10000 full=y file=/home/Data_Oracle/01.dmp

至此就结束了，以下是常用命令：

9.查询dmp数据的用户名：

head -10 /home/yanqilong/Data_Oracle/dagux262.dmp |strings

10.导入文件很大时需要加一个undo表空间

alter tablespace UNDOTBS1 add datafile '/home/yanqilong/Oracle/oradata/orcldb/undotbs02.dbf' size 1000m autoextend on next 200m

11.删除用户

drop user 用户名 CASCADE;

12.删除表空间

drop tablespace 表空间名 including contents and datafiles;

13.查看表空间

SELECT df.tablespace_name, COUNT (*) datafile_count,

ROUND (SUM (df.BYTES) / 1048576) size_mb,

ROUND (SUM (free.BYTES) / 1048576, 2) free_mb,

ROUND (SUM (df.BYTES) / 1048576 - SUM (free.BYTES) / 1048576, 2 ) used_mb,

ROUND (MAX (free.maxbytes) / 1048576, 2) maxfree,

100 - ROUND (100.0 * SUM (free.BYTES) / SUM (df.BYTES), 2) pct_used,

ROUND (100.0 * SUM (free.BYTES) / SUM (df.BYTES), 2) pct_free

FROM dba_data_files df,

(SELECT tablespace_name, file_id, SUM (BYTES) BYTES, MAX (BYTES) maxbytes FROM dba_free_space GROUP BY tablespace_name, file_id

WHERE

df.tablespace_name = free.tablespace_name(+)

AND df.file_id = free.file_id(+)

GROUP BY df.tablespace_name ORDER BY 8

;