Oracle 数据库 常用命令

（一）有时候数据死了，或者没有监听，不必重启机子，重启一下数据库服务行了，下面是linux下的启动步骤，没有在win下执行过，可能也可以！
以oracle9i为例
（1） 以oracle身份登录数据库，命令：su – oracle
（2） 进入Sqlplus控制台，命令：sqlplus /nolog
（3） 以系统管理员登录，命令：connect / as sysdba

（4） 启动数据库，命令：startup
（5） 如果是关闭数据库，命令：shutdown immediate
（6） 退出sqlplus控制台，命令：exit
（7） 进入监听器控制台，命令：lsnrctl
（8） 启动监听器，命令：start
（9） 退出监听器控制台，命令：exit
（10） 重启数据库结束

（二）在启动、关闭或者重启oracle监听器之前确保使用lsnrctl status命令检查oracle监听器的状态：
1、$lsnrctl status：检查当前监听器的状态
2、$lsnrctl start [listener-name] 启动所有的监听器,可以指定名字来启动特定的监听器
3、$lsnrctl stop [listener-name]  关闭所有的监听器，可以指定名字来关闭特定的监听器
4、$lsnrctl reload  重启监听器，此命令可以代替lsnrctl stop,lsnrctl start
5、lsnrctl hep      可以显示所有可用的监听器命令
常用命令参数：
start - Start the Oracle listener
stop - Stop the Oracle listener
status - Display the current status of the Oracle listener
services - Retrieve the listener services information
version - Display the oracle listener version information
reload - This will reload the oracle listener SID and parameter files. This is equivalent to lsnrctl stop and lsnrctl start.
save_config - This will save the current settings to the listener.ora file and also take a backup of the listener.ora file before overwriting it. If there are no changes, it will display the message “No changes to save for LISTENER”
trace - Enable the tracing at the listener level. The available options are ‘trace OFF’, ‘trace USER’, ‘trace ADMIN’ or ‘trace SUPPORT’
spawn - Spawns a new with the program with the spawn_alias mentioned in the listener.ora file
change_password - Set the new password to the oracle listener (or) change the existing listener password.
show - Display log files and other relevant listener information

查询某一日期的记录：

Select * from tb_student t where t.createdate = to_date('2013-9-8','yyyy-mm-dd');

查询在日期之间的记录：

