oracle常用语句

本文基于linux系统下对oracle数据库进行操作。

一.连接数据库

SQL*Plus 连接

​ SQL*Plus 是 Oracle 提供的命令行工具，可以通过以下命令连接到数据库：

本地连接

sqlplus username/password

远程连接

sqlplus username/password@//host:port/service_name

• host：数据库服务器的 IP 地址或主机名。

• port：监听器端口号，默认是 1521。

• service_name：数据库服务名。

使用操作系统认证

以 DBA 身份登录本地数据库：

sqlplus / as sysdba

二.管理数据库

启动数据库

STARTUP [MOUNT|NOMOUNT|OPEN];

• NOMOUNT：仅启动实例（初始化参数文件加载，但未与控制文件交互）。
• MOUNT：启动实例并加载控制文件，但数据库文件未打开。
• OPEN：完全启动数据库，用户可以访问数据。

关闭数据库

SHUTDOWN [NORMAL|IMMEDIATE|ABORT];

• NORMAL：等待所有会话结束后关闭。
• IMMEDIATE：强制结束会话并关闭。
• ABORT：立即关闭，无需等待。

三.数据字典和视图

视图：

视图是预定义的 SQL 查询，可以像表一样使用，先整理视图以方便查询。

1.数据库基础信息视图

V$DATABASE

• 提供有关数据库的基本信息，如名称、创建时间等。

SELECT name, dbid, created 
FROM v$database;

V$INSTANCE

• 提供实例状态和配置的信息。

SELECT instance_name, status, host_name, version 
FROM v$instance;

V$VERSION

• 提供数据库版本信息。

SELECT * 
FROM v$version;

2.用户和权限管理视图

DBA_USERS

• 列出所有用户的详细信息。

SELECT username, account_status, default_tablespace, temporary_tablespace 
FROM dba_users;

DBA_ROLE_PRIVS

• 查看用户或角色授予的角色权限。

SELECT grantee, granted_role 
FROM dba_role_privs
WHERE grantee = 'USERNAME';

DBA_SYS_PRIVS

• 查看系统权限授予情况。

SELECT grantee, privilege 
FROM dba_sys_privs
WHERE grantee = 'USERNAME';

DBA_TAB_PRIVS

• 查看对象权限授予情况。

SELECT grantee, table_name, privilege 
FROM dba_tab_privs
WHERE grantee = 'USERNAME';

3.表空间和存储视图

DBA_TABLESPACES

• 提供表空间的基本信息。

SELECT tablespace_name, status, contents 
FROM dba_tablespaces;

DBA_DATA_FILES

• 查看数据文件的信息，包括大小、路径等。

SELECT file_name, tablespace_name, bytes / 1024 / 1024 AS size_mb, autoextensible 
FROM dba_data_files;

DBA_FREE_SPACE

• 查看表空间的剩余空间。

SELECT tablespace_name, file_id, block_id, bytes / 1024 / 1024 AS free_space_mb 
FROM dba_free_space;

DBA_TEMP_FILES

• 查看临时文件信息。

SELECT file_name, tablespace_name, bytes / 1024 / 1024 AS size_mb 
FROM dba_temp_files;

4.表和索引管理视图

ALL_TABLES

• 查看用户可以访问的表的信息。

SELECT table_name, tablespace_name, num_rows 
FROM all_tables
WHERE owner = 'USERNAME';

DBA_TABLES

• 查看数据库中所有表的信息。

SELECT table_name, tablespace_name, logging 
FROM dba_tables;

DBA_INDEXES

• 提供索引的详细信息。

SELECT index_name, table_name, uniqueness, status 
FROM dba_indexes
WHERE owner = 'USERNAME';

DBA_SEGMENTS

• 查看段的存储信息，包括表段、索引段等。

SELECT segment_name, segment_type, bytes / 1024 / 1024 AS size_mb 
FROM dba_segments
WHERE tablespace_name = 'TABLESPACE_NAME';

5. 性能监控视图

V$SESSION

• 查看当前数据库中的活动会话。

SELECT sid, serial#, username, status, machine 
FROM v$session;

V$SYSTEM_EVENT

• 查看系统中等待事件的统计信息。

SELECT event, total_waits, time_waited 
FROM v$system_event
ORDER BY time_waited DESC;

V$SQL

• 提供正在执行或已经执行的 SQL 的统计信息。

SELECT sql_id, sql_text, executions, elapsed_time 
FROM v$sql
WHERE rownum <= 10;

V$PROCESS

• 显示当前正在运行的后台进程。

SELECT pid, spid, program 
FROM v$process;

6. 日志和恢复视图

V$LOG

• 查看联机重做日志的状态。

SELECT group#, sequence#, bytes / 1024 / 1024 AS size_mb, status 
FROM v$log;

V$ARCHIVED_LOG

• 查看归档日志的信息。

SELECT sequence#, first_time, next_time, applied 
FROM v$archived_log
ORDER BY sequence# DESC;

