本文共 4120 字，大约阅读时间需要 13 分钟。

Oracle 数据库操作指南

用户操作

在 Oracle 数据库中，用户管理是日常运维的重要环节。以下是对用户权限和操作的详细说明。

1. 用户权限

• 默认用户权限：Oracle 提供了三种默认用户权限：
  • sys：拥有最高权限 system。
  • scott：本地管理员，权限级别为次高。
  • 普通用户：默认密码为 tiger，且未锁定。

2. 登录方式

• sys 用户登录：使用以下命令登录系统管理员账户：

  sqlplus / as sysdba;

• scott 用户登录：普通用户登录方式：

  sqlplus scott/tiger@orcl;

3. 用户管理

• 创建用户：管理员可以创建普通用户账户：

  create user username identified by password;

• 解锁用户：激活用户账户：

  alter user username account unlock;

• 修改密码：管理员可更改用户密码：

  alter user username identified by new_password;

• 授予权限：根据需求赋予用户权限，例如：

  grant create session to username;
  grant insert on tablespace_name to username;

4. 撤销权限

• 撤销操作：使用 revoke 语句撤销权限：

  revoke create session from username;

表空间操作

表空间是 Oracle 数据库中存储数据的逻辑容器，管理表空间可以优化数据库性能。

1. 创建表空间

• 临时表空间：创建用于临时使用的表空间：

  create temporary tablespace username_temp tempfile '/path/to/temp.dbf' size 50m autoextend on next 50m maxsize 20480m;

• 数据表空间：创建持久化表空间：

  create tablespace username_data datafile '/path/to/data.dbf' size 50m autoextend on next 50m maxsize 20480m;

2. 表空间管理

• 删除表空间：删除不再使用的表空间：

  drop tablespace tablespace_name;

• 重命名表空间：修改表空间名称：

  alter tablespace tablespace_name rename to new_name;

3. 查看表空间信息

• 表空间列表：查询所有表空间：

  select * from dba_tablespaces;

• 表空间大小：查看表空间存储情况：

  select t.tablespace_name, round(sum(bytes / (1024 * 1024)), 0) ts_size
  from dba_tablespaces t, dba_data_files d
  where t.tablespace_name = d.tablespace_name
  group by t.tablespace_name;

数据库信息查看

了解数据库状态和版本信息对于日常维护至关重要。

1. 查看数据库版本

• 数据库版本：查询数据库版本信息：

  select version from v$version;

2. 查看控制文件和日志文件

• 控制文件：查询控制文件位置：

  select name from v$controlfile;

• 日志文件：查找日志文件成员：

  select member from v$logfile;

表结构操作

表结构管理涉及表名改动、字段变更等操作。

1. 表名改动

• 重命名表：修改表名：

  alter table old_table rename to new_table;

2. 表字段操作

• 字段添加：新增字段：

  alter table table_name add column_name column_data_type;

• 字段删除：移除不再使用的字段：

  alter table table_name drop column column_name;

3. 主键管理

• 添加主键：创建主键约束：

  alter table table_name add constraint pk_column_name primary key (column1, column2);

• 删除主键：移除主键约束：

  alter table table_name drop constraint pk_column_name;

SQL 标准格式

编写高效的 SQL 语句是提升工作效率的关键。

1. 插入操作

• 简单插入：

  insert into table_name (column1, column2) values ('值1', '值2');

• 复杂插入：使用多行插入：

  insert all
  into table_name (column1, column2)
  values
  ('值1', '值2'),
  ('值3', '值4');

2. 更新操作

• 简单更新：

  update table_name
  set column_name = '新值'
  where 条件;

• 存在则更新不存在则插入：

  begin
    update table_name
    set column_name = '新值'
    where 条件;
    if sql%rowcount = 0 then
      insert into table_name (column1, column2) values ('值1', '值2');
    end if;
  end;

特殊场景处理

处理复杂场景时，掌握关键技巧可以大大提升效率。

1. 数据复制

• 复制表数据：直接复制表结构和数据：

  insert into table_name select * from table_name_other;

• 部分数据复制：选择特定字段：

  insert into tableA (column1, column2)
  select column1, column2
  from tableB;

2. 数据统计

• 最大值查询：使用 GREATEST 和 LEAST 函数：

  select greatest(col1, col2, col3) from table_name;
  select least(col1, col2, col3) from table_name;

• 平均值计算：结合 SUM 和 AVG 函数：

  select (sum(column1) + sum(column2)) / count(*) / n from table_name;

3. 条件判断

• 条件判断：使用 CASE 语句：

  select case when col > 95 then 0 else col end from table_name;

Oracle 11g 空表处理

1. 导出空表

• 空表导出：由于 Oracle 11g 不支持空表导出，可先分析表：

  analyze table table_name compute statistics;

• 空表处理：通过 ALTER TABLE 语句分配空间：

  alter table table_name allocate extent;

用户过期管理

1. 永久激活用户

• 修改用户过期时间：

  alter profile default limit password_life_time unlimited;

连接数管理

1. 查看连接数

• 连接线程数：

  select count(*) from v$process;

• 连接配置：查看数据库参数：

  select * from v$parameter where name = 'processes';

2. 修改最大连接数

• 配置修改：

  alter system set processes = 1000 scope = spfile;

其他数据库问题

1. 系统时间查询

• 当前时间：

  select systimestamp from dual;

• 日期查询：

  select sysdate from dual;

2. MySQL 安装

• 登录 MySQL：

  mysql -u root -p;

• 授权 root 用户远程登录：

  grant all privileges on *.* to 'root'@'%' identified by 'root';
  flush privileges;

3. 表删除

• 删除用户表：

  select 'drop table '||table_name||';' from user_tables;

Oracle 回收站管理

1. 查询回收站

• 查询回收站内容：

  select object_name, type, original_name from user_recyclebin;

2. 清理回收站

• 清理回收站：

  purge recyclebin;

导出与导入

1. 数据库导出

• 导出多张表：

  exp username/password@orcl tables=(table1, table2) file=e:\table.dmp;

• 导出单张表：

  exp username/password@orcl table=table1 file=e:\table.dmp;

2. 数据库导入

• 导入数据：

  imp username/password@orcl file=e:\table.dmp full=y commit=y;

总结

以上文档涵盖了 Oracle 数据库的核心操作，包括用户管理、表空间操作、数据库信息查看、表结构操作、SQL 标准格式、特殊场景处理等内容。通过合理运用这些操作，可以有效地进行数据库维护和管理，确保数据库运行的稳定性和安全性。