首页 > 代码库 > oracle
oracle
oracle常用命令 自己总结
1. 启动和关闭数据库
以oracle用户登录
su - oracle
sqlplus / as sysdba;或者
sqlplus /nolog;
SQL >conn / as sysdba;
启动数据库
SQL >startup
（若启动文件名不是ORACLE缺省的文件名，则启动时应带启动目录与文件名）
SQL>startup pfile=<file-pathr/init-file>
关闭数据库
SQL >shutdown immediate
# show parameter instance_name; //直接显示实例名。
或者# select * from v$instance; //实例名，查看主机名， oracleb版本信息，启动时间，状态。
1监听
（1）启动监听
lsnrctl start
（2）停止监听
lsnrctl stop
（3）查看监听状态
lsnrctl status
2启动
（1）以oracle用户进入
su - oracle
运行sqlplus命令，进入sqlplus环境，nolog参数表示不登录；
sqlplus /nolog
（2）以管理员身份进入
conn /as sysdba
（3）启动数据库
startup
（4）停止数据库
shutdown immediate
（5）远程连接数据库
sqlplus /nolog
conn sys/sys@IP:1521/orainstance as sysdba
（6）直接运行
dbstart //启动数据库脚本
dbshut //停止数据库脚本
3用户管理
（1）创建用户
create user "username" identified by "userpassword";
PS：后面可带表空间
（2）删除用户
drop user “username” cascade;
PS：cascade 参数是级联删除该用户所有对象，经常遇到如用户有对象而未加此参数则用户删不了的问题，所以习惯性的加此参数
（3）用户授权
grant connect,resource,dba to "username";
（4）查看当前用户的角色
select * from user_role_privs;
select * from session_privs;
（5）查看当前用户的系统权限和表级权限
select * from user_sys_privs;
select * from user_tab_privs;
（6）查询用户表
select username from dba_users;
（7）修改用户口令
alter user "username" identified by "password";
（8）显示当前用户
show user;
4表空间
（1）创建表空间
CREATE TABLESPACE data01 DATAFILE ‘/oracle/oradata/db/tablespace_name.dbf‘ SIZE 500M;
（2）删除表空间
DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;
（3）修改表空间大小
alter database datafile ‘/path/tablespace_name.dbf‘ resize 3000M;
（4）增加表空间
alter tablespace Name add datafile ‘/path/tablespace_name_add.dbf‘ size 3000M;
（5）查询数据库文件
select * from dba_data_files;
（6）查询当前存在的表空间
select * from v$tablespace;
（7）表空间分配情况
select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;
（8）查询表空间剩余空间
select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;
5连接数
（1）修改连接数
alter system set processes=1000 scope=spfile;
shutdown immediate;
startup;
（2）查看用户当前连接数
select count(*) from sys.v_$session；
6使用pfile启动Oracle例程
lsnrctl start
sqlplus /nolog
conn user@instance as sysdba
password
create spfile from pfile=‘/Oracle/instance/pfile/init.ora.789456123‘;
startup
2. 使用sqlplus进入用户
sqlplus 用户名/密码@远程连接的数据库名
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后，则不论用户实体有多大，都一并删除。
4。查询用户
DBA_USERS-所有用户
ALL_USERS---当前用户可以看到的用户
USER_USERS—当前用户
select USERNAME from dba_users;
5.修改密码
alter user system IDENTIFIED by huawei;
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的权限管理。
l 创建角色，如创建一个名为dept1的角色，口令为hello：
CREATE ROLE ROLEiMUSE01 IDENTIFIED BY hello；
l 使用角色，可以通过修改用户的缺省角色来使用角色，或通过授权的方法来将角色授予其它角色或用户。如将imuse01用户的缺省角色修改为RoleTmp：
ALTER USER imuse01 DEFAULT ROLE RoleTmp；
将角色RoleTmp角色授予imuse01：
GRANT RoleTmpTO imuse01；
l 使角色生效或失效，DBA可以通过控制角色的生效或失效，来暂时回收用户的一部分权限。如使RoleTmp角色失效：
SET ROLE RoleTmp DISABLE；
l 删除角色，这将会影响到拥有该角色的用户和其它角色的权限。用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．查询某张表所占的表空间大小
select sum(bytes)/1024/1024 from user_extents where segment_name=’PERSISTENTCOOKIETABLE’
5.查询表空间信息
DBA_TABLESPACE,USER_TABLESPACE两个视图中存储有系统或用户可访问的表空间信息。
select tablespace_name from DBA_tablespaces;
DBA_DATA_FILES存储表空间文件对应关系
6、删除表空间
drop tablespace name INCLUDING CONTENTS;
drop tablespace WAPGW_tmp INCLUDING CONTENTS and datafiles
9. 数据文件被误删后的处理
如果不小心物理上删除了一Oracle的数据文件，比如说，某应用表空间所对应数据文件”adc.dbf”，Oracle读控制文件时，和打开数据库时所面对的参数不一致，Oracle数据库将启动不了，解决这种问题的方法是把该文件对应的表空间先卸下，再删除，以保证控制文件描述和物理上存在文件一致。
以sys用户登录并进入Sql*Plus：
SQL >startup mount
SQL >alter database datafile ‘/directory/abc.dbf’ offline drop;
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. 显示当前用户的表
select TABLE_NAME from user_tables;
15. 查看数据库文件
共有三种数据库文件：控制文件、数据文件、日志文件
1.查看控制文件
select * from v$controlfile;
2．查看数据文件
select status,bytes,name from v$datafile;
3．查看日志文件
select name from v$logfile;
16. 将select查询出的结果保存至一个文件
SQL>spool /result.txt
SQL>select * from basetab;
SQL>spool off
则从basetab查询出的结果都被保存到当前路径下的result.txt文件中
17. 存储过程
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．如果执行存储过程时提示：缺少表达式，有可能是某个变量没被赋值。
18. 数据库的备份与恢复
ORACLE系统提供的Export/转入（备份）、Import/转出（恢复）应用程序实现备份与恢复功能。
1 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中
exp system/manager@TEST file=d:\daochu.dmp full=y
2 将数据库中system用户与sys用户的表导出
exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)
3 将数据库中的表table1 、table2导出
exp system/manager@TEST file=d:\daochu.dmp tables=(table1,table2)
4 将数据库中的表table1中的字段filed1以"00"打头的数据导出
exp system/manager@TEST file=d:\daochu.dmp tables=(table1) query=\" where filed1 like ‘00%‘\"
数据的导入
1 将D:\daochu.dmp 中的数据导入 TEST数据库中。
imp system/manager@TEST file=d:\daochu.dmp
上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导入。
在后面加上 ignore=y 就可以了。
2 将d:\daochu.dmp中的表table1 导入
imp system/manager@TEST file=d:\daochu.dmp tables=(table1)
19. 创建应用数据库表空间
创建应用表空间的操作步骤如下：
1. 以oracle用户登录WAPGW_DBMS_001。
2. 在/opt/oracle/product/9.2下创建目录db。
bash-2.03$ cd /opt/oracle/product/9.2
bash-2.03$ mkdir db
3. 将WISG安装包的db目录FTP到/opt/oracle/product/9.2/db下。
4. 修改/opt/oracle/product/9.2/db目录下create_tablespace.sql文件，保证创建表空间使用的裸设备路径和实际需要使用的相同并且相关裸设备已经建立。
/dev/vx/rdsk/datadg/lv_user01 （oracle的user01数据库空间） 2000MB
/dev/vx/rdsk/datadg/lv_user02 （oracle的user02数据库空间） 2000MB
实际情况下，路径名和裸设备名称可能与上述内容不一致，请根据现场情况调整。
5. 增加db目录下数据库脚本的执行权限
bash-2.03$ cd /opt/oracle/product/9.2/db
bash-2.03$ chmod a+x *
6. 运行建立表空间语句。
bash-2.03$ cd /opt/oracle/product/9.2/db
bash-2.03$ sqlplus /nolog
SQL*Plus: Release 9.2.0.6.0 - Production on 星期一 4月 5 21:32:51 2004
(c) Copyright 2000 Oracle Corporation. All rights reserved.
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 236896416 bytes
Fixed Size 73888 bytes
Variable Size 79355904 bytes
Database Buffers 157286400 bytes
Redo Buffers 180224 bytes
Database mounted.
Database opened.
SQL> @create_tablespace.sql
说明：
如果数据库已经启动，则无需在SQL命令下执行startup命令。如果执行脚本过程中提示权限不够，可以将创建表空间的脚本直接拷贝到oracle用户$HOME下执行。
20. 创建应用数据库表和存储过程
& 说明：
1. 本节所讲操作只需在主机上执行。
1 检查默认的数据库用户名和密码
检查/opt/oracle/product/9.2/db目录下create_user.sql文件语句”create user wapgw identified by "wapgw" default tablespace wapgw …”。
检查数据库用户名与密码是否正确。其中，create user wapgw是指创建数据库用户名为wapgw，identified by “wapgw”是指创建的数据库用户密码为wapgw。
如果不使用默认值，可以进行修改数据库用户名和密码为其他值。
2 创建用户
使用如下命令创建用户。
bash-2.03$ cd /opt/oracle/product/9.2/db
bash-2.03$ sqlplus /nolog
SQL*Plus: Release 9.2.0.6.0 - Production on 星期一 4月 5 21:32:51 2004
(c) Copyright 2000 Oracle Corporation. All rights reserved.
SQL> connect / as sysdba
SQL> @create_user.sql
SQL> exit
3 创建表和存储过程
使用如下语句创建表和存储过程。
bash-2.03$ cd /opt/oracle/product/9.2/db
SQL> sqlplus wapgw/wapgw@wapgw
SQL*Plus: Release 9.2.0.6.0 - Production on 星期一 4月 5 21:32:51 2004
(c) Copyright 2000 Oracle Corporation. All rights reserved.
SQL> @create_table.sql
SQL> @create_procedure.sql
完成用户、表、触发器和存储过程的创建。如果用户和表创建成功，以wapgw用户登录数据库，执行以下命令，就可以看到所有创建的表。
bash-2.03$ sqlplus wapgw/wapgw@wapgw
SQL>select * from tab;
21. 配置PLSQL
oracle/configuration and migration tools/net configuration assistant
22. 远程连接数据库
使用oracle用户进入根目录
product/9.2/network/admin/tnsnames.ora配置
WAPGW216=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.164.74.216)(PORT = 1521))
)
(CONNECT_DATA =http://www.mamicode.com/
(SERVICE_NAME = wisg)
)
)
WAPGW216是在本机数据库名，HOST =配置要连接的数据库IP，PORT =配置要连接的数据库端口，SERVICE_NAME = wisg是要连接的数据库的服务名
23. 需要修复数据库（如需要扩充或删除数据文件时）
以oracle用户登录后，直接输入：
$ sqlplus system/manager
SQL*Plus: Release 8.1.7.0.0 - Production on Fri Apr 25 19:37:29 2003
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
命令：
SQL> alter tablespace smsc_center add datafile ‘/dev/rdbsmsc21‘ size 2000M;
Tablespace altered.
24. 查询数据库使用何种字符集
SVRMGR> connect internal
Connected.
SVRMGR> select value$ from props$ where name=‘NLS_CHARACTERSET‘;
VALUE$
--------------------------------------------------------------------------------
ZHS16GBK
1 row selected.
25. 查询某天历史表中数据记录数
以oracle用户登录后，直接输入：
$ sqlplus smsc/oracle
SQL> select count(*) from sm_histable0425;
26. 查询数据库设置的并发进程数
select name,value from v$parameter where name=’processes’
27. 查看和修改参数
SQL> show parameter//查看Orcale的配置参数
SQL> alter system set processes=500 scope=spfile;//修改一些参数,这些参数在spfile中描述
28. 查询数据库版本
select * from product_component_version;
29. 数据库由于误删除了redo 日志文件而启动不了
SQL> conn cdrserver/cdrserver@imuse30
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
警告: 您不再连接到 ORACLE。
SQL> conn sys/wisgrpt@imuse30 as sysdba
已连接。
SQL> shutdown abort
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 1368466872 bytes
Fixed Size 456120 bytes
Variable Size 318767104 bytes
Database Buffers 1048576000 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: ‘D:\ORACLE\ORADATA\WISG\REDO01.LOG‘
SQL> ls
SP2-0042: 未知命令"ls" -- 其余行忽略。
SQL> alter database drop logfile member ‘D:\ORACLE\ORADATA\WISG\REDO01.LOG‘
2 /
alter database drop logfile member ‘D:\ORACLE\ORADATA\WISG\REDO01.LOG‘
*
ERROR 位于第 1 行:
ORA-00361: cannot remove last log member D:\ORACLE\ORADATA\WISG\REDO01.LOG for
group 1
SQL> alter database drop logfile group 1;
数据库已更改。
SQL> alter database add logfile group 1 (‘D:\ORACLE\ORADATA\WISG\REDO01.LOG‘) si
ze 500k;
数据库已更改。
SQL> alter database open
2 /
alter database open
*
ERROR 位于第 1 行:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: ‘D:\ORACLE\ORADATA\WISG\REDO02.LOG‘
SQL> alter database drop logfile group 2;
数据库已更改。
SQL> alter database add logfile group 1 (‘D:\ORACLE\ORADATA\WISG\REDO02..LOG‘) s
ize 500k;
alter database add logfile group 1 (‘D:\ORACLE\ORADATA\WISG\REDO02..LOG‘) size 5
00k*
ERROR 位于第 1 行:
ORA-07345: The datafile name must not contain the string ‘..‘.
OSD-04026: Invalid parameter passed.
SQL> alter database add logfile group 1 (‘D:\ORACLE\ORADATA\WISG\REDO02.LOG‘) si
ze 500k;
alter database add logfile group 1 (‘D:\ORACLE\ORADATA\WISG\REDO02.LOG‘) size 50
0k
*
ERROR 位于第 1 行:
ORA-01184: logfile group 1 already exists
SQL> alter database add logfile group 2 (‘D:\ORACLE\ORADATA\WISG\REDO02.LOG‘) si
ze 500k
2 /
数据库已更改。
SQL> alter database open;
数据库已更改。