Oracle数据库常用操作笔记（持续更新）

置顶 Dong_Xu1121

已于 2024-04-23 11:09:29 修改

阅读量1.3k

点赞数 3

分类专栏：数据库文章标签： Oracle 数据库常用管理 sql

于 2019-02-28 16:22:19 首次发布

本文链接：https://blog.csdn.net/Dong_Xu1121/article/details/88033199

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

一、基础操作

1.创建表

create table tablename （列名 类型,列名 类型......）;

2.修改表名

rename [OldTableName]  to [NewTableName];

3.添加带条件的唯一索引

create unique index UN_TABLE on TABLE(
	(case when DELFLAG = '0' then field1 else null end),
	(case when DELFLAG = '0' then field2 else null end),
	(case when DELFLAG = '0' then field3 else null end),
	(case when DELFLAG = '0' then field4 else null end)
) ;

二、常用函数

1. 字符串操作

length()：获取字段长度
select LENGTH([field]) from [TableName]
field：待判断长度字段
例：select LENGTH(‘abc’) from dual;返回 3；select LENGTH(‘中国’) from dual;返回 2；
substr()：从某一位置开始截取指定长度字符串
SUBSTR(string, start, [length])
lenth：可选，不传则截取到最后
例：substr(‘abcde’,2) ——>bcde substr(‘abcde’,2,2) ——>bc
instr() ：出现指定字符的位置
INSTR(string，child_string，[start]，[show_time])
start：默认从1开始，负数则从右侧开始计算
listagg() ：合并一列至一个字段（纵向合并）
select LISTAGG ([field],[spacer]) WITHIN GROUP( ORDER BY [key]) from [TableName] where ...
field：待合并字段
spacer：分隔符
key：每条数据主键
例：人员表：YX_USER 与学生表：YX_STUDENT
(SELECT LISTAGG(user.xm,‘,’) WITHIN GROUP( ORDER BY user.id) from YX_USER user where student.id = user.id) xms
wm_concat()：合并不同列至一个字段（横向合并）【12版本及以上废弃】
select wm_concat(field1||field2||field3||.....fieldn) from TableName where ......
例：select wm_concat(ID||NAME) from YX_STUDENT; 返回：123张三。
REGEXP_SUBSTR()：通过正则表达式拆分字段【同理 regexp_instr()可尝试使用】
例（以逗号拆分’AA-a,B-B,cc-CC’）：
SELECT DISTINCT REGEXP_SUBSTR ('AA-a,B-B,cc-CC','[^,]+',1,LEVEL) FROM DUAL CONNECT BY REGEXP_SUBSTR ('AA-a,B-B,cc-CC','[^,]+',1,LEVEL) IS NOT NULL order by 1
结果：
1 AA-a
2 B-B
3 cc-CC

2. 数字操作

floor() ：取整数位例：floor(5.84)——>5
ceil()：取进一（或等于）整数例：ceil(5.14)——>6
round()：按照精度四舍五入
round(number,[digits])
digits：可选，保留小数位数，只保留整数则不传参
例：round(5.86,1)——>5.7
trunc()：按照指定精度取值例：trunc(5.8418,3)——>5.841

3. 逻辑操作

nvl()：null值处理函数
nvl(field1,field2)
当field1为null时，返回field2的值
decode()：SQL中的三元表达式(支持多级嵌套)
decode(field,condition_1,trueResult_1,condition_2,trueResult_2,......falseResult)
例：
select decode(name,null,‘我没有名字’,‘man’,‘我是男生’,‘我是女生’) as studentName;
case when … then … else … end：if else语法
case when (condition) then exec1 else exec2 end;
当条件condition成立时，执行exec1 ，否则执行exec2

4.CLOB操作

JSON_VALUE(clob,key) ：读取clob中的json数据的值
例：
select JSON_VALUE(contentjson,'$.name') from tablename

5.JOB操作

创建JOB

begin
  sys.dbms_scheduler.create_job(job_name            => 'USER.JOB_TEST',
                                job_type            => 'STORED_PROCEDURE',
                                job_action          => 'PKG_GENERATE_TEST.proc_test',
                                start_date          => to_date('21-02-2019 00:00:00', 'dd-mm-yyyy hh24:mi:ss'),
                                repeat_interval     => 'Freq=Hourly;Interval=24',
                                end_date            => to_date(null),
                                job_class           => 'DEFAULT_JOB_CLASS',
                                enabled             => true,
                                auto_drop           => false,
                                comments            => '');
end;

删除JOB

Begin
  Dbms_Scheduler.Drop_Job (Job_Name => 'JOB_TEST');
END;

三、常用语句

1.Start With 树形结构查询

SELECT ... FROM    + 表名
WHERE              + 条件3
START WITH         + 条件1
CONNECT BY PRIOR   + 条件2

–示例
Select * From DEMO
Start With ID = ‘1’
Connect By Prior ID = PID

2. declare语句块

declare
  rc sys_refcursor; --定义游标
begin
  for rc in(
    select aa from table
    )loop
         --具体循环操作
    end loop;
