[原创]Oracle常见问题总结

SQL语句

1.       获取有哪些用户在使用数据库

 select username from v$session;

2.       获取数据库的SID

 select name from v$database;

 

3.       查看最大会话数

 SELECT * FROM V$PARAMETER WHERE NAME LIKE 'proc%';

 

4.       查看数据库中所有用户及所有表

 Select owner, table_name from dba_tables;

 

5.       分辨某个用户是从哪台机器登陆ORACLE的

 SELECT machine , terminal FROM V$SESSION;

 

6.       查询每个用户的权限

 SELECT * FROM DBA_SYS_PRIVS;

7.       查询当前用户对象

 SELECT * FROM USER_OBJECTS;

 

表空间管理和用户管理

 

8.       查询表空间信息

 SELECT * FROM DBA_DATA_FILES;

 

9.       运行SQLPLUS时不用输入用户名和密码，进入之后使用CONNECT
         SQLPLUS /NOLOG
        SQL>CONNECT SCOTT/TIGER

10.       把表移到另一个表空间
ALTER TABLE TABLENAME MOVE TABLESPACE TABLESPACENAME;

11.   创建表

CREAE TABLE TABLENAME
(COLUMN1 COLUTYPE DEFAULT(VALUE) NOT NULL)
(COLUMN2 COLUTYPE DEFAULT(VALUE) NOT NULL);

12.   建立主键

ALTER TABLE TABLENAME
ADD CONSTRAINT CONSTRAINTNAME PRIMARY KEY(COLUMN1,COLUMN2)

 

13.   删除约束

ALTER TABLE TABLENAME  DROP CONSTRAINT constraintname;

DROP TABLE TABLENAEM CASCADE CONSTRAINTS;(删除表后将所用的外键删除)

 

14.   给表增加列

 ALTER TABLE TABLENAME
ADD COLUMN COLUTYPE DEFAULT(VALUE) NOT NULL;

 

15.   给列增加缺省值

 ALTER TABLE TABLENAME
MODIFY COLUMNNAME  DEFAULT(VALUE) NOT NULL;

 

16.   建立主键

ALTER TABLE TABLENAME
ADD CONSTRAINT CONSTRAINTNAME PRIMARY KEY(COLUMN1,COLUMN2)

17.   给表增加外键

 ALTER TABLE  TABLENAME
 ADD CONSTRAINT  CONSTRAINTNAME
 FOREIGN KEY(COLUMN) REFERENCES TABLE1NAME(COLUMN1);

 

18.   重新建立索引

 ALTER INDEX INDEXNAME REBUILD TABLESPACE  TABLESPACE;

 

19.   创建用户、赋予权限

 CREATE USER USER_DATA PROFILE DEFAULT IDENTIFIED BY  USER_DATA
    DEFAULT
    TABLESPACE USER_DATA  TEMPORARY
    TABLESPACE USER_DATA  ACCOUNT UNLOCK;
 
GRANT CONNECT TO USER_DATA;
GRANT RESOURCE TO USER_DATA;

20.   修改数据文件大小

 ALTER DATABASE
 DATAFILE 'c:/USERS01113.DBF' RESIZE 40M;

 

21.   删除表空间

DROP TABLESPACE USER_DATA INCLUDING CONTENTS;

22.   增加数据文件

ALTER TABLESPACE  USER_DATA
  ADD DATAFILE 'c:/USERS01113.DBF' SIZE 50M;

ALTER TABLESPACE USER_DATA
  ADD DATAFILE 'c:/USERS01114.DBF'  SIZE 50M
  AUTOEXTEND ON

23.   查看表空间和数据文件

 select file_name,tablespace_name,autoextensible from dba_data_files;

 

24.   在SQLPLUS中调用存储过程

 SET SERVEROUTPUT ON
declare
 out_param varchar2(100);
begin
 your_proc(1,out_param);
 dbms_output.put_line(out_param);
end;
/
SET SERVEROUTPUT OFF

 

25.   查看当前会话

 

(1)    userenv() 函数

select userenv('language') from dual 字符集
        select userenv('isdba') from dual 是否DBA
        select userenv('sessionid') from dual sessionid
        select userenv('TERMINAL') from dual 客户端名字
        select userenv('INSTANCE') from dual 实例数

 

(2)    SYS_CONTEXT() 函数

