Oracle导入导出及基础操作
1、imp，exp 基本用法 1）导出具体用实例 exp 导出用户/密码 file=导出路径/导出文件名.dmp owner=实例1,实例2...... 2）导入具体实例 imp system/*** file=/root/***/***.dmp fromuser=** touser=** log=dible_db ignore=n; 3）导出具体表 exp ***/*** file=/***/***/***.dmp tables=(tablename1,tablename2,tablename3) 4）导入具体表 imp ***/*** file=/***/***/***.dmp fromuser=*** tables=(tablename1,tablename2,tablename3) log=dible_db ignore=n;
参数：rows=n 控制只导入/导出表结构 exp导出有空表无法导出问题，甚至有数据的表也无法导出，百度得知，使用语句： select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0; 查询出所有空数据表设置segment。亲测，还是有问题。 观察得知，在user_tables视图中，所有可以导出的表segment_created属性均为YES，于是修改上述语句为： select 'alter table '||table_name||' allocate extent;' from user_tables where segment_created = 'NO'; 再对查询结果进行处理，可以导出所有表。 若一个数据库软件上有多个实例，在连接和导入导出时需指定完整的IP及SID： sqlplus username/password@host:1521/sid imp username/password@host:1521/sid
FULL=Y
2、impdb,expdb数据泵方式用法 1）导出指定方案 expdp uname/pword@localhost/pdborcl directory=DUMP_DIR dumpfile=***.dmp version='11.2.0.4.0' logfile=***.log schemas=***,***; 备注：directory参数指定逻辑路径，需预先在用户下创建，创建方式： create or replace directory DUMP_DIR as '/tmp'; 集群方式不好处理。 tables=表1,表2 //此参数与schemas参数不可同时出现 GRANT read, write ON DIRECTORY DUMP_DIR TO XXX//逻辑目录授权 密码中特殊字符处理方式：expdp 'username/"pwd@test"' directory=DATA_PUMP_DIR dumpfile=username.dmp logfile=username.log 2）导入具体方法 impdp ***/***directory=DUMP_DIR dumpfile=***.DMP logfile=***.log remap_schema=导出方案名1: 导入方案名1,导出方案名2:导入方案名2 transform=segment_attributes:n TABLE_EXISTS_ACTION=REPLACE; 备注：TABLE_EXISTS_ACTION 参数指定若有同名表直接替换DDL 1) skip：默认操作 2) replace：先drop表，然后创建表，最后插入数据 3) append：在原来数据的基础上增加数据 3.1 DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS 控制忽略违反约束数据 4) truncate：先truncate，然后再插入数据 参数：CONTENT=METADATA_ONLY控制只导入/导出表结构。 CONTENT={ALL | DATA_ONLY | METADATA_ONLY} 导入指定表： tables=schema1.table1,schema1.table2 remap_schema=schema1:schema2
3、其他基本操作 1）检测死锁
select object_name, machine, s.sid, s.serial#
from v$locked_object l, dba_objects o, v$session s
where l.object_id = o.object_id
and l.session_id = s.sid;
/*2.下面的语句用来杀死一个进程：
www.2cto.com */
alter system kill session '139,1274'; /* (其中24,111分别是上面查询出的sid,serial#) */
/*可以用如下查询批量得到上面类似的语句： */
select 'alter system kill session ''' || s.sid || ',' || s.serial# ||
'''; '
from v$locked_object l, dba_objects o, v$session s
where l.object_id = o.object_id
and l.session_id = s.sid;
/*3.如果利用上面的命令杀死一个进程后，进程状态被置为 "killed", 但是锁定的资源很长时间没有被释放，那么可以在os一级再杀死相应的进程(线程)，首先获得进程(线程)号： */
select spid, osuser, s.program
from v$session s, v$process p
where s.paddr = p.addr
and s.sid = #sid; /*(#sid是上面的sid) */
- 追踪当前执行sql select /*+no_unnest*/'alter system kill session'''|| s.sid||','||s.SERIAL# ||''' immediate;',p.SPID,q.ROWS_PROCESSED,s.USERNAME,s.EVENT,s.LAST_CALL_ET,s.LOCKWAIT,s.STATUS,q.SQL_FULLTEXT,q.SQL_ID,s.BLOCKING_SESSION from v$session s,v$sql q,v$process p where s.sid >1 and s.PADDR=p.ADDR and q.sql_id(+)=s.sql_id and s.username is not null --and s.status='ACTIVE' and s.event not like '%SQL*Net message from client%' --and last_call_et>1 order by last_call_et desc,sid; 流程说明： select object_name,machine,s.sid,s.serial# from v$locked_object l,dba_objects o ,v$session s where l.object_id = o.object_id and l.session_id=s.sid; a、select * from v$locked_object; 查出被锁定的对象，object_id是对象的ID,session_id是被锁定对象的session ID； b、select object_name, object_type from dba_objects where object_id = 刚才查出来的object_id; 查询被锁定对象的名字。 c、select sid, serial#, machine, program from v$session where sid =第1步中查出来的session_id; d、alter system kill session 'sid,serial#';用来杀死这个会话；
2）初始化表空间及用户 --查看Oracle版本及位数 select * from v$version --创建表空间 create tablespace ***_data datafile 'F:\data\***_data.dbf' size 500m autoextend on next 10m maxsize unlimited; --创建索引表空间 create tablespace ***_index datafile 'F:\data\***_index.dbf' size 500m autoextend on next 10m maxsize unlimited; --创建用户并指定表空间 create user *** identified by *** default tablespace ***_data; --用户授权dba grant dba to ***; --修改用户密码,重启生效 alter user sys identified by sys123； --创建逻辑目录 create or replace directory DUMP_DIR1 as 'F:\data'; --查询已创建逻辑目录 select * from dba_directories; --查询表空间物理文件路径 select * from dba_data_files; -- 查询数据库名： select name,dbid from v$database;或者show parameter db_name; -- 查询实例名： select instance_name from v$instance;或者show parameter instance_name; -- 修改用户默认表空间 alter user user default tablespace tablespaceName -- 查看用户表空间 user_users 或者 dba_users
3）清表 drop user user_name cascade; 此操作会删除用户及用户下的所有表，在非当前dba用户下执行，若有session连接，无法删除，需先kill session kill session方式 --查看当前连接用户 select username,sid,serial# from v$session; --找到要删除用户的sid,和serial，并删除 alter system kill session'532,4562' ; 若无权限，也可执行select 'drop table '||table_name||';' from cat where table_type='TABLE'语句，复制sql至文本执行。 cat系统视图。
4）权限一览 连接权限 grant connect,resource to 用户名; --创建session的权限，即登陆权限 grant create session to TEST; --表空间权限 grant unlimited session to TEST; --创建表权限 grant create table to TEST; --删除表权限 grant drop table to TEST; --删除表权限 grant insert table to TEST; --修改表权限 grant update table to TEST; --授予所有权限(all)给所有用户(public) grant all to public; --授予用户查看指定表的权限 grant select on tablename to TEST; --授予删除表的权限 grant drop on tablename to TEST; --授予插入的权限 grant insert on tablename to TEST; --授予修改表的权限 grant update on tablename to TEST; --授予对指定表特定字段的插入和修改权限，只能是insert和update grant insert(id) on tablename to TEST; grant update(id) on tablename to TEST;
4）解除用户锁定 --dba权限用户登入 sqlplus / as sysdba; --解除用户锁定 alter user dbt1011 account unlock;
5）重置索引 alter index PK_UQC_MRL_TEMPLATE rebuild;
6）session 与 process 连接管理参数 --当前session数 select count(*) from v$session; --数据库参数 show parameter processes; show parameter sessions; 备注：旧版本关系 sessions = 1.1*processes + 5 --修改方式-重启生效 alter system set processes = 300 scope = spfile; alter system set sessions = 335 scope = spfile; 备注：测试效果：修改 processes后sessions自动调整
7）字符集 查看 i)服务端字符集：select userenv('language') from dual; select * from nls_database_parameters; dmp文件字符集： ii)用oracle的exp工具导出的dmp文件也包含了字符集信息，dmp文件的第2和第3个字节记录了dmp文件的字符集。如果dmp文件不大，比如只有 几M或几十M，可以用UltraEdit打开(16进制方式)，看第2第3个字节的内容，如0354，然后用以下SQL查出它对应的字符集: cat exp.dmp |od -x|head -1|awk '{print $2 $3}'|cut -c 3-6 SQL> select nls_charset_name(to_number('0354','xxxx')) from dual; iii)客户端字符集 windows set nls_lang=AMERICAN_AMERICA.ZHS16GBK linux $echo $NLS_LANG select * from nls_instance_parameters; 修改： 服务端不要改，客户端 改环境变量
8）表空间扩容，转至： https://www.cnblogs.com/jianshuai520/p/9766970.html --查看表空间的名字及文件所在位置 select tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) total_space from sys.dba_data_files order by tablespace_name
--查询表空间信息 select username,default_tablespace,t.* from dba_users t
--查询当前表空间下使用情况 select a.tablespace_name, a.bytes / 1024 / 1024 "sum MB", (a.bytes - b.bytes) / 1024 / 1024 "used MB", b.bytes / 1024 / 1024 "free MB", round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "used%" from (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes, max(bytes) largest from dba_free_space group by tablespace_name) b where a.tablespace_name = b.tablespace_name order by ((a.bytes - b.bytes) / a.bytes) desc;
-扩展 alter database datafile '表空间位置' resize 新的尺寸 alter database datafile 'D:\APP\ADMINISTRATOR\ORADATA\XYSHARE\SGSHARE.DBF' resize 4096m
9)查看日志文件地址 showparameterdump_dest 监听日志： 监听日志listener.log日志放在了$ORACLE_BASE/diag/tnslsnr/机器名/listener/trace目录下
