Oracle 超全常用命令与内置函数实战文档

sql
# 本地超级管理员登录
sqlplus / as sysdba

# 普通用户登录
sqlplus 用户名/密码

# 远程登录
sqlplus 用户名/密码@IP:1521/服务名

# 静默登录（不输出提示）
sqlplus -s 用户名/密码

sql
-- 设置行宽、页高，解决换行错乱
set linesize 300;
set pagesize 200;
set long 9999;
set colsep '  ';
set feedback off;
set heading on;
set timing on;    -- 显示SQL执行时间
set serveroutput on; -- 开启打印输出

-- 基础查询
show user;        -- 查看当前登录用户
select user from dual;
clear screen;     -- 清屏

sql
-- 启动数据库
startup;

-- 正常关闭（等待事务结束）
shutdown immediate;

-- 强制关闭（生产慎用，应急使用）
shutdown abort;

-- 只读启动
startup mount;
alter database open read only;

sql
-- 查看数据库版本
select banner from v$version;

-- 查看实例状态、实例名
select instance_name,status,startup_time from v$instance;

-- 查看数据库名称、日志模式（归档/非归档）
select name,log_mode,open_mode from v$database;

-- 查看数据库服务名
select value from v$parameter where name='service_names';

-- 查看数据库字符集
select userenv('language') from dual;

sql
-- 创建用户+指定表空间+无限配额
create user test_dev identified by Test@123
default tablespace users
temporary tablespace temp
quota unlimited on users;

-- 修改用户密码
alter user test_dev identified by New@123;

-- 锁定/解锁用户
alter user test_dev account lock;
alter user test_dev account unlock;

-- 删除用户（级联删除所有对象）
drop user test_dev cascade;

sql
-- 基础登录权限
grant create session to test_dev;

-- 常用开发对象权限
grant create table,create view,create sequence,create synonym to test_dev;

-- 资源角色（开发必备，拥有建表等所有资源权限）
grant resource to test_dev;

-- DBA管理员权限
grant dba to test_dev;

-- 回收权限
revoke dba,resource from test_dev;

-- 授予查询其他用户表权限
grant select on scott.emp to test_dev;

sql
-- 查询当前用户拥有的角色
select * from user_role_privs;

-- 查询指定用户的角色权限
select * from dba_role_privs where grantee='TEST_DEV';

-- 查询用户系统权限
select * from dba_sys_privs where grantee='TEST_DEV';

-- 查询用户对象权限
select * from dba_tab_privs where grantee='TEST_DEV';

sql
-- 1、查询所有表空间状态
select tablespace_name,status,contents,extent_management from dba_tablespaces;

-- 2、查询表空间使用率（精准使用率，生产常用）
SELECT
    A.TABLESPACE_NAME,
    TOTAL_SIZE/1024/1024 TOTAL_MB,
    (TOTAL_SIZE-FREE_SIZE)/1024/1024 USED_MB,
    FREE_SIZE/1024/1024 FREE_MB,
    ROUND((TOTAL_SIZE-FREE_SIZE)/TOTAL_SIZE*100,2) USED_RATE
FROM (
    SELECT TABLESPACE_NAME,SUM(BYTES) TOTAL_SIZE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME
) A,
(
    SELECT TABLESPACE_NAME,SUM(BYTES) FREE_SIZE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME
) B
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
ORDER BY USED_RATE DESC;

-- 3、查询临时表空间使用率
select tablespace_name,used_blocks,free_blocks from v$temp_space_header;

-- 4、查询数据文件路径、大小、自动扩展
select file_name,bytes/1024/1024 size_mb,autoextensible,maxbytes/1024/1024 max_size_mb
from dba_data_files;

sql
-- 创建永久表空间
create tablespace dev_ts
datafile '/u01/app/oracle/oradata/orcl/dev_ts01.dbf'
size 200m autoextend on next 100m maxsize 4096m;

-- 新增数据文件
alter tablespace dev_ts add datafile '/u01/app/oracle/oradata/orcl/dev_ts02.dbf' size 200m;

-- 手动扩容数据文件
alter database datafile '/u01/app/oracle/oradata/orcl/dev_ts01.dbf' resize 500m;

-- 删除表空间及数据文件
drop tablespace dev_ts including contents and datafiles;

sql
-- 标准建表（含约束、默认值）
create table user_info(
    id      number(10)    primary key,
    username varchar2(50) not null,
    phone   varchar2(20),
    status  number(2)     default 1,
    create_time date       default sysdate,
    remark  varchar2(500)
);