V$RECOVERY_FILE_DEST

• 查看闪回恢复区的信息。

SELECT name, space_limit / 1024 / 1024 AS limit_mb, space_used / 1024 / 1024 AS used_mb 
FROM v$recovery_file_dest;

7. 数据块管理视图

DBA_EXTENTS

• 查看段使用的区信息。

SELECT segment_name, segment_type, tablespace_name, bytes / 1024 / 1024 AS size_mb 
FROM dba_extents;

DBA_BLOCKS

• 查看数据块的使用情况。

SELECT file_id, block_id, blocks 
FROM dba_blocks;

8. 系统统计视图

V$SYSTEM_STAT

• 提供数据库级别的性能统计。

SELECT name, value 
FROM v$sysstat
WHERE rownum <= 10;

V$RESOURCE_LIMIT

• 查看系统资源使用情况。

SELECT resource_name, current_utilization, max_utilization, limit_value 
FROM v$resource_limit;

四.表空间管理

创建表空间：

CREATE TABLESPACE tablespace_name
  [ DATAFILE 'file_path' [ SIZE size [ K | M | G ] ] [ AUTOEXTEND ON | OFF ] [ NEXT size [ K | M | G ] ] [ MAXSIZE max_size [ K | M | G ] ] ]
  [ EXTENT MANAGEMENT LOCAL | DICTIONARY ]
  [ SEGMENT SPACE MANAGEMENT MANUAL | AUTO ]
  [ LOGGING | NOLOGGING ];

• tablespace_name: 表空间的名称，必须是唯一的。

数据文件配置

• DATAFILE 'file_path': 数据文件的位置和名称。这个文件会用于存储表空间的数据。
• SIZE size [ K | M | G ]: 数据文件的初始大小。K 表示 KB，M 表示 MB，G 表示 GB。
• AUTOEXTEND ON | OFF: 是否允许数据文件自动扩展。当表空间已满时，数据文件可以自动扩展。
• NEXT size [ K | M | G ]: 数据文件自动扩展时每次增加的空间大小。
• MAXSIZE max_size [ K | M | G ]: 数据文件可以扩展的最大大小。如果设置为 UNLIMITED，则没有大小限制。

管理方式

• EXTENT MANAGEMENT LOCAL | DICTIONARY:
  • LOCAL: 表示表空间使用本地管理的区段（extents）。推荐使用此选项，因为它提供更好的性能和灵活性。
  • DICTIONARY: 表示表空间使用数据字典来管理区段。
• SEGMENT SPACE MANAGEMENT MANUAL | AUTO:
  • AUTO: 自动管理空间。
  • MANUAL: 手动管理空间，通常用于向旧的应用程序迁移时。

日志选项

• LOGGING | NOLOGGING:
  • LOGGING: 启用事务日志记录，确保对表空间的所有修改都被记录。
  • NOLOGGING: 禁用事务日志记录，通常用于数据加载操作，以提高性能（但有数据丢失风险）。

五.用户管理

查询当前用户：

show user

用户登录：

conn语句只能在SQL*Plus已经建立会话的情况下使用，它是SQL*Plus的内部语句。

conn username/password@connect_identifier

• username：数据库用户名。
• password：用户对应的密码。
• connect_identifier：可以是以下几种：
  • host:port/SID：例如 localhost:1521/orcl，这是主机、端口和数据库标识符。
  • service_name：例如 mydb，这是数据库的服务名。
  • 通过 TNS 名称：例如 mytns，对应 tnsnames.ora 文件中的 TNS 名称。

用户的锁定与解锁：

锁定用户

锁定用户后，该用户将无法登录数据库。

ALTER USER username ACCOUNT LOCK;

解锁用户

解锁用户后，该用户可以正常登录数据库。

ALTER USER username ACCOUNT UNLOCK;

创建用户：

CREATE USER username
  IDENTIFIED BY password
  [ DEFAULT TABLESPACE tablespace_name ]
  [ TEMPORARY TABLESPACE temp_tablespace_name ]
  [ PROFILE profile_name ]
  [ ACCOUNT LOCK | UNLOCK ];

• username：要创建的用户名。

• password：用户的密码。

• DEFAULT TABLESPACE：为用户指定一个默认表空间，所有该用户创建的对象（如表）将存储在这个表空间中。

• TEMPORARY TABLESPACE：指定该用户的临时表空间，通常用于排序和临时存储。

• PROFILE：用户的密码配置文件（可选）。

• ACCOUNT LOCK 或 UNLOCK：创建时可以选择锁定账户或解锁账户。

赋予系统权限

系统权限是指允许用户执行某些数据库操作的权限，比如创建表、创建用户等。

GRANT system_privilege [, system_privilege ] ...
TO user [, user ] ...
[ WITH ADMIN OPTION ];

• 赋予用户创建会话的权限（登录数据库）：

  GRANT CREATE SESSION TO user_name;
  

