- 一、查询数据库执行性能
- 二、表空间信息查询
- 三、数据库用户操作
- 四、库表数据操作
- 五、数据库信息查询
- 六、Oracle更改数据文件位置
- 七、数据库导入导出
一、查询数据库执行性能
1、查出oracle当前的被锁对象
SELECT l.session_id sid,
s.serial#,
l.locked_mode, --锁模式
l.oracle_username, --登录用户
l.os_user_name, --登录机器用户名
s.machine, --机器名
s.terminal, --终端用户名
o.object_name, --被锁对象名
s.logon_time --登录数据库时间
FROM v$locked_object l, all_objects o, v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
ORDER BY sid, s.serial#;
2、杀掉被锁对象的对应进程进行解锁
alter system kill session '66,58333' --其中66是sid 58333是serial#
3、删除被锁对象进程失败时处理方式
删除时报错ORA-00031: session marked for kill的处理方法
SELECT P.SPID, S.SID, S.SERIAL#, S.USERNAME, S.PROGRAM
FROM V$PROCESS P, V$SESSION S
WHERE P.ADDR=S.PADDR AND S.STATUS='KILLED';
或搜索 查找出spid值
select spid, osuser, s.program
from v$session s, v$process p
where s.paddr = p.addr
and s.sid =66
在oralce机器上执行
kill –9 $SPID
4、各个客户端当前连接数
select count(*),program, machine,osuser from v$session group by program,machine,osuser having count(*) >1;
1）当前连接数
select count(0) from v$process;
2）当前会话数
select count(*) from v$session;
3）系统设置的最大连接数
select value from v$parameter where name='processes';
4）系统设置的最大会话数
select value from v$parameter where name='sessions';
5）系统当前活动的会话数
select count(*) from v$session where status='ACTIVE';
6）按用户名统计会话数
select username,count(username) from v$session where username is not null group by username;
7）修改最大连接数
alter system set processes=2000 scope=spfile;
8）修改最大会话数
alter system set sessions=2205 scope=spfile;
备注：连接和会话修改后必须重启才能生效，他们之间的关系 sessions=(1.1 * processes + 5)
9）查找目标用户的当前进程
select sid,serial# from v$session where username='CSBSYS' AND STATUS='ACTIVE';
断开连接
alter system kill session '35,54311';
备注：通过select 语句可能返回多行记录,所以要多次执行alter语句
5、性能耗时SQL
相关视图
v$sql，v$sqlarea
1）查看CPU消耗时间最多的前10条SQL语句
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.cpu_time desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;
2）查看总消耗时间最多的前10条SQL语句
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.elapsed_time desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;
3）查看消耗磁盘读取最多的前10条SQL语句
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.disk_reads desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;
4）需要大量缓冲读取（逻辑读）操作的查询
select buffer_gets, sql_text from (select sql_text, buffer_gets, dense_rank() over(order by buffer_gets desc) buffer_gets_rank from v$sql) where buffer_gets_rank <= 5;
5）列出使用频率最高的5个
select sql_text, executions from (select sql_text, executions, rank() over(order by executions desc)exec_rank from v$sql) where exec_rank <= 5;
5、查看数据库最近所有的操作
select * from v$sql order by last_active_time desc;
6、查看数据库日志路径
select * from v$logfile;
7、插入和查询性能提升方法
插入数据前删除索引，再执行插入，可大大提高插入效率；
查询数据需根据情况添加合适的索引。索引不是越多越好，越多占用空间大查询反而耗时长。
二、表空间信息查询
相关表
dba_data_files（表空间数据文件），dba_free_space（空闲空间），dba_segments
1、表空间总信息
1）查看表空间
select * from dba_tablespaces;
2）查看表空间利用率
SELECT a.tablespace_name "表空间名",
a.bytes / 1024 / 1024 "表空间大小(M)",
(a.bytes - b.bytes) / 1024 / 1024 "已使用空间(M)",
b.bytes / 1024 / 1024 "空闲空间(M)",
round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "使用比"
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;
3）查看空闲表空间
select tablespace_name,sum(bytes), sum(blocks) from dba_free_space group by tablespace_name
4）查看临时表空间文件大小
select * from dba_temp_files
5）删除表空间
drop tablespace TS_DACM_DATA;
6）删除表空间及数据文件
drop tablespace xxx including datafiles and contents
7）删除表空间或数据文件后释放磁盘空间
解决：删除数据库物理文件，磁盘空间不释放问题
数据库服务器执行命令，查询进程id，通过kill -9 spid杀掉进程即可释放
lsof |grep deleted
2、查看用户占用空间大小
1）查看用户对应的表空间
select distinct tablespace_name from DBA_SEGMENTS t where owner = 'CSBSYS';
2）查看表空间对应的数据文件
select * from dba_data_files where tablespace_name='CSBSYS_DW';
3）查看用户占用的空间大小
select sum(bytes / 1024 / 1024) "占用空间(M)" from SYS.DBA_SEGMENTS t where owner = 'CSBSYS';
4）查看用户各个表占用的空间大小
select segment_name,BYTES from SYS.DBA_SEGMENTS t where owner = 'CSBSYS' ORDER BY BYTES DESC;
5）查看表占用空间大小
select sum(bytes / 1024 / 1024) "占用空间(M)" from SYS.DBA_SEGMENTS t where owner = 'CSBSYS' AND SEGMENT_NAME = 'T_GA_ZA_RKXXGLXT_CZRK'
6）查看数据文件对应的表空间
查看永久表空间对应的数据文件
select TABLESPACE_NAME from dba_data_files where FILE_NAME='数据文件全路径';
查看临时表空间对应的数据文件
select TABLESPACE_NAME from dba_temp_files where FILE_NAME='数据文件全路径';
7）查看数据文件的使用情况
select
b.file_name 物理文件名,
b.tablespace_name 表空间,
b.bytes/1024/1024 大小M,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name;
3、查看undo表空间命令
sqlplus / as sysdba
show parameter undo_tablespace
1）undo表空间满的处理
a.创建新的undo表空间
create undo tablespace undotbs2 datafile '/opt/oracle/oradata/ge01/UNDOTBS2.dbf' size 100m autoextend on next 50m maxsize 1000m;
b.切换UNDO表空间为新的UNDO表空间
alter system set undo_tablespace=undotbs2 scope=both;
c.设置原来的undo表空间offline
alter database datafile '/opt/oracle/oradata/ge01/UNDOTBS1.dbf' offline;
d.删除原来的undo表空间和数据文件
drop tablespace undotbs1 including contents and datafiles;
4、修改表空间大小
1）给表空间增加数据文件
alter tablespace CSBSYS_DW add datafile '/u01/app/oracle/tablespace_sde/CSBSYS_SDE/SDE_CSBSYS_DW_08.dbf' SIZE 1024M AUTOEXTEND ON NEXT 1024M MAXSIZE 20480M ;
临时表空间时则将“datafile”改成“TEMPFILE ”，如下所示：
alter tablespace CSBSYS_TEMP add TEMPFILE '/u01/app/oracle/tablespace_sdc1/CSBSYS_SDC1/SDC1_CSBSYS_TEMP_01.dbf' SIZE 1024M AUTOEXTEND ON NEXT 1024M MAXSIZE 20480M ;
2）增加表空间数据文件大小
alter database datafile '/u01/app/oracle/tablespace/CSBSYS/CSBSYS_DW36.dbf' AUTOEXTEND ON NEXT 1024M MAXSIZE 20480M ;
临时表空间时则将“datafile”改成“tempfile ”，如下所示：
alter database tempfile ‘/u01/app/oracle/tablespace/CSBSYS/CSBSYS_TEMP.dbf’ AUTOEXTEND ON NEXT 1024M MAXSIZE 20480M ;
备注：若要修改成无限大则设置20480M改成unlimited，但不建议修改成无限，以免数据空间不足造成数据库异常。
3）缩小表空间数据文件大小
注意：存在空闲的表空间大小才可缩小
alter database datafile '/u01/app/oracle/tablespace/CSBSYS/CSBSYS_DW30.dbf' resize 6600m;
临时表空间时则将“datafile”改成“tempfile ”，如下所示：
alter database tempfile '/u01/app/oracle/tablespace/CSBSYS/CSBSYS_TEMP.dbf' resize 100M;
4）修改表空间数据大小
alter database datafile '/database/datafiles/oradata/ORCL/fdbl/PART_BL10.ORA' AUTOEXTEND ON NEXT 10M MAXSIZE 10240M;
5、创建表空间
1）初始化默认表空间
CREATE TABLESPACE CSBZHK_DW DATAFILE
'/u01/app/oracle/tablespace/csbzhk/CSBZHK_DW.dbf' SIZE 1024M AUTOEXTEND ON NEXT 10M MAXSIZE 2048M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;
2）初始化索引表空间
CREATE TABLESPACE CSBZHK_INDEX DATAFILE
'/u01/app/oracle/tablespace/csbzhk/CSBZHK_INDEX.dbf' SIZE 256M AUTOEXTEND ON NEXT 10M MAXSIZE 2048M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;
3）初始化大对象表空间
CREATE TABLESPACE CSBZHK_LOB DATAFILE
'/u01/app/oracle/tablespace/csbzhk/CSBZHK_LOB.dbf' SIZE 256M AUTOEXTEND ON NEXT 10M MAXSIZE 2048M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;
4）初始化临时表空间
CREATE TEMPORARY TABLESPACE CSBZHK_TEMP TEMPFILE
'/u01/app/oracle/tablespace/csbzhk/CSBZHK_TEMP.dbf' SIZE 256M AUTOEXTEND ON NEXT 10M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
5）初始化用户
create user CSBZHK
identified by dragon
default tablespace CSBZHK_DW
temporary tablespace CSBZHK_TEMP
profile DEFAULT;
-- Grant/Revoke role privileges
grant connect to CSBZHK;
grant resource to CSBZHK;
-- Grant/Revoke system privileges
grant select any sequence to CSBZHK;
grant select any table to CSBZHK;
grant create database link to CSBZHK;
grant create any synonym to CSBZHK;
grant create any sequence to CSBZHK;
grant create any view to CSBZHK;
grant create any procedure to CSBZHK;
grant create any table to CSBZHK;
grant create any trigger to CSBZHK;
grant unlimited tablespace to CSBZHK;
6）创建guest用户权限
create user GUEST
identified by guest
default tablespace USERS
temporary tablespace TEMP
profile DEFAULT;
grant connect to GUEST;
grant resource to GUEST;
grant SELECT ANY DICTIONARY to GUEST;
grant SELECT ANY TABLE to GUEST;
grant SELECT ANY TRANSACTION to GUEST;
grant SELECT ANY SEQUENCE to GUEST;
7）创建表空间
CREATE TABLESPACE CSBZHK_DW DATAFILE
'/u01/app/oracle/tablespace/csbzhk/CSBZHK_DW.dbf' SIZE 1024M AUTOEXTEND ON NEXT 10M MAXSIZE 3072M
三、数据库用户操作
1、查看数据库中的所有用户
select * from all_users;
1、删除数据库用户后重建
注意：删除数据库的时候要先断开数据库的连接
将test替换成相应的用户帐号
conn / as sysdba
drop user test cascade;
create user test identified by password;
conn user/password
2、用户权限授权
进入被授权的用户执行sql语句
grant all to user 所有表的权限都授予
grant alter on T_ALARM_RECORD to qbxxgl; 授权某个表的读权限给用户
grant insert on T_ALARM_RECORD to qbxxgl;授权某个表的写权限给用户
grant update on T_ALARM_RECORD to qbxxgl;授权某个表的更新权限给用户
3、修改用户名（慎重）
注意：修改后数据库表都丢失了，需使用DBA用户登录
select user#, name from user$ where name = 'ARWEN';
update user$ set name = 'WEIWENHP' where user# = 250;
4、修改用户密码
alter user arwen（用户名） identified by abc（密码）;
5、设置用户密码永不过期
（1）dba账户登录查看dba_users，确认用户使用的porfile，一般默认是default
SELECT username,PROFILE FROM dba_users;
（2）查看有效期
SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
（3）设置密码永不失效
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
6、用户锁住处理
（1） conn sys/oracle as sysdba
（2）select username,account_status from dba_users where username='xxx';，查看到用户锁了
（3）解锁xxx用户，SQL> alter user xxx account unlock;
（4）查看用户限制登陆次数
select * from dba_profiles where RESOURCE_NAME='FAILED_LOGIN_ATTEMPTS';
（5）alter profile default limit failed_login_attempts unlimited;修改用户无论输入多少次都不会被锁定
7、用户角色信息
1）角色查询
select * from role_sys_privs where role='角色名';
2）创建角色
create role role1;
3）角色授权
grant create any table,create procedure to role1;
4） 授予角色给用户
grant role1 to user1;
5） 角色生效
set role role1
6）查看用户生效的角色
select * from SESSION_ROLES
7）修改指定用户默认角色
alter user user1 default role role1;
8）删除角色
drop role role1；
9）查看用户角色
select * from dba_role_privs where grantee='用户名'
10)查看某个角色包括哪些系统权限
select * from dba_sys_privs where grantee='DBA'
11）查看oracle中所有的角色
select * from dba_roles;
8、DBA权限授权
grant dba to kettle
取消DBA授权
revoke dba from kettle,dw_fjzz,testzhk,csbzhk,dw_tj,csbsys（用户名称）
四、库表数据操作
1、连接不同的用户进行查询
连接同IP数据库是zdry_hn.t_ry_jbxx（用户名.表名），非同IP需创建DBLINK，使用@数据库名连接，如：t_ry_jbxx@dids
1）创建数据库连接
跨IP数据库连接
create database link DIDS
connect to DIDS identified by dragon
using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.2.56)(PORT = 1521)))
(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl)))';
2）创建同义词
create or replace synonym BM_ZDRY_LXBJ for info_henan.BM_ZDRY_LXBJ;
2、用户表结构操作
1）创建表结构
create table renyuan
(
id numeric(10),
renyuan_name varchar2(50),
zjhm varchar2(50)
)
2）增加表字段
alter table test add name varchar2(10);
3）删除表字段
alter table T_BS_GXR（表名） drop column TASK_ID_（字段）
4）删除表结构
drop table renyuan;
5）修改表字段长度
alter table <表名> alter column <字段名> 新类型名(长度)
alter table T_BS_RK_SWRY_CSB modify ryid varchar(32);
3、用户表数据操作（增删改查）
1）插入表数据
insert into sql_test(test1,test2) values(1,11);
2）删除表数据
delete from 表名 where 条件
注意：当删除数据量比较大时，可使用truncate，如下所示：
truncate table T_ZPXX_RESULT;
删除表数据并释放表空间
truncate table tablename DROP STORAGE;
3）更新表数据
1、编辑表数据行直接更新
update 表名set 列值1=dd,列值2=cc where 条件值
PL/SQL进入更新状态时，前面加ROWID即可直接操作，类似update的操作
SELECT T.*,ROWID FROM t_md_dataobject T WHERE T.DATAOBJECT_ID = 'VW_ZDRY_INDX_JXXX'
2、批量更新数据库中的uuid
注意：数据量大于1万时操作可能会出现无法响应，此时需要更改成其他处理方式，如ETL或者其他
begin
for i in 1 .. 148
loop
execute immediate
'update T_GA_XZ_XZXT_ASJ set id = (lower(sys_guid())) where id is null and rownum<=5000';
commit;
end loop;
end;
更新语句有单引号时需要再增加单引号进行转义操作，如下：
begin
for i in 1 .. 5836
loop
execute immediate
'update T_GA_ZA_RKXXGLXT_CZRK set rksj = to_char(sysdate,''yyyyMMddHH24miss'') where rksj is null and rownum<=10000';
commit;
end loop;
end;
3、更新数据来自其他表字段值
update fd_bl_bl b
set b.gzry1_sfzh =
(select t.username
from T_S_BASE_USER@FDBL_SC_XN t
WHERE t.id = b.GZRY1USERID
) where b.GZRY1_SFZH IS NULL and rownum <= 1
4）查询表数据
1、查询表名
select * from 表名
2、查询表数据量
select count(主键名) from user_tables;
3、查询数据库当前时间
select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;
4、查询rownum范围内的数据
select ajbh from (select a.*,rownum rc from T_GA_XZ_XZXT_ASJ a where rownum<=300000) b where rc>150000；
5、查询重复数据
select rid,count(*) from vw_fd_bl_ajryinfo_jmy group by rid having count(*)>1
6、SQL分组过滤，保证字段值不重复
select left_node_id,max(right_node_id) as right_node_id from T_GA_GX_RYXSAJ group by left_node_id
按org_id进行分组查询
select org_id,count(1) from T_S_USER_ORG group by org_id having count(1)=1
7、left join 从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行
查询出笔录关联的案件人员，并获取相关银行卡信息
select k.id,k.cjsj,k.gmsfzhm,k.hjdpcsmc,t.yhzh,t.yhmc from(select b.id,b.cjsj,r.gmsfzhm,r.hjdpcsmc from fd_bl_bl b,T_GA_ZA_RKXXGLXT_CZRK r where b.cyry1_id = r.rid and b.id not in (select bilu_id from fd_bl_goods_card) and rownum<=?1) k left join T_GA_YHZH t on t.gmsfzhm = k.gmsfzhm
8、查询日期字段范围数据
select * from T_GA_XZ_XZXT_ASJ t where rksj >= to_date(q'/2020-04-15 00:00:00/', 'yyyy-mm-dd hh24:mi:ss')
select to_char(t.lqsj,'YYYY-MM-DD HH24:MI:SS.FF3'),t.* from T_RECEIVE_LDBK010001 t
where lqsj >= to_timestamp('2020-06-01 15:50:04.0', 'yyyy-mm-dd hh24:mi:ss.ff')
9、分页查询
SELECT * FROM (SELECT a.*,ROW_NUMBER() OVER(ORDER BY gmsfzhm,rksj) p FROM T_GA_ZA_RKXXGLXT_CZRK a ) t WHERE t.p BETWEEN 1 AND 100
5）复制表（包括表结构和数据）
create table t_ry_zp2 as select * from t_ry_zp where sfzh='340102199412052024';
6）统计类SQL函数
1、求和函数
select sum(column_name) from table_name;
SELECT sum(高考分数) as 高考总分数 FROM [ts].[dbo].[学生表]
select SUM(SJL) from DATA_JLDPSJZK where DIC_SJFLALY='1' AND DIC_SHDW='ZNB' AND DIC_QGGSBH='35' and DIC_SJTJPL='31';
2、求某一列值的最小值
SELECT MIN(高考分数) as 高考最低分数 FROM [ts].[dbo].[学生表]
3、FIRST()返回指定字段的第一个记录的值
select fist(column_name) from table_name
4、求某一列值的最大值
select max(column_name) as 最高分 from table_name;
5、求平均值
select avg(column_name) from table_name;
6、统计字段的长度
length(字段名称)
7）常用函数
1、常用的日期函数
SYSDATE取数据库系统日期 SYSDATE+/-N 当前日期的N天 后/前 的日期 ADD_MONTHS(D,N)日期D加上N个月后的日期，N可以为负 LAST_DAY(D) 日期D所在的月的最后一天 NEXT_DAY(D,N)取得D下一个星期N的日期，N必须在1～7之间取值，且1表示星期日，2表示星期一，以此类推
例：select ADD_MONTHS(sysdate,1) from dual; 表示当前系统时间1个月后
2、转换函数
在日期和字符串之间转换 TO_CHAR(D[,’format’])：将DATE类型的D按照 format指定的格式转换为字符串 TO_DATE(S[,’format’])：将字符串S按照 format指定的格式转换为日期 如果不指定格式字符串，以系统默认的格式处理 如： Select to_char(sysdate,’YY-MM-DD HH24:MI:SS’) from dual Select to_date(‘2009-08-06’,’yyyy-mm-dd’) from dual
将varchar的日期转成所需格式：to_char(to_date(xt_zhxgsj,'yyyy-MM-dd hh24:mi:ss'),'yyyyMMddHH24MISS')
例varchar数据如下，转成20200725151702
3、字符函数
获取身份证号的前6位
select substr(sfzh,0,6) from T_TEMP_BS_RK_JNLK where sfzh='340102196509272089';
4、取余函数mod
select mod(substr(sfzh,17,1),2) from t_zdry_idx_jxxx_bg_jl where sfzh='350782199401160058'
5、获取最后一个逗号后面的字符串
substr(posid,instr(posid,',',-1)+1)
五、数据库信息查询
1、查询数据库字符集
select userenv('language') from dual;
2、查看数据库的版本
select * from v$version;
3、查看数据库安装路径
echo $ORACLE_HOME
六、Oracle更改数据文件位置
前言
在数据库运行、管理、维护维优过程中，有可能会碰到需要移动数据文件到其他硬盘或目录的情况。
删除或移动数据文件，都不像在操作系统中使用rm或cp命令那么简单。如果只是简单的rm或cp数据文件，那么数据库运行时会因为找不到正确的数据文件位置而报错。这是因为数据文件的位置和信息都被记录在控制文件中，rm或cp命令是不会也不可能更改控制文件记录的，这时必须通过alter操作去更改刷新数据库控制文件中数据文件的相关信息，以此确保数据库能够正常运行。
更改数据文件位置前，建议暂停所有针对此数据文件的数据操作。
方法一：offline表空间
适用范围：数据库处于open状态，表空间在offline的状态下才可更改。不能移动system表空间，undo表空间和temp表空间的数据文件，故此种方法更适合于做用户数据文件的迁移
1、offline表空间
alter tablespace CSBSYS_DW offline;
2、移动数据文件到新的位置
mv /u01/app/oracle/tablespace/CSBSYS/CSBSYS_DW1.dbf /u01/app/oracle/tablespace_sde/CSBSYS_SDE/
更改目录属性
chown oracle:oinstall /u01/app/oracle/tablespace_sde/CSBSYS_SDE/CSBSYS_DW1.dbf
3、rename修改表空间数据文件为新的位置，并修改控制文件
alter tablespace CSBSYS_DW rename datafile '/u01/app/oracle/tablespace/CSBSYS/CSBSYS_DW1.dbf' to '/u01/app/oracle/tablespace_sde/CSBSYS_SDE/CSBSYS_DW1.dbf';
4、online表空间
alter tablespace CSBSYS_DW online;
方法二：SQL修改数据文件位置
适用范围：可以移动任何表空间的数据文件，但其要求数据库必须处于mount状态，故此种方法更适合做整体数据库的迁移。
1、关闭数据库
shutdown immediate;
2、移动数据文件到新的位置
mv /u01/app/oracle/tablespace/CSBSYS/CSBSYS_DW1.dbf /u01/app/oracle/tablespace_sde/CSBSYS_SDE/
更改目录属性
chown oracle:oinstall /u01/app/oracle/tablespace_sde/CSBSYS_SDE/CSBSYS_DW1.dbf
3、启动数据库到mount状态
startup mount
4、通过SQL修改数据文件位置
alter database rename file '/u01/app/oracle/tablespace/CSBSYS/CSBSYS_DW1.dbf' to '/u01/app/oracle/tablespace_sde/CSBSYS_SDE/CSBSYS_DW1.dbf';
5、打开数据库
alter database open;
检查数据文件存放位置
select name from v$datafile;
select file_name,tablespace_name from dba_data_files where tablespace_name='CSBSYS_DW';
七、数据库导入导出
方法一：exp和imp（旧版本）
1、数据库导出需确保服务器端的字符集与数据库的字符集一致
修改服务器端的字符集方法：
cd ~
vi .bash_profile 按insert插入内容，增加语句
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
按esc退出，按:wq保存内容
source .bash_profile 执行生效
2、root用户创建可读写文件夹
创建文件夹datafile
mkdir /opt/datafile
设置文件夹datafile可读写
chmod 777 datafile
3、exp导出数据
切换oracle用户执行以下语句：su - oracle
exp 数据库用户名/密码@数据库名 file=文件路径 full=y
系统管理员导出多个用户数据：
nohup exp system/dragon@192.168.5.105:1521/orcl file=/opt/datafile/0126.dmp log=/opt/datafile/0126.log owner="(rygl_eeds,rygk_eeds)" &
DBA用户导出数据：nohup exp "sys/csbdb@orcl as sysdba " file=/opt/aspttybds.dmp log=/opt/aspttybds.log owner="(aspttybds)" &
导出指定表的数据：
exp username/password@服务名 tables=table1,table2 file=文件路径及文件名.dmp
exp导出条件查询的数据：
exp csbsys/dragon@192.168.0.13/orcl file = d:\m_hz_czrkjbxx.dmp tables=(m_hz_czrkjbxx) query = "where hh is not null"
导出指定表结构：
exp csbsys/dragon@192.168.0.13:1521/orcl file=d:\datadmp\csbsys_tableobject.dmp log=D:\datadmp\csbsys_tableobject.log statistics=none TABLES=(M_HZ_CZRKJBXX,M_HZ_HXX,M_WB_SWJL) rows=n
导出数据库用户的所有数据：
exp zdry_eeds/dragon@192.168.0.216/orcl file=D:\zdry_eeds.dmp log=D:\zdry_eeds.log
示例：
导出多张表：exp csbsys/Dragon#618@192.168.6.18:1521/orcl tables=JCDM_XB,JCDM_MZ,M_MH_LKCJXX file=XbMzMhdp.dmp
导出单张表：exp fdbl_sc_xn/dragon@192.168.5.247:1521/orcl tables=T_BL_BL file=tblbl.dmp
4、imp导入数据
①提前创建好要导入的数据库账号
②在本机上建立文件夹datafile并赋予读写权限
cd /opt/
mkdir datafile
chmod 777 datafile
③拷贝源数据库导出的dmp文件到目标数据库的服务器上
scp -P 22 -r root@192.168.5.107:/opt/datafile/0205.dmp /opt/datafile/
④切换至oracle用户导入数据
su - oracle
imp 数据库用户名/密码@数据库名 fromusr=来源数据库用户名 touser=导入库数据库名 file=文件路径 ignore=y
导入用户全部表
nohup imp system/dragon@192.168.5.51:1521/orcl file=/opt/datafile/0205.dmp log=/opt/datafile/0205.log fromuser="(info_henan)" touser="(info_zhaotong)" ignore=y &
导入具体表
imp fd_znbl40_zh_xn/dragon@10.201.8.123:1521/tsh1 file=/database/dmp/tblbl.dmp log=/database/dmp/tblbl.log fromuser=fdbl_sc_xn TABLES=T_BL_BL ignore=y
方法二：数据泵导入导出expdp和impdp（oracle10之后都有，效率高些）
按表导入
说明：expdp_dir指定dmp和log文件的路径，tables指定导入的表名（哪个用户哪张表）
impdp csbsys/dragon@orcl dumpfile=orcl_20200109.dmp logfile=imp_TAO.log directory=expdp_dir tables=CSBSYS.TAO
按用户导入
create or replace directory dir_dp as '/opt/datafile';
*Grant read,write on directory dir_dp to zftang;
SELECT privilege, directory_name, DIRECTORY_PATH FROM user_tab_privs t, all_directories d WHERE t.table_name(+) = d.directory_name ORDER BY 2, 1;
按表模式导出：
expdp zftang/zftang@fgisdb tables=zftang.b$i_exch_info,zftang.b$i_manhole_info dumpfile =expdp_test2.dmp logfile=expdp_test2.log directory=dir_dp job_name=my_job
按查询条件导出：
expdp zftang/zftang@fgisdb tables=zftang.b$i_exch_info dumpfile =expdp_test3.dmp logfile=expdp_test3.log directory=dir_dp job_name=my_job query='"where rownum<11"'
按表空间导出：
Expdp zftang/zftang@fgisdb dumpfile=expdp_tablespace.dmp tablespaces=GCOMM.DBF logfile=expdp_tablespace.log directory=dir_dp job_name=my_job
导出方案
expdp sys/dragon DIRECTORY=dir_dp DUMPFILE=23_120.dmp SCHEMAS=INFO_SX,QBXXGL_SHANXI_NEW,RYGL_SHANXI,RYGL_SX,SX_DP,SZXYTH_SHANXI,ZAJC_NM,ZDRY_SHANXI,ZDSJ_SHANXI,ZDS_SX
导出整个数据库：
expdp zftang/zftang@fgisdb dumpfile=full.dmp full=y logfile=full.log directory=dir_dp job_name=my_job
1、按表导入
p_street_area.dmp文件中的表，此文件是以gwm用户按schemas=gwm导出的：
impdp gwm/gwm@fgisdb dumpfile=p_street_area.dmp logfile=imp_p_street_area.log directory=dir_dp tables=p_street_area job_name=my_job
2、按用户导入（可以将用户信息直接导入，即如果用户信息不存在的情况下也可以直接导入）
impdp gwm/gwm@fgisdb schemas=gwm dumpfile =expdp_test.dmp logfile=expdp_test.log directory=dir_dp job_name=my_job
3、不通过expdp的步骤生成dmp文件而直接导入的方法：
--从源数据库中向目标数据库导入表p_street_area
impdp gwm/gwm directory=dir_dp NETWORK_LINK=igisdb tables=p_street_area logfile=p_street_area.log job_name=my_job
igisdb是目的数据库与源数据的链接名，dir_dp是目的数据库上的目录
4、更换表空间
采用remap_tablespace参数
--导出gwm用户下的所有数据
expdp system/orcl directory=data_pump_dir dumpfile=gwm.dmp SCHEMAS=gwm
注：如果是用sys用户导出的用户数据，包括用户创建、授权部分，用自身用户导出则不含这些内容
--以下是将gwm用户下的数据全部导入到表空间gcomm（原来为gmapdata表空间下）下
impdp system/orcl directory=data_pump_dir dumpfile=gwm.dmp remap_tablespace=gmapdata:gcomm
5、更换用户
impdp sys/dragon directory=dir_dp dumpfile=23_120.dmp logfile=23_120.log REMAP_SCHEMA=INFO_SX:INFO_SX,QBXXGL_SHANXI_NEW:QBXXGL_SHANXI,RYGL_SHANXI:RYGL_SHANXI,RYGL_SX:RYGL_SX,SX_DP:SX_DP,SZXYTH_SHANXI:SZXYTH_SHANXI,ZAJC_NM:ZAJC_NM,ZDRY_SHANXI:ZDRY_SHANXI,ZDSJ_SHANXI:ZDSJ_SHANXI,ZDS_SX:ZDS_SX
6、使用sys时，密码格式
expdp 'sys/oracle AS SYSDBA' schemas=system directory=DATA_PUMP_DIR dumpfile=system_%U.dmp parallel=2 job_name=jobexpdpimpdp
-- 在cmd下执行
SELECT 'exp ' || username || '/' || username || ' file=d:/' || username || '.dmp owner=' || username || ' log=d:/' ||
username || '.txt'
FROM dba_users
WHERE account_status = 'OPEN';
-- 在被导出库执行，获取语句后在导出库执行
SELECT 'create user ' || username ||' identified by ' || username || ' default tablespace ' || default_tablespace || ' temporary tablespace ' || temporary_tablespace || ';'
FROM dba_users
WHERE account_status = 'OPEN';
SELECT 'grant dba to ' || username || ';' FROM dba_users WHERE account_status = 'OPEN';
-- 在cmd下执行
SELECT 'imp ' || username || '/' || username || ' file=d:/' || username || '.dmp fromuser=' || username || ' touser=' ||
username || ' ignore=y log=d:/' || username || '_imp.txt'
FROM dba_users
WHERE account_status = 'OPEN';
导出和导入数据库版本存在差异时可修改dmp文件
示例：
sed -i '1s#11.02.00#10.02.01#' /database/dmp/tblbl.dmp