win平台登录sqlplus报ora-01031错误解决

今天一朋友告诉我,他的win平台的oracle不能在本地使用sqlplus / as sysdba登录数据库,提示ora-01031的错误,他说是administrator用户,应该不会出现权限不足的情况。我的登录上去一看,果真是这样的情况:

01031, 00000, "insufficient privileges"
// *Cause: An attempt was made to change the current username or password
//         without the appropriate privilege. This error also occurs if
//         attempting to install a database without the necessary operating
//         system privileges.
//         When Trusted Oracle is configure in DBMS MAC, this error may occur
//         if the user was granted the necessary privilege at a higher label
//         than the current login.
// *Action: Ask the database administrator to perform the operation or grant
//          the required privileges.
//          For Trusted Oracle users getting this error although granted the
//          the appropriate privilege at a higher label, ask the database
//          administrator to regrant the privilege at the appropriate label.
根据这个错误提示,我的第一反应就是当前的用户不属于ora_dba用户组,通过计算机管理–>本地用户和组–>administrator用户属性–>隶属于中只有一个administrators,果然没有ora_dba组

接下来的事情,就是添加ora_dba组到administrator用户中
点击刚刚隶属于下面的添加–>高级–>立即查找–选择ora_dba–点击确定–>再点击选择组中的确定–>点击用户属性的确定


添加把ora_dba添加到administrator用户所属组中,再尝试登录

ok,登录成功了,看来在win系统中,要想使用sqlplus / as sysdba 登录数据库,必须要隶属于ora_dba组,就算administrators组也不能越俎代庖。

ORA-00845: MEMORY_TARGET not supported on this system

在Oracle 11g中如果采用AMM内存管理,那么当MEMORY_TARGET的值大于/dev/shm的时候,就会报ORA-00845: MEMORY_TARGET not supported on this system错误,解决办法增加/dev/shm大小,在redhat系列系统中,/dev/shm的默认值是系统总内存的一半

1、错误重现

SQL>SELECT *  FROM V$VERSION;
 
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
PL/SQL Release 11.2.0.3.0 – Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 – Production
NLSRTL Version 11.2.0.3.0 – Production
 
SQL>show parameter memory;
 
NAME                                 TYPE        VALUE
———————————— ———– ——————————
hi_shared_memory_address             integer     0
memory_max_target                    big integer 500M
memory_target                        big integer 500M
shared_memory_address                integer     0
SQL>alter system set memory_max_target=800m;
alter system set memory_max_target=800m
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
 
 
SQL>alter system set memory_max_target=800m scope=spfile;
 
System altered.
 
SQL>alter system set memory_target=800m scope=spfile; 
 
System altered.
 
SQL>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@xifenfei admin]$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.3.0 Production on Sat Nov 5 19:01:18 2011
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
Connected to an idle instance.
 
SQL>startup
ORA-00845: MEMORY_TARGET not supported on this system
SQL>!oerr ora 845
00845, 00000, "MEMORY_TARGET not supported on this system"
// *Cause: The MEMORY_TARGET parameter was not supported on this operating system or /dev/shm was not sized correctly on Linux.
// *Action: Refer to documentation for a list of supported operating systems. Or, size /dev/shm to be at least the SGA_MAX_SIZE on each Oracle instance running on the system.

2、修改/dev/shm大小

