Oracle数据库的日常使用命令(SAG 考核)

Oracle 数据库的日常使用命令 1 基本知识 2 2 启动和关闭数据库 3 3 控制监听 3 4 数据库用户管理 3 5 Oracle 的权限管理 4 6 更改字符集为中文 5 7 查询语句 5 8 表空间管理 6 9 数据文件被误删后的处理 7 10 查询当前系统的配置参数 7 11 显示当前用户 8 12 Oracle 排错处理 8 13 查看表结构 8 14 查看数据库文件 8 15 将 select 查询出的结果保存至一个文件 9 16 存储过程 9 17 数据库的备份与恢复 10 Export 转入程序 10 Import 恢复程序 12 增量卸出 装入 14 18 如何查看各个表空间占用磁盘情况 15 19 如何知道数据裤中某个表所在的 tablespace 15 20 内核参数的应用 15 21 如何单独备份一个或多个表 16 22 如何单独备份一个或多个用户 16 23 如何显示当前连接用户 16 24 如何外连接 16 25 如何执行脚本 SQL 文件 17 26 如何搜索出前 N 条记录 18 27 为表创建序列 18 28 查看本用户下的各种对象的 SQL 脚本 18 29 SQL Plus 系统环境变量有哪些 如何修改 20 30 如何在 PL SQL 中读写文件 20 31 某个数据文件损坏 如何打开数据库 21 1 基本知识基本知识 一个表空间只能属于一个数据库 每个数据库最少有一个控制文件 建议 3 个 分别放在不同的磁盘上 每个数据库最少有一个表空间 SYSTEM 表空间 建立 SYSTEM 表空间的目的是尽量将目的相同的表存放在一起 以提高使用效率 只 应存放数据字典 每个数据库最少有两个联机日志组 每组最少一个联机日志文件 一个数据文件只能属于一个表空间 一个数据文件一旦被加入到一个表空间中 就不能再从这个表空间中移走 也不能再 加入到其他表空间中 建立新的表空间需要建立新的数据文件 数据文件被 ORACLE 格式化为 ORACLE 块 Oracle9i 以前版本中 ORACLE 块的大 小是在第一次创建数据库时设定的 并且以后不能改变 要想改变 只能重建数据库 一个段 segment 只能属于一个表空间 但可以属于多个数据文件 一个区 extent 只能属于一个数据文件 即区间 extent 不能跨越数据文件 PCTFREE 和 PCTUSED 总和不能大于等于 100 单独一个事务不能跨越多个回滚段 索引表不含 ROWID 值 一个事务即使不被提交 也会被写入到重做日志中 一个块的最大长度为 16KB 有 2K 4K 8K 16K 每个数据库最大文件数 按块大小 2K 块 20000 个文件 4K 块 40000 个文件 8K 块或以上 65536 个文件 2 启动和关闭数据库启动和关闭数据库 sqlplus nolog SQL conn as sysdba SQL startup 若启动文件名不是 ORACLE 缺省的文件名 则启动时应带启动目录与文件名 SQL startup pfile SQL shutdown immediate 3 控制监听控制监听 1 启动监听 lsnrctl start 2 停止监听 lsnrctl stop 3 查看监听状态 lsnrctl status 4 数据库用户管理数据库用户管理 1 创建用户 如 create user imuse203 identified by imuse203 default tablespace IMUSE01 temporary tablespace IMUSE01 TMP 2 修改用户 将 imuse203 的口令改为 hello alter user imuse203 identified by hello 将 imuse203 的缺省表空间改为 IMUSE02 alter user imuse203 default tablespace IMUSE02 将 imuse203 的临时表空间改为 IMUSE02 TMP alter user imuse203 tempory tablespace IMUSE02 TMP 3 删除用户 删除用户的命令为 DROP USER 用户名 CASCADE 若不使用 CASCADE 选项 则必须在该用户的所有实体都删除之后 才能删除该 用户 使用 CASCADE 后 则不论用户实体有多大 都一并删除 5 Oracle 的权限管理的权限管理 1 系统权限 ORACLE7 提供了 80 多种系统权限 每种系统权限允许用户执行特定的数据库操作 系统权限的授予命令为 GRANT 例如把创建任何表视图的权限授予 imuse01 用户 GRANT create any view TO imuse01 系统权限的回收命令为 REVOKE 例如将 create any view 权限从 imuse01 用户手中收 回 REVOKE create any view FROM imuse01 2 实体权限 每种类型的实体有与之相关的实体权限 授予实体权限的命令举例 将 basetab 表上的 Select 和 Insert 权限授给 imuse01 GRANT select insert ON basetab TO imuse01 回收实体权限的命令举例 将 basetab 表上的 Select 权限从 imuse01 手中回收 REVOKE select ON basetab FROM imuse01 3 管理角色 角色是许多权限和角色的组合 它极大地方便了 ORACLE 的权限管理 创建角色 如创建一个名为 dept1 的角色 口令为 hello CREATE ROLE ROLEiMUSE01 IDENTIFIED BY hello 使用角色 可以通过修改用户的缺省角色来使用角色 或通过授权的方法来将角色 授予其它角色或用户 如将 imuse01 用户的缺省角色修改为 RoleTmp ALTER USER imuse01 DEFAULT ROLE RoleTmp 将角色 RoleTmp 角色授予 imuse01 GRANT RoleTmpTO imuse01 使角色生效或失效 DBA 可以通过控制角色的生效或失效 来暂时回收用户的一部 分权限 如使 RoleTmp 角色失效 SET ROLE RoleTmp DISABLE 删除角色 这将会影响到拥有该角色的用户和其它角色的权限 用 DROP ROLE 命 令删除角色 如 DROP ROLE RoleTmp 6 更改字符集为中文更改字符集为中文 sqlplus nolog SQL conn as sysdba SQL SHUTDOWN IMMEDIATE SQL STARTUP MOUNT SQL ALTER SYSTEM ENABLE RESTRICTED SESSION SQL ALTER SYSTEM SET JOB QUEUE PROCESSES 0 SQL ALTER DATABASE OPEN SQL ALTER DATABASE CHARACTER SET ZHS16GBK 这一步一般会出错 所以需要重复执行上面从 SHUTDOWN IMMEDIATE 开始的所有语句 SQL SHUTDOWN IMMEDIATE SQL STARTUP 7 查询语句查询语句 当前存在哪些表空间 Select from v tablespace 表空间有多大 Select tablespace name sum bytes 1024 1024 from dba data files group by tablespace name 表空间还剩多少空闲空间 Select tablespace name sum bytes 1024 1024 from dba free space group by tablespace name 查询 imuse01 用户所使用的缺省表空间 select default tablespace from dba users where username imuse01 查询 imuse01 用户所使用的临时表空间 select temporary tablespace from dba users where username imuse01 查询当前用户所拥有的角色 select from session roles 查看违反唯一索引的表及列 如果插入数据时系统提示 unique constraint IMUSE01 SYS C004960 violated 则说明在为 IMUSE01 用户插入数据时违反了唯一索引 SYS C004960 查看违反唯一索引的表 select table name from user indexes where index name SYS C004960 查看违反唯一索引的列 select column name from user ind columns where index name SYS C004960 查看编译无效的存储过程 select object name from user objects where status INVALID and object type PROCEDURE 查看当前运行的实例名 select instance name from v instance 8 表空间管理表空间管理 1 创建表空间 create tablespace IMUSE01 datafile export home oracle oradata mdspdata imuse01 dat1 size 100M 2 增加表空间的大小 如将表空间 IMUSE01 增加 100M alter tablespace IMUSE01 add datafile export home oracle oradata mdspdata imuse01 dat2 size 100M 3 修改表空间的大小 如将表空间 IMUSE01 改为 1000M alter database datafile export home oracle oradata mdspdata imuse01 dat1 resize 1000M 4 删除表空间 DROP tablespace BPOS01 temp 5 创建临时表空间 create TEMPORARY TABLESPACE BPOS01 temp tempfile opt oracle oradata orcl bpos01 dat3 size 100M 9 数据文件被误删后的处理数据文件被误删后的处理 如果不小心物理上删除了一 Oracle 的数据文件 比如说 某应用表空间所对应数据文件 adc dbf Oracle 读控制文件时 和打开数据库时所面对的参数不一致 Oracle 数据库将启 动不了 解决这种问题的方法是把该文件对应的表空间先卸下 再删除 以保证控制文件 描述和物理上存在文件一致 以 sys 用户登录并进入 Sql Plus SQL startup mount SQL alter database datafile directory abc dbf offline SQL alter database open SQL drop tablespace abc 10 查询当前系统的配置参数查询当前系统的配置参数 有三种查询方法 1 静态查询 即直接查询initXXXX ora文件 XXXX为ORACLE的SID 因为有很多系统参数使用的是 缺省值 并未在该文件中给出 所以该方法不能看到所有参数及其含义 2 在 SQL PLUS 中用命令查询 1 显示所有数据库参数值 SQL show parameters 2 显示含有 sort 的参数的值 SQL show parameter sort 3 在 SQL PLUS 中用 SQL 语句查询 SQL select name type value from v parameter where name db block buffers 11 显示当前用户显示当前用户 sql show user 12 Oracle 排错处理排错处理 1 错误说明 ORACLE中出现的错误的格式为 错误类型 错误代码 错误信息 例如 ORA 1652 unable to extend temp segment by 128 in tablespace TEMP 一般来说 这种错误信息比较简单 但是可以根据这个信息用oerr命令得到更详细的信息 2 查看错误详细说明 oerr 是ORACLE提供的一个在服务器端使用的错误信息帮助命令 使用该命令前 必须先 用ORACLE用户登录到服务器上 命令格式为 oerr 错误类型 错误代码 返回信息格式为 错误代码 通用错误信息 错误原因 应采取的动作 如对上面的错误可用如下命令 oerr ora 1652 3 alert XXXX ora XXXX为ORALE的SID 文件的说明 alert XXXX ora是ORACLE中一个十分有用的的文件 该文件在服务器的具体位置由 initXXXX ora中的参数 background dump dest 的值决定 该文件中的信息有 数据库每 次STARTUP SHUTDOWN的具体信息 在数据库中进行的各种DML操作 数据库中出现 的各种错误的信息等等 内容十分详细 并且有各种信息发生的具体时间 如果遇到问题 可以仔细浏览该文件 根据问题发生的时间来寻找相应的信息 13 查看表结构查看表结构 SQL desc 表名 14 查看数据库文件查看数据库文件 共有三种数据库文件 控制文件 数据文件 日志文件 1 查看控制文件 select from v controlfile 2 查看数据文件 select status bytes name from v datafile 3 查看日志文件 select name from v logfile 15 将将 select 查询出的结果保存至一个文件查询出的结果保存至一个文件 SQL spool result txt SQL select from basetab SQL spool off 则从 basetab 查询出的结果都被保存到当前路径下的 result txt 文件中 16 存储过程存储过程 1 存储过程的写法 create or replace procedure proc name ifield1 in number sfield2 out varchar as v err code int v err msg varchar2 2048 begin select field2 into sfield2 from tabSp where field1 ifield1 DBMS OUTPUT PUT LINE sfield2 exception when others then begin v err code sqlcode v err msg sqlerrm DBMS OUTPUT PUT LINE v err code v err msg rollback end end proc name 注意 1 存储过程的输入输出参数以逗号间隔 局部变量部分以分号间隔 2 存储过程的输入输出参数部分 最后一个参数后没有逗号 3 存储过程的局部变量部分 最后一个变量后有分号 4 可把多个存储过程保存到一个文件中 文件名必须用 sql 后缀 5 每个存储过程结束后 要用 作为提交 2 存储过程的创建 sqlplus 用户名 密码 数据库标识 存储过程文件名 这里的存储过程文件名可以省略 sql 后缀 因为文件后缀缺省是 sql 3 存储过程的执行 sql execute 存储过程名字 参数 注意 1 如果执行存储过程时提示 必须说明标识符 存储过程名 则表明该存储过程不存在或 编译未成功 可用如下命令重新编译该存储过程 SQL alter procedure 存储过程名 compile 2 如果执行存储过程时提示 未找到数据在 imuse01 test adduser 有可能是在该存储过 程中存在类似 select col name into tmp from table name where 这样的语句 而查询出 的结果为空的缘故 3 如果执行存储过程时提示 SQL 缓冲区中无可执行的程序 说明此时缓冲区是空的 如在执行上面找不到相应记录的脚本后会提示该错误 4 如果执行存储过程时提示 输入被截为 1 个字符 表明某个 之后少一个回车符 5 如果执行存储过程时提示 创建的过程带有编译错误 可能是某个存储过程结束处少一 个 6 如果执行存储过程时提示 缺少表达式 有可能是某个变量没被赋值 17 数据库的备份与恢复数据库的备份与恢复 ORACLE 系统提供的 Export 转入 备份 Import 转出 恢复 应用程序实现备份与恢复 功能 Export 是在数据库打开并能使用的情况下备份数据库数据的实用程序 用 Export 将数据库 中的数据写到以二进制形式表示的操作系统文件中 ORACLE 该文件叫卸出文件 用 Export 可实现应用程序失败时的恢复 例如可把某个表或某些表恢复到执行该 Export 时的 状态 由于卸出文件的特殊格式 所以只能用 Import 实用程序将其读入数据库中 Export 转入程序 ORACLE 数据库有两类备份方法 第一类为物理备份 该方法实现数据库的完整恢复 但 数据库必须运行在归档模式下 且需要极大的外部存储设备 例如磁带机 第二类备份方 式为逻辑备份 客户服务中心业务数据库就是采用这种方式 这种方法不需要数据库运行 在归档模式下 不但备份简单 而且可以不需要外部存储设备 逻辑备份又分为三种模式 表模式 T 这种模式可以卸出当前用户数据库模式下的表 甚至是所有的表 具有特 权的用户可根据所指定的数据库模式来 限制表 卸出他们所包含的表 缺省情况是卸出 属于当前正在进行卸出的用户的所有表 用户模式 U 这种模式可以卸出当前用户数据库模式下的所有实体 表 数据和索引 全数据库模式 F 只有具有 EXP FULL DATABASE 角色的用户才可能以这种模式卸 出 以这种模式进行卸出的用户 除 SYS 模式下的内容之外 数据库中所有实体都可以卸 出 下面列出给用户赋予 EXP FULL DATABASE 角色的方法 要选择表 用户或全数据库方式 可相应指定 TABLES tablelist OWNER userlist 或 FULL y 1 表模式 EXP imuse01 imuse01 BUFFER 8192 或 64000 FILE imuse01 dmp 或 磁带设备 dev rmt0 TABLES imuse01 basetab 或 imuse01 basetab imuse01 serviceinfo ROWS Y COMPRESS N LOG EXP IMUSE01 SERVICEINFO LOG 参数说明 BUFFER缓冲区大小 FILE由 Export 创建的输出文件的名字 TABLES将要卸出的表名列表 ROWS指明是否卸出表中数据的行数 缺省为 Y COMPRESS指明在装入期间是否将表中数据压缩到一个区域中 如果 在卸出数据时 指定参数 COMPRESS Y 那么装入时 就会将数据压缩到一个初始区域中 这种选择可以保持初 始化区域的原始大小 缺省为 Y LOG指定一个接收有用信息和错误信息的文件 2 用户模式 EXP imuse01 imuse01 OWNER imuse01 BUFFER 8192 或 64000 FILE imuse01 dmp 或 磁带设备 dev rmt0 ROWS Y COMPRESS N LOG EXP IMUSE01 LOG 参数说明 OWNER将要卸出的用户名列表 BUFFER FILE ROWS COMPRESS L OG 同上 3 全数据库模式 EXP imuse01 imuse01 BUFFER 8192 或 64000 FILE EXP IMUSE01 dmp 或磁带设备 dev rmt0 FULL Y ROWS Y COMPRESS N LOG EXP IMUSE01 DB LOG 对于数据库备份 建议采用增量备份 即只备份上一次备份以来更改的数据 增量备份命令 EXP ICDMAIN ICD BUFFER 8192 或 64000 FILE EXP ICDMAIN DB DMP 或磁带设备 dev rmt0 FULL Y INCTYPE incremental ROWS Y COMPRESS N LOG EXP ICDMAIN DB LOG 参数说明 BUFFER FILE ROWS COMPR ESS LOG 同上 FULL指明是否卸出完整的数据库 如果 FULL Y 将以全数据库模式进行 卸出 INCTYPE增加卸出的类型 有效值有 complete 完全 comulative 固 定 和 incremental 增量 complete输出所有表 comulative将输入第一次完全输出后修改过的 表 incremental将输出前一次输出后修改过的表 说明 关于增量备份必须满足下列条件 只对数据库备份有效 且第一次需要 FULL Y 参数 以后需要 INCTYPE INCREMENTAL 参数 用户必须有 EXP FULL DATABASE 权限 Import 恢复程序 Import 和 Export 是两个相配套的实用程序 Export 把数据库中的数据卸出到操作系统文件 中 而 Import 实用程序则把 Export 卸出的数据恢复到数据库中 按备份方案确定恢复方案 例如 采用表逻辑备份方案 则恢复方案也采用恢复到表的方 式 不应恢复到用户 要使用 Import 必须具有 CREATE SESSION 特权 以便能注册到 ORACLE RDBMS 中去 这一特权属于在数据库创建时所建立的 CONNECT 角色 如果卸出文件是由某用户利用 EXP FULL DATABASE 角色创建的全数据库卸出 那么只 有具有 IMP FULL DATABASE 角色的用户才能装入这样的文件 数据库的逻辑恢复分为表 用户 数据库三种模式 1 表模式 恢复方法为 IMP imuse01 imuse01 FILE 文件名 LOG LOG 文件名 ROWS Y COMMIT Y BUFFER Y IGNORE Y TABLES 表名 1 表名 2 表名 3 表名 4 参数说明 BUFFER缓冲区大小 FILE用于装入的卸出文件名字 TABLES将要装入的表名列表 ROWS指明是否装入表数据的行数 缺省为 Y IGNORE指明如何处理实体创建错误 指定 IGNORE Y 当试图创 建数据库实体时 忽略实体存在错误 对除了表之外的其他 实体 指定 IGNORE Y Import 不报告错误 继续执行 而指定 IGNORE N 时 Import 在继续执行前报告实体创建 错误 COMMIT指明在每个矩阵插入之后是否提交 缺省时 Import 在装入 每个实体之后提交 指定 COMMIT N 时 如有错误产生 Import 在记录装入下一个实体之前 完成一个回退 指定 COMMIT Y 时 可以抑制回滚字段无限制增大 并改善大 量装入时的性能 表具有唯一约束时 这种选择比较好 如 果再次开始装入 将拒绝装入已经装入的任何行 原因是非 致命性错误 表具有非唯一约束时 指定 COMMIT N 可能 是比较好的选择 因为重新装入可能会产生重复行 LOG指定一个接收有用信息和错误信息的文件 2 用户模式 如果备份方式为用户模式 采用下列恢复方法 IMP system manager FROMUSER imuse01 TOUSER imuse01 FILE 文件名 LOG LOG 文件名 ROWS Y COMMIT Y BUFFER Y IGNORE Y 参数说明同上 3 数据库模式 如果备份方式为数据库模式 采用下列恢复方法 IMP system manager FULL Y FILE 文件名 LOG LOG 文件名 ROWS Y COMMIT Y BUFFER Y IGNORE Y 字符集转换 对于单字节字符集 例如 US7ASCII 恢复时 数据库自动转换为该会话的字符集 NLA LANG 参数 对于多字节字符集 例如 ZHS168CGB 恢复时 应尽量使字符集 相同 避免转换 如果要转换 目标数据库的字符集应是输出数据库字符集的超集 增量卸出 装入 下面介绍利用 Export Import 实用程序对 ORACLE 数据库进行备份 恢复的方法 增量卸 出 装入 增量卸出是一种常用的数据备份方法 包括 3 个子类 1 完全 增量卸出 就是对整个 ORACLE 数据库进行完全卸出 如 exp system 口令 inctype complete full y file today dmp 1 增量型 增量卸出 即从 ORACLE 数据库中卸出上次卸出操作之后所有数据库的变化信息 如 exp system 口令 inctype incremental file today dmp 增量型卸出文件的大小 可能只是完全卸出文件大小的 1 具体要看 新信息或更新过 的信息 的总量而定 2 累积型 增量卸出 累积型卸出方式只是卸出自上次 完全 卸出之后数据库中变化了的信息 用法如下 exp system 口令 inctype cumulative file today dmp DBA 可以排定一个备份日程表 用数据卸出的三个不同方式合理高效地完成数据库的备份 任务 比如 DBA 作如下安排 星期一 完全卸出 F1 星期二 增量卸出 I1 星期三 增量卸出 I2 星期四 累积卸出 C1 星期五 增量卸出 I3 星期六 增量卸出 I4 如果在星期日 数据库遭到意外破坏 DBA 可按以下步骤来恢复数据库 用命令 CREATE DATABASE 重新生成你的数据库结构 最近增量装入 I4 imp system 口令 inctype system full y file I4 完全增量装入 F1 imp system 口令 inctype restore full y file F1 累积增量装入 C1 imp system 口令 inctype restore full y file C1 对于由累积装入或完全装入尚未能装入的信息 作增量装入 imp system 口令 inctype restore full y file I3 imp system 口令 inctype restore full y file I4 注意 在 I1 和 I2 中的信息已包括在 C1 中了 18 如何查看各个表空间占用磁盘情况 如何查看各个表空间占用磁盘情况 SQL col tablespace format a20 SQL select b file id 文件 ID 号 b tablespace 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 id b bytes order by b file id 19 如何知道数据裤中某个表所在的如何知道数据裤中某个表所在的 tablespace select tablespace name from user tables where table name TEST select from user tables 中有个字段 TABLESPACE NAME oracle select from dba segments where 20 内核参数的应用内核参数的应用 shmmax 含义 这个设置并不决定究竟 Oracle 数据库或者操作系统使用多少物理内存 只决 定了最多可以使用的内存数目 这个设置也不影响操作系统的内核资源 设置方法 0 5 物理内存 例子 Set shmsys shminfo shmmax 10485760 shmmin 含义 共享内存的最小大小 设置方法 一般都设置成为 1 例子 Set shmsys shminfo shmmin 1 shmmni 含义 系统中共享内存段的最大个数 例子 Set shmsys shminfo shmmni 100 shmseg 含义 每个用户进程可以使用的最多的共享内存段的数目 例子 Set shmsys shminfo shmseg 20 semmni 含义 系统中 semaphore identifierer 的最大个数 设置方法 把这个变量的值设置为这个系统上的所有 Oracle 的实例的 init ora 中 的最大的那个 processes 的那个值加 10 例子 Set semsys seminfo semmni 100 semmns 含义 系统中 emaphores 的最大个数 设置方法 这个值可以通过以下方式计算得到 各个 Oracle 实例的 initSID ora 里边的 processes 的值的总和 除去最大的 Processes 参数 最大的那个 Processes 2 10 Oracle 实例的个数 例子 Set semsys seminfo semmns 200 semmsl 含义 一个 set 中 semaphore 的最大个数 设置方法 设置成为 10 所有 Oracle 实例的 InitSID ora 中最大的 Processes 的值 例子 Set semsys seminfo semmsl 200 21 如何单独备份一个或多个表 如何单独备份一个或多个表 exp 用户 密码 tables 表 1 表 2 22 如何单独备份一个或多个用户 如何单独备份一个或多个用户 exp system manager owner 用户 1 用户 2 用户 n file 导出文件 23 如何显示当前连接用户如何显示当前连接用户 SHOW USER 24 如何外连接如何外连接 Select a from bsempms a bsdptms b where a dpt no b dpt no Select a from bsempms a bsdptms b wherea dpt no b dpt no 25 如何执行脚本如何执行脚本 SQL 文件文件 SQL PATH filename sql 如 opt oracle 下建立 sql 目录 存放待执行的 SQL 文件 oracle linux66 sql ll total 192 rw r r 1 wwz users 580 2009 11 12 09 46 bme create seq sql rw r r 1 wwz users 78027 2009 11 12 09 46 bme oracle data sql rw r r 1 wwz users 26731 2009 11 12 09 46 bme oracle sql rw r r 1 wwz users 20466 2009 11 12 09 46 BPOS create table sql rw r r 1 wwz users 1309 2009 11 12 09 46 BPOS drop table sql rw r r 1 wwz users 732 2009 11 12 09 46 createtablespace sql rw r r 1 wwz users 1543 2009 11 12 09 46 drop tables sql rw r r 1 wwz users 132 2009 11 12 09 46 drop talbe list sql rw r r 1 wwz users 1865 2009 11 12 09 46 init BPOS sql rw r r 1 wwz users 16312 2009 11 12 09 46 init dic sql rw r r 1 wwz users 366 2009 11 12 09 46 init oracle list sql rw r r 1 wwz users 1194 2009 11 12 09 46 init order sql rw r r 1 wwz users 6198 2009 11 12 09 46 init right sql rw r r 1 wwz users 254 2009 11 12 09 46 oracle list sql 以 bposuser 用户登录 执行 SQL 文件 oracle linux66 sql sqlplus bposuser bposuser SQL Plus Release 11 1 0 7 0 Production on Thu Nov 12 13 49 28 2009 Copyright c 1982 2008 Oracle All rights reserved Connected to Oracle Database 11g Enterprise Edition Release 11 1 0 7 0 64bit Production With the Partitioning Oracle Label Security OLAP Data Mining Oracle Database Vault and Real Application Testing options SQL sql init oracle list sql 26 如何搜索出前如何搜索出前 N 条记录 条记录 SELECT FROM empLOYEE WHERE ROWNUM show all 查看所有 68 个系统变量值 SQL show user 显示当前连接用户 SQL show error 显示错误 SQL set heading off 禁止输出列标题 默认值为 ON SQL set feedback off 禁止显示最后一行的计数反馈信息 默认值为 对 6 个或更多的记录 回送 ON SQL set timing on 默认为 OFF 设置查询耗时 可用来估计 SQL 语句的执行时间 测试性能 SQL set sqlprompt SQL 设置默认提示符 默认值就是 SQL SQL set linesize 1000 设置屏幕显示行宽 默认 100 SQL set autocommit ON 设置是否自动提交 默认为 OFF SQL set pause on 默认为 OFF 设置暂停 会使屏幕显示停止 等待按下 ENTER 键 再显示下一页 SQL set arraysize 1 默认为 15 SQL set long 1000 默认为 80 30 如何在如何在 PL SQL 中读写文件 中读写文件 PL SQL 3 3 以上的版本中 UTL FILE 包允许用户通过 PL SQL 读写操作系统文件 如 下 declare file handle UTL FILE FILE TYPE begin file handle UTL FILE FOPEN tmp 文件名 w UTL FILE PUTF file handle 写入的信息 n UTL FILE FCLOSE file handle exception WHEN utl file invalid path THEN raise application error 20000 ERROR Invalid path for file or path not in INIT ORA end PutF 过程用来以指定格式把文本写入一个文件 Put Line 过程把一个指定的字符串写入文件并在文件中开始新的一行 31 某个数据文件损坏 如何打开数据库 某个数据文件损坏 如何打开数据库 C svrmgrl svrmgrl connect internal svrmgrl shutdown svrmgrl startup mount ARCHIVELOG 模式命令 文件名要大写 svrmgrl alter database datafile C TEST ORA offline NOARCHIVELOG 模式命令 svrmgrl alter database datafile C TEST ORA offline drop svrmgrl alter database open Oracle数据库用户管理 1 创建一个用户 create user username identified by userpasswd default tablespace data tablespace 默认表空间 数据表空间 temporary tablespace temp table 临时表空间 quota 15M on data tablespace 在数据表空间上的使用限额 password expire 确定用户第一次登陆时更改密码 2 对用户加锁和解锁 alter user username account lock alter user username account unlock 3 删除用户 drop user username 此用户没有数据对象且没有连接到数据库 drop user username cascade 删除用户以及用户的数据对象 4 查看用户信息 desc dba users select from dba users 用户状态信息等 desc dba ts quotas select from dba ts quotas 查看用户使用表空间的限额等 5 更改用户对表空间的使用限额 Alter user username quota 15M on data tablespace 注 1 用户不能更改在undo 回滚 表空间上使用限额 此时提示错误 2 用户根改对 temp 临时 表空间的使用限额 没有意义 但不会提示错误 ORACLE LINUX Linux 使用 ORACLE 的常用设置与查看 设置每页显示条数 set pagesize 9999 每页显示条 数 0 50000 设置统计 sql 使用时间 set timing on 查看数据库表的结构 desc dba users 表名或视图名 查看 sqlplus 当前连接的用户 show user 从 sqlplus 切换到主机 host exit 把 sqlplus 执行命令输出到文件 spool filename SQL spool off ORCALE 数据字典 静态数据字典与动态数据字典 静态数据字典主要是在用户访问数据字典时不会发生变化 动态数据字典是依赖数据库运行的性能的 反映数据库运行 的一些内在信息 故在访问这类数据字典时 往往不是一成不变的 一 静态数据字典 这类数据字典主要是由表和视图组成 应该注意的是 数据字典中的表是不能直接被访问 的 但是可以访问数据字典中的视图 静态数据字典中的视图分为三类 它们分别由三个前缀 够成 user all dba 1 USER 视图 只有当前用户可以访问的视图 该视图存储了关于当前用户所拥有的对象 的信息 即所有在该用户模式下的对象 2 All 视图 用户可以访问的所有视图 该试图存储了当前用户能够访问的对象的信息 与user 相比 all 并不需要拥有该对象 只需要具有访问该对象的权限即可 3 DBA 视图 只有sysdba才能访问的视图 该视图存储了数据库中所有对象的信息 前 提是当前用户具有访问这些数据库的权限 一般来说必须具有管理员权限 常用的静态视图 user users视图 主要描述当前用户的信息 主要包括当前用户名 帐户id 帐户状态 表空间名 创建时间等 例如执行下列命令即可返回这些信息 select from user users user tables视图 主要描述当前用户拥有的所有表的信息 主要包括表名 表空间名 簇名等 通过此视图可以 清楚了解当前用户可以操作的表有哪些 执行命令为 select from user tables user objects视图 主要描述当前用户拥有的所有对象的信息 对象包括表 视图 存储过程 触发器 包 索引 序列等 该视图比user tables视图更加全面 例如 需要获取一个名为 package1 的对 象类型和其状态的信息 可以执行下面命令 select object type status from user objects where object name upper package1 这里需注意upper的使用 数据字典里的所有对象均为大写形式 而PL SQL里不是大小写敏 感的 所以在实际操作中一定要注意大小写匹配 二 动态数据字典 Oracle包含了一些潜在的由系统管理员如SYS维护的表和视图 由于当数据库运行的时候它 们会不断进行更新 所以称它们为动态数据字典 或者是动态性能视图 这些视图提供了关 于内存和磁盘的运行情况 所以我们只能对其进行只读访问而不能修改它们 Oracle中这些动 态性能视图都是以v 开头的视图 常用的静态视图 v session V SESSION是基础信息视图 用于找寻用户SID或SADDR 不过 它也有一些列会动态的变 化 可用于检查用户 如STATUS列 该列用来判断session状态的 一般有 Achtive 正执 行SQL语句 waiting for using a resource Inactive 等待操作 即等待需要执行的SQL语句 Killed 被标注为删除 V SESSION常用列 Session信息 SID SESSION标识 常用于连接其它列 SADDR session的标记的地址 即会话地址 SERIAL 如果某个SID又被其它的session使用的话则此数值自增加 当一个 SESSION结束 另 一个SESSION开始并使用了同一个SID AUDSID 审查session ID唯一性 确认它通常也用于当寻找并行查询模式 USERNAME 当前session在oracle中的用户名 Client信息 数据库session被一个运行在数据库服务器上或从中间服务器甚至桌面通过SQL Net连接到 数据库的客户端进程启动 下列各列提供这个客户端的信息 OSUSER 客户端操作系统用户名 MACHINE 客户端执行的机器 TERMINAL 客户端运行的终端 PROCESS 客户端进程的ID PROGRAM 客户端执行的客户端