oracle11g 修改字符集 修改为ZHS16GBK

admin 发表了文章 • 0 个评论 • 115 次浏览 • 2018-04-29 08:48 • 来自相关话题

1.cmd下,cd到oracle数据库软件的服务器端 如:D:\app\Administrator\product\11.2.0\dbhome_1\BIN

2.输入set ORACLE_SID=你想进入的数据库的那个sid

3.输入 sqlplus /nolog

4.将数据库启动到RESTRICTED模式下做字符集更改: 

SQL> conn /as sysdba 

Connected. 

SQL> shutdown immediate; 

Database closed. 

Database dismounted. 

ORACLE instance shut down. 

SQL> startup mount 

ORACLE instance started. 

Total System Global Area  236000356 bytes 

Fixed Size                   451684 bytes 

Variable Size             201326592 bytes 

Database Buffers           33554432 bytes 

Redo Buffers                 667648 bytes 

Database mounted. 

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION; 

System altered. 

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; 

System altered. 

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0; 

System altered. 

SQL> alter database open; 

Database altered. 

SQL> ALTER DATABASE CHARACTER SET ZHS16GBK; 

ALTER DATABASE CHARACTER SET ZHS16GBK 



ERROR at line 1: 

ORA-12712: new character set must be a superset of old character set 

 

提示我们的字符集:新字符集必须为旧字符集的超集,这时我们可以跳过超集的检查做更改: 

SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK; 

Database altered. 

SQL> select * from v$nls_parameters; 

略 

19 rows selected. 

重启检查是否更改完成: 

SQL> shutdown immediate; 

Database closed. 

Database dismounted. 

ORACLE instance shut down. 

SQL> startup 

ORACLE instance started. 

Total System Global Area  236000356 bytes 

Fixed Size                   451684 bytes 

Variable Size             201326592 bytes 

Database Buffers           33554432 bytes 

Redo Buffers                 667648 bytes 

Database mounted. 

Database opened. 

SQL> select * from v$nls_parameters; 

alter system disable restricted session;    查看全部

1.cmd下,cd到oracle数据库软件的服务器端 如:D:\app\Administrator\product\11.2.0\dbhome_1\BIN

2.输入set ORACLE_SID=你想进入的数据库的那个sid

3.输入 sqlplus /nolog

4.将数据库启动到RESTRICTED模式下做字符集更改: 

SQL> conn /as sysdba 

Connected. 

SQL> shutdown immediate; 

Database closed. 

Database dismounted. 

ORACLE instance shut down. 

SQL> startup mount 

ORACLE instance started. 

Total System Global Area  236000356 bytes 

Fixed Size                   451684 bytes 

Variable Size             201326592 bytes 

Database Buffers           33554432 bytes 

Redo Buffers                 667648 bytes 

Database mounted. 

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION; 

System altered. 

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; 

System altered. 

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0; 

System altered. 

SQL> alter database open; 

Database altered. 

SQL> ALTER DATABASE CHARACTER SET ZHS16GBK; 

ALTER DATABASE CHARACTER SET ZHS16GBK 



ERROR at line 1: 

ORA-12712: new character set must be a superset of old character set 

 

提示我们的字符集:新字符集必须为旧字符集的超集,这时我们可以跳过超集的检查做更改: 

SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK; 

Database altered. 

SQL> select * from v$nls_parameters; 

略 

19 rows selected. 

重启检查是否更改完成: 

SQL> shutdown immediate; 

Database closed. 

Database dismounted. 

ORACLE instance shut down. 

SQL> startup 

ORACLE instance started. 

Total System Global Area  236000356 bytes 

Fixed Size                   451684 bytes 

Variable Size             201326592 bytes 

Database Buffers           33554432 bytes 

Redo Buffers                 667648 bytes 

Database mounted. 

Database opened. 

SQL> select * from v$nls_parameters; 

alter system disable restricted session;   

安装sql之前怎么删除挂起

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

1)添加/删除程序中彻底删除sql server。 2)将没有删除的sql server目录也删除掉。 3)打开注册表编辑器,在HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager中找到PendingFileRenameOperations项目,并删除它。这样就可以清除安装暂挂项目。 4)删除注册表中跟sql server相关的键。 ? ? 将以下两个从注册表中删除,一定可以解决SQL挂起问题: 一、开始\运行\输入regedit回车 +HKEY_LOCAL_MACHINE +SYSTEM +CurrentControlSet +Control +Session Manager下的FileRenameOperations删掉 二、+HKEY_LOCAL_MACHINE +SOFTWARE +Microsoft +Windows +CurrentVersion +Setup +ExceptionComponents下的东西全部删掉如: {077ACEC7-979C-40AB-9835-435BA1511E0D} {30C7234B-6482-4A55-A11D-ECD9030313F2} {3FDF25EE-E592-4495-8391-6E9C504DAC2B} {60204BB3-7078-4F70-8F69-68297621941C}...... 总之有几项,就删几项,删掉后重启,再装SQL Server ? ? 3、清除注册表中有关的信息: 进入注册表的命令是:regedit 将HKEY_CURRENT_USER-->Software-->Microsoft-->Microsoft SQL Server删除 还有:HKEY_LOCAL_MACHINE-->Software-->Microsoft-->Microsoft SQL Server删除 4、到安装SQL Server 2005的目录下将整个文件夹删除。C:\Program Files\Microsoft SQL Server 最后重启一下就可以了。 这个比较彻底,至少我重装数据库就是这样做的……
  查看全部

1)添加/删除程序中彻底删除sql server。 2)将没有删除的sql server目录也删除掉。 3)打开注册表编辑器,在HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager中找到PendingFileRenameOperations项目,并删除它。这样就可以清除安装暂挂项目。 4)删除注册表中跟sql server相关的键。 ? ? 将以下两个从注册表中删除,一定可以解决SQL挂起问题: 一、开始\运行\输入regedit回车 +HKEY_LOCAL_MACHINE +SYSTEM +CurrentControlSet +Control +Session Manager下的FileRenameOperations删掉 二、+HKEY_LOCAL_MACHINE +SOFTWARE +Microsoft +Windows +CurrentVersion +Setup +ExceptionComponents下的东西全部删掉如: {077ACEC7-979C-40AB-9835-435BA1511E0D} {30C7234B-6482-4A55-A11D-ECD9030313F2} {3FDF25EE-E592-4495-8391-6E9C504DAC2B} {60204BB3-7078-4F70-8F69-68297621941C}...... 总之有几项,就删几项,删掉后重启,再装SQL Server ? ? 3、清除注册表中有关的信息: 进入注册表的命令是:regedit 将HKEY_CURRENT_USER-->Software-->Microsoft-->Microsoft SQL Server删除 还有:HKEY_LOCAL_MACHINE-->Software-->Microsoft-->Microsoft SQL Server删除 4、到安装SQL Server 2005的目录下将整个文件夹删除。C:\Program Files\Microsoft SQL Server 最后重启一下就可以了。 这个比较彻底,至少我重装数据库就是这样做的……
 

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

admin 发表了文章 • 0 个评论 • 123 次浏览 • 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 个评论 • 104 次浏览 • 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 个评论 • 120 次浏览 • 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 个评论 • 119 次浏览 • 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 个评论 • 125 次浏览 • 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 个评论 • 110 次浏览 • 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 个评论 • 124 次浏览 • 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 个评论 • 131 次浏览 • 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就可以了。