ORA-28547:连接服务器失败,可能是Oracle Net管理错误

admin 发表了文章 • 0 个评论 • 103 次浏览 • 2018-04-28 09:45 • 来自相关话题

*1). 绑定你的IP地址,别让其动态获取!

 *2). 修改oracle的网络配置文件,具体做法是:在oracle安装目录下搜索sqlnet.ora文件,一般在$\NETWORK\ADMIN目录下, 
用记事本打开把文件中的一行:  
SQLNET_AUTHENTICATION_SERVICES= (NTS)  
修改为:  
SQLNET_AUTHENTICATION_SERVICES= (NONE) 

--*3).同时修改监听器配置文件,具体做法是: 在oracle安装目录下搜索listener.ora文件,一般在$\NETWORK\ADMIN目录下,把主机名用ip地址代替,例如(ADDRESS = (PROTOCOL = TCP)(HOST = HBONLINE1)(PORT = 1523))改为(ADDRESS = (PROTOCOL = TCP)(HOST = 168.168.168.42)(PORT = 1523))。


 

Oracle数据库服务器IP变动出现以上问题(具体原因还不明确)

1、把服务改为固定IP

2、). 修改sqlnet.ora文件,一般在$\NETWORK\ADMIN目录下, 
用记事本打开把文件中的一行:  
SQLNET_AUTHENTICATION_SERVICES= (NTS)  
修改为:  
SQLNET_AUTHENTICATION_SERVICES= (NONE) 

 

SQLNET_AUTHENTICATION_SERVICES值的含义参考:

http://blog.csdn.net/ppp_10001 ... 45802 查看全部
*1). 绑定你的IP地址,别让其动态获取!

 *2). 修改oracle的网络配置文件,具体做法是:在oracle安装目录下搜索sqlnet.ora文件,一般在$\NETWORK\ADMIN目录下, 
用记事本打开把文件中的一行:  
SQLNET_AUTHENTICATION_SERVICES= (NTS)  
修改为:  
SQLNET_AUTHENTICATION_SERVICES= (NONE) 

--*3).同时修改监听器配置文件,具体做法是: 在oracle安装目录下搜索listener.ora文件,一般在$\NETWORK\ADMIN目录下,把主机名用ip地址代替,例如(ADDRESS = (PROTOCOL = TCP)(HOST = HBONLINE1)(PORT = 1523))改为(ADDRESS = (PROTOCOL = TCP)(HOST = 168.168.168.42)(PORT = 1523))。


 

Oracle数据库服务器IP变动出现以上问题(具体原因还不明确)

1、把服务改为固定IP

2、). 修改sqlnet.ora文件,一般在$\NETWORK\ADMIN目录下, 
用记事本打开把文件中的一行:  
SQLNET_AUTHENTICATION_SERVICES= (NTS)  
修改为:  
SQLNET_AUTHENTICATION_SERVICES= (NONE) 

 

SQLNET_AUTHENTICATION_SERVICES值的含义参考:

http://blog.csdn.net/ppp_10001 ... 45802

SQLNET.AUTHENTICATION_SERVICES

admin 发表了文章 • 0 个评论 • 96 次浏览 • 2018-04-28 09:41 • 来自相关话题

$ORACLE_HOME/network/admin/sqlnet.ora

 

如果使用了SQLNET.AUTHENTICATION_SERVICES=(NTS)
则说明可以使用OS认证就,只要conn / as sysdba 就可以登陆
但如果注释掉或SQLNET.AUTHENTICATION_SERVICES=(none)
必须要使用conn sys/password@oracle as sysdba才能登陆

1、在windows下,SQLNET.AUTHENTICATION_SERVICES必须设置为NTS或者 ALL才能使用OS认证;不设置或者设置为其他任何值都不能使用OS认证。
2、在linux下,在SQLNET.AUTHENTICATION_SERVICES的值设置为ALL,或者不设置的情况下,OS验证才能成功;设置为 其他任何值都不能使用OS认证。

 

 

 

我们看看ORACLE对这个设置是怎么解释的:
SQLNET.AUTHENTICATION_SERVICES
Purpose

Use the parameter SQLNET.AUTHENTICATION_SERVICES to enable one or more authentication services. If authentication has been installed, it is recommended that this parameter be set to either none or to one of the authentication methods.
Default
None

Values
Authentication Methods Available with Oracle Net Services:

* none for no authentication methods. A valid username and password can be used to access the database.
* all for all authentication methods
* nts for Windows NT native authentication

Windows NT native authentication

An authentication method that enables a client single login access to a Windows NT server and a database running on the server. 查看全部
$ORACLE_HOME/network/admin/sqlnet.ora

 

如果使用了SQLNET.AUTHENTICATION_SERVICES=(NTS)
则说明可以使用OS认证就,只要conn / as sysdba 就可以登陆
但如果注释掉或SQLNET.AUTHENTICATION_SERVICES=(none)
必须要使用conn sys/password@oracle as sysdba才能登陆

1、在windows下,SQLNET.AUTHENTICATION_SERVICES必须设置为NTS或者 ALL才能使用OS认证;不设置或者设置为其他任何值都不能使用OS认证。
2、在linux下,在SQLNET.AUTHENTICATION_SERVICES的值设置为ALL,或者不设置的情况下,OS验证才能成功;设置为 其他任何值都不能使用OS认证。

 

 

 

我们看看ORACLE对这个设置是怎么解释的:
SQLNET.AUTHENTICATION_SERVICES
Purpose

Use the parameter SQLNET.AUTHENTICATION_SERVICES to enable one or more authentication services. If authentication has been installed, it is recommended that this parameter be set to either none or to one of the authentication methods.
Default
None

Values
Authentication Methods Available with Oracle Net Services:

* none for no authentication methods. A valid username and password can be used to access the database.
* all for all authentication methods
* nts for Windows NT native authentication

Windows NT native authentication

An authentication method that enables a client single login access to a Windows NT server and a database running on the server.

无监听

admin 发表了文章 • 0 个评论 • 107 次浏览 • 2018-04-28 09:35 • 来自相关话题

昨天我在一台win7笔记本中安装了oracle11g,然后打算用另一个win10的笔记本使用plsql developer局域网内连接访问oracle数据库。但是遇到ORA-12541:TNS:无监听程序的错误。

首先我看了一下win7中的以下的两个服务是开启。 


解决方案: 
1.首先查看win7的ip地址,并用win10ping通才可以。

2.修改..\app\admin\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora 
SID_NAME = CLRExtProc 改为 SID_NAME = orcl (orcl是实例名) 
HOST = localhost 改为 HOST = 192.168.1.111

3.修改..\app\admin\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora 
两处 HOST = localhost 改为 HOST = 192.168.1.111 
SID = CLRExtProc 改为 SID = orcl

4.好了,重启oracle的上边的两个服务 查看全部
昨天我在一台win7笔记本中安装了oracle11g,然后打算用另一个win10的笔记本使用plsql developer局域网内连接访问oracle数据库。但是遇到ORA-12541:TNS:无监听程序的错误。

首先我看了一下win7中的以下的两个服务是开启。 


解决方案: 
1.首先查看win7的ip地址,并用win10ping通才可以。

2.修改..\app\admin\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora 
SID_NAME = CLRExtProc 改为 SID_NAME = orcl (orcl是实例名) 
HOST = localhost 改为 HOST = 192.168.1.111

3.修改..\app\admin\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora 
两处 HOST = localhost 改为 HOST = 192.168.1.111 
SID = CLRExtProc 改为 SID = orcl

4.好了,重启oracle的上边的两个服务

ORACL 常用查询

admin 发表了文章 • 0 个评论 • 106 次浏览 • 2018-04-28 09:16 • 来自相关话题

--查看用户和默认表空间的关系
select username,default_tablespace from dba_users;
--查看当前用户能访问的表
select * from user_tables; 
--Oracle查询用户表
select * from user_all_tables;

--Oracle查询用户视图
select * from user_views;
--查询所有函数和储存过程:
select * from user_source;
--查询所有用户:
select * from all_users;
--select * from dba_users
--查看当前用户连接:
select * from v$Session;
--查看用户角色
SELECT * FROM USER_ROLE_PRIVS;
--查看当前用户权限:
select * from session_privs;
--查看所有用户所拥有的角色
SELECT * FROM DBA_ROLE_PRIVS;
--查看所有角色
select * from dba_roles;
--查看数据库名
SELECT NAME FROM V$DATABASE;
--查看所有表空间使用情况
select a.file_id "FileNo",
       a.tablespace_name "Tablespace_name",
       a.bytes "Bytes",
       a.bytes - sum(nvl(b.bytes, 0)) "Used",
       sum(nvl(b.bytes, 0)) "Free",
       sum(nvl(b.bytes, 0)) / a.bytes * 100 "%free"
  from dba_data_files a, dba_free_space b
 where a.file_id = b.file_id(+)
 group by a.tablespace_name, a.file_id, a.bytes
 order by a.tablespace_name; 查看全部

--查看用户和默认表空间的关系
select username,default_tablespace from dba_users;
--查看当前用户能访问的表
select * from user_tables; 
--Oracle查询用户表
select * from user_all_tables;

--Oracle查询用户视图
select * from user_views;
--查询所有函数和储存过程:
select * from user_source;
--查询所有用户:
select * from all_users;
--select * from dba_users
--查看当前用户连接:
select * from v$Session;
--查看用户角色
SELECT * FROM USER_ROLE_PRIVS;
--查看当前用户权限:
select * from session_privs;
--查看所有用户所拥有的角色
SELECT * FROM DBA_ROLE_PRIVS;
--查看所有角色
select * from dba_roles;
--查看数据库名
SELECT NAME FROM V$DATABASE;
--查看所有表空间使用情况
select a.file_id "FileNo",
       a.tablespace_name "Tablespace_name",
       a.bytes "Bytes",
       a.bytes - sum(nvl(b.bytes, 0)) "Used",
       sum(nvl(b.bytes, 0)) "Free",
       sum(nvl(b.bytes, 0)) / a.bytes * 100 "%free"
  from dba_data_files a, dba_free_space b
 where a.file_id = b.file_id(+)
 group by a.tablespace_name, a.file_id, a.bytes
 order by a.tablespace_name;

oracle 11g 导入表时 提示 ***值太大拒绝执行的错误 修改字符集

admin 发表了文章 • 0 个评论 • 114 次浏览 • 2018-04-28 09:05 • 来自相关话题

经查询,这个是由于字符集设置的不是gbk的,导致导入时遇到中文字符出现的问题,

1.打开cmd

2.输入sqlplus

3.登录管理员账号:用户名/密码 as sysdba

4.查看版本号:select userenv('language') from dual;  注意带上分好

查询字符集结果有可能是AL32UTF8

更改字符集为ZHS16GBK

cmd命令

SQL> 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 system set AQ_TM_PROCESSES=0;
SQL> alter database open;

SQL>ALTER DATABASE character set INTERNAL_USE ZHS16GBK;
SQL> ALTER DATABASE CHARACTER SET ZHS16GBK;

SQL>shutdown immediate;

SQL>startup;
SQL>alter system disable restricted session; 查看全部
经查询,这个是由于字符集设置的不是gbk的,导致导入时遇到中文字符出现的问题,

1.打开cmd

2.输入sqlplus

3.登录管理员账号:用户名/密码 as sysdba

4.查看版本号:select userenv('language') from dual;  注意带上分好

查询字符集结果有可能是AL32UTF8

更改字符集为ZHS16GBK

cmd命令

SQL> 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 system set AQ_TM_PROCESSES=0;
SQL> alter database open;

SQL>ALTER DATABASE character set INTERNAL_USE ZHS16GBK;
SQL> ALTER DATABASE CHARACTER SET ZHS16GBK;

SQL>shutdown immediate;

SQL>startup;
SQL>alter system disable restricted session;

ORACLE日期时间函数大全

admin 发表了文章 • 0 个评论 • 99 次浏览 • 2018-04-26 20:27 • 来自相关话题

   TO_DATE格式(以时间:2007-11-02   13:45:25为例)
   
        Year:      
        yy two digits 两位年                显示值:07
        yyy three digits 三位年                显示值:007
        yyyy four digits 四位年                显示值:2007
            
        Month:      
        mm    number     两位月              显示值:11
        mon    abbreviated 字符集表示          显示值:11月,若是英文版,显示nov     
        month spelled out 字符集表示          显示值:11月,若是英文版,显示november 
          
        Day:      
        dd    number         当月第几天        显示值:02
        ddd    number         当年第几天        显示值:02
        dy    abbreviated 当周第几天简写    显示值:星期五,若是英文版,显示fri
        day    spelled out   当周第几天全写    显示值:星期五,若是英文版,显示friday        
        ddspth spelled out, ordinal twelfth 
             
              Hour:
              hh    two digits 12小时进制            显示值:01
              hh24 two digits 24小时进制            显示值:13
              
              Minute:
              mi    two digits 60进制                显示值:45
              
              Second:
              ss    two digits 60进制                显示值:25
              
              其它
              Q     digit         季度                  显示值:4
              WW    digit         当年第几周            显示值:44
              W    digit          当月第几周            显示值:1
              
        24小时格式下时间范围为: 0:00:00 - 23:59:59....      
        12小时格式下时间范围为: 1:00:00 - 12:59:59 .... 
            