-- 仅复制表结构（无数据）
create table user_info_bak as select * from user_info where 1=0;

-- 复制表结构+所有数据
create table user_info_bak2 as select * from user_info;

sql
-- 新增字段
alter table user_info add email varchar2(100);

-- 修改字段类型/长度
alter table user_info modify email varchar2(150);

-- 重命名字段
alter table user_info rename column email to user_email;

-- 删除字段
alter table user_info drop column user_email;

-- 表重命名
rename user_info to sys_user;

-- 添加唯一约束
alter table sys_user add constraint uk_user_phone unique(phone);

-- 添加非空约束
alter table sys_user modify username not null;

sql
-- 查询当前用户所有表
select table_name from user_tables order by table_name;

-- 查询指定用户所有表
select table_name from dba_tables where owner='TEST_DEV';

-- 查询表字段详情
select column_name,data_type,data_length,nullable,data_default
from user_tab_columns where table_name='SYS_USER';

-- 查询表约束
select constraint_name,constraint_type,column_name
from user_cons_columns join user_constraints using(constraint_name)
where table_name='SYS_USER';

sql
-- 插入数据
insert into sys_user(id,username,phone) values(1,'张三','13800138000');
commit;

-- 批量插入
insert into sys_user(id,username,phone)
select id,username,phone from sys_user_bak;

-- 更新数据
update sys_user set status=2 where id=1;
commit;

-- 删除数据
delete from sys_user where id=1;
commit;

-- 基础条件查询
select * from sys_user where status=1 and create_time>sysdate-7;

-- 模糊查询
select * from sys_user where username like '%张%';

-- 范围查询
select * from sys_user where id between 1 and 100;

-- 空值判断
select * from sys_user where phone is null;
select * from sys_user where phone is not null;

-- 去重查询
select distinct username from sys_user;

sql
-- 大小写转换
select upper('oracle') from dual; -- 大写
select lower('ORACLE') from dual; -- 小写

-- 字符串拼接
select concat('Hello','Oracle') from dual;
select 'Hello'||'Oracle' from dual;

-- 截取字符串 substr(字段,起始位置,长度)
select substr('ABCDEFG',1,3) from dual; -- ABC

-- 字符串长度
select length('测试') from dual;

-- 去除空格
select ltrim('  123') from dual; -- 左空格
select rtrim('123  ') from dual; -- 右空格
select trim('  123  ') from dual; -- 首尾空格

-- 替换字符串
select replace('abc123','123','456') from dual;

-- 查找字符位置
select instr('abcdef','c') from dual;

-- 补齐字符
select lpad('123',5,'0') from dual; -- 00123
select rpad('123',5,'0') from dual; -- 12300

sql
-- 四舍五入
select round(3.1415,2) from dual;

-- 向上取整、向下取整
select ceil(2.1) from dual;
select floor(2.9) from dual;

-- 取绝对值
select abs(-100) from dual;

-- 取余数
select mod(10,3) from dual;

-- 幂运算、开方
select power(2,3) from dual;
select sqrt(16) from dual;

sql
-- 当前系统时间
select sysdate from dual;
select systimestamp from dual; -- 带毫秒

-- 日期加减
select sysdate+1 from dual; -- 加1天
select sysdate-1/24 from dual; -- 减1小时

-- 月份加减
select add_months(sysdate,1) from dual; -- 加1月
select add_months(sysdate,-1) from dual; -- 减1月

-- 当月最后一天
select last_day(sysdate) from dual;

-- 两个日期月份差
select months_between(sysdate,to_date('2025-01-01','yyyy-mm-dd')) from dual;

-- 日期截断（清零时分秒）
select trunc(sysdate) from dual;

-- 提取年月日
select extract(year from sysdate) from dual;
select extract(month from sysdate) from dual;
select extract(day from sysdate) from dual;

sql
-- 字符串转日期
select to_date('2025-12-01','yyyy-mm-dd') from dual;

-- 日期转字符串
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

-- 数字转字符串
select to_char(12345) from dual;

-- 字符串转数字
select to_number('12345') from dual;

sql
-- 空值处理 nvl(字段,默认值)
select nvl(null,0) from dual;
select nvl(phone,'暂无号码') from sys_user;

-- 双空值判断
select nvl2(phone,'有号码','无号码') from sys_user;

