Oracle服务器

下面介绍一些oracle服务器常用的命令，后期我会把在安装、维护过程中遇到的问题粘出来，供大家参考。

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 )