分类目录归档：Oracle

Startup Migrate

发表于 2011 年 10 月 9 日由惜分飞

1、为什么要使用Startup Migrate
STARTUP MIGRATE was introduced in 9.2 as a mechanism to be sure that most everything that needs to be done to run an upgrade script or a patch script is done automatically. In the past, customers were expected to adjust certain initialization parameters prior to beginning an upgrade or applying a a patch, but most of this is now done automatically by STARTUP MIGRATE. When a customer starts a database in MIGRATE mode, the following ALTER SYSTEM commands will be set automatically:
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET “_SYSTEM_TRIG_ENABLED”=FALSE SCOPE=MEMORY;
ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=MEMORY;
ALTER SYSTEM SET undo_retention=900 SCOPE=MEMORY;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0 SCOPE=MEMORY;
ALTER SYSTEM SET AQ_TM_PROCESSES=0 SCOPE=MEMORY;
这些我们可以从数据库的Startup Migrate命令启动数据库的日志中可以看出来

Sun Oct  9 21:53:04 2011
ALTER SYSTEM enable restricted session;
Sun Oct  9 21:53:04 2011
ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;
Autotune of undo retention is turned off. 
Sun Oct  9 21:53:04 2011
ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=MEMORY;
Sun Oct  9 21:53:04 2011
ALTER SYSTEM SET undo_retention=900 SCOPE=MEMORY;
MMNL started with pid=12, OS id=8452
Sun Oct  9 21:53:04 2011
ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY;
Sun Oct  9 21:53:04 2011
Resource Manager disabled during database migration: plan '' not set
Sun Oct  9 21:53:04 2011
ALTER SYSTEM SET resource_manager_plan='' SCOPE=MEMORY;
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN MIGRATE

2、Startup Migrate主用作用
在9i，无论升级/降级数据库都是startup migrate
10g后增加了upgrade参数，升级可直接用startup upgrade，降级仍是startup migrate

3、Startup Migrate辅助作用（解决部分ORA-00701）

SQL> alter index I_H_OBJ#_COL# rebuild;
alter index I_H_OBJ#_COL# rebuild
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup migrate;
ORACLE instance started.
Total System Global Area 139531744 bytes
Fixed Size 452064 bytes
Variable Size 121634816 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> alter index I_H_OBJ#_COL# rebuild;

Index altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 139531744 bytes
Fixed Size 452064 bytes
Variable Size 121634816 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

发表在 Oracle | 评论关闭

sqlplus 使用小技巧

发表于 2011 年 10 月 6 日由惜分飞

1、sqlplus显示语言设置
export NLS_LANG=”AMERICAN_AMERICA.ZHS16GBK”
export NLS_LANG=”SIMPLIFIED CHINESE_CHINA.ZHS16GBK”
注意数据库编码：ZHS16GBK/UTF8

2、dbms_output.put_line显示最前面空格
SQL> set serveroutput on
SQL> exec dbms_output.put_line(‘ abc’);
abc

PL/SQL procedure successfully completed.

SQL> set serveroutput on format wrapped
SQL> exec dbms_output.put_line(‘ abc’);
abc

PL/SQL procedure successfully completed.

3、sqlplus 语句中间查询对象结构
SQL> select owner
2 # desc input
Name Null? Type
—————————————– ——– —————————-
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)

2 ,status from input where rownum<10;

no rows selected
下一行以#开头, 就可以执行一条sql*plus命令, 执行完后, 刚才的语句可以继续输入

4、Sql*plus中sql语句中间有空行
SQL> select owner
2
SQL> ,status from input where rownum<10; SP2-0734: unknown command beginning “,status fr…” – rest of line ignored. 原因是sqlplus遇到空行就认为是语句结束了. 其实要改变这种现象, 只要使用SQLBLANKLINES参数就可以了 SQL> SET SQLBLANKLINES ON
SQL> select owner
2
3 ,status from input where rownum<10;

no rows selected

发表在 Oracle | 评论关闭

ORA-01465: invalid hex number

发表于 2011 年 9 月 28 日由惜分飞

网友反馈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-xxxxx | 标签为 ORA-01465 | 评论关闭

17813235971

QQ 咨询

分类目录归档：Oracle

Startup Migrate

sqlplus 使用小技巧

ORA-01465: invalid hex number

文章分类

最近发表

分类目录归档：Oracle

Startup Migrate

sqlplus 使用小技巧

ORA-01465: invalid hex number

标签云

文章分类

最近发表