Select * from tb_student t where t.createdate between to_date('2013-9-8','yyyy-mm-dd') and to_date('2013-10-14,'yyyy-mm-dd');
 SPOOL将屏幕所有的输出输出到指定文件

-- spool 文件路径名;

spool g:\mysql.sql;

--业务操作

--结束输出

spool off;

执行一个SQL脚本文件

我们可以将多条sql语句保存在一个文本文件中，这样当要执行这个文件中的所有的sql语句时，用上面的任一命令即可，这类似于dos中的批处理。

--start file_name

-- @ file_name

start g:\mysql.sql;

@ g:\mysql.sql;

对当前的输入进行编辑

edit

ed

重新运行上一次运行的sql语句

/

显示一个表的结构

desc table_name ;

清屏

clear screen;

退出

exit;

置当前session是否对修改的数据进行自动提交

--SET AUTO[COMMIT] {ON|OFF|IMM[EDIATE]| n}

set autocommit on;

在用start命令执行一个sql脚本时，是否显示脚本中正在执行的SQL语句

-- SET ECHO {ON|OFF};

set echo on;

是否显示当前sql语句查询或修改的行数

--SET FEED[BACK] {6|n|ON|OFF}

-- 默认只有结果大于6行时才显示结果的行数。如果set feedback 1 ，则不管查询到多少行都返回。当为off 时，一律不显示查询的行数

set feedback 1;

是否显示列标题

--当set heading off 时，在每页的上面不显示列标题，而是以空白行代替

--SET HEA[DING] {ON|OFF}

set heading on;

设置一行可以容纳的字符数

-- 如果一行的输出内容大于设置的一行可容纳的字符数，则折行显示

--SET LIN[ESIZE] {80|n}

set linesize 100;

设置页与页之间的分隔

-- SET NEWP[AGE] {1|n|NONE}

--当set newpage 0 时，会在每页的开头有一个小的黑方框。

--当set newpage n 时，会在页和页之间隔着n个空行。

--当set newpage none 时，会在页和页之间没有任何间隔

set newpage 1;

设置一页有多少行数

--如果设为0，则所有的输出内容为一页并且不显示列标题

--SET PAGES[IZE] {24|n}

set pagesize 20;

是否显示用DBMS_OUTPUT.PUT_LINE包进行输出的信息。

--SET SERVEROUT[PUT] {ON|OFF}

set serveroutput on;

是否在屏幕上显示输出的内容，主要用与SPOOL结合使用。

--在用spool命令将一个大表中的内容输出到一个文件中时，将内容输出在屏幕上会耗费大量的时间，

--设置set termspool off后，则输出的内容只会保存在输出文件中，不会显示在屏幕上，极大的提高了spool的速度

--SET TERM[OUT] {ON|OFF}

set termout off;

在dos里连接oracle数据库

CONNECT user_name/passwd@l_jiayou

在sql*plus中连接到指定的数据库

CONNECT user_name/passwd@数据库名称

显示当前用户

show user;

显示当前环境变量的值：

show all;

显示当前在创建函数、存储过程、触发器、包等对象的错误信息

Show error

显示数据库的版本：

--show REL[EASE]

show release

显示SGA的大小

show SGA

显示初始化参数的值：

--show PARAMETERS [parameter_name]

show parameters;

查看当前用户的缺省表空间

select username,default_tablespace from user_users

查看当前用户的角色

select * from user_role_privs

查看当前用户的系统权限和表级权限

select * from user_sys_privs;

select * from user_tab_privs;

查看用户下所有的表

select * from user_tables

查看名称包含log字符的表

select object_name,object_id from user_objects where instr(object_name,'LOG')>0;

查看某表的创建时间

select object_name,created from user_objects where object_name=upper('&table_name');

查看某表的大小

select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&table_name');

查看放在ORACLE的内存区里的表

select table_name,cache from user_tables where instr(cache,'Y')>0;

查看索引个数和类别

select index_name,index_type,table_name from user_indexes order by table_name;

查看索引被索引的字段

select * from user_ind_columns where index_name=upper('&index_name');

查看索引的大小

select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&index_name');

查看序列号，last_number是当前值

select * from user_sequences;

查看视图的名称

--select view_name from user_views;

查看创建视图的select语句

select view_name,text_length from user_views;

set long 2000;                说明：可以根据视图的text_length值设定set long 的大小

select text from user_views where view_name=upper('&view_name');

查看同义词的名称

select * from user_synonyms

查看某表的约束条件

select constraint_name, constraint_type,search_condition, r_constraint_name

                from user_constraints where table_name = upper('&table_name');

               

select c.constraint_name,c.constraint_type,cc.column_name

from user_constraints c,user_cons_columns cc where c.owner = upper('&table_owner') and c.table_name = upper('&table_name')

and c.owner = cc.owner and c.constraint_name = cc.constraint_name order by cc.position;

查看函数和过程

select object_name,status from user_objects where object_type='FUNCTION';

select object_name,status from user_objects where object_type='PROCEDURE';

查看函数和过程的源代码

select text from all_source where owner=user and name=upper('&plsql_name');

查看表空间的名称及大小

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;

   

查看表空间物理文件的名称及大小

    select tablespace_name, file_id, file_name,

    round(bytes/(1024*1024),0) total_space

    from dba_data_files

    order by tablespace_name;

查看回滚段名称及大小

    select segment_name, tablespace_name, r.status,

    (initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,

    max_extents, v.curext CurExtent

    From dba_rollback_segs r, v$rollstat v

    Where r.segment_id = v.usn(+)

    order by segment_name ;

查看控制文件

    select name from v$controlfile;

   

查看日志文件

    select member from v$logfile;

   

查看表空间的使用情况

select sum(bytes)/(1024*1024) as free_space,tablespace_name

from dba_free_space group by tablespace_name;

SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,

    (B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"

    FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C

    WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;

   

查看数据库库对象

select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;

查看数据库的版本

Select version FROM Product_component_version

    Where SUBSTR(PRODUCT,1,6)='Oracle';

查看数据库的创建日期和归档方式

Select Created, Log_Mode, Log_Mode From V$Database;

用系统管理员，查看当前数据库有几个用户连接：

select username,sid,serial# from v$session;

如果要停某个连接用

alter system kill session 'sid,serial#';

如果这命令不行,找它UNIX的进程数

select pro.spid from v$session ses,v$process pro where ses.sid=21 and ses.paddr=pro.addr;

--说明：21是某个连接的sid数,然后用 kill 命令杀此进程号。

例子：

表test122,有两个字段t_id varchar2(20),t_name varchar2(10);

要求t_id的值为当天日期加上0001,0002的形式递加作为序列，如20070209_0001,200709_0002；

思路：查讯当天的t_id的最大值加1,然后生成序列；

insert into test122 values

(to_char(sysdate,'yyyymmdd')||'_'||(select lpad(to_number(ltrim(substr(max(t_id),length(max(t_id))-3),'0'))+1,4,0)

from test122 where substr(t_id,0,length(t_id)-5)=to_char(sysdate,'yyyymmdd')),'ok');

树形递归查询：Start with...Connect By

准备：

create table mymenu(tree_id varchar(10),tree_pid varchar(10),tree_lable varchar(50),tree_link varchar(100))

insert into mymenu values('1','0','蔬菜','')

insert into mymenu values('2','0','水果','')

insert into mymenu values('3','0','谷物','')

insert into mymenu values('4','0','肉类','')

insert into mymenu values('5','1','白菜','')

insert into mymenu values('6','1','茄子','htt://www.baidu.com')

insert into mymenu values('7','5','四月白','http://www.google.cn')

insert into mymenu values('8','5','冬白菜','htt://www.baidu.com')

insert into mymenu values('9','2','西瓜','http://www.google.cn')

insert into mymenu values('10','2','桔子','htt://www.baidu.com')

insert into mymenu values('11','3','大米','http://www.google.cn')

insert into mymenu values('12','3','大豆','htt://www.baidu.com')

insert into mymenu values('13','4','猪肉','http://www.google.cn')

insert into mymenu values('14','4','鱼','')

insert into mymenu values('15','14','昌鱼','http://www.google.cn')

insert into mymenu values('16','14','王八','htt://www.baidu.com')

从根往树末梢查询：

select * from mymenu start with tree_pid='0' connect by prior tree_id=tree_pid;//查询所有

select * from mymenu start with tree_id='1' connect by prior tree_id=tree_pid; //查询指定ID

从树末梢向根查询：

select * from mymenu start with tree_pid='0' connect by prior tree_pid=tree_id

select * from mymenu start with tree_id='8' connect by prior tree_pid=tree_id

如果还有其他条件用and 加在语句后面

select * from mymenu start with tree_pid='0' connect by prior tree_id=tree_pid and tree_link is null

select * from mymenu start with tree_pid='0' connect by prior tree_id=tree_pid and tree_link is not null

oracle客户端连接的文件配置：

oracle的目录/network/ADMIN/tnsnames.ora

内容：

MIMI(客户端连接的名称) =

(DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.254)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = NTDB.RUNNER)

    )

)