-- decode 条件判断（Oracle专属）
select decode(status,1,'正常',2,'禁用','未知') from sys_user;

-- case when 多条件判断
select
    id,username,
    case status
        when 1 then '正常'
        when 2 then '禁用'
        else '未知'
    end as status_name
from sys_user;

sql
select
    count(*) total_num,        -- 总条数
    count(phone) have_phone,   -- 非空手机号数量
    max(id) max_id,
    min(id) min_id,
    avg(id) avg_id,
    sum(id) sum_id
from sys_user;

-- 分组查询
select status,count(*) num from sys_user group by status;

-- 分组条件筛选
select status,count(*) num from sys_user group by status having count(*)>10;

sql
-- 行号排序（分页、排名必备）
select
    username,
    row_number() over(order by id) rn,  -- 连续行号 1,2,3
    rank() over(order by id) rk,        -- 跳跃排名 1,2,2,4
    dense_rank() over(order by id) drk  -- 密集排名 1,2,2,3
from sys_user;

-- 分组排名
select
    status,username,
    row_number() over(partition by status order by id) rn
from sys_user;

-- 累计求和
select id,sum(id) over(order by id) sum_total from sys_user;

sql
-- 普通索引
create index idx_user_name on sys_user(username);

-- 复合索引
create index idx_user_status_time on sys_user(status,create_time);

-- 唯一索引
create unique index idx_user_phone on sys_user(phone);

-- 查询表所有索引
select index_name,column_name,index_type
from user_ind_columns join user_indexes using(index_name)
where table_name='SYS_USER';

-- 删除索引
drop index idx_user_name;

sql
-- 创建视图
create view v_user_normal as select id,username,phone from sys_user where status=1;

-- 创建自增序列
create sequence seq_user_id start with 1 increment by 1 nocache nomaxvalue;
select seq_user_id.nextval from dual;

-- 创建同义词（简化访问）
create synonym su for sys_user;

sql
-- 1、基础分页 1-10条
select * from (
    select t.*,rownum rn from sys_user t
) where rn between 1 and 10;

-- 2、排序分页（生产常用）
select * from (
    select t.*,rownum rn from (
        select * from sys_user order by create_time desc
    ) t
) where rn between 11 and 20;

-- 3、分析函数分页（高效）
select * from (
    select t.*,row_number() over(order by id desc) rn from sys_user t
) where rn between 21 and 30;

sql
-- 查询锁阻塞会话
SELECT  
    S.SID,S.SERIAL#,S.USERNAME,S.MACHINE,S.PROGRAM,S.STATUS,
    L.TYPE,O.OBJECT_NAME
FROM V$SESSION S
JOIN V$LOCK L ON S.SID=L.SID
LEFT JOIN DBA_OBJECTS O ON L.ID1=O.OBJECT_ID
WHERE L.TYPE IN ('TX','TM');

-- 杀死锁会话
alter system kill session 'SID,SERIAL#';
alter system kill session '156,8972' immediate;

sql
-- 查询所有在线会话
select sid,serial#,username,machine,program,status,logon_time from v$session where username is not null;

-- 查询当前总连接数、活跃连接数
select count(*) total_conn from v$session;
select count(*) active_conn from v$session where status='ACTIVE';

sql
-- 查看SQL执行计划
explain plan for select * from sys_user where username='张三';
select * from table(dbms_xplan.display());

-- 查询近期高耗时SQL
select sql_id,elapsed_time/1000000 cost_sec,sql_text
from v$sql order by elapsed_time desc;

sql
-- 创建数据泵目录
create directory dump_dir as '/u01/oracle/dump';
grant read,write on directory dump_dir to test_dev;

-- 导出整用户
expdp test_dev/123456 directory=dump_dir dumpfile=dev_user.dmp schemas=test_dev

-- 导出指定表
expdp test_dev/123456 directory=dump_dir dumpfile=user_table.dmp tables=sys_user

-- 导入数据
impdp test_dev/123456 directory=dump_dir dumpfile=dev_user.dmp full=y

sql
-- 查询当前用户所有视图
select view_name from user_views;

-- 查询所有序列
select sequence_name from user_sequences;

-- 查询表数据量
select table_name,num_rows from user_tables;

-- 查询数据库归档状态
select log_mode,archive_change# from v$database;

-- 查询重做日志文件
select group#,member,status from v$logfile;

-- 查询参数配置
select name,value from v$parameter where name like '%memory%';