1. 日期和字符转换函数用法(to_date,to_char)
         
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;   //日期转化为字符串   
select to_char(sysdate,'yyyy') as nowYear   from dual;   //获取时间的年   
select to_char(sysdate,'mm')    as nowMonth from dual;   //获取时间的月   
select to_char(sysdate,'dd')    as nowDay    from dual;   //获取时间的日   
select to_char(sysdate,'hh24') as nowHour   from dual;   //获取时间的时   
select to_char(sysdate,'mi')    as nowMinute from dual;   //获取时间的分   
select to_char(sysdate,'ss')    as nowSecond from dual;   //获取时间的秒

    
select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss')    from dual//

2.      
    select to_char( to_date(222,'J'),'Jsp') from dual      
    
    显示Two Hundred Twenty-Two    

3.求某天是星期几      
   select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;      
   星期一      
   select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;      
   monday      
   设置日期语言      
   ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';      
   也可以这样      
   TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')    

4. 两个日期间的天数      
    select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;    

5. 时间为null的用法      
   select id, active_date from table1      
   UNION      
   select 1, TO_DATE(null) from dual;      
   
   注意要用TO_DATE(null)    

6.月份差   
   a_date between to_date('20011201','yyyymmdd') and to_date('20011231','yyyymmdd')      
   那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。      
   所以,当时间需要精确的时候,觉得to_char还是必要的 
      
7. 日期格式冲突问题      
    输入的格式要看你安装的ORACLE字符集的类型, 比如: US7ASCII, date格式的类型就是: '01-Jan-01'      
    alter system set NLS_DATE_LANGUAGE = American      
    alter session set NLS_DATE_LANGUAGE = American      
    或者在to_date中写      
    select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;      
    注意我这只是举了NLS_DATE_LANGUAGE,当然还有很多,      
    可查看      
    select * from nls_session_parameters      
    select * from V$NLS_PARAMETERS    

8.      
   select count(*)      
   from ( select rownum-1 rnum      
       from all_objects      
       where rownum <= to_date('2002-02-28','yyyy-mm-dd') - to_date('2002-      
       02-01','yyyy-mm-dd')+1      
      )      
   where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' )      
        not in ( '1', '7' )      
   
   查找2002-02-28至2002-02-01间除星期一和七的天数      
   在前后分别调用DBMS_UTILITY.GET_TIME, 让后将结果相减(得到的是1/100秒, 而不是毫秒).    

9. 查找月份     
    select months_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;      
    1      
   select months_between(to_date('02-01-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;      
    1.03225806451613 
       
10. Next_day的用法      
    Next_day(date, day)      
    
    Monday-Sunday, for format code DAY      
    Mon-Sun, for format code DY      
    1-7, for format code D    

11      
   select to_char(sysdate,'hh:mi:ss') TIME from all_objects      
   注意:第一条记录的TIME 与最后一行是一样的      
   可以建立一个函数来处理这个问题      
   create or replace function sys_date return date is      
   begin      
   return sysdate;      
   end;      
   
   select to_char(sys_date,'hh:mi:ss') from all_objects;   
     
12.获得小时数      
     extract()找出日期或间隔值的字段值
    SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 2:38:40') from offer      
    SQL> select sysdate ,to_char(sysdate,'hh') from dual;      
    
    SYSDATE TO_CHAR(SYSDATE,'HH')      
    -------------------- ---------------------      
    2003-10-13 19:35:21 07      
    
    SQL> select sysdate ,to_char(sysdate,'hh24') from dual;      
    
    SYSDATE TO_CHAR(SYSDATE,'HH24')      
    -------------------- -----------------------      
    2003-10-13 19:35:21 19    

       
13.年月日的处理      
   select older_date,      
       newer_date,      
       years,      
       months,      
       abs(      
        trunc(      
         newer_date-      
         add_months( older_date,years*12+months )      
        )      
       ) days 
       
   from ( select      
        trunc(months_between( newer_date, older_date )/12) YEARS,      
        mod(trunc(months_between( newer_date, older_date )),12 ) MONTHS,      
        newer_date,      
        older_date      
        from ( 
              select hiredate older_date, add_months(hiredate,rownum)+rownum newer_date      
              from emp 
             )      
      )    

14.处理月份天数不定的办法      
   select to_char(add_months(last_day(sysdate) +1, -2), 'yyyymmdd'),last_day(sysdate) from dual    

16.找出今年的天数      
   select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual    

   闰年的处理方法      
   to_char( last_day( to_date('02'    | | :year,'mmyyyy') ), 'dd' )      
   如果是28就不是闰年    

17.yyyy与rrrr的区别      
   'YYYY99 TO_C      
   ------- ----      
   yyyy 99 0099      
   rrrr 99 1999      
   yyyy 01 0001      
   rrrr 01 2001    

18.不同时区的处理      
   select to_char( NEW_TIME( sysdate, 'GMT','EST'), 'dd/mm/yyyy hh:mi:ss') ,sysdate      
   from dual;    

19.5秒钟一个间隔      
   Select TO_DATE(FLOOR(TO_CHAR(sysdate,'SSSSS')/300) * 300,'SSSSS') ,TO_CHAR(sysdate,'SSSSS')      
   from dual    

   2002-11-1 9:55:00 35786      
   SSSSS表示5位秒数    

20.一年的第几天      
   select TO_CHAR(SYSDATE,'DDD'),sysdate from dual
        
   310 2002-11-6 10:03:51    

21.计算小时,分,秒,毫秒      
    select      
     Days,      
     A,      
     TRUNC(A*24) Hours,      
     TRUNC(A*24*60 - 60*TRUNC(A*24)) Minutes,      
     TRUNC(A*24*60*60 - 60*TRUNC(A*24*60)) Seconds,      
     TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60)) mSeconds      
    from      
    (      
     select      
     trunc(sysdate) Days,      
     sysdate - trunc(sysdate) A      
     from dual      
   )    


   select * from tabname      
   order by decode(mode,'FIFO',1,-1)*to_char(rq,'yyyymmddhh24miss');      
   
   //      
   floor((date2-date1) /365) 作为年      
   floor((date2-date1, 365) /30) 作为月      
   d(mod(date2-date1, 365), 30)作为日.

23.next_day函数      返回下个星期的日期,day为1-7或星期日-星期六,1表示星期日
   next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。      
   1 2 3 4 5 6 7      
   日 一 二 三 四 五 六    
   
   --------------------------------------------------------------- 
   
   select    (sysdate-to_date('2003-12-03 12:55:45','yyyy-mm-dd hh24:mi:ss'))*24*60*60 from ddual
   日期 返回的是天 然后 转换为ss
     
24,round[舍入到最接近的日期](day:舍入到最接近的星期日)
   select sysdate S1,
   round(sysdate) S2 ,
   round(sysdate,'year') YEAR,
   round(sysdate,'month') MONTH ,
   round(sysdate,'day') DAY from dual

25,trunc[截断到最接近的日期,单位为天] ,返回的是日期类型
   select sysdate S1,                     
     trunc(sysdate) S2,                 //返回当前日期,无时分秒
     trunc(sysdate,'year') YEAR,        //返回当前年的1月1日,无时分秒
     trunc(sysdate,'month') MONTH ,     //返回当前月的1日,无时分秒
     trunc(sysdate,'day') DAY           //返回当前星期的星期天,无时分秒
   from dual

26,返回日期列表中最晚日期
   select greatest('01-1月-04','04-1月-04','10-2月-04') from dual

27.计算时间差
     注:oracle时间差是以天数为单位,所以换算成年月,日
     
      select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))/365) as spanYears from dual        //时间差-年
      select ceil(moths_between(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanMonths from dual        //时间差-月
      select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanDays from dual             //时间差-天
      select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24) as spanHours from dual         //时间差-时
      select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60) as spanMinutes from dual    //时间差-分
      select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60*60) as spanSeconds from dual //时间差-秒

28.更新时间
     注:oracle时间加减是以天数为单位,设改变量为n,所以换算成年月,日
     select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n*365,'yyyy-mm-dd hh24:mi:ss') as newTime from dual        //改变时间-年
     select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),add_months(sysdate,n) as newTime from dual                                 //改变时间-月
     select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n,'yyyy-mm-dd hh24:mi:ss') as newTime from dual            //改变时间-日
     select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24,'yyyy-mm-dd hh24:mi:ss') as newTime from dual         //改变时间-时
     select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual      //改变时间-分
     select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24/60/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual   //改变时间-秒

29.查找月的第一天,最后一天
     SELECT Trunc(Trunc(SYSDATE, 'MONTH') - 1, 'MONTH') First_Day_Last_Month,
       Trunc(SYSDATE, 'MONTH') - 1 / 86400 Last_Day_Last_Month,
       Trunc(SYSDATE, 'MONTH') First_Day_Cur_Month,
       LAST_DAY(Trunc(SYSDATE, 'MONTH')) + 1 - 1 / 86400 Last_Day_Cur_Month
   FROM dual;


三. 字符函数(可用于字面字符或数据库列)

1,字符串截取
   select substr('abcdef',1,3) from dual

2,查找子串位置
   select instr('abcfdgfdhd','fd') from dual

3,字符串连接
   select 'HELLO'||'hello world' from dual;

4, 1)去掉字符串中的空格
    select ltrim(' abc') s1,
    rtrim('zhang ') s2,
    trim(' zhang ') s3 from dual
   2)去掉前导和后缀
    select trim(leading 9 from 9998767999) s1,
    trim(trailing 9 from 9998767999) s2,
    trim(9 from 9998767999) s3 from dual;
   
5,返回字符串首字母的Ascii值
   select ascii('a') from dual

6,返回ascii值对应的字母
   select chr(97) from dual

7,计算字符串长度 
   select length('abcdef') from dual

8,initcap(首字母变大写) ,lower(变小写),upper(变大写)
   select lower('ABC') s1, 
       upper('def') s2, 
       initcap('efg') s3
   from dual;

9,Replace
   select replace('abc','b','xy') from dual;

10,translate
   select translate('abc','b','xx') from dual; -- x是1位

11,lpad [左添充] rpad [右填充](用于控制输出格式)
   select lpad('func',15,'=') s1, rpad('func',15,'-') s2 from dual;
   select lpad(dname,14,'=') from dept;

12, decode[实现if ..then 逻辑]   注:第一个是表达式,最后一个是不满足任何一个条件的值
   select deptno,decode(deptno,10,'1',20,'2',30,'3','其他') from dept;
   例:
   select seed,account_name,decode(seed,111,1000,200,2000,0) from t_userInfo//如果seed为111,则取1000;为200,取2000;其它取0
   select seed,account_name,decode(sign(seed-111),1,'big seed',-1,'little seed','equal seed') from t_userInfo//如果seed>111,则显示大;为200,则显示小;其它则显

示相等

13 case[实现switch ..case 逻辑]
    SELECT CASE X-FIELD 
         WHEN X-FIELD < 40 THEN 'X-FIELD 小于 40'
         WHEN X-FIELD < 50 THEN 'X-FIELD 小于 50'
         WHEN X-FIELD < 60 THEN 'X-FIELD 小于 60'
         ELSE 'UNBEKNOWN'
        END
   FROM DUAL 
   
   注:CASE语句在处理类似问题就显得非常灵活。当只是需要匹配少量数值时,用Decode更为简洁。

四.数字函数
1,取整函数(ceil 向上取整,floor 向下取整)
   select ceil(66.6) N1,floor(66.6) N2 from dual;

2, 取幂(power) 和 求平方根(sqrt)
   select power(3,2) N1,sqrt(9) N2 from dual;

3,求余
   select mod(9,5) from dual;

4,返回固定小数位数 (round:四舍五入,trunc:直接截断)
   select round(66.667,2) N1,trunc(66.667,2) N2 from dual;

5,返回值的符号(正数返回为1,负数为-1)
   select sign(-32),sign(293) from dual;

五.转换函数
1,to_char()[将日期和数字类型转换成字符类型]
   1) select to_char(sysdate) s1,
        to_char(sysdate,'yyyy-mm-dd') s2,
        to_char(sysdate,'yyyy') s3,
        to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') s4,
        to_char(sysdate, 'hh24:mi:ss') s5,
        to_char(sysdate,'DAY') s6 
    from dual;
   2) select sal,to_char(sal,'$99999') n1,to_char(sal,'$99,999') n2 from emp

2, to_date()[将字符类型转换为日期类型] 
    insert into emp(empno,hiredate) values(8000,to_date('2004-10-10','yyyy-mm-dd'));
   
3, to_number() 转换为数字类型 
    select to_number(to_char(sysdate,'hh12')) from dual; //以数字显示的小时数
   