修改表结构

alter table m_gl_gls3_history add (aaaaa varchar2(20),bbbbb varchar2(10))
alter table m_gl_gls3_history modify (aaaaa varchar2(10))
--要改变表中的字段的类型或缩小字段长度，该字段的所有记录值必须为空。

--如果改字段存在记录值，则该字段长度只能扩大，不能缩小。

alter table m_gl_gls3_history drop (aaaaa , bbbbb )

一、忘记除SYS、SYSTEM用户之外的用户的登录密码。

 用SYS (或SYSTEM)用户登录: CONN SYS/PASS_WORD AS SYSDBA;

 使用如下语句修改用户的密码: ALTER USER user_name IDENTIFIED BY "newpass";

 注意：密码不能全是数字。并且不能是数字开头。否则会出现：ORA-00988: 口令缺失或无效

二、忘记SYS用户，或者是SYSTEM用户的密码。

 如果是忘记SYSTEM用户的密码，可以用SYS用户登录。然后用ALTER USER 命令修改密码：

 CONN SYS/PASS_WORD AS SYSDBA;

 ALTER USER SYSTEM IDENTIFIED BY "newpass";

 如果是忘记SYS用户的密码，可以用SYSTEM用户登录。然后用ALTER USER 命令修改密码。

 CONN SYSTEM/PASS_WORD ;

 ALTER USER SYSTEM IDENTIFIED BY "newpass";

