Oracle 常用SQL

Oracle常用SQL操作汇总

原创已于 2025-08-12 14:47:08 修改 · 499 阅读

8 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库 #oracle

于 2025-05-24 11:11:22 首次发布

开发笔记同时被 2 个专栏收录

8 篇文章

订阅专栏

Oracle

1 篇文章

订阅专栏

1. 建表、索引、同义词、授权

--建表
CREATE TABLE 表名(
 字段名称  类型-长度     约束
 ID       NUMBER(18)   PRIMARY KEY,
 NAME     VARCHER2(20) NOT NULL,
 SEX      VARCHER2(1)  DEFAULT '1'
)
TABLESPACE 表空间名
PCTFREE 10    -- 数据块中保留的 空闲空间比例
INITRANS 1    -- 初始事务槽（Transaction Slot）的数量
MAXTRANS 255  -- 最大事务槽数量
STORAGE (    --设置存储参数
    INITIAL 64K    -- 初始区大小
    NEXT 1M        -- 下次扩展大小
    MINEXTENTS 1   -- 表创建时初始分配的区数量
    MAXEXTENTS UNLIMITED -- 表最多可分配的区数量（UNLIMITED 表示无限制）
    PCTINCREASE 0        -- 扩展增量百分比（0 表示固定大小扩展）
);

-- 表注释
COMMENT ON TABLE 表名 IS '***表';
-- 列注释
COMMENT ON COLUMN 表名.字段名称 IS '****';

--变更表字段
  --增加
	ALETR TABLE 表名 ADD 字段名 类型-长度;
  --修改
	ALETR TABLE 表名 MODIFY 字段名 类型-长度;
  --删除
	ALETR TABLE 表名 DELETE 字段名;

--约束
  --添加：
	--主键
		ALTER TABLE 表名 ADD CONSTRAINT 主键名 PRIMARY KEY (字段名);
	--唯一约束
		ALTER TABLE 表名 ADD CONSTRAINT 约束名称 UNIQUE (字段名);
	--NOT NULL
		ALTER TABLE 表名 MODIFY 字段 数据类型 (NOT) NULL;
	--外键
		ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (字段名) REFERENCES 关联表 (字段名);
  --删除
	ALTER TABLE 表名 DROP CONSTRAINT 约束名;
	--删除外键
		--禁用外键约束
		  ALTER TABLE 表名 DISABLE CONSTRAINT 外键名;
		--删除外键约束（在禁用后）
		  ALTER TABLE 表名 DROP CONSTRAINT 外键名;
--索引
  CREATE INDEX 索引名称 ON 表名 (字段名);
  CREATE UNIQUE INDEX 索引名称 ON 表名 (字段名);
  --删除
	DROP INDEX 索引名称;

--授权
GRANT INSERT,DELETE,UPDATE,SELECT ON 拥有用户.表名 TO 用户;

--同义词
CREATE OR REPLACE SYNONYM 表名 FOR 其他用户名.表名;
DROP SYNONYM 同义词名称;

2. 拷贝表

1. 复制表结构及其数据

  create table table_name_new as select * from table_name_old

2. 只复制表结构

  create table table_name_new as select * from table_name_old where 1=2;
  或者：
  create table table_name_new like table_name_old

3. 只复制表数据

--如果两个表结构一样：
  insert into table_name_new select * from table_name_old
--如果两个表结构不一样：
  insert into table_name_new(column1,column2...) select column1,column2... from table_name_old

3. 视图

--创建视图：
  CREATE OR REPLACE VIEW 视图名称 AS 查询的SQL;
  --示例：
	CREATE OR REPLACE VIEW USER_VIEW AS 
		SELECT ID_NO,NAME FROM USER_INFO
		UNION
		SELECT ID_NO,NAME FROM USER_INFO_HIS;
--删除视图
  DROP VIEW 视图名称

4. 序列

CREATE SEQUENCE 序列名称 INCREMENT BY 1 START WITH 1 MAXVALUE 9999999 MINVALUE 1 CACHE 10;
/*
  INCREMENT BY：定义序列的步长，如果省略，则默认为1，如果出现负值，则按此步长递减的。
  START WITH：定义序列的初始值(即产生的第1个值)，默认为1。
  MAXVALUE： 定义序列生成器能产生的最大值   最大值是10的27次方。
  MINVALUE： 定义序列生成器能产生的最小值
  CYCLE和NOCYCLE： 表示当序列生成器的值达到限制值后是否循环。
  CACHE(缓冲)：定义存放序列的内存块的大小，默认为20。
*/

--获取序列
SELECT 序列名称.NEXTVAL FROM DUAL;

--查看创建的所有序列
SELECT * FROM USER_SEQUENCES;

--删除序列
DROP SEQUENCE 序列名称;

5. 自定义函数