六.其他函数
   1.user: 
    返回登录的用户名称 
    select user from dual;
    
   2.vsize: 
    返回表达式所需的字节数
    select vsize('HELLO') from dual;
   
   3.nvl(ex1,ex2):
    ex1值为空则返回ex2,否则返回该值本身ex1(常用) 
    例:如果雇员没有佣金,将显示0,否则显示佣金 
    select comm,nvl(comm,0) from emp;
   
   4.nullif(ex1,ex2): 
    值相等返空,否则返回第一个值
    例:如果工资和佣金相等,则显示空,否则显示工资
    select nullif(sal,comm),sal,comm from emp;
   
   5.coalesce:
    返回列表中第一个非空表达式
    select comm,sal,coalesce(comm,sal,sal*10) from emp;
   
   6.nvl2(ex1,ex2,ex3) :
    如果ex1不为空,显示ex2,否则显示ex3
    如:查看有佣金的雇员姓名以及他们的佣金 
      select nvl2(comm,ename,') as HaveCommName,comm from emp;
   
   
七.分组函数
max min avg count sum
1,整个结果集是一个组
   1) 求部门30 的最高工资,最低工资,平均工资,总人数,有工作的人数,工种数量及工资总和
     select max(ename),max(sal), 
     min(ename),min(sal),
     avg(sal),
     count(*) ,count(job),count(distinct(job)) ,
     sum(sal) from emp where deptno=30;
2, 带group by 和 having 的分组
   1)按部门分组求最高工资,最低工资,总人数,有工作的人数,工种数量及工资总和
    select deptno, max(ename),max(sal),
    min(ename),min(sal),
    avg(sal),
    count(*) ,count(job),count(distinct(job)) ,
    sum(sal) from emp group by deptno;
   
   2)部门30的最高工资,最低工资,总人数,有工作的人数,工种数量及工资总和 
    select deptno, max(ename),max(sal),
    min(ename),min(sal),
    avg(sal),
    count(*) ,count(job),count(distinct(job)) ,
    sum(sal) from emp group by deptno having deptno=30;
   
3, stddev 返回一组值的标准偏差
    select deptno,stddev(sal) from emp group by deptno;
    variance 返回一组值的方差差
    select deptno,variance(sal) from emp group by deptno;

4, 带有rollup和cube操作符的Group By
    rollup 按分组的第一个列进行统计和最后的小计
    cube 按分组的所有列的进行统计和最后的小计
    select deptno,job ,sum(sal) from emp group by deptno,job;
    select deptno,job ,sum(sal) from emp group by rollup(deptno,job); 
    cube 产生组内所有列的统计和最后的小计
    select deptno,job ,sum(sal) from emp group by cube(deptno,job);

八、临时表
   只在会话期间或在事务处理期间存在的表.
   临时表在插入数据时,动态分配空间 
   create global temporary table temp_dept
   (dno number,
   dname varchar2(10))
   on commit delete rows;
   insert into temp_dept values(10,'ABC');
   commit;
   select * from temp_dept; --无数据显示,数据自动清除
   on commit preserve rows:在会话期间表一直可以存在(保留数据)
   on commit delete rows:事务结束清除数据(在事务结束时自动删除表的数据) 查看全部


   TO_DATE格式(以时间:2007-11-02   13:45:25为例)
   
        Year:      
        yy two digits 两位年                显示值:07
        yyy three digits 三位年                显示值:007
        yyyy four digits 四位年                显示值:2007
            
        Month:      
        mm    number     两位月              显示值:11
        mon    abbreviated 字符集表示          显示值:11月,若是英文版,显示nov     
        month spelled out 字符集表示          显示值:11月,若是英文版,显示november 
          
        Day:      
        dd    number         当月第几天        显示值:02
        ddd    number         当年第几天        显示值:02
        dy    abbreviated 当周第几天简写    显示值:星期五,若是英文版,显示fri
        day    spelled out   当周第几天全写    显示值:星期五,若是英文版,显示friday        
        ddspth spelled out, ordinal twelfth 
             
              Hour:
              hh    two digits 12小时进制            显示值:01
              hh24 two digits 24小时进制            显示值:13
              
              Minute:
              mi    two digits 60进制                显示值:45
              
              Second:
              ss    two digits 60进制                显示值:25
              
              其它
              Q     digit         季度                  显示值:4
              WW    digit         当年第几周            显示值:44
              W    digit          当月第几周            显示值:1
              
        24小时格式下时间范围为: 0:00:00 - 23:59:59....      
        12小时格式下时间范围为: 1:00:00 - 12:59:59 .... 
            
1. 日期和字符转换函数用法(to_date,to_char)
         
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;   //日期转化为字符串   
select to_char(sysdate,'yyyy') as nowYear   from dual;   //获取时间的年   
select to_char(sysdate,'mm')    as nowMonth from dual;   //获取时间的月   
select to_char(sysdate,'dd')    as nowDay    from dual;   //获取时间的日   
select to_char(sysdate,'hh24') as nowHour   from dual;   //获取时间的时   
select to_char(sysdate,'mi')    as nowMinute from dual;   //获取时间的分   
select to_char(sysdate,'ss')    as nowSecond from dual;   //获取时间的秒

    
select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss')    from dual//

2.      
    select to_char( to_date(222,'J'),'Jsp') from dual      
    
    显示Two Hundred Twenty-Two    

3.求某天是星期几      
   select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;      
   星期一      
   select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;      
   monday      
   设置日期语言      
   ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';      
   也可以这样      
   TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')    

4. 两个日期间的天数      
    select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;    

5. 时间为null的用法      
   select id, active_date from table1      
   UNION      
   select 1, TO_DATE(null) from dual;      
   
   注意要用TO_DATE(null)    

6.月份差   
   a_date between to_date('20011201','yyyymmdd') and to_date('20011231','yyyymmdd')      
   那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。      
   所以,当时间需要精确的时候,觉得to_char还是必要的 
      
7. 日期格式冲突问题      
    输入的格式要看你安装的ORACLE字符集的类型, 比如: US7ASCII, date格式的类型就是: '01-Jan-01'      
    alter system set NLS_DATE_LANGUAGE = American      
    alter session set NLS_DATE_LANGUAGE = American      
    或者在to_date中写      
    select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;      
    注意我这只是举了NLS_DATE_LANGUAGE,当然还有很多,      
    可查看      
    select * from nls_session_parameters      
    select * from V$NLS_PARAMETERS    

8.      
   select count(*)      
   from ( select rownum-1 rnum      
       from all_objects      
       where rownum <= to_date('2002-02-28','yyyy-mm-dd') - to_date('2002-      
       02-01','yyyy-mm-dd')+1      
      )      
   where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' )      
        not in ( '1', '7' )      
   
   查找2002-02-28至2002-02-01间除星期一和七的天数      
   在前后分别调用DBMS_UTILITY.GET_TIME, 让后将结果相减(得到的是1/100秒, 而不是毫秒).    

9. 查找月份     
    select months_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;      
    1      
   select months_between(to_date('02-01-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;      
    1.03225806451613 
       
10. Next_day的用法      
    Next_day(date, day)      
    
    Monday-Sunday, for format code DAY      
    Mon-Sun, for format code DY      
    1-7, for format code D    

11      
   select to_char(sysdate,'hh:mi:ss') TIME from all_objects      
   注意:第一条记录的TIME 与最后一行是一样的      
   可以建立一个函数来处理这个问题      
   create or replace function sys_date return date is      
   begin      
   return sysdate;      
   end;      
   
   select to_char(sys_date,'hh:mi:ss') from all_objects;   
     
12.获得小时数      
     extract()找出日期或间隔值的字段值
    SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 2:38:40') from offer      
    SQL> select sysdate ,to_char(sysdate,'hh') from dual;      
    
    SYSDATE TO_CHAR(SYSDATE,'HH')      
    -------------------- ---------------------      
    2003-10-13 19:35:21 07      
    
    SQL> select sysdate ,to_char(sysdate,'hh24') from dual;      
    
    SYSDATE TO_CHAR(SYSDATE,'HH24')      
    -------------------- -----------------------      
    2003-10-13 19:35:21 19    

       
13.年月日的处理      
   select older_date,      
       newer_date,      
       years,      
       months,      
       abs(      
        trunc(      
         newer_date-      
         add_months( older_date,years*12+months )      
        )      
       ) days 
       
   from ( select      
        trunc(months_between( newer_date, older_date )/12) YEARS,      
        mod(trunc(months_between( newer_date, older_date )),12 ) MONTHS,      
        newer_date,      
        older_date      
        from ( 
              select hiredate older_date, add_months(hiredate,rownum)+rownum newer_date      
              from emp 
             )      
      )    

14.处理月份天数不定的办法      
   select to_char(add_months(last_day(sysdate) +1, -2), 'yyyymmdd'),last_day(sysdate) from dual    

16.找出今年的天数      
   select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual    

   闰年的处理方法      
   to_char( last_day( to_date('02'    | | :year,'mmyyyy') ), 'dd' )      
   如果是28就不是闰年    

17.yyyy与rrrr的区别      
   'YYYY99 TO_C      
   ------- ----      
   yyyy 99 0099      
   rrrr 99 1999      
   yyyy 01 0001      
   rrrr 01 2001    

18.不同时区的处理      
   select to_char( NEW_TIME( sysdate, 'GMT','EST'), 'dd/mm/yyyy hh:mi:ss') ,sysdate      
   from dual;    

19.5秒钟一个间隔      
   Select TO_DATE(FLOOR(TO_CHAR(sysdate,'SSSSS')/300) * 300,'SSSSS') ,TO_CHAR(sysdate,'SSSSS')      
   from dual    

   2002-11-1 9:55:00 35786      
   SSSSS表示5位秒数    

20.一年的第几天      
   select TO_CHAR(SYSDATE,'DDD'),sysdate from dual
        
   310 2002-11-6 10:03:51    

21.计算小时,分,秒,毫秒      
    select      
     Days,      
     A,      
     TRUNC(A*24) Hours,      
     TRUNC(A*24*60 - 60*TRUNC(A*24)) Minutes,      
     TRUNC(A*24*60*60 - 60*TRUNC(A*24*60)) Seconds,      
     TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60)) mSeconds      
    from      
    (      
     select      
     trunc(sysdate) Days,      
     sysdate - trunc(sysdate) A      
     from dual      
   )    


   select * from tabname      
   order by decode(mode,'FIFO',1,-1)*to_char(rq,'yyyymmddhh24miss');      
   
   //      
   floor((date2-date1) /365) 作为年      
   floor((date2-date1, 365) /30) 作为月      
   d(mod(date2-date1, 365), 30)作为日.

23.next_day函数      返回下个星期的日期,day为1-7或星期日-星期六,1表示星期日
   next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。      
   1 2 3 4 5 6 7      
   日 一 二 三 四 五 六    
   
   --------------------------------------------------------------- 
   
   select    (sysdate-to_date('2003-12-03 12:55:45','yyyy-mm-dd hh24:mi:ss'))*24*60*60 from ddual
   日期 返回的是天 然后 转换为ss
     
24,round[舍入到最接近的日期](day:舍入到最接近的星期日)
   select sysdate S1,
   round(sysdate) S2 ,
   round(sysdate,'year') YEAR,
   round(sysdate,'month') MONTH ,
   round(sysdate,'day') DAY from dual

25,trunc[截断到最接近的日期,单位为天] ,返回的是日期类型
   select sysdate S1,                     
     trunc(sysdate) S2,                 //返回当前日期,无时分秒
     trunc(sysdate,'year') YEAR,        //返回当前年的1月1日,无时分秒
     trunc(sysdate,'month') MONTH ,     //返回当前月的1日,无时分秒
     trunc(sysdate,'day') DAY           //返回当前星期的星期天,无时分秒
   from dual

26,返回日期列表中最晚日期
   select greatest('01-1月-04','04-1月-04','10-2月-04') from dual

27.计算时间差
     注:oracle时间差是以天数为单位,所以换算成年月,日
     
      select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))/365) as spanYears from dual        //时间差-年
      select ceil(moths_between(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanMonths from dual        //时间差-月
      select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanDays from dual             //时间差-天
      select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24) as spanHours from dual         //时间差-时
      select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60) as spanMinutes from dual    //时间差-分
      select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60*60) as spanSeconds from dual //时间差-秒

28.更新时间
     注:oracle时间加减是以天数为单位,设改变量为n,所以换算成年月,日
     select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n*365,'yyyy-mm-dd hh24:mi:ss') as newTime from dual        //改变时间-年
     select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),add_months(sysdate,n) as newTime from dual                                 //改变时间-月
     select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n,'yyyy-mm-dd hh24:mi:ss') as newTime from dual            //改变时间-日
     select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24,'yyyy-mm-dd hh24:mi:ss') as newTime from dual         //改变时间-时
     select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual      //改变时间-分
     select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24/60/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual   //改变时间-秒

29.查找月的第一天,最后一天
     SELECT Trunc(Trunc(SYSDATE, 'MONTH') - 1, 'MONTH') First_Day_Last_Month,
       Trunc(SYSDATE, 'MONTH') - 1 / 86400 Last_Day_Last_Month,
       Trunc(SYSDATE, 'MONTH') First_Day_Cur_Month,
       LAST_DAY(Trunc(SYSDATE, 'MONTH')) + 1 - 1 / 86400 Last_Day_Cur_Month
   FROM dual;


