Oracle数据库常用操作

目录

• 一、查询数据库执行性能
  • 1、查出oracle当前的被锁对象
  • 2、杀掉被锁对象的对应进程进行解锁
  • 3、删除被锁对象进程失败时处理方式
  • 4、各个客户端当前连接数
    • 1）当前连接数
    • 2）当前会话数
    • 3）系统设置的最大连接数
    • 4）系统设置的最大会话数
    • 5）系统当前活动的会话数
    • 6）按用户名统计会话数
    • 7）修改最大连接数
    • 8）修改最大会话数
    • 9）查找目标用户的当前进程
  • 5、性能耗时SQL
    • 相关视图
    • 1）查看CPU消耗时间最多的前10条SQL语句
    • 2）查看总消耗时间最多的前10条SQL语句
    • 3）查看消耗磁盘读取最多的前10条SQL语句
    • 4）需要大量缓冲读取（逻辑读）操作的查询
    • 5）列出使用频率最高的5个
  • 5、查看数据库最近所有的操作
  • 6、查看数据库日志路径
  • 7、插入和查询性能提升方法
• 二、表空间信息查询
  • 相关表
  • 1、表空间总信息
    • 1）查看表空间
    • 2）查看表空间利用率
    • 3）查看空闲表空间
    • 4）查看临时表空间文件大小
    • 5）删除表空间
    • 6）删除表空间及数据文件
    • 7）删除表空间或数据文件后释放磁盘空间
  • 2、查看用户占用空间大小
    • 1）查看用户对应的表空间
    • 2）查看表空间对应的数据文件
    • 3）查看用户占用的空间大小
    • 4）查看用户各个表占用的空间大小
    • 5）查看表占用空间大小
    • 6）查看数据文件对应的表空间
    • 7）查看数据文件的使用情况
  • 3、查看undo表空间命令
    • 1）undo表空间满的处理
  • 4、修改表空间大小
    • 1）给表空间增加数据文件
    • 2）增加表空间数据文件大小
    • 3）缩小表空间数据文件大小
    • 4）修改表空间数据大小
  • 5、创建表空间
    • 1）初始化默认表空间
    • 2）初始化索引表空间
    • 3）初始化大对象表空间
    • 4）初始化临时表空间
    • 5）初始化用户
    • 6）创建guest用户权限
    • 7）创建表空间
• 三、数据库用户操作
  • 1、查看数据库中的所有用户
  • 2、用户权限授权
  • 3、修改用户名（慎重）
  • 4、修改用户密码
  • 5、设置用户密码永不过期
  • 6、用户锁住处理
  • 7、用户角色信息
    • 1）角色查询
    • 2）创建角色
    • 3）角色授权
    • 4） 授予角色给用户
    • 5） 角色生效
    • 6）查看用户生效的角色
    • 7）修改指定用户默认角色
    • 8）删除角色
    • 9）查看用户角色
    • 10)查看某个角色包括哪些系统权限
    • 11）查看oracle中所有的角色
  • 8、DBA权限授权
• 四、库表数据操作
  • 1、连接不同的用户进行查询
    • 1）创建数据库连接
    • 2）创建同义词
  • 2、用户表结构操作
    • 1）创建表结构
    • 2）增加表字段
    • 3）删除表字段
    • 4）删除表结构
    • 5）修改表字段长度
  • 3、用户表数据操作（增删改查）
    • 1）插入表数据
    • 2）删除表数据
    • 3）更新表数据
    • 1、编辑表数据行直接更新
      • 2、批量更新数据库中的uuid
      • 3、更新数据来自其他表字段值
    • 4）查询表数据
      • 1、查询表名
      • 2、查询表数据量
      • 3、查询数据库当前时间
      • 4、查询rownum范围内的数据
      • 5、查询重复数据
      • 6、SQL分组过滤，保证字段值不重复
      • 7、left join 从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行
      • 8、查询日期字段范围数据
      • 9、分页查询
    • 5）复制表（包括表结构和数据）
    • 6）统计类SQL函数
      • 1、求和函数
      • 2、求某一列值的最小值
      • 3、FIRST()返回指定字段的第一个记录的值
      • 4、求某一列值的最大值
      • 5、求平均值
      • 6、统计字段的长度
    • 7）常用函数
      • 1、常用的日期函数
      • 2、转换函数
      • 3、字符函数
      • 4、取余函数mod
      • 5、获取最后一个逗号后面的字符串
• 五、数据库信息查询
  • 1、查询数据库字符集
  • 2、查看数据库的版本
  • 3、查看数据库安装路径
• 六、Oracle更改数据文件位置
  • 前言
  • 方法一：offline表空间
    • 1、offline表空间
    • 2、移动数据文件到新的位置
    • 3、rename修改表空间数据文件为新的位置，并修改控制文件
    • 4、online表空间
  • 方法二：SQL修改数据文件位置
    • 1、关闭数据库
    • 2、移动数据文件到新的位置
    • 3、启动数据库到mount状态
    • 4、通过SQL修改数据文件位置
    • 5、打开数据库
• 七、数据库导入导出
  • 方法一：exp和imp（旧版本）
    • 1、数据库导出需确保服务器端的字符集与数据库的字符集一致
    • 2、root用户创建可读写文件夹
    • 3、exp导出数据
    • 4、imp导入数据
      • ①提前创建好要导入的数据库账号
      • ②在本机上建立文件夹datafile并赋予读写权限
      • ③拷贝源数据库导出的dmp文件到目标数据库的服务器上
      • ④切换至oracle用户导入数据
  • 方法二：数据泵导入导出expdp和impdp（oracle10之后都有，效率高些）
    • 按表导入
    • 按用户导入
    • 1、按表导入
    • 2、按用户导入（可以将用户信息直接导入，即如果用户信息不存在的情况下也可以直接导入）
    • 3、不通过expdp的步骤生成dmp文件而直接导入的方法：
    • 4、更换表空间
    • 5、更换用户
    • 6、使用sys时，密码格式
  • 导出和导入数据库版本存在差异时可修改dmp文件

一、查询数据库执行性能

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