CREATE [OR REPLACE] FUNCTION 函数名 (
  参数1 [IN] 数据类型, 
  参数2 [IN] 数据类型, 
  ...
  )
  RETURN 返回值数据类型
  [IS | AS]
    -- 声明部分（变量、常量等）
  BEGIN
    -- 执行逻辑
    RETURN 返回值;
  [EXCEPTION]  -- 异常处理
  END 函数名;
/

--示例：
CREATE OR REPLACE FUNCTION FUN_INFO(
  ID IN VARCHAR2
  )
  RETURN VARCHAR2
  AS
    NAME VARCHAR2(200);
  BEGIN
    SELECT OPPNAME INTO NAME FROM USER_INFO WHERE ID_NO=ID;
  RETURN NAME;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RETURN 'Not Found';
  END FUN_INFO;
/

6. 定时任务

1. 创建要执行任务的存储过程
    CREATE OR REPLACE PROCEDURE DELETE_DETAILJOB AS 
    BEGIN 
      DELETE BEDC_MIDDETAIL WHERE ACCID='1163'; 
	  COMMIT;
    END;
	/

使用 DBMS_SCHEDULER（推荐，Oracle 10g+）
	BEGIN
	DBMS_SCHEDULER.CREATE_JOB (
		job_name        => 'DELETE_DETAIL_JOB',
		job_type        => 'STORED_PROCEDURE',
		job_action      => 'DELETE_DETAILJOB',
		start_date      => TRUNC(SYSDATE) + INTERVAL '2' HOUR,  -- 次日凌晨 2 点
		repeat_interval => 'FREQ=DAILY; BYHOUR=2',              -- 每天 2 点执行
		enabled         => TRUE			--TRUE（立即启用）或 FALSE（手动启用）
	);
	END;
	/

使用DBMS_JOB（兼容旧版本）
    DECLARE 
    JOB NUMBER; 	--替换成编号
    BEGIN 
    DBMS_JOB.SUBMIT(JOB, 		-- 作业编号变量
					'DELETE_DETAILJOB;',	--要执行的存储过程
					SYSDATE,
					'TRUNC(SYSDATE,''MI'') + 1 / (24*60)'	--时间间隔
					); 
    END
	/

2. 查看任务列表
-- DBMS_SCHEDULER
	SELECT JOB_NAME, ENABLED, STATE, LAST_START_DATE, NEXT_RUN_DATE FROM USER_SCHEDULER_JOBS;
-- DBMS_JOB
	SELECT JOB, WHAT, NEXT_DATE, INTERVAL FROM USER_JOBS;

3. 手动启停服务
-- 启用DBMS_SCHEDULER
	EXEC DBMS_SCHEDULER.ENABLE('DELETE_DETAIL_JOB');
-- 启用DBMS_JOB
	BEGIN
      DBMS_JOB.RUN(JOB_ID);
    END;
    /

-- 禁用DBMS_SCHEDULER
	EXEC DBMS_SCHEDULER.DISABLE('DELETE_DETAIL_JOB');
-- 停止DBMS_JOB
	BEGIN
	  DBMS_JOB.BROKEN(JOB_ID,SYS.DIUTIL.INT_TO_BOOL(1));--停止一个定时器
    END;
    /

4. 删除任务
-- DBMS_SCHEDULER
	EXEC DBMS_SCHEDULER.DROP_JOB('DELETE_DETAIL_JOB');
-- DBMS_JOB
	BEGIN
	  DBMS_JOB.REMOVE(JOB_ID);
	COMMIT;
	END;
	/

6.1 任务时间参数

日历语法（DBMS_SCHEDULER）
场景	repeat_interval 值
每 30 分钟执行一次	`FREQ=MINUTELY; INTERVAL=30`
每周一和周五 10 点执行	`FREQ=WEEKLY; BYDAY=MON,FRI; BYHOUR=10`
每月最后一天执行	`FREQ=MONTHLY; BYMONTHDAY=-1`
每年 1 月 1 日执行	`FREQ=YEARLY; BYMONTH=1; BYMONTHDAY=1`

PL/SQL 表达式（DBMS_JOB）
场景	interval 表达式
每3秒钟执行一次	TRUNC(sysdate+3/(246060))
每分钟执行	TRUNC(sysdate,’mi’) + 1 / (24*60)
每天的凌晨2点执行	TRUNC(sysdate) + 1 +2 / (24)
每周一凌晨2点执行	TRUNC(next_day(sysdate,2))+2/24
每月1日凌晨2点执行	TRUNC(LAST_DAY(SYSDATE))+1+2/24
每季度的第一天凌晨2点执行	TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 2/24
每年7月1日和1月1日凌晨2点	ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24
每年1月1日凌晨2点执行	ADD_MONTHS(trunc(sysdate,'yyyy'),12)+2/24

7. dmp包操作

7.1 expdp、 impdp