三. 字符函数(可用于字面字符或数据库列)

1,字符串截取
   select substr('abcdef',1,3) from dual

2,查找子串位置
   select instr('abcfdgfdhd','fd') from dual

3,字符串连接
   select 'HELLO'||'hello world' from dual;

4, 1)去掉字符串中的空格
    select ltrim(' abc') s1,
    rtrim('zhang ') s2,
    trim(' zhang ') s3 from dual
   2)去掉前导和后缀
    select trim(leading 9 from 9998767999) s1,
    trim(trailing 9 from 9998767999) s2,
    trim(9 from 9998767999) s3 from dual;
   
5,返回字符串首字母的Ascii值
   select ascii('a') from dual

6,返回ascii值对应的字母
   select chr(97) from dual

7,计算字符串长度 
   select length('abcdef') from dual

8,initcap(首字母变大写) ,lower(变小写),upper(变大写)
   select lower('ABC') s1, 
       upper('def') s2, 
       initcap('efg') s3
   from dual;

9,Replace
   select replace('abc','b','xy') from dual;

10,translate
   select translate('abc','b','xx') from dual; -- x是1位

11,lpad [左添充] rpad [右填充](用于控制输出格式)
   select lpad('func',15,'=') s1, rpad('func',15,'-') s2 from dual;
   select lpad(dname,14,'=') from dept;

12, decode[实现if ..then 逻辑]   注:第一个是表达式,最后一个是不满足任何一个条件的值
   select deptno,decode(deptno,10,'1',20,'2',30,'3','其他') from dept;
   例:
   select seed,account_name,decode(seed,111,1000,200,2000,0) from t_userInfo//如果seed为111,则取1000;为200,取2000;其它取0
   select seed,account_name,decode(sign(seed-111),1,'big seed',-1,'little seed','equal seed') from t_userInfo//如果seed>111,则显示大;为200,则显示小;其它则显

示相等

13 case[实现switch ..case 逻辑]
    SELECT CASE X-FIELD 
         WHEN X-FIELD < 40 THEN 'X-FIELD 小于 40'
         WHEN X-FIELD < 50 THEN 'X-FIELD 小于 50'
         WHEN X-FIELD < 60 THEN 'X-FIELD 小于 60'
         ELSE 'UNBEKNOWN'
        END
   FROM DUAL 
   
   注:CASE语句在处理类似问题就显得非常灵活。当只是需要匹配少量数值时,用Decode更为简洁。

四.数字函数
1,取整函数(ceil 向上取整,floor 向下取整)
   select ceil(66.6) N1,floor(66.6) N2 from dual;

2, 取幂(power) 和 求平方根(sqrt)
   select power(3,2) N1,sqrt(9) N2 from dual;

3,求余
   select mod(9,5) from dual;

4,返回固定小数位数 (round:四舍五入,trunc:直接截断)
   select round(66.667,2) N1,trunc(66.667,2) N2 from dual;

5,返回值的符号(正数返回为1,负数为-1)
   select sign(-32),sign(293) from dual;

五.转换函数
1,to_char()[将日期和数字类型转换成字符类型]
   1) select to_char(sysdate) s1,
        to_char(sysdate,'yyyy-mm-dd') s2,
        to_char(sysdate,'yyyy') s3,
        to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') s4,
        to_char(sysdate, 'hh24:mi:ss') s5,
        to_char(sysdate,'DAY') s6 
    from dual;
   2) select sal,to_char(sal,'$99999') n1,to_char(sal,'$99,999') n2 from emp

2, to_date()[将字符类型转换为日期类型] 
    insert into emp(empno,hiredate) values(8000,to_date('2004-10-10','yyyy-mm-dd'));
   
3, to_number() 转换为数字类型 
    select to_number(to_char(sysdate,'hh12')) from dual; //以数字显示的小时数
   
六.其他函数
   1.user: 
    返回登录的用户名称 
    select user from dual;
    
   2.vsize: 
    返回表达式所需的字节数
    select vsize('HELLO') from dual;
   
   3.nvl(ex1,ex2):
    ex1值为空则返回ex2,否则返回该值本身ex1(常用) 
    例:如果雇员没有佣金,将显示0,否则显示佣金 
    select comm,nvl(comm,0) from emp;
   
   4.nullif(ex1,ex2): 
    值相等返空,否则返回第一个值
    例:如果工资和佣金相等,则显示空,否则显示工资
    select nullif(sal,comm),sal,comm from emp;
   
   5.coalesce:
    返回列表中第一个非空表达式
    select comm,sal,coalesce(comm,sal,sal*10) from emp;
   
   6.nvl2(ex1,ex2,ex3) :
    如果ex1不为空,显示ex2,否则显示ex3
    如:查看有佣金的雇员姓名以及他们的佣金 
      select nvl2(comm,ename,') as HaveCommName,comm from emp;
   
   
七.分组函数
max min avg count sum
1,整个结果集是一个组
   1) 求部门30 的最高工资,最低工资,平均工资,总人数,有工作的人数,工种数量及工资总和
     select max(ename),max(sal), 
     min(ename),min(sal),
     avg(sal),
     count(*) ,count(job),count(distinct(job)) ,
     sum(sal) from emp where deptno=30;
2, 带group by 和 having 的分组
   1)按部门分组求最高工资,最低工资,总人数,有工作的人数,工种数量及工资总和
    select deptno, max(ename),max(sal),
    min(ename),min(sal),
    avg(sal),
    count(*) ,count(job),count(distinct(job)) ,
    sum(sal) from emp group by deptno;
   
   2)部门30的最高工资,最低工资,总人数,有工作的人数,工种数量及工资总和 
    select deptno, max(ename),max(sal),
    min(ename),min(sal),
    avg(sal),
    count(*) ,count(job),count(distinct(job)) ,
    sum(sal) from emp group by deptno having deptno=30;
   
3, stddev 返回一组值的标准偏差
    select deptno,stddev(sal) from emp group by deptno;
    variance 返回一组值的方差差
    select deptno,variance(sal) from emp group by deptno;

4, 带有rollup和cube操作符的Group By
    rollup 按分组的第一个列进行统计和最后的小计
    cube 按分组的所有列的进行统计和最后的小计
    select deptno,job ,sum(sal) from emp group by deptno,job;
    select deptno,job ,sum(sal) from emp group by rollup(deptno,job); 
    cube 产生组内所有列的统计和最后的小计
    select deptno,job ,sum(sal) from emp group by cube(deptno,job);

八、临时表
   只在会话期间或在事务处理期间存在的表.
   临时表在插入数据时,动态分配空间 
   create global temporary table temp_dept
   (dno number,
   dname varchar2(10))
   on commit delete rows;
   insert into temp_dept values(10,'ABC');
   commit;
   select * from temp_dept; --无数据显示,数据自动清除
   on commit preserve rows:在会话期间表一直可以存在(保留数据)
   on commit delete rows:事务结束清除数据(在事务结束时自动删除表的数据)

ORA-12154: TNS: 无法解析指定的连接标识符

admin 发表了文章 • 0 个评论 • 113 次浏览 • 2018-04-26 17:26 • 来自相关话题

      相信使用过Oracle数据库的人一定碰到过“ORA-12154: TNS: 无法解析指定的连接标识符”错误,我在此做一个小小的总结。

      在程序中连接Oracle数据库的方式与其他常用数据库,如:MySql,Sql Server不同,这些数据库可以通过直接指定IP的方式连接,但是Oracle则需要通过Oracle客户端配置网络服务名的方式来连接。否则就会出现“ORA-12154: TNS: 无法解析指定的连接标识符”。配置Oracle客户端详细步骤如下:
      安装好Oracle客户端之后,在开始菜单中依次找到“Oracle - OraClient10g_home1—>配置和移植工具—>Net Manager”并打开(Win7,Win8用户如果没有取得Administrator账号登陆需要以管理员方式运行),依次展开“Oracle Net配置—>本地—>服务命名”点击左侧“加号”配置新的网络服务

[image]

[image]

[image]

[image]

      最后,点击“下一步”完成即可,如果不知道或者忘记Oracle服务名可以通过查看服务获得,比如我这里配置的主机名是我本机,在我的电脑服务中找到oracle相关的服务

[image]

通过上面的服务名称,可以看出我安装的服务名就是“ORCL”,或者在服务端通过SQL Plus使用sys账号登陆,输入命令
show parameter service_name查看。
OracleDBConsoleORCL是Oracle网页端管理工具的服务,访问地址一般为“http://127.0.0.1:1158/em/console/logon/logon”,如果不习惯用这个来管理数据库可以不用启动。
OracleJobSchedulerORCL是管理Oracle中计划任务的,一般不用启动。
OracleOraDb10g_home1iSQL*Plus是SQL Plus的服务,如果不习惯在命令行下面操作数据库,可以不用启动。
OracleServiceORCL,OracleOraDb10g_home1TNSListener都需要开启,前者是主服务,后者是监听服务。

     当在Oracle客户端中配置好Net Manager中配置好服务后就可以通过我们配置的网络服务名访问Oracle数据库了,此配置实质想D:\Oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora中增加了一行配置(由于我的Oracle是安装在D盘Oracle目录中,具体路径根据安装时选择的路径而定)。[复制代码]
# tnsnames.ora Network Configuration File: d:\Oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora # Generated by Oracle configuration tools. ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL) ) )
[复制代码]    如果在同一台电脑上面同时安装了Oracle客户端和服务端,那么在服务端的安装目录中也会存在同样的配置文件D:\Oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora,在这种情况下如果我们配置好了客户端Net Manager然后连接Oracle数据库,可能会出现“ORA-12154: TNS: 无法解析指定的连接标识符”这时,你就应该怀疑,我到底访问的是哪个路径下的配置文件呢?此时可以使用tnsping命令

[image]

      如果你是在客户端Net Manager中配置的网络服务,但是此时解析出来的使用参数文件却是D:\Oracle\product\10.2.0\db_1\NETWORK\ADMIN\sqlnet.ora,那么我想你应该明白了。这种情况有两种解决办法:
      第一:在服务端的Net Manager也配置相同的网络服务,或者直接拷贝D:\Oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora中的配置到D:\Oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora,每次配置了新的网络服务你都需要这么做,如果不想这么麻烦那就使用第二种解决办法。     
      第二:修改系统环境变量Path值,将D:\Oracle\product\10.2.0\client_1\bin;路径移至D:\Oracle\product\10.2.0\db_1\bin;前面即可一劳永逸。
      注意:造成以上这种情况出现的原因是:在一台电脑上同时安装了oracle客户端和数据库服务,并且是先安装客户端,再安装数据库服务造成的,如果先安装数据库服务,再安装客户端就不会出现这种情况了。
      根据以上方式配置完oracle后,相信在程序中就可以正常连接oracle数据库了。

      当我安装好PLSQL Developer软件后登陆时任然出现了“ORA-12154: TNS: 无法解析指定的连接标识符”,如果是Win7,Win8用户甚至可以会出现弹出一个白框的情况,这时需要以管理员身份运行PLSQL Developer,并且确保你的PLSQL Developer是读取的oracle客户端配置,直接取消登陆,在PLSQL Developer工具栏中找到“Tools—>Preferences”,在Oracle的Connection节点中找到Oracle Home选择OraClient10g_home1,保存后关闭PLSQL Developer就可以正常登陆了。

[image]

     需要注意的是,在安装PLSQL Developer时不能安装在带有括号的目录中,如64bit系统的Program Files (x86)目录,就会出现无论你怎么设置,它依然不停的弹出“ORA-12154: TNS: 无法解析指定的连接标识符”。同样的问题也会出现在其他Oracle数据库管理软件中,比如Toad for Oracle

     “ORA-12154: TNS: 无法解析指定的连接标识符”是使用Oracle数据库的新手遇到的比较常见的问题之一,只要我们了解了它的工作方式,冷静排查,相信很容易就能够找到问题的解决办法的。 查看全部

      相信使用过Oracle数据库的人一定碰到过“ORA-12154: TNS: 无法解析指定的连接标识符”错误,我在此做一个小小的总结。

      在程序中连接Oracle数据库的方式与其他常用数据库,如:MySql,Sql Server不同,这些数据库可以通过直接指定IP的方式连接,但是Oracle则需要通过Oracle客户端配置网络服务名的方式来连接。否则就会出现“ORA-12154: TNS: 无法解析指定的连接标识符”。配置Oracle客户端详细步骤如下:
      安装好Oracle客户端之后,在开始菜单中依次找到“Oracle - OraClient10g_home1—>配置和移植工具—>Net Manager”并打开(Win7,Win8用户如果没有取得Administrator账号登陆需要以管理员方式运行),依次展开“Oracle Net配置—>本地—>服务命名”点击左侧“加号”配置新的网络服务

[image]

[image]

[image]