[root@xifenfei ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_xifenfei-lv_root
                       17G   13G  3.9G  77% /
tmpfs                 590M     0  590M   0% /dev/shm
/dev/sda1             485M   30M  430M   7% /boot
[root@xifenfei ~]# mount -o size=900M -o nr_inodes=1000000 -o noatime,nodiratime -o remount /dev/shm
[root@xifenfei ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_xifenfei-lv_root
                       17G   13G  3.9G  77% /
tmpfs                 900M     0  900M   0% /dev/shm
/dev/sda1             485M   30M  430M   7% /boot
[root@xifenfei ~]# vi /etc/fstab 
 
 
#
# /etc/fstab
# Created by anaconda on Sat Nov  5 02:49:30 2011
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
/dev/mapper/vg_xifenfei-lv_root /                       ext4    defaults        1 1
UUID=7ace6c04-d232-43ac-9ef5-70ea92fe49bd /boot                   ext4    defaults        1 2
/dev/mapper/vg_xifenfei-lv_swap swap                    swap    defaults        0 0
tmpfs                   /dev/shm                tmpfs   defaults,size=900M        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0

3、启动数据库验证

[oracle@xifenfei admin]$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.3.0 Production on Sat Nov 5 19:03:51 2011
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
sys@XFF>show parameter memory;
 
NAME                                 TYPE        VALUE
———————————— ———– ——————————
hi_shared_memory_address             integer     0
memory_max_target                    big integer 800M
memory_target                        big integer 800M
shared_memory_address                integer     0

4、官方解释
Starting with Oracle Database 11g, the Automatic Memory Management feature requires more shared memory (/dev/shm)and file descriptors. The size of the shared memory should be at least the greater of MEMORY_MAX_TARGET and MEMORY_TARGET for each Oracle instance on the computer. If MEMORY_MAX_TARGET or MEMORY_TARGET is set to a non zero value, and an incorrect size is assigned to the shared memory, it will result in an ORA-00845 error at startup.

5、解决问题建议
5.1. If you are installing Oracle 11g on a Linux system, note that Memory Size (SGA and PGA), which sets the initialization parameter MEMORY_TARGET or MEMORY_MAX_TARGET, cannot be greater than the shared memory filesystem (/dev/shm) on your operating system. To resolve the current error, increase the /dev/shm file size.

5.2. If configuring AMM is not possible due to lack of space on /dev/shm mount point, you can configure ASMM instead of AMM, i.e. set SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET instead of MEMORY_TARGET.

SP2-1503 SP2-0152 错误解决

一、现场描述
服务器上有Oracle 10g环境变量分别为
Oracle 10g环境变量

export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_PATH=$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin
export ORACLE_SID=ecp
export NLS_LANG=AMERICAN_AMERICA.zhs16gbk
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
export ORACLE_TERM=xterm
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORA_NLS10=$ORACLE_HOME/nls/data
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export CLASSPATH=$ORACLE_HOME/JRE
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export THREADS_FLAG=native
export TEMP=/tmp
export TMPDIR=/tmp
export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin:$ORACLE_BASE:$ORACLE_HOME
export PATH=${PATH}:$ORACLE_HOME/bin:$ORA_CRS_HOME:$ORA_CRS_HOME/bin
export PATH=${PATH}:$LD_LIBRARY_PATH:$CLASSPATH:$ORACLE_PATH
 
现在在上面Oracle 11g,在shell中执行下面命令修改环境变量
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=ora11g
然后安装数据库,安装过程一切顺利,安装完成,当使用sqlplus的时候报如下错误:
[oracle@node1 tmp]$ sqlplus /nolog
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly
 
二、问题原因
[oracle@node1 ~]$ export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
[oracle@node1 ~]$ export ORACLE_SID=ora11g
[oracle@node1 ~]$ $ORACLE_HOME/bin/sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 31 17:04:17 2011
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
 
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
[oracle@node1 ~]$ sqlplus / as sysdba
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly
因为系统的环境变量中的PATH没有修改过来,直接使用sqlplus的时候,还是会调用Oracle 10g中的sqlplus,而此时ORACLE_HOME与其不匹配,导致出现上面错误
 
三、证明猜想,试验如下
[oracle@node1 ~]$ export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
[oracle@node1 ~]$  env|grep ORACLE
ORACLE_PATH=/opt/oracle/common/oracle/sql:.:/opt/oracle/product/10.2.0/db_1/rdbms/admin
ORACLE_SID=ecp
ORACLE_BASE=/opt/oracle
ORACLE_TERM=xterm
ORACLE_HOME=/opt/oracle/product/11.2.0/db_1
[oracle@node1 ~]$ sqlplus / as sysdba
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly
说明:修改ORACLE_HOME,指向Oracle 11g的目录,然后执行sqlplus(该程序属于10g),报同样错误

四、补充说明
1、如果在一台服务器上安装不同版本数据库,建议使用不用用户安装,这样可以减少很多管理上没必要的麻烦,提高工作效率
2、sqlplus程序需要和ORACLE_HOME向对应,不然就会出现SP2-1503   SP2-0152错误
3、本次排错中学习到知识,如果要执行一个shell文件修改环境变量,可以使用source
4、如果必须使用同一个用户安装,那么建议新安装的oracle版本建一个shell文件,然后使用source shell_filename,修改环境变量(特别注意PATH)

ORA-01465: invalid hex number

网友反馈blob插入一些字段报错,查询metalink,发现使用rawtohex处理即可,测试如下:

SQL> create table t_lob (t blob);

Table created.

SQL> insert into t_lob values('-------');
insert into t_lob values('-------')
                         *
ERROR at line 1:
ORA-01465: invalid hex number


SQL> insert into t_lob values(rawtohex('---------'));

1 row created.

SQL> insert into t_lob values('----------&');
insert into t_lob values('----------&')
                         *
ERROR at line 1:
ORA-01465: invalid hex number

SQL> insert into t_lob values(rawtohex('----------&'));

1 row created.

SQL> commit;

Commit complete.

网友提供java处理代码如下:

///插入
public void test()
	{
		conn=DBUtil.getActiveConnection();
		String sqlStr="Set define off";
		String ss="————";
		String sql="insert into test1(names,btestname) values('12',rawtohex('"+ss+"'))";
		System.out.println(sqlStr);
		System.out.println(sql);
		try {
			pstmt=conn.prepareStatement(sqlStr);
			pstmt.addBatch();  
			
			pstmt=conn.prepareStatement(sql);
			pstmt.addBatch();
			pstmt.executeBatch();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			DBUtil.closeStatement(pstmt);
			DBUtil.closeConnection(conn);
		}
	}

////查询语句
public void getTest()
	{
		conn=DBUtil.getActiveConnection();
		String sql="select * from test1 where names='12'";
		//oracle.sql.BLOB blob = null;
		InputStream inStream=null;
		
		try {
			pstmt=conn.prepareStatement(sql);
			set=pstmt.executeQuery();
			if(set!=null && set.next())
			{
				//接收blob类型
				java.sql.Blob blob = (oracle.sql.BLOB)set.getBlob("btestname");
				//注意
				inStream = blob.getBinaryStream();
		        if(blob!=null) System.out.println("有值============");
				try {
					byte[] data = new byte[200];
						
					 int length=0;//每次读取的实际字节长度
						//is.read()方法:从buff缓中区的第0个位开始读取字节,每次最多读取200,
						//方法会返回一个实际读取的长度,用length接收,当值为-1时,表示所有的字节全部读取完毕
					 while((length=inStream.read(data,0,200))!=-1)
					{
						//把字节以平台的默认编码方式转为字符,从buff的第0个位开始转换,实际要转换的长度是length
						String str=new String(data,0,length);	
						System.out.println("最终结果====  "+str+"  ====");
					}
	 
					//关闭流
					inStream.close();	
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

			}	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			DBUtil.closeResultSet(set);
			DBUtil.closeStatement(pstmt);
			DBUtil.closeConnection(conn);
		}
	}

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

truncate清空一张表记录发现ORA-02266,进行模拟测试

SQL> truncate table p;
truncate table p
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

--错误原因
SQL> !oerr ora 02266
02266, 00000, "unique/primary keys in table referenced by enabled foreign keys"
// *Cause: An attempt was made to truncate a table with unique or 
//         primary keys referenced by foreign keys enabled in another table.
//         Other operations not allowed are dropping/truncating a partition of a
//         partitioned table or an ALTER TABLE EXCHANGE PARTITION.
// *Action: Before performing the above operations the table, disable the 
//          foreign key constraints in other tables. You can see what 
//          constraints are referencing a table by issuing the following 
//          command:
//          SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";

--查询是否有主键或者唯一index
SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF 
SET LONG 999999    
SET PAGESIZE 1000
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);--不显示存储信息
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','P') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','P')
--------------------------------------------------------------------------------

  CREATE TABLE "CHF"."P"
   (    "X" NUMBER(*,0),
         PRIMARY KEY ("X")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  TABLESPACE "USERS"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS

--查询主外键关系
SQL> col 主键表名 for a20
SQL> col 主键列名 for a20
SQL> col 外键表名 for a20
SQL> col 外键列名  for a20
SQL> select b.table_name  主键表名,
       b.column_name 主键列名,
       a.table_name  外键表名,
       a.column_name 外键列名
  from (select a.constraint_name,
               b.table_name,
               b.column_name,
               a.r_constraint_name
          from dba_constraints a, dba_cons_columns b
         WHERE a.constraint_type = 'R'
           and a.constraint_name = b.constraint_name) a,
       (select distinct a.r_constraint_name, b.table_name, b.column_name
          from dba_constraints a, dba_cons_columns b
         WHERE a.constraint_type = 'R'
           and a.r_constraint_name = b.constraint_name) b
 where a.r_constraint_name = b.r_constraint_name
and b.table_name='P'; 

主键表名             主键列名             外键表名             外键列名
-------------------- -------------------- -------------------- --------------------
P                    X                    C                    X

1 row selected.

--处理方法一
SQL> alter table p disable primary key cascade;

Table altered.

SQL> truncate table p;

Table truncated.

SQL> alter table p enable primary key;

Table altered.

--处理方法二
SQL> delete from t;

1 row deleted.


SQL> commit;

Commit complete.