end;

–示例，将总分数大于90的类型设置为A
declare
v_type varchar2 default ‘A’;
begin
for student_clo in (select * from ScoreTable where sumScore>90) loop
update set type = v_type where ID = student_clo.ID;
end loop;
end;

3.PARTITION 数据分组编号

select row_number() OVER(PARTITION by [分区字段] order by [排序字段]) 序号 from 表名

–示例，对成绩单中每个学生的成绩进行分组排序
select row_number() OVER(PARTITION by studentID order by core) 序号,name,core from schoolreport;

四、管理操作

1.查询数据库版本

select * from v$version;

2.查询数据库服务端使用的字符集编码

  select userenv('language') from dual;

3.设置系统字符集

查看可用字符集(SELECT * FROM v$nls_valid_values;)

set NLS_LANG=[字符集]

如：常用unicode字符集 set NLS_LANG=CHINESE_CHINA.AL32UTF8

4.IMPDP/EXPDP数据泵导入导出数据

1.导出
导出整个实例：

 expdp user/password@orcl directory=DATAPUMP dumpfile=xxxxx.dmp logfile=xxxxx.log compression=all  parallel=32

附：linux exp命令操作

登录至oracle所在的linux系统
切换至oracle（su - oracle）
执行导出命令（exp user/password@orcl file=xxx/xxx.dmp full=y statistics=none）
可选级别：全库 full=y | 指定表空间 tablespaces=(XXX) | 指定用户 owner=(student)
只导出表结构：rows=n

导出指定表：

 expdp user/password@orcl directory=DATAPUMP tables='tablesname1,tablesname2...' dumpfile=xxxxx.dmp logfile=xxxxx.log parallel=8

2.导入
导入实例：

impdp user/password@orcl directory=DATA_PUMP_DIR dumpfile=xxxxx.dmp logfile=xxxxx.log parallel=8

附：linux imp命令操作

登录至oracle所在的linux系统
切换至oracle（su - oracle）
执行导出命令（imp user/password@orcl file=xxx/xxx.dmp full=y）
若字符集报错，修改字符集：export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

导入指定表：

impdp user/password@orcl directory=DATAPUMP dumpfile=xxxxx.dmp TABLES ='tablesname'` DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS TABLE_EXISTS_ACTION=APPEND  CONTENT=DATA_ONLY logfile=xxxxx.log parallel=8

3.参数说明

	orcl--实例名
    content=metadata_only--仅导入表结构
    table_exists_action=replace--可直接删除表后创建表并导入数据
    TABLE_EXISTS_ACTION=APPEND--可追加导入{skip 是如果已存在表，则跳过并处理下一个对象；append是为表增加数据；truncate是截断表，然后为其增加新数据；replace是删除已存在表，重新建表并追加数据}
    compression=all/data_only/metadata_only/none

5. 查询、创建、删除DBLink

1.查询DBLINK信息

select * from dba_db_links;
select owner,object_name from dba_objects where object_type='DATABASE LINK';
select * from ALL_DB_LINKS;

2.创建dblink
报错:ORA-01017、ORA-02063解决：
根据ORA-01017的提示是连接到另一方的用户密码错误，当9i或10g的版本的Oracle数据库连接11g的版本时，会自动将密码转化为大写。
**密码最好用双引号引起来，否则可能在连接的时候报错

create database link dblink名
  connect to 数据库用户名 identified by "数据库密码"
 using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';

– 如果创建全局 dblink，必须使用 systm 或 sys 用户，在 database 前加 public。

create public database link dblink名
　　connect to 数据库用户名 identified by "数据库密码"
　　using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';

3.删除DBLINK

DROP DATABASE LINK [name];  
--或  
DROP PUBLIC DATABASE LINK [name];

6. oracle创建用户操作

数据库所在环境，打开命令提示框输入以下内容：

输入：sqlplus /nolog //进入oralce控制台
输入：conn /as sysdba //以管理员权限登录
输入：create user abc identified by 123456; //创建用户名adc密码123456
输入：grant dba to abc; //授予DBA权限

7.锁表追踪及解锁

查看哪些表被锁住：

select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;

查看引起死锁的会话：

select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;

查看被阻塞的会话：

select * from dba_waiters;

释放锁或者杀掉ORACLE进程：

alter system kill session 'sid,serial#';

8.回滚表数据到指定时间

开启表行移动功能
alter table 表名 enable row movement;
回滚误操作表指定日期的数据
flashback table 表名 to timestamp to_timestamp(‘2011-03-04 05:00:00’,‘yyyy-mm-dd HH24:MI:SS’);
查询误操作表的历史数据信息（此步用于查看可回滚的日期，可选择最近的正确数据日期进行回滚）
select * from 表名 as of timestamp to_timestamp(‘2019-08-23 17:15:00’, ‘yyyy-mm-dd hh24:mi:ss’);
查看数据闪回功能是否开启
select open_mode,log_mode,flashback_on from v$database;
关闭表行移动功能
alter table 表名 disable row movement;