select SYS_CONTEXT('USERENV','CURRENT_SCHEMA')  from dual;  当前模式
        select SYS_CONTEXT('USERENV','CURRENT_SCHEMAID')  from dual;  当前模式ID
        select SYS_CONTEXT('USERENV','CURRENT_USER')  from dual; 当前用户 
        select SYS_CONTEXT('USERENV','DB_NAME')  from dual; 数据库
        select SYS_CONTEXT('USERENV','HOST')  from dual; 主机

select SYS_CONTEXT('USERENV','IP_ADDRESS')  from dual; 查看IP

 

26.   使用OEM备份或者EXP的步骤

 

WIN2000下：

(1). 控制面板――>管理工具―― >计算机管理――>本地用户和组――>用户――>新建用户sys和sysman(sys和sysman 的帐号要和登陆数据库的帐号相同)；
(2)．控制面板――>管理工具―― >本地安全策略――>本地策略――>用户权利指派――>
作为批处理作业登陆――>添加sys和sysman两个帐号。
(3)．使用Enterprise Manager配置辅助工具
开始→程序→Oracle - OraHome81→Enterprise Manager→Configuration Assistant
a、使用Configuration Assistant工具来创建一个新的资料档案库。

(4)．控制面板――>管理工具―― > 服务，查看OracleOraHome81ManagementServer是否启动，如果没有启动，则手动启动该服务。
(5)．以sysman/oem_temp（default）登陆DBA Studio
（第二个选项：登陆到Oracle Management Server）,立即修改密码为你刚才在NT下建的用户sysman的密码。
(6)． 以sysman/ *** (bluesky) 从开始→程序→Oracle - OraHome81→Console 登陆到 控制台。
在 系统→首选项→首选身份证明（我的首选身份设置如下：）
DEFAULT节点：name:sysman
DEFAULT数据库：name:sys
(7)． 在搜索/添加结点后，以sysman/ *** 登陆到该结点，以sys/ *** as sysdba登陆数据库（也就是在首选身份设置的结果）。
(8)． 在工具→备份管理→向导→预定义备份策略（自定义备份策略）→提交备份计划
(9)．从开始→程序→Oracle - OraHome81→Console 登陆到 控制台，查看活动（历史记录）可以看到你的备份是否成功，如果不成功，可以点击备份看明细。（我第一次也没成功，后来我修改系统的临时目录C:/WINNT/Temp→c:/temp/systmp,重新启动机器就ok了）

 

27.   修改表的列名

 Oracle9i:
alter table xxx rename column xx to yy;

Oracle8i & lower version
connect sys/passed;
update col$ set name=xx where obj#=对象id and name = 字段
(一般不要这样用，会造成意想不到的结果)
注：最好是删除再建立新的列

 

28.   ORACLE的登录问题，用户名和密码

可以直接输入：
    internal/oracle@serivce_name
　　sys/change_on_install@serivce_name
　　system/manager@serivce_name
　　scott/tiger@serivce_name
注意：
9i中没有internal/oracle
如果选择典型安装则有　scott用户
如果自定义可以不安装 　scott用户
如果是本机则可以省略@serivce_name

oem:（ORACLE ENTERPRISE MANAGER）
sysman/oem_temp

 

29.   如何在Windows 2000下将Oracle完全卸载？

一、系统环境：
(1)、操作系统：Windows 2000 Server，机器内存128M
(2)、数据库：　Oracle 8i R2 (8.1.6) for NT 企业版
(3)、安装路径：D:/ORACLE

二、卸载步骤：
(1)、开始－＞设置－＞控制面板－＞管理工具－＞服务
   停止所有Oracle服务。

(2)、开始－＞程序－＞Oracle - OraHome81－＞Oracle Installation Products－＞Universal Installer
   卸装所有Oracle产品

(3)、运行regedit，选择HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE，按del键删除这个入口。

(4)、运行regedit，选择HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services，滚动这个列表，删除所有Oracle入口

(5)、从桌面上、STARTUP（启动）组、程序菜单中，删除所有有关Oracle的组和图标

(6)、重新启动计算机，重起后才能完全删除Oracle所在目录

(7)、删除与Oracle有关的文件，选择Oracle所在的缺省目录C:/Oracle，删除这个入口目录及所有子目录，

　 并从Windows 2000目录（一般为C:/WINNT）下删除以下文件

　 ORACLE.INI、oradim80.INI