三、如果SYS,SYSTEM用户的密码都忘记或是丢失。

 可以使用ORAPWD.EXE 工具修改密码。

 开始菜单->运行->输入‘CMD’,打开命令提示符窗口，输入如下命令：

 orapwd file=D:\oracle10g\database\pwdctcsys.ora password=newpass

 这个命令重新生成了数据库的密码文件。密码文件的位置在ORACLE_HOME目录下的\database目录下。

 这个密码是修改sys用户的密码。除sys和system其他用户的密码不会改变。</p>

不过Oracle提供了两种验证方式，一种是OS验证，另一种密码文件验证方式，如果是第一种方式用以下方法修改密码：

　　sqlplus /nolog;

　　connect / as sysdba

　　alter user sys identified by ;

　　alter user system identified by ;

　　如果是第二种方法就用上述方式修改，也可以下方法修改密码：

　　orapwd file=pwdxxx.ora password=你设定的新密码 entries=10

　　设定完后，重新启动服务，再次登陆就可以了。</p>

oracle 11g

在本机安装完Oracle以后，不记得sys用户的密码了，采用如下方法可以修改密码：

1.打开cmd，输入sqlplus /nolog，回车；输入“conn / as sysdba”;输入“alter user sys identified by 新密码”，注意：新密码最好以字母开头，否则可能出现错误Ora-00988。有了这个方法后，只要自己对oracle服务器有管理员权限，不记得密码的时候就可以随意修改密码了。

2.在命令行执行如下命令：sqlplus “/@服务名 as sysdba”,然后在sqlplus中alter user sys identified by 新密码;

alter user system identified by 新密码;

3.运行到C盘根目录

1)输入：SET ORACLE_SID = 你的SID名称

2)输入：sqlplus/nolog

3)输入：connect/as sysdba

4)输入：alert user sys identified by sys

5)输入：alert user system identified by system

6)更改完成，密码是Oracle数据库初始密码

4.首先,在CMD下输入SQLPLUS/NOLOG然后再在出来的界面中打入CONN/AS SYSDBA,这样就会以本地系统登录的用户为信任用户进入数据库的操作.解决这个问题的方式有两种,一个是：ALTER USER （USERNAME） IDENTIFIED BY “密码”;这个是可以改变USERNAME的密码.当然这个USERNAME必须已经存在的

另一种是：CREATE USER (USERNAME) IDENTIFIED BY “密码”;改变用户权限的命令是：GRANT ROLES TO （USERNAME）;以上2种方法都是针对ORACLE 9I的版本 。

5.用orapwd.exe命令，可以修改命令。

orapwd file=’/oracle/pwdsid.ora’ password=123456这个命令是修改sys用户的密码。你把生成的文件覆盖原来的密码文件。除sys和system其他用户的密码不会改变。

6.su - oracle

sqlplus /nolog

conn / as sysdba

startup (如果数据库不是处于启动状态则启动)

alter user sys identified by 123456

然后就可以使用sys用户密码登陆了

操作系统认证方式

conn / as sysdba

alter user sys identified by xxx

你在系统中，用oracle用户登陆操作系统，如果是unix系统。

#su - oracle

$svrmgrl

SVRMGRL>;connect internal

SVRMGRL>;alter user system identified by new_pass;

SVRMGRL>;exit

 

这样，你就可以改回system的密码了。。。