Oracle数据库常用SQL语句与操作指南

数据库连接与环境设置

在进行任何数据库操作前，首先需要通过 SQL*Plus 连接到 Oracle 实例。启动时建议以管理员身份登录以便查看系统级信息，随后可切换至普通业务用户进行操作。

-- 1. 连接数据库 (system/manager 为常见默认管理员账户)
SQL*Plus system/manager 

-- 2. 显示当前连接的内部用户
SQL> show user 

-- 3. 查看系统内所有已创建的用户账号
SQL> select * from all_users;

用户管理与权限授权

数据库安全依赖于严格的权限控制。以下命令展示了如何从零创建一个新用户，为其分配角色，以及后续更改密码和解锁账户的操作逻辑。

第一步：新建用户并分配基础权限

使用 Create User 语句建立新账户，并通过 Grant 赋予其连接和创建对象的资源权限。这一步通常将用户默认放置在 SYSTEM 或 USERS 表空间下。

-- 创建用户 a，密码也为 a (默认建在SYSTEM表空间下)
SQL> create user a identified by a;

-- 授予基础权限：connect(连接) 和 resource(创建对象)
SQL> grant connect,resource to a;

第二步：验证与切换用户

权限配置完成后，通过 conn 命令切换到新用户，验证其是否具备基本的会话能力。

SQL> conn a/a

第三步：高级用户配置（可选）

对于需要更复杂权限控制或特定配置文件限制的场景，可使用更详细的参数进行账户设定，例如指定默认表空间、临时表空间及账户锁定状态。

-- 复杂用户创建示例 (包含PROFILE、TABLESPACE等详细参数)
CREATE USER "user_id" PROFILE "DEFAULT" IDENTIFIED BY "password"
DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "user_id";
ALTER USER "user_id" DEFAULT ROLE ALL;

数据表基础操作

完成用户登录后，接下来的核心工作是在该用户下创建和管理表对象。这包括视图查看、表结构复制、字段增删改查以及数据记录的增删更新。

第四步：查看与创建表

首先列出当前用户下的所有对象，接着演示如何基于现有结构快速创建空表，这是开发和测试阶段常用的技巧。

-- 6. 查询当前用户下所有对象 (如视图、表、序列等)
SQL> select * from tab;

-- 7. 建立第一个基础表
SQL> create table a(a number);

-- 8. 查询表的具体结构 (列名、数据类型等)
SQL> desc a

-- 高级技巧：创建一个与原表a结构相同但无数据的空表b
SQL> create table b as select * from a where 1=2;

第五步：数据增删改查 (CRUD)

针对表内容进行常规的数据维护操作。注意在处理大量数据变更时，合理使用事务提交与回滚机制以保证数据一致性。

-- 9. 插入新记录
SQL> insert into a values(1);

-- 10. 查询记录
SQL> select * from a;

-- 11. 更改记录 (全表更新需注意范围条件)
SQL> update a set a=2;

-- 12. 删除记录
SQL> delete from a;

-- 13. 事务回滚：撤销未提交的更改
SQL> rollback;

-- 14. 事务提交：永久保存更改
SQL> commit;

表结构与属性进阶修改

在实际生产环境中，表结构往往随着需求变化而调整。Oracle 对字段的修改有着特定的约束（如非空约束、长度缩减限制），以下命令覆盖了常见的维护场景。

/* 向一个表格末尾添加新字段 */
alter table alist_table add address varchar2(100);

/* 修改已有字段的宽度属性 (若字段有数据，只能增加宽度) */
alter table alist_table modify address varchar2(80);

/* 修改列名：Oracle不直接支持rename column，需重建表
   步骤：创建副本 -> 复制数据 -> 删除旧表 -> 重命名副本 */
create table alist_table_copy as select ID,NAME,PHONE,EMAIL,
QQ as QQ2, /*qq 改为qq2*/
ADDRESS from alist_table;
drop table alist_table;
rename alist_table_copy to alist_table;

系统监控与信息查询

除了业务数据操作，DBA 或运维人员还需要掌握系统层面的信息查询，包括内存回滚段状态、表空间剩余容量以及未断开的用户进程分析。

第六步：表空间使用率监控

利用 dba_free_space 和 dba_data_files 联合查询，可以直观计算各物理文件的总大小、已用空间和剩余百分比，防止磁盘爆满。

SQL > col tablespace format a20
SQL > select b.file_id 文件ID,
b.tablespace_name 表空间,
b.file_name 物理文件名,
b.bytes 总字节数,
(b.bytes-sum(nvl(a.bytes,0))) 已使用,
sum(nvl(a.bytes,0)) 剩余,
sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比
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.file_id,b.bytes
order by b.tablespace_name
/;

第七步：其他系统关键查询

以下集合了日期处理、字符串拼接、回滚段状态检查以及断开连接诊断等实用 SQL 片段。

-- 查看现有回滚段及其状态 (用于排查并发冲突)
SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,STATUS FROM DBA_ROLLBACK_SEGS;

-- 查找未断开的用户连接信息
select process,osuser,username,machine,logon_time ,sql_text
from v$session a,v$sqltext b 
where a.sql_address=b.address;

-- 连接字符串演示
SQL > select concat(列1,列2) from 表1;

-- 查询当前精确时间
SQL > select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual;

数据字典视图速查

Oracle 提供了丰富的数据字典供不同层级用户查阅元数据，主要分为三类前缀视图：

1. USER_* 系列：仅显示当前用户拥有的对象（如 user_tables）。
2. ALL_* 系列：显示当前用户有权访问的所有对象（如 all_tables）。
3. DBA_* 系列：数据库管理员专用视图，包含全库信息（需 DBA 权限）。