• 赋予用户创建表的权限：

  GRANT CREATE TABLE TO user_name;
  

• 赋予多个权限给用户：

  GRANT CREATE TABLE, CREATE VIEW TO user_name;
  

• 赋予权限并允许用户转授（WITH ADMIN OPTION）：

  GRANT CREATE USER TO user_name WITH ADMIN OPTION;
  

赋予对象权限

对象权限是指允许用户对特定数据库对象（如表、视图、序列等）执行某些操作的权限。

GRANT object_privilege [, object_privilege ] ...
ON object_name
TO user [, user ] ...
[ WITH GRANT OPTION ];

常见对象权限

• 允许用户查询表数据：

  GRANT SELECT ON schema_name.table_name TO user_name;
  

• 允许用户插入和更新表数据：

  GRANT INSERT, UPDATE ON schema_name.table_name TO user_name;
  

• 允许用户转授权限（WITH GRANT OPTION）：

  GRANT SELECT ON schema_name.table_name TO user_name WITH GRANT OPTION;
  

• 赋予用户对某张表的所有权限：

  GRANT ALL ON schema_name.table_name TO user_name;
  

赋予角色

角色是一组权限的集合，创建角色后可以将权限赋给角色，再将角色赋给用户。

GRANT role [, role ] ...
TO user [, user ] ...
[ WITH ADMIN OPTION ];

• 创建角色：

  CREATE ROLE role_name;
  

• 赋予权限给角色：

  GRANT CREATE SESSION, CREATE TABLE TO role_name;
  

• 将角色赋给用户：

  GRANT role_name TO user_name;
  

• 允许用户管理角色：

  GRANT role_name TO user_name WITH ADMIN OPTION;
  

常见角色

• dba:数据库管理员，系统最高权限，可以创建数据结构（表空间等。
• resource:可以创建实体（表、视图），不可以创建数据库的结构。
• connect:连接的权限，可以登录数据库，但是不可以创建实体和不可以创建数据库结构。

收回权限:

如果需要收回用户的权限，可以使用 REVOKE 命令。

REVOKE privilege [, privilege ] ...
ON object_name
FROM user [, user ] ...;

• 收回用户的查询权限：

  REVOKE SELECT ON schema_name.table_name FROM user_name;
  

• 收回用户的系统权限：

  REVOKE CREATE SESSION FROM user_name;
  

• 收回用户的角色：

  REVOKE role_name FROM user_name;
  

指定表空间

ALTER USER username DEFAULT TABLESPACE tablespace_name;

六.表管理

创建表：

CREATE TABLE table_name (
    column1 data_type [constraint],
    column2 data_type [constraint],
    ...
    [table_constraints]
)
TABLESPACE tablespace_name     -- 指定存储表的表空间	 
STORAGE (
    INITIAL size               -- 第一个区的大小
    NEXT size                  -- 后续扩展区的大小
    MINEXTENTS number          -- 初始分配的区数量
    MAXEXTENTS number | UNLIMITED -- 最大分配的区数量
    PCTINCREASE percentage     -- 每次扩展区大小增长的百分比
)
;

• table_name：表名，必须唯一。

• column1, column2：列名，每个列需要指定数据类型。

• data_type：列的数据类型，如 VARCHAR2, NUMBER, DATE。

• constraint：列级别约束，如 PRIMARY KEY, NOT NULL, UNIQUE。

• table_constraints：表级别约束，比如主键、外键等。

操作表：

修改表：

1.ALTER TABLE 用于修改现有表的结构。

添加列

ALTER TABLE table_name ADD (column_name datatype [constraints]);

修改列

ALTER TABLE table_name MODIFY (column_name datatype);

删除列

ALTER TABLE table_name DROP COLUMN column_name;

添加约束

ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_type (column_name);

2.DROP TABLE 用于删除表及其所有数据。

删除列

DROP TABLE table_name [CASCADE CONSTRAINTS];

3.TRUNCATE TABLE 用于快速清空表中的所有数据，但保留表结构。

清空表数据

TRUNCATE TABLE table_name;

数据操作：

1 插入数据

INSERT INTO 用于向表中添加数据。

插入一列

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

插入所有列

INSERT INTO table_name VALUES (value1, value2, ...);

2 更新数据

UPDATE 用于修改表中的数据。

更新所选数据

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

3 删除数据

DELETE 用于从表中删除数据。

删除选择数据

DELETE FROM table_name WHERE condition;

删除所有数据

DELETE FROM table_name;

与 TRUNCATE TABLE 的区别是，DELETE 可以使用事务回滚，而 TRUNCATE 不支持回滚。

4 查询数据

SELECT 用于从表中检索数据。

基本语法

SELECT column1, column2, ... FROM table_name WHERE condition;

查询所有列

SELECT * FROM table_name;

排序查询

SELECT * FROM employees ORDER BY salary DESC;

分组查询

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;