(8)、WIN.INI文件中若有[ORACLE]的标记段，删除该段

--------------------------------------------------------------------

 

30.   exp与imp的具体用法

 

exp username/password@mzbs_61 full=y file=yourdata.dmp grants=y rows=y
imp username/password full=y ignore=y file=yourdata.dmp grants=y

exp mzbs/mzbs@mzbs_61 file = c:/zzzzzzz.dmp grants = y rows = y
imp mzbs/mzbs@mzbs_61 file = c:/zzzzzzz.dmp grants = y ignore=y  FULL=Y

(1)

exp参数：
关键字   说明（默认）  
----------------------------------------------
USERID   用户名/口令
FULL   导出整个文件 (N)
BUFFER   数据缓冲区的大小
OWNER   所有者用户名列表
FILE   输出文件 (EXPDAT.DMP)
TABLES   表名列表
COMPRESS  导入一个范围 (Y)
RECORDLENGTH  IO 记录的长度
GRANTS   导出权限 (Y)
INCTYPE   增量导出类型
INDEXES   导出索引 (Y)
RECORD   跟踪增量导出 (Y)
ROWS   导出数据行 (Y)
PARFILE   参数文件名
CONSTRAINTS  导出限制 (Y)
CONSISTENT  交叉表一致性
LOG   屏幕输出的日志文件
STATISTICS  分析对象 (ESTIMATE)
DIRECT   直接路径 (N)
TRIGGERS  导出触发器 (Y)
FEEDBACK  显示每 x 行 (0) 的进度
FILESIZE  各转储文件的最大尺寸
QUERY   选定导出表子集的子句

imp参数：
关键字   说明（默认）  
----------------------------------------------
USERID   用户名/口令
FULL   导入整个文件 (N)
BUFFER   数据缓冲区大小
FROMUSER  所有人用户名列表
FILE   输入文件 (EXPDAT.DMP)
TOUSER   用户名列表
SHOW   只列出文件内容 (N)
TABLES   表名列表
IGNORE   忽略创建错误 (N)
RECORDLENGTH  IO 记录的长度
GRANTS   导入权限 (Y)
INCTYPE   增量导入类型
INDEXES   导入索引 (Y)
COMMIT   提交数组插入 (N)
ROWS   导入数据行 (Y)
PARFILE   参数文件名
LOG   屏幕输出的日志文件
CONSTRAINTS  导入限制 (Y)
DESTROY   覆盖表空间数据文件 (N)
INDEXFILE  将表/索引信息写入指定的文件
SKIP_UNUSABLE_INDEXES 跳过不可用索引的维护 (N)
ANALYZE   执行转储文件中的 ANALYZE 语句 (Y)
FEEDBACK  显示每 x 行 (0) 的进度
TOID_NOVALIDATE  跳过指定类型 id 的校验
FILESIZE  各转储文件的最大尺寸
RECALCULATE_STATISTICS 重新计算统计值 (N)

(2)

一、建立一个expdata.sql文件

USERID=RMTAFIS/3       这里写你的用户名和密码
BUFFER=32768
OWNER=RMTAFIS         这里写导出的用户
FILE=E:/Exp/RMTAFIS.DMP      导出的文件,可以是相对路径
ROWs=Y
GRANTS=Y
COMPRESS=Y
CONSISTENT=Y

二、建立一个expdata.bat
exp parfile=expdata.sql
如果是805
exp80 parfile=expdata.sql
双击expdata.bat就导出数据了

 

30.   全数据库的导入与导出

 exp username/password full=y file=yourdata.dmp grants=y rows=y
imp username/password full=y ignore=y file=yourdata.dmp grants=y

 

31.   单引号的插入问题

SQL> insert into a values('i''m good');　　　　　　 --两个''可以表示一个'

SQL> insert into a values('i'||chr(39)||'m good');　--chr(39)代表字符'

SQL> insert into a values('a'||'&'||'b');　　　　　

 

32.   ORACLE动态库及配置文件

Tnsnames.ora CORE35O.DLL NASNSNT.DLL NAUNTSNT.DLL NCRNT.DLL Nlnt.dll NLSRTL32.DLL Nnfdnt.dll NNFNNT.DLL NSNT.DLL NTNT.DLL NTTNT.DLL CIW32.DLL Ora73.dll OTRACE73.DLL Sqlnet.ora Sqltnsnt.dll CORE35.DLL