[image]

      最后,点击“下一步”完成即可,如果不知道或者忘记Oracle服务名可以通过查看服务获得,比如我这里配置的主机名是我本机,在我的电脑服务中找到oracle相关的服务

[image]

通过上面的服务名称,可以看出我安装的服务名就是“ORCL”,或者在服务端通过SQL Plus使用sys账号登陆,输入命令
show parameter service_name查看。
OracleDBConsoleORCL是Oracle网页端管理工具的服务,访问地址一般为“http://127.0.0.1:1158/em/console/logon/logon”,如果不习惯用这个来管理数据库可以不用启动。
OracleJobSchedulerORCL是管理Oracle中计划任务的,一般不用启动。
OracleOraDb10g_home1iSQL*Plus是SQL Plus的服务,如果不习惯在命令行下面操作数据库,可以不用启动。
OracleServiceORCL,OracleOraDb10g_home1TNSListener都需要开启,前者是主服务,后者是监听服务。

     当在Oracle客户端中配置好Net Manager中配置好服务后就可以通过我们配置的网络服务名访问Oracle数据库了,此配置实质想D:\Oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora中增加了一行配置(由于我的Oracle是安装在D盘Oracle目录中,具体路径根据安装时选择的路径而定)。[复制代码]
# tnsnames.ora Network Configuration File: d:\Oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora # Generated by Oracle configuration tools. ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL) ) )
[复制代码]    如果在同一台电脑上面同时安装了Oracle客户端和服务端,那么在服务端的安装目录中也会存在同样的配置文件D:\Oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora,在这种情况下如果我们配置好了客户端Net Manager然后连接Oracle数据库,可能会出现“ORA-12154: TNS: 无法解析指定的连接标识符”这时,你就应该怀疑,我到底访问的是哪个路径下的配置文件呢?此时可以使用tnsping命令

[image]

      如果你是在客户端Net Manager中配置的网络服务,但是此时解析出来的使用参数文件却是D:\Oracle\product\10.2.0\db_1\NETWORK\ADMIN\sqlnet.ora,那么我想你应该明白了。这种情况有两种解决办法:
      第一:在服务端的Net Manager也配置相同的网络服务,或者直接拷贝D:\Oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora中的配置到D:\Oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora,每次配置了新的网络服务你都需要这么做,如果不想这么麻烦那就使用第二种解决办法。     
      第二:修改系统环境变量Path值,将D:\Oracle\product\10.2.0\client_1\bin;路径移至D:\Oracle\product\10.2.0\db_1\bin;前面即可一劳永逸。
      注意:造成以上这种情况出现的原因是:在一台电脑上同时安装了oracle客户端和数据库服务,并且是先安装客户端,再安装数据库服务造成的,如果先安装数据库服务,再安装客户端就不会出现这种情况了。
      根据以上方式配置完oracle后,相信在程序中就可以正常连接oracle数据库了。

      当我安装好PLSQL Developer软件后登陆时任然出现了“ORA-12154: TNS: 无法解析指定的连接标识符”,如果是Win7,Win8用户甚至可以会出现弹出一个白框的情况,这时需要以管理员身份运行PLSQL Developer,并且确保你的PLSQL Developer是读取的oracle客户端配置,直接取消登陆,在PLSQL Developer工具栏中找到“Tools—>Preferences”,在Oracle的Connection节点中找到Oracle Home选择OraClient10g_home1,保存后关闭PLSQL Developer就可以正常登陆了。

[image]

     需要注意的是,在安装PLSQL Developer时不能安装在带有括号的目录中,如64bit系统的Program Files (x86)目录,就会出现无论你怎么设置,它依然不停的弹出“ORA-12154: TNS: 无法解析指定的连接标识符”。同样的问题也会出现在其他Oracle数据库管理软件中,比如Toad for Oracle

     “ORA-12154: TNS: 无法解析指定的连接标识符”是使用Oracle数据库的新手遇到的比较常见的问题之一,只要我们了解了它的工作方式,冷静排查,相信很容易就能够找到问题的解决办法的。

cmd重启ORACLE数据

admin 发表了文章 • 0 个评论 • 119 次浏览 • 2018-04-26 11:52 • 来自相关话题

建立一个 startup.sql的文件,内容:

1
2
3
conn /as sysdba;
shutdow immediate;
startup

然后再创建一个restartup.bat

1
sqlplus /nolog @startup.sql

然后用restartup.bat就可以了。 查看全部
建立一个 startup.sql的文件,内容:

1
2
3
conn /as sysdba;
shutdow immediate;
startup

然后再创建一个restartup.bat

1
sqlplus /nolog @startup.sql

然后用restartup.bat就可以了。

记32位Oracle客户端登录报12560协议适配器错误的解决办法

admin 发表了文章 • 0 个评论 • 111 次浏览 • 2018-04-26 11:26 • 来自相关话题

64位windows操作系统,必须装64位Oracle数据库服务器端,pl/sql必须连接32位Oracle数据库客户端,那么就必须要下一个Oracle的32位客户端程序。我下了,然后安装了,这里注意一点:第二步选择管理员。然后问题出现了,先别说pl/sql能不能连上Oracle,就连sqlplus都连不上,输入用户名密码后,提示一个tns12560:协议适配器错误的报错信息。笔者也是Oracle新手啊,找问题原因期间遇到了无数次失败,又无数次地问度娘。这里说说度娘给的几点很有用的意见:1、OracleOraDb11g_home1TNSListener这个服务必须启动运行(注意11g_home1跟你安装数据库的版本以及路径有关系);2、OracleServiceORCL这个服务也必须启动运行(orcl与你Oracle数据库名称有关系);3、环境变量中最好设置变量名为oracle_sid,变量值为orcl的变量(orcl与你Oracle数据库名称有关系);4、启用和禁用用户名的命令是alter user scott account lock/unlock;(scott是一个用户名);5、用户启用后首次登录会提示更改预设密码,改为原密码也可以;6、还有人没有安装Oracle数据库32位客户端程序,而是安装了一个instant_client_basic(名字好像是这个,可能不准确)程序,然后仅将Oracle服务器端里的network/admin/文件夹下的配置文件复制到那个程序的文件夹中,且将pl/sql的OCI库设置到那个程序文件夹即可(这里表述不是很清晰,请有用这个方法的人自行百度,很多很全)。可是我的问题没解决呢!经过多番尝试,明确一点,那就是Oracle数据库服务器配置没有问题,用服务器端里的sqlplus能够正确连接并登录数据库。然后打开客户端的网络配置(NET Manager),发现里面没有服务,猜想是不是这里的问题呢?于是就将服务器端的网络配置打开,那里面已经配好了两个服务,其中一个就是我默认的orcl。然后参照里面的参数在客户端的网络配置中配置了一个一模一样的服务,这里有个小插曲,当时已经启用的scott这个用户,但是还没有登录过数据库,在配置最后一步进行测试时,默认用scott用户测试,结果报错,错误信息当时没看懂。于是用sqlplus登录scott用户(当然这是还用的服务器端的sqlplus),结果并没有立刻进去,而是提示请立即更改预设密码,这是回看刚才的报错,一样的信息嘛!更改以后,再到配置最后一步进行测试,提示连接成功。这次该到客户端的sqlplus中登录用户了,可惜还是登录不上去,到底为什么呢?这时回忆刚才度娘里那些前人给的说法,依稀记得有人用“用户名/密码@服务名”方式登录数据库,这种方式在之前导入导出数据库时也用过,于是就用这种格式尝试,sqlplus终于不提示协议适配器错误了,而是显示连接到数据库服务器中了,泪流满面啊!还差最后一步了,启动pl/sql developer程序,先不登录用户,点击取消按钮,直接进入软件,在工具-首选项-连接中(其实就是进入首选项后的第一个界面,用英文的朋友差不多能找到吧),Oracle主目录名选择下拉列表框中给出的选项即可,OCI库也是一样(这里注意一定要是客户端安装目录,比如路径中还有client_1这样的字眼,跟你安装客户端是的路径有关系,当然一般下拉列表框中给出的选项就是客户端的)。然后退出pl/sql程序,重新启动并进入,这时登录用户名为scott、密码为tiger、数据库为orcl,这几个值都跟个人的设置有关系,我这里给出的是默认值。然后pl/sql启动画面显示已连接,过几秒就进入软件主窗体界面,至此问题全都解决,可以在64位系统下使用Oracle数据库喽!

以上就是今天解决问题的全过程。真心想用64位操作系统,但是之前的内存太小,而且64位存在兼容问题,于是迟迟没有用。今天借着预装正版64位系统的东风,熟悉一下,并且解决了一个小问题。以后会坚定的走在64位平台上,服务器从win2008开始就没有32位了,而手机也快迈入64位了,我们开发人员不要固守32位系统了,当然如果你有需要,可以用虚拟机嘛,我的下一篇文章可能就是解决使用虚拟机碰到的问题。希望这篇文章能够帮到遇到同样问题的朋友! 查看全部
64位windows操作系统,必须装64位Oracle数据库服务器端,pl/sql必须连接32位Oracle数据库客户端,那么就必须要下一个Oracle的32位客户端程序。我下了,然后安装了,这里注意一点:第二步选择管理员。然后问题出现了,先别说pl/sql能不能连上Oracle,就连sqlplus都连不上,输入用户名密码后,提示一个tns12560:协议适配器错误的报错信息。笔者也是Oracle新手啊,找问题原因期间遇到了无数次失败,又无数次地问度娘。这里说说度娘给的几点很有用的意见:1、OracleOraDb11g_home1TNSListener这个服务必须启动运行(注意11g_home1跟你安装数据库的版本以及路径有关系);2、OracleServiceORCL这个服务也必须启动运行(orcl与你Oracle数据库名称有关系);3、环境变量中最好设置变量名为oracle_sid,变量值为orcl的变量(orcl与你Oracle数据库名称有关系);4、启用和禁用用户名的命令是alter user scott account lock/unlock;(scott是一个用户名);5、用户启用后首次登录会提示更改预设密码,改为原密码也可以;6、还有人没有安装Oracle数据库32位客户端程序,而是安装了一个instant_client_basic(名字好像是这个,可能不准确)程序,然后仅将Oracle服务器端里的network/admin/文件夹下的配置文件复制到那个程序的文件夹中,且将pl/sql的OCI库设置到那个程序文件夹即可(这里表述不是很清晰,请有用这个方法的人自行百度,很多很全)。可是我的问题没解决呢!经过多番尝试,明确一点,那就是Oracle数据库服务器配置没有问题,用服务器端里的sqlplus能够正确连接并登录数据库。然后打开客户端的网络配置(NET Manager),发现里面没有服务,猜想是不是这里的问题呢?于是就将服务器端的网络配置打开,那里面已经配好了两个服务,其中一个就是我默认的orcl。然后参照里面的参数在客户端的网络配置中配置了一个一模一样的服务,这里有个小插曲,当时已经启用的scott这个用户,但是还没有登录过数据库,在配置最后一步进行测试时,默认用scott用户测试,结果报错,错误信息当时没看懂。于是用sqlplus登录scott用户(当然这是还用的服务器端的sqlplus),结果并没有立刻进去,而是提示请立即更改预设密码,这是回看刚才的报错,一样的信息嘛!更改以后,再到配置最后一步进行测试,提示连接成功。这次该到客户端的sqlplus中登录用户了,可惜还是登录不上去,到底为什么呢?这时回忆刚才度娘里那些前人给的说法,依稀记得有人用“用户名/密码@服务名”方式登录数据库,这种方式在之前导入导出数据库时也用过,于是就用这种格式尝试,sqlplus终于不提示协议适配器错误了,而是显示连接到数据库服务器中了,泪流满面啊!还差最后一步了,启动pl/sql developer程序,先不登录用户,点击取消按钮,直接进入软件,在工具-首选项-连接中(其实就是进入首选项后的第一个界面,用英文的朋友差不多能找到吧),Oracle主目录名选择下拉列表框中给出的选项即可,OCI库也是一样(这里注意一定要是客户端安装目录,比如路径中还有client_1这样的字眼,跟你安装客户端是的路径有关系,当然一般下拉列表框中给出的选项就是客户端的)。然后退出pl/sql程序,重新启动并进入,这时登录用户名为scott、密码为tiger、数据库为orcl,这几个值都跟个人的设置有关系,我这里给出的是默认值。然后pl/sql启动画面显示已连接,过几秒就进入软件主窗体界面,至此问题全都解决,可以在64位系统下使用Oracle数据库喽!

以上就是今天解决问题的全过程。真心想用64位操作系统,但是之前的内存太小,而且64位存在兼容问题,于是迟迟没有用。今天借着预装正版64位系统的东风,熟悉一下,并且解决了一个小问题。以后会坚定的走在64位平台上,服务器从win2008开始就没有32位了,而手机也快迈入64位了,我们开发人员不要固守32位系统了,当然如果你有需要,可以用虚拟机嘛,我的下一篇文章可能就是解决使用虚拟机碰到的问题。希望这篇文章能够帮到遇到同样问题的朋友!

Oracle常用命令大全(很有用,做笔记)