1. 以SYSDBA或具有CREATE DIRECTORY权限的用户登录
 -- 1.先看 directory 对象是否存在
   SELECT * FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME = 'EXP_DIR';
 -- 2.若不存在，创建存放导出.dmp文件的目录
   CREATE DIRECTORY EXP_DIR AS '/app/oracle/exports';
	-- 授予 create 权限
	GRANT CREATE ANY DIRECTORY TO 用户;
	-- 授予 READ，WRITE 权限
	GRANT READ, WRITE ON DIRECTORY EXP_DIR TO 用户;
	-- 查看拥有 DIRECTORY 相关权限的用户
	SELECT * FROM DBA_SYS_PRIVS WHERE PRIVILEGE LIKE '%DIRECTORY%';
	-- 查询拥有 READ，WRITE 相关权限的用户
	SELECT * FROM DBA_TAB_PRIVS WHERE PRIVILEGE IN ('READ', 'WRITE');

2. 导出
-- 实际开发中，只保留一个空格、不换行，此处仅为了 "显示差异" 
        expdp 用户名/密码@数据库    目录              转储文件（二进制）          导出模式               是否开启日志
表：    expdp scott/scott@orcl      directory=EXP_DIR   dumpfile=tables.dmp         tables=表名1,表名2   logfile=tables.log
模式：  expdp scott/scott@orcl      directory=EXP_DIR   dumpfile=schemas.dmp        schemas=scott        logfile=schemas.log
表对象：expdp scott/scott@orcl      directory=EXP_DIR   dumpfile=tablespaces.dmp    tablespaces=users    logfile=tablespaces.log
全库：  expdp system/system@orcl    directory=EXP_DIR   dumpfile=full.dmp           full=Y               nologfile=Y
--导出 scott 下的表 emp 中符合条件的记录
   expdp scott/scott@orcl directory=EXP_DIR dumpfile=文件名.dmp tables=表名 query='表名:"WHERE deptno=10"' logfile=日志文件.log

3. 导入
-- 一般格式
   impdp scott/scott@orcl directory=EXP_DIR dumpfile=文件名.dmp tables=表名 logfile=日志文件.log
-- 导入，追加
   impdp scott/scott@orcl directory=EXP_DIR dumpfile=文件名.dmp tables=表名 table_exists_action=APPEND

7.2 exp、imp

-- 1.全库导出、入
exp/imp 用户名/密码@orcl file=文件路径 full=Y
-- 2.按用户导出、入
exp/imp 用户名/密码@orcl file=文件路径 owner=用户
-- 3.按表导出、入
exp/imp 用户名/密码@orcl file=文件路径 tables=(表1, 表2, 表n) log=loger
-- 4.指定表中数据
exp 用户名/密码@orcl file=文件路径 tables=(表) query=\" where id like 'a%' \"

8. 表空间

8.1 创建 / 删除表空间

--创建
CREATE SMALLFILE TABLESPACE BEDC_DATA 
       DATAFILE '/oracle/oradata/testdb/bedc_data.dbf' 
       SIZE 8G 
       AUTOEXTEND ON NEXT 100M 
       MAXSIZE UNLIMITED 
       LOGGING 
       EXTENT MANAGEMENT LOCAL 
       SEGMENT SPACE MANAGEMENT AUTO;
/**
-SMALLFILE    指定创建小型表空间（默认类型），最多包含 1022 个数据文件
-BIGFILE（单个大文件表空间，最大 128TB）

TABLESPACE     指定表空间名称
DATAFILE      指定文件路径（确保 Oracle 用户对该目录有读写权限）
SIZE 8G       指定文件初始大小8G（Oracle 11g+ 支持 G/M/K 单位）
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED        自动扩展：每次增加 100MB，大小不限制

-LOGGING    所有操作生成重做日志（确保数据可恢复）
-NOLOGGING（不生成日志，性能高但不可恢复）

EXTENT MANAGEMENT LOCAL    本地管理表空间（使用位图管理区分配）
SEGMENT SPACE MANAGEMENT AUTO    自动段空间管理（ASSM），使用位图代替自由列表（FREELIST），减少争用
*/

--目录权限验证
SELECT * FROM DBA_DIRECTORIES WHERE DIRECTORY_PATH LIKE '/oracle/oradata/%';
----若无权限，创建目录
CREATE OR REPLACE DIRECTORY ORADATA_DIR AS '/oracle/oradata/testdb';
GRANT READ, WRITE ON DIRECTORY ORADATA_DIR TO SYSTEM;

--删除表空间
drop tablespace BEDC_DATA including contents and datafiles;

8.2 查看表空间的数据信息