admin 发表了文章 • 0 个评论 • 75 次浏览 • 2018-04-26 10:05 • 来自相关话题

Oracle常用命令大全(很有用,做笔记)

一、ORACLE的启动和关闭 
1、在单机环境下 
要想启动或关闭ORACLE系统必须首先切换到ORACLE用户,如下 
su - oracle 

a、启动ORACLE系统 
oracle>svrmgrl 
SVRMGR>connect internal 
SVRMGR>startup 
SVRMGR>quit 

b、关闭ORACLE系统 
oracle>svrmgrl 
SVRMGR>connect internal 
SVRMGR>shutdown 
SVRMGR>quit 

启动oracle9i数据库命令: 
$ sqlplus /nolog 

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Oct 31 13:53:53 2003 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved. 

SQL> connect / as sysdba  
Connected to an idle instance. 
SQL> startup^C 

SQL> startup 
ORACLE instance started. 


2、在双机环境下 
要想启动或关闭ORACLE系统必须首先切换到root用户,如下 
su - root 

a、启动ORACLE系统 
hareg -y oracle 

b、关闭ORACLE系统 
hareg -n oracle 

Oracle数据库有哪几种启动方式 


说明: 

有以下几种启动方式: 
1、startup nomount 
非安装启动,这种方式启动下可执行:重建控制文件、重建数据库 

读取init.ora文件,启动instance,即启动SGA和后台进程,这种启动只需要init.ora文件。 


2、startup mount dbname 
安装启动,这种方式启动下可执行: 
数据库日志归档、 
数据库介质恢复、 
使数据文件联机或脱机, 
重新定位数据文件、重做日志文件。 

执行“nomount”,然后打开控制文件,确认数据文件和联机日志文件的位置, 
但此时不对数据文件和日志文件进行校验检查。 


3、startup open dbname 
先执行“nomount”,然后执行“mount”,再打开包括Redo log文件在内的所有数据库文件, 
这种方式下可访问数据库中的数据。 


4、startup,等于以下三个命令 
startup nomount 
alter database mount 
alter database open 


5、startup restrict 
约束方式启动 
这种方式能够启动数据库,但只允许具有一定特权的用户访问 
非特权用户访问时,会出现以下提示: 
ERROR: 
ORA-01035: ORACLE 只允许具有 RESTRICTED SESSION 权限的用户使用 


6、startup force 
强制启动方式 
当不能关闭数据库时,可以用startup force来完成数据库的关闭 
先关闭数据库,再执行正常启动数据库命令 


7、startup pfile=参数文件名 
带初始化参数文件的启动方式 
先读取参数文件,再按参数文件中的设置启动数据库 
例:startup pfile=E:Oracleadminoradbpfileinit.ora 


8、startup EXCLUSIVE 
二、用户如何有效地利用数据字典 
   ORACLE的数据字典是数据库的重要组成部分之一,它随着数据库的产生而产生, 随着数据库的变化而变化, 
体现为sys用户下的一些表和视图。数据字典名称是大写的英文字符。 

    数据字典里存有用户信息、用户的权限信息、所有数据对象信息、表的约束条件、统计分析数据库的视图等。 
我们不能手工修改数据字典里的信息。 

很多时候,一般的ORACLE用户不知道如何有效地利用它。 

dictionary   全部数据字典表的名称和解释,它有一个同义词dict 
    dict_column   全部数据字典表里字段名称和解释 

    如果我们想查询跟索引有关的数据字典时,可以用下面这条SQL语句: 

    SQL>select * from dictionary where instr(comments,'index')>0; 

    如果我们想知道user_indexes表各字段名称的详细含义,可以用下面这条SQL语句: 

    SQL>select column_name,comments from dict_columns where table_name='USER_INDEXES'; 

    依此类推,就可以轻松知道数据字典的详细名称和解释,不用查看ORACLE的其它文档资料了。 

    下面按类别列出一些ORACLE用户常用数据字典的查询使用方法。 

    1、用户 

            查看当前用户的缺省表空间 
            SQL>select username,default_tablespace from user_users; 

        查看当前用户的角色 
        SQL>select * from user_role_privs; 

        查看当前用户的系统权限和表级权限 
        SQL>select * from user_sys_privs; 
        SQL>select * from user_tab_privs; 

    2、表 

            查看用户下所有的表 
            SQL>select * from user_tables; 

            查看名称包含log字符的表 
            SQL>select object_name,object_id from user_objects 
                where instr(object_name,'LOG')>0; 

            查看某表的创建时间 
            SQL>select object_name,created from user_objects where object_name=upper('&table_name'); 

            查看某表的大小 
            SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments 
                where segment_name=upper('&table_name'); 

            查看放在ORACLE的内存区里的表 
            SQL>select table_name,cache from user_tables where instr(cache,'Y')>0; 

    3、索引 

            查看索引个数和类别 
            SQL>select index_name,index_type,table_name from user_indexes order by table_name; 

            查看索引被索引的字段 
            SQL>select * from user_ind_columns where index_name=upper('&index_name'); 

            查看索引的大小 
            SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments 
                where segment_name=upper('&index_name'); 

    4、序列号 

            查看序列号,last_number是当前值 
            SQL>select * from user_sequences; 

    5、视图 

            查看视图的名称 
            SQL>select view_name from user_views; 

            查看创建视图的select语句 
            SQL>set view_name,text_length from user_views; 
            SQL>set long 2000;                说明:可以根据视图的text_length值设定set long 的大小 
            SQL>select text from user_views where view_name=upper('&view_name'); 

    6、同义词 

            查看同义词的名称 
            SQL>select * from user_synonyms; 

    7、约束条件 

            查看某表的约束条件 
            SQL>select constraint_name, constraint_type,search_condition, r_constraint_name 
                from user_constraints where table_name = upper('&table_name'); 

        SQL>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; 

    8、存储函数和过程 

            查看函数和过程的状态 
            SQL>select object_name,status from user_objects where object_type='FUNCTION'; 
            SQL>select object_name,status from user_objects where object_type='PROCEDURE'; 

            查看函数和过程的源代码 
            SQL>select text from all_source where owner=user and name=upper('&plsql_name'); 


三、查看数据库的SQL 
1、查看表空间的名称及大小 

    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; 

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

    select tablespace_name, file_id, file_name, 
    round(bytes/(1024*1024),0) total_space 
    from dba_data_files 
    order by tablespace_name; 

3、查看回滚段名称及大小 

    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 ; 

4、查看控制文件 

    select name from v$controlfile; 

5、查看日志文件 

    select member from v$logfile; 

6、查看表空间的使用情况 

    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; 

7、查看数据库库对象 

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

8、查看数据库的版本 

    Select version FROM Product_component_version 
    Where SUBSTR(PRODUCT,1,6)='Oracle'; 

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

    Select Created, Log_Mode, Log_Mode From V$Database; 
四、ORACLE用户连接的管理 

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

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

如果要停某个连接用 

SQL> alter system kill session 'sid,serial#'; 

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

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

说明:21是某个连接的sid数 

然后用 kill 命令杀此进程号。 


五、SQL*PLUS使用 
a、近入SQL*Plus 
$sqlplus 用户名/密码 

   退出SQL*Plus 
SQL>exit 

b、在sqlplus下得到帮助信息 
列出全部SQL命令和SQL*Plus命令 
SQL>help 
列出某个特定的命令的信息 
SQL>help 命令名 

c、显示表结构命令DESCRIBE 
SQL>DESC 表名 

d、SQL*Plus中的编辑命令 
显示SQL缓冲区命令 
SQL>L 

修改SQL命令 
首先要将待改正行变为当前行 
SQL>n 
用CHANGE命令修改内容 
SQL>c/旧/新 
重新确认是否已正确 
SQL>L 

使用INPUT命令可以在SQL缓冲区中增加一行或多行 
SQL>i 
SQL>输入内容 

e、调用外部系统编辑器 
SQL>edit 文件名 
可以使用DEFINE命令设置系统变量EDITOR来改变文本编辑器的类型,在login.sql文件中定义如下一行 
DEFINE_EDITOR=vi 

f、运行命令文件 
SQL>START test 
SQL>@test 

常用SQL*Plus语句 
a、表的创建、修改、删除 
创建表的命令格式如下: 
create table 表名 (列说明列表); 

为基表增加新列命令如下: 
ALTER TABLE 表名 ADD (列说明列表) 
例:为test表增加一列Age,用来存放年龄 
    sql>alter table test 
        add (Age number(3)); 

修改基表列定义命令如下: 
ALTER TABLE 表名 
MODIFY (列名 数据类型) 
例:将test表中的Count列宽度加长为10个字符 
    sql>alter atble test 
        modify (County char(10)); 

b、将一张表删除语句的格式如下: 
DORP TABLE 表名; 
例:表删除将同时删除表的数据和表的定义 
sql>drop table test 

c、表空间的创建、删除 


六、ORACLE逻辑备份的SH文件 

完全备份的SH文件:exp_comp.sh 

rq=` date +"%m%d" ` 

su - oracle -c "exp system/manager full=y inctype=complete file=/oracle/export/db_comp$rq.dmp" 

累计备份的SH文件:exp_cumu.sh 

rq=` date +"%m%d" ` 

su - oracle -c "exp system/manager full=y inctype=cumulative file=/oracle/export/db_cumu$rq.dmp" 

增量备份的SH文件: exp_incr.sh 

rq=` date +"%m%d" ` 

su - oracle -c "exp system/manager full=y inctype=incremental file=/oracle/export/db_incr$rq.dmp" 

root用户crontab文件 
/var/spool/cron/crontabs/root增加以下内容 

0 2 1 * * /oracle/exp_comp.sh 

30 2 * * 0-5 /oracle/exp_incr.sh 

45 2 * * 6 /oracle/exp_cumu.sh 

当然这个时间表可以根据不同的需求来改变的,这只是一个例子。 


七、ORACLE 常用的SQL语法和数据对象 

一.数据控制语句 (DML) 部分 

1.INSERT  (往数据表里插入记录的语句) 

INSERT INTO 表名(字段名1, 字段名2, ……) VALUES ( 值1, 值2, ……); 
INSERT INTO 表名(字段名1, 字段名2, ……)  SELECT (字段名1, 字段名2, ……) FROM 另外的表名; 

字符串类型的字段值必须用单引号括起来, 例如: ’GOOD DAY’ 
如果字段值里包含单引号’ 需要进行字符串转换, 我们把它替换成两个单引号''. 
字符串类型的字段值超过定义的长度会出错, 最好在插入前进行长度校验. 

日期字段的字段值可以用当前数据库的系统时间SYSDATE, 精确到秒 
或者用字符串转换成日期型函数TO_DATE(‘2001-08-01’,’YYYY-MM-DD’) 
TO_DATE()还有很多种日期格式, 可以参看ORACLE DOC. 
年-月-日 小时:分钟:秒 的格式YYYY-MM-DD HH24:MI:SS 

INSERT时最大可操作的字符串长度小于等于4000个单字节, 如果要插入更长的字符串, 请考虑字段用CLOB类型, 
方法借用ORACLE里自带的DBMS_LOB程序包. 

INSERT时如果要用到从1开始自动增长的序列号, 应该先建立一个序列号 
CREATE SEQUENCE 序列号的名称 (最好是表名+序列号标记) INCREMENT BY 1  START  WITH  1 
MAXVALUE  99999  CYCLE  NOCACHE; 
其中最大的值按字段的长度来定, 如果定义的自动增长的序列号 NUMBER(6) , 最大值为999999 
INSERT 语句插入这个字段值为: 序列号的名称.NEXTVAL 

2.DELETE  (删除数据表里记录的语句) 

DELETE FROM表名 WHERE 条件; 

注意:删除记录并不能释放ORACLE里被占用的数据块表空间. 它只把那些被删除的数据块标成unused. 

如果确实要删除一个大表里的全部记录, 可以用 TRUNCATE 命令, 它可以释放占用的数据块表空间 
TRUNCATE TABLE 表名; 
此操作不可回退. 

3.UPDATE  (修改数据表里记录的语句) 

UPDATE表名 SET 字段名1=值1, 字段名2=值2, …… WHERE 条件; 

如果修改的值N没有赋值或定义时, 将把原来的记录内容清为NULL, 最好在修改前进行非空校验; 
值N超过定义的长度会出错, 最好在插入前进行长度校验.. 

注意事项: 
A.        以上SQL语句对表都加上了行级锁, 
        确认完成后, 必须加上事物处理结束的命令 COMMIT 才能正式生效, 
        否则改变不一定写入数据库里. 
        如果想撤回这些操作, 可以用命令 ROLLBACK 复原. 

B.        在运行INSERT, DELETE 和 UPDATE 语句前最好估算一下可能操作的记录范围, 
        应该把它限定在较小 (一万条记录) 范围内,. 否则ORACLE处理这个事物用到很大的回退段. 
        程序响应慢甚至失去响应. 如果记录数上十万以上这些操作, 可以把这些SQL语句分段分次完成, 
        其间加上COMMIT 确认事物处理. 
二.数据定义 (DDL) 部分 