SELECT 
    TABLESPACE_NAME AS "表空间名称",
    FILE_NAME AS "物理文件路径",
    BYTES/1024/1024 AS "文件大小(MB)",
    AUTOEXTENSIBLE AS "是否自动扩展",
    MAXBYTES/1024/1024 AS "最大可扩展至(MB)",
    INCREMENT_BY * (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_block_size')/1024/1024 AS "每次扩展大小(MB)"
FROM 
    DBA_DATA_FILES
ORDER BY 
    TABLESPACE_NAME, FILE_NAME;

8.3 查看临时表空间文件

SELECT 
    TABLESPACE_NAME AS "临时表空间名称",
    FILE_NAME AS "临时文件路径",
    BYTES/1024/1024 AS "文件大小(MB)",
    AUTOEXTENSIBLE AS "是否自动扩展",
    MAXBYTES/1024/1024 AS "最大可扩展至(MB)"
FROM 
    DBA_TEMP_FILES
ORDER BY 
    TABLESPACE_NAME;

8.4 查看表空间使用情况

SELECT 
    A.TABLESPACE_NAME "表空间名",
    TOTAL "表空间大小",
    FREE "表空间剩余大小",
    (TOTAL - FREE) "表空间使用大小",
    TOTAL / (1024 * 1024 * 1024) "表空间大小(G)",
    FREE / (1024 * 1024 * 1024) "表空间剩余大小(G)",
    (TOTAL - FREE) / (1024 * 1024 * 1024) "表空间使用大小(G)",
    ROUND((TOTAL - FREE) / TOTAL, 4) * 100 "使用率 %"
FROM (
     SELECT TABLESPACE_NAME, SUM(BYTES) FREE
       FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME
) A,
  (SELECT TABLESPACE_NAME, SUM(BYTES) TOTAL
       FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME;

8.5 查看回滚表空间文件

SELECT 
    TABLESPACE_NAME AS "回滚表空间名称",
    FILE_NAME AS "回滚文件路径",
    BYTES/1024/1024 AS "文件大小(MB)"
FROM 
    DBA_DATA_FILES
WHERE 
    TABLESPACE_NAME IN (SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE CONTENTS = 'UNDO')
ORDER BY 
    TABLESPACE_NAME;

8.6 查看回滚字段名称及大小

SELECT 
    RS.SEGMENT_NAME AS "回滚段名称",
    RS.TABLESPACE_NAME AS "所属表空间",
    RS.STATUS AS "状态",
    ROUND(S.BYTES/1024/1024, 2) AS "实际占用大小(MB)"
FROM 
    DBA_ROLLBACK_SEGS RS JOIN DBA_SEGMENTS S ON RS.SEGMENT_NAME = S.SEGMENT_NAME;

8.7 查看表空间中各表占用情况

SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 SX
  FROM DBA_SEGMENTS
 WHERE TABLESPACE_NAME = '表空间名称'
 GROUP BY SEGMENT_NAME;

9. 用户操作

--查询所有用户
    SELECT * FROM ALL_USERS WHERE USERNAME='用户名';

--创建用户并制定表空间
    create user 用户名称 identified by 密码 
    default tablespace 表空间名称 temporary tablespace 临时表空间;
--修改密码
    alter user 用户名称 identified by 密码;
--赋予用户权限
    grant connect,resource,dba to 用户名称;
--删除用户
    drop user 用户名称 cascade;

--查看用户连接
    SELECT USERNAME, SID, SERIAL#, MACHINE 
        FROM V$SESSION WHERE USERNAME = '用户名称' AND INSTR(MACHINE, 'ndnapp') = 0;
--强制终止连接
    ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

10. 其他

10.1 锁表

--1.查出锁定表
  select c.sid,c.serial#,d.name,b.object_name,c.username,c.program,c.osuser,a.locked_mode,a.oracle_username,logon_time from gv$locked_object a, all_objects b, gv$session c, audit_actions d
       where a.object_id = b.object_id and a.inst_id = c.inst_id(+) and a.session_id = c.sid(+) and c.command = d.action;
--2.杀掉进程 sid,serial#
  alter system kill session'sid,serial#';

10.2 查询表某个时间的快照

select * from 表名 as of timestamp to_timestamp('2021-03-09 14:00:11','yyyy-mm-dd hh24:mi:ss');

10.3 other

--查看控制文件
SELECT NAME FROM V$CONTROLFILE;
--查看日志文件
SELECT MEMBER FROM V$LOGFILE;
--查看数据库库对象
SELECT OWNER, OBJECT_TYPE, STATUS, COUNT(*) COUNT# FROM ALL_OBJECTS GROUP BY OWNER, OBJECT_TYPE, STATUS;
--查看数据库的版本　
SELECT VERSION FROM PRODUCT_COMPONENT_VERSION WHERE SUBSTR(PRODUCT, 1, 6) = 'Oracle';
--查看数据库的创建日期和归档方式
SELECT CREATED, LOG_MODE, LOG_MODE FROM V$DATABASE;