1.CREATE (创建表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等) 

ORACLE常用的字段类型有 
CHAR                        固定长度的字符串 
VARCHAR2                可变长度的字符串 
NUMBER(M,N)                数字型M是位数总长度, N是小数的长度 
DATE                        日期类型 

创建表时要把较小的不为空的字段放在前面, 可能为空的字段放在后面 

创建表时可以用中文的字段名, 但最好还是用英文的字段名 

创建表时可以给字段加上默认值, 例如 DEFAULT SYSDATE 
这样每次插入和修改时, 不用程序操作这个字段都能得到动作的时间 

创建表时可以给字段加上约束条件 
例如 不允许重复 UNIQUE, 关键字 PRIMARY KEY 

2.ALTER        (改变表, 索引, 视图等) 

改变表的名称 
ALTER TABLE 表名1  TO 表名2; 

在表的后面增加一个字段 
ALTER TABLE表名 ADD 字段名 字段名描述; 

修改表里字段的定义描述 
ALTER TABLE表名 MODIFY字段名 字段名描述; 

给表里的字段加上约束条件 
ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY (字段名); 
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (字段名); 

把表放在或取出数据库的内存区 
ALTER TABLE 表名 CACHE; 
ALTER TABLE 表名 NOCACHE; 

3.DROP        (删除表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等) 

删除表和它所有的约束条件 
DROP TABLE 表名 CASCADE CONSTRAINTS; 

4.TRUNCATE (清空表里的所有记录, 保留表的结构) 

TRUNCATE 表名; 

三.查询语句 (SELECT) 部分 

SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE 条件; 

字段名可以带入函数 
  例如:  COUNT(*), MIN(字段名),  MAX(字段名),  AVG(字段名), DISTINCT(字段名), 
           TO_CHAR(DATE字段名,'YYYY-MM-DD HH24:MI:SS') 

NVL(EXPR1, EXPR2)函数 
解释: 
IF EXPR1=NULL 
                RETURN EXPR2 
ELSE 
                       RETURN EXPR1 

DECODE(AA﹐V1﹐R1﹐V2﹐R2....)函数 
解释: 
IF AA=V1 THEN RETURN R1 
IF AA=V2 THEN RETURN R2 
..… 
ELSE 
RETURN NULL 

LPAD(char1,n,char2)函数 
解释: 
字符char1按制定的位数n显示,不足的位数用char2字符串替换左边的空位 

字段名之间可以进行算术运算 
例如:  (字段名1*字段名1)/3 

查询语句可以嵌套 
例如: SELECT …… FROM 
(SELECT …… FROM表名1, [表名2, ……] WHERE 条件) WHERE 条件2; 

两个查询语句的结果可以做集合操作 
例如: 并集UNION(去掉重复记录), 并集UNION ALL(不去掉重复记录), 差集MINUS,  交集INTERSECT 

分组查询 
SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] GROUP BY字段名1 
[HAVING 条件] ; 

两个以上表之间的连接查询 

SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE 
                表名1.字段名 = 表名2. 字段名 [ AND ……] ; 

SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE 
                表名1.字段名 = 表名2. 字段名(+) [ AND ……] ; 

有(+)号的字段位置自动补空值 

查询结果集的排序操作, 默认的排序是升序ASC, 降序是DESC 

SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] 
ORDER BY字段名1, 字段名2 DESC; 

字符串模糊比较的方法 

INSTR(字段名, ‘字符串’)>0 
字段名 LIKE  ‘字符串%’  [‘%字符串%’] 

每个表都有一个隐含的字段ROWID, 它标记着记录的唯一性. 

四.ORACLE里常用的数据对象 (SCHEMA) 

1.索引 (INDEX) 

CREATE INDEX 索引名ON 表名 ( 字段1, [字段2, ……] ); 
ALTER INDEX 索引名 REBUILD; 

一个表的索引最好不要超过三个 (特殊的大表除外), 最好用单字段索引, 结合SQL语句的分析执行情况, 
也可以建立多字段的组合索引和基于函数的索引 

ORACLE8.1.7字符串可以索引的最大长度为1578 单字节 
ORACLE8.0.6字符串可以索引的最大长度为758 单字节 

2.视图 (VIEW) 

CREATE VIEW 视图名AS SELECT …. FROM …..; 
ALTER VIEW视图名 COMPILE; 

视图仅是一个SQL查询语句, 它可以把表之间复杂的关系简洁化. 

3.同义词 (SYNONMY) 
CREATE SYNONYM同义词名FOR 表名; 
CREATE SYNONYM同义词名FOR 表名@数据库链接名; 

4.数据库链接 (DATABASE LINK) 
CREATE DATABASE LINK数据库链接名CONNECT TO 用户名 IDENTIFIED BY 密码 USING ‘数据库连接字符串’; 

数据库连接字符串可以用NET8 EASY CONFIG或者直接修改TNSNAMES.ORA里定义. 

数据库参数global_name=true时要求数据库链接名称跟远端数据库名称一样 

数据库全局名称可以用以下命令查出 
SELECT * FROM GLOBAL_NAME; 

查询远端数据库里的表 
SELECT …… FROM 表名@数据库链接名; 

五.权限管理 (DCL) 语句 

1.GRANT        赋于权限 
常用的系统权限集合有以下三个: 
CONNECT(基本的连接), RESOURCE(程序开发), DBA(数据库管理) 
常用的数据对象权限有以下五个: 
ALL         ON 数据对象名,         SELECT ON 数据对象名,         UPDATE ON 数据对象名, 
DELETE         ON 数据对象名,  INSERT ON 数据对象名,   ALTER  ON 数据对象名 

GRANT CONNECT, RESOURCE TO 用户名; 
GRANT SELECT ON 表名 TO 用户名; 
GRANT SELECT, INSERT, DELETE ON表名 TO 用户名1, 用户名2; 

2.REVOKE 回收权限 

REVOKE CONNECT, RESOURCE FROM 用户名; 
REVOKE SELECT ON 表名 FROM 用户名; 
REVOKE SELECT, INSERT, DELETE ON表名 FROM 用户名1, 用户名2; 


查询数据库中第63号错误: 
select orgaddr,destaddr from sm_histable0116 where error_code='63'; 

查询数据库中开户用户最大提交和最大下发数: select MSISDN,TCOS,OCOS from ms_usertable; 


查询数据库中各种错误代码的总和: 
select error_code,count(*) from sm_histable0513 group by error_code order 
by error_code; 

查询报表数据库中话单统计种类查询。 
select sum(Successcount) from tbl_MiddleMt0411 where ServiceType2=111 
select sum(successcount),servicetype from tbl_middlemt0411 group by servicetype  查看全部
Oracle常用命令大全(很有用,做笔记)

一、ORACLE的启动和关闭 
1、在单机环境下 
要想启动或关闭ORACLE系统必须首先切换到ORACLE用户,如下 
su - oracle 

a、启动ORACLE系统 
oracle>svrmgrl 
SVRMGR>connect internal 
SVRMGR>startup 
SVRMGR>quit 

b、关闭ORACLE系统 
oracle>svrmgrl 
SVRMGR>connect internal 
SVRMGR>shutdown 
SVRMGR>quit 

启动oracle9i数据库命令: 
$ sqlplus /nolog 

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Oct 31 13:53:53 2003 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved. 

SQL> connect / as sysdba  
Connected to an idle instance. 
SQL> startup^C 

SQL> startup 
ORACLE instance started. 


2、在双机环境下 
要想启动或关闭ORACLE系统必须首先切换到root用户,如下 
su - root 

a、启动ORACLE系统 
hareg -y oracle 

b、关闭ORACLE系统 
hareg -n oracle 

Oracle数据库有哪几种启动方式 


说明: 

有以下几种启动方式: 
1、startup nomount 
非安装启动,这种方式启动下可执行:重建控制文件、重建数据库 

读取init.ora文件,启动instance,即启动SGA和后台进程,这种启动只需要init.ora文件。 


2、startup mount dbname 
安装启动,这种方式启动下可执行: 
数据库日志归档、 
数据库介质恢复、 
使数据文件联机或脱机, 
重新定位数据文件、重做日志文件。 

执行“nomount”,然后打开控制文件,确认数据文件和联机日志文件的位置, 
但此时不对数据文件和日志文件进行校验检查。 


3、startup open dbname 
先执行“nomount”,然后执行“mount”,再打开包括Redo log文件在内的所有数据库文件, 
这种方式下可访问数据库中的数据。 


4、startup,等于以下三个命令 
startup nomount 
alter database mount 
alter database open 


5、startup restrict 
约束方式启动 
这种方式能够启动数据库,但只允许具有一定特权的用户访问 
非特权用户访问时,会出现以下提示: 
ERROR: 
ORA-01035: ORACLE 只允许具有 RESTRICTED SESSION 权限的用户使用 


6、startup force 
强制启动方式 
当不能关闭数据库时,可以用startup force来完成数据库的关闭 
先关闭数据库,再执行正常启动数据库命令 


7、startup pfile=参数文件名 
带初始化参数文件的启动方式 
先读取参数文件,再按参数文件中的设置启动数据库 
例:startup pfile=E:Oracleadminoradbpfileinit.ora 


8、startup EXCLUSIVE 
二、用户如何有效地利用数据字典 
   ORACLE的数据字典是数据库的重要组成部分之一,它随着数据库的产生而产生, 随着数据库的变化而变化, 
体现为sys用户下的一些表和视图。数据字典名称是大写的英文字符。 

    数据字典里存有用户信息、用户的权限信息、所有数据对象信息、表的约束条件、统计分析数据库的视图等。 
我们不能手工修改数据字典里的信息。 

很多时候,一般的ORACLE用户不知道如何有效地利用它。 

dictionary   全部数据字典表的名称和解释,它有一个同义词dict 
    dict_column   全部数据字典表里字段名称和解释 

    如果我们想查询跟索引有关的数据字典时,可以用下面这条SQL语句: 

    SQL>select * from dictionary where instr(comments,'index')>0; 

    如果我们想知道user_indexes表各字段名称的详细含义,可以用下面这条SQL语句: 

    SQL>select column_name,comments from dict_columns where table_name='USER_INDEXES'; 

    依此类推,就可以轻松知道数据字典的详细名称和解释,不用查看ORACLE的其它文档资料了。 

    下面按类别列出一些ORACLE用户常用数据字典的查询使用方法。 

    1、用户 

            查看当前用户的缺省表空间 
            SQL>select username,default_tablespace from user_users; 

        查看当前用户的角色 
        SQL>select * from user_role_privs; 

        查看当前用户的系统权限和表级权限 
        SQL>select * from user_sys_privs; 
        SQL>select * from user_tab_privs; 

    2、表 

            查看用户下所有的表 
            SQL>select * from user_tables; 

            查看名称包含log字符的表 
            SQL>select object_name,object_id from user_objects 
                where instr(object_name,'LOG')>0; 

            查看某表的创建时间 
            SQL>select object_name,created from user_objects where object_name=upper('&table_name'); 

            查看某表的大小 
            SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments 
                where segment_name=upper('&table_name'); 

            查看放在ORACLE的内存区里的表 
            SQL>select table_name,cache from user_tables where instr(cache,'Y')>0; 

    3、索引 

            查看索引个数和类别 
            SQL>select index_name,index_type,table_name from user_indexes order by table_name; 

            查看索引被索引的字段 
            SQL>select * from user_ind_columns where index_name=upper('&index_name'); 

            查看索引的大小 
            SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments 
                where segment_name=upper('&index_name'); 

    4、序列号 

            查看序列号,last_number是当前值 
            SQL>select * from user_sequences; 

    5、视图 

            查看视图的名称 
            SQL>select view_name from user_views; 

            查看创建视图的select语句 
            SQL>set view_name,text_length from user_views; 
            SQL>set long 2000;                说明:可以根据视图的text_length值设定set long 的大小 
            SQL>select text from user_views where view_name=upper('&view_name'); 

    6、同义词 

            查看同义词的名称 
            SQL>select * from user_synonyms; 

    7、约束条件 

            查看某表的约束条件 
            SQL>select constraint_name, constraint_type,search_condition, r_constraint_name 
                from user_constraints where table_name = upper('&table_name'); 

        SQL>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; 

    8、存储函数和过程 

            查看函数和过程的状态 
            SQL>select object_name,status from user_objects where object_type='FUNCTION'; 
            SQL>select object_name,status from user_objects where object_type='PROCEDURE'; 

            查看函数和过程的源代码 
            SQL>select text from all_source where owner=user and name=upper('&plsql_name'); 


三、查看数据库的SQL 
1、查看表空间的名称及大小 

    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; 

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

    select tablespace_name, file_id, file_name, 
    round(bytes/(1024*1024),0) total_space 
    from dba_data_files 
    order by tablespace_name; 

3、查看回滚段名称及大小 

    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 ; 

4、查看控制文件 

    select name from v$controlfile; 

5、查看日志文件 

    select member from v$logfile; 

6、查看表空间的使用情况 

    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; 

7、查看数据库库对象 

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

8、查看数据库的版本 

    Select version FROM Product_component_version 
    Where SUBSTR(PRODUCT,1,6)='Oracle'; 

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

    Select Created, Log_Mode, Log_Mode From V$Database; 
四、ORACLE用户连接的管理 

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

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

如果要停某个连接用 

SQL> alter system kill session 'sid,serial#'; 

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

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

说明:21是某个连接的sid数 

然后用 kill 命令杀此进程号。 


五、SQL*PLUS使用 
a、近入SQL*Plus 
$sqlplus 用户名/密码 

   退出SQL*Plus 
SQL>exit 

b、在sqlplus下得到帮助信息 
列出全部SQL命令和SQL*Plus命令 
SQL>help 
列出某个特定的命令的信息 
SQL>help 命令名 

c、显示表结构命令DESCRIBE 
SQL>DESC 表名 

d、SQL*Plus中的编辑命令 
显示SQL缓冲区命令 
SQL>L 

修改SQL命令 
首先要将待改正行变为当前行 
SQL>n 
用CHANGE命令修改内容 
SQL>c/旧/新 
重新确认是否已正确 
SQL>L 

使用INPUT命令可以在SQL缓冲区中增加一行或多行 
SQL>i 
SQL>输入内容 

e、调用外部系统编辑器 
SQL>edit 文件名 
可以使用DEFINE命令设置系统变量EDITOR来改变文本编辑器的类型,在login.sql文件中定义如下一行 
DEFINE_EDITOR=vi 

f、运行命令文件 
SQL>START test 
SQL>@test 

常用SQL*Plus语句 
a、表的创建、修改、删除 
创建表的命令格式如下: 
create table 表名 (列说明列表); 

为基表增加新列命令如下: 
ALTER TABLE 表名 ADD (列说明列表) 
例:为test表增加一列Age,用来存放年龄 
    sql>alter table test 
        add (Age number(3)); 

修改基表列定义命令如下: 
ALTER TABLE 表名 
MODIFY (列名 数据类型) 
例:将test表中的Count列宽度加长为10个字符 
    sql>alter atble test 
        modify (County char(10)); 

b、将一张表删除语句的格式如下: 
DORP TABLE 表名; 
例:表删除将同时删除表的数据和表的定义 
sql>drop table test 

c、表空间的创建、删除 


六、ORACLE逻辑备份的SH文件 

完全备份的SH文件:exp_comp.sh 

rq=` date +"%m%d" ` 

su - oracle -c "exp system/manager full=y inctype=complete file=/oracle/export/db_comp$rq.dmp" 

累计备份的SH文件:exp_cumu.sh 

rq=` date +"%m%d" ` 

su - oracle -c "exp system/manager full=y inctype=cumulative file=/oracle/export/db_cumu$rq.dmp" 

增量备份的SH文件: exp_incr.sh 

rq=` date +"%m%d" ` 

su - oracle -c "exp system/manager full=y inctype=incremental file=/oracle/export/db_incr$rq.dmp" 

root用户crontab文件 
/var/spool/cron/crontabs/root增加以下内容 

0 2 1 * * /oracle/exp_comp.sh 

30 2 * * 0-5 /oracle/exp_incr.sh 

45 2 * * 6 /oracle/exp_cumu.sh 

当然这个时间表可以根据不同的需求来改变的,这只是一个例子。 


七、ORACLE 常用的SQL语法和数据对象 

一.数据控制语句 (DML) 部分 

1.INSERT  (往数据表里插入记录的语句) 

INSERT INTO 表名(字段名1, 字段名2, ……) VALUES ( 值1, 值2, ……); 
INSERT INTO 表名(字段名1, 字段名2, ……)  SELECT (字段名1, 字段名2, ……) FROM 另外的表名; 

字符串类型的字段值必须用单引号括起来, 例如: ’GOOD DAY’ 
如果字段值里包含单引号’ 需要进行字符串转换, 我们把它替换成两个单引号''. 
字符串类型的字段值超过定义的长度会出错, 最好在插入前进行长度校验. 

日期字段的字段值可以用当前数据库的系统时间SYSDATE, 精确到秒 
或者用字符串转换成日期型函数TO_DATE(‘2001-08-01’,’YYYY-MM-DD’) 
TO_DATE()还有很多种日期格式, 可以参看ORACLE DOC. 
年-月-日 小时:分钟:秒 的格式YYYY-MM-DD HH24:MI:SS 

INSERT时最大可操作的字符串长度小于等于4000个单字节, 如果要插入更长的字符串, 请考虑字段用CLOB类型, 
方法借用ORACLE里自带的DBMS_LOB程序包. 

INSERT时如果要用到从1开始自动增长的序列号, 应该先建立一个序列号 
CREATE SEQUENCE 序列号的名称 (最好是表名+序列号标记) INCREMENT BY 1  START  WITH  1 
MAXVALUE  99999  CYCLE  NOCACHE; 
其中最大的值按字段的长度来定, 如果定义的自动增长的序列号 NUMBER(6) , 最大值为999999 
INSERT 语句插入这个字段值为: 序列号的名称.NEXTVAL 

2.DELETE  (删除数据表里记录的语句) 

DELETE FROM表名 WHERE 条件; 

注意:删除记录并不能释放ORACLE里被占用的数据块表空间. 它只把那些被删除的数据块标成unused. 

如果确实要删除一个大表里的全部记录, 可以用 TRUNCATE 命令, 它可以释放占用的数据块表空间 
TRUNCATE TABLE 表名; 
此操作不可回退. 

3.UPDATE  (修改数据表里记录的语句) 

UPDATE表名 SET 字段名1=值1, 字段名2=值2, …… WHERE 条件; 

如果修改的值N没有赋值或定义时, 将把原来的记录内容清为NULL, 最好在修改前进行非空校验; 
值N超过定义的长度会出错, 最好在插入前进行长度校验.. 

注意事项: 
A.        以上SQL语句对表都加上了行级锁, 
        确认完成后, 必须加上事物处理结束的命令 COMMIT 才能正式生效, 
        否则改变不一定写入数据库里. 
        如果想撤回这些操作, 可以用命令 ROLLBACK 复原. 

B.        在运行INSERT, DELETE 和 UPDATE 语句前最好估算一下可能操作的记录范围, 
        应该把它限定在较小 (一万条记录) 范围内,. 否则ORACLE处理这个事物用到很大的回退段. 
        程序响应慢甚至失去响应. 如果记录数上十万以上这些操作, 可以把这些SQL语句分段分次完成, 
        其间加上COMMIT 确认事物处理. 
二.数据定义 (DDL) 部分 

1.CREATE (创建表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等) 

ORACLE常用的字段类型有 
CHAR                        固定长度的字符串 
VARCHAR2                可变长度的字符串 
NUMBER(M,N)                数字型M是位数总长度, N是小数的长度 
DATE                        日期类型 

创建表时要把较小的不为空的字段放在前面, 可能为空的字段放在后面 

创建表时可以用中文的字段名, 但最好还是用英文的字段名 

创建表时可以给字段加上默认值, 例如 DEFAULT SYSDATE 
这样每次插入和修改时, 不用程序操作这个字段都能得到动作的时间 

创建表时可以给字段加上约束条件 
例如 不允许重复 UNIQUE, 关键字 PRIMARY KEY 

2.ALTER        (改变表, 索引, 视图等) 

改变表的名称 
ALTER TABLE 表名1  TO 表名2; 

在表的后面增加一个字段 
ALTER TABLE表名 ADD 字段名 字段名描述; 

修改表里字段的定义描述 
ALTER TABLE表名 MODIFY字段名 字段名描述; 

给表里的字段加上约束条件 
ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY (字段名); 
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (字段名); 

把表放在或取出数据库的内存区 
ALTER TABLE 表名 CACHE; 
ALTER TABLE 表名 NOCACHE; 

3.DROP        (删除表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等) 

删除表和它所有的约束条件 
DROP TABLE 表名 CASCADE CONSTRAINTS; 

4.TRUNCATE (清空表里的所有记录, 保留表的结构) 

TRUNCATE 表名; 

三.查询语句 (SELECT) 部分 

SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE 条件; 

字段名可以带入函数 
  例如:  COUNT(*), MIN(字段名),  MAX(字段名),  AVG(字段名), DISTINCT(字段名), 
           TO_CHAR(DATE字段名,'YYYY-MM-DD HH24:MI:SS') 

NVL(EXPR1, EXPR2)函数 
解释: 
IF EXPR1=NULL 
                RETURN EXPR2 
ELSE 
                       RETURN EXPR1 

DECODE(AA﹐V1﹐R1﹐V2﹐R2....)函数 
解释: 
IF AA=V1 THEN RETURN R1 
IF AA=V2 THEN RETURN R2 
..… 
ELSE 
RETURN NULL 

LPAD(char1,n,char2)函数 
解释: 
字符char1按制定的位数n显示,不足的位数用char2字符串替换左边的空位 

字段名之间可以进行算术运算 
例如:  (字段名1*字段名1)/3 

查询语句可以嵌套 
例如: SELECT …… FROM 
(SELECT …… FROM表名1, [表名2, ……] WHERE 条件) WHERE 条件2; 

两个查询语句的结果可以做集合操作 
例如: 并集UNION(去掉重复记录), 并集UNION ALL(不去掉重复记录), 差集MINUS,  交集INTERSECT 

分组查询 
SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] GROUP BY字段名1 
[HAVING 条件] ; 

两个以上表之间的连接查询 

SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE 
                表名1.字段名 = 表名2. 字段名 [ AND ……] ; 

SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE 
                表名1.字段名 = 表名2. 字段名(+) [ AND ……] ; 

有(+)号的字段位置自动补空值 

查询结果集的排序操作, 默认的排序是升序ASC, 降序是DESC 

SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] 
ORDER BY字段名1, 字段名2 DESC; 

字符串模糊比较的方法 

INSTR(字段名, ‘字符串’)>0 
字段名 LIKE  ‘字符串%’  [‘%字符串%’] 

每个表都有一个隐含的字段ROWID, 它标记着记录的唯一性. 

四.ORACLE里常用的数据对象 (SCHEMA) 

1.索引 (INDEX) 

CREATE INDEX 索引名ON 表名 ( 字段1, [字段2, ……] ); 
ALTER INDEX 索引名 REBUILD; 

一个表的索引最好不要超过三个 (特殊的大表除外), 最好用单字段索引, 结合SQL语句的分析执行情况, 
也可以建立多字段的组合索引和基于函数的索引 

ORACLE8.1.7字符串可以索引的最大长度为1578 单字节 
ORACLE8.0.6字符串可以索引的最大长度为758 单字节 

2.视图 (VIEW) 

CREATE VIEW 视图名AS SELECT …. FROM …..; 
ALTER VIEW视图名 COMPILE; 

视图仅是一个SQL查询语句, 它可以把表之间复杂的关系简洁化. 

3.同义词 (SYNONMY) 
CREATE SYNONYM同义词名FOR 表名; 
CREATE SYNONYM同义词名FOR 表名@数据库链接名; 

4.数据库链接 (DATABASE LINK) 
CREATE DATABASE LINK数据库链接名CONNECT TO 用户名 IDENTIFIED BY 密码 USING ‘数据库连接字符串’; 

数据库连接字符串可以用NET8 EASY CONFIG或者直接修改TNSNAMES.ORA里定义. 

数据库参数global_name=true时要求数据库链接名称跟远端数据库名称一样 

数据库全局名称可以用以下命令查出 
SELECT * FROM GLOBAL_NAME; 

查询远端数据库里的表 
SELECT …… FROM 表名@数据库链接名; 

五.权限管理 (DCL) 语句 

1.GRANT        赋于权限 
常用的系统权限集合有以下三个: 
CONNECT(基本的连接), RESOURCE(程序开发), DBA(数据库管理) 
常用的数据对象权限有以下五个: 
ALL         ON 数据对象名,         SELECT ON 数据对象名,         UPDATE ON 数据对象名, 
DELETE         ON 数据对象名,  INSERT ON 数据对象名,   ALTER  ON 数据对象名 

GRANT CONNECT, RESOURCE TO 用户名; 
GRANT SELECT ON 表名 TO 用户名; 
GRANT SELECT, INSERT, DELETE ON表名 TO 用户名1, 用户名2; 

2.REVOKE 回收权限 

REVOKE CONNECT, RESOURCE FROM 用户名; 
REVOKE SELECT ON 表名 FROM 用户名; 
REVOKE SELECT, INSERT, DELETE ON表名 FROM 用户名1, 用户名2; 


查询数据库中第63号错误: 
select orgaddr,destaddr from sm_histable0116 where error_code='63'; 

查询数据库中开户用户最大提交和最大下发数: select MSISDN,TCOS,OCOS from ms_usertable; 


查询数据库中各种错误代码的总和: 
select error_code,count(*) from sm_histable0513 group by error_code order 
by error_code; 

查询报表数据库中话单统计种类查询。 
select sum(Successcount) from tbl_MiddleMt0411 where ServiceType2=111 
select sum(successcount),servicetype from tbl_middlemt0411 group by servicetype