Oracle 11g introduces Case-sensitive passwords for database authentication. Along with this if you wish to change the password (temporarily) and reset it back to old , you will find that password field in dba_users is empty. Prior to 11g we could use following technique to change/restore password
SQL> alter user amit identified by abc;
User altered.
SQL> conn amit/abc
Connected.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter user LIU identified by values'9DEC0D889E8E9A6B';
User altered.
SQL> conn liu/liu
Connected.
In 11g if you query password field, itwill return NULL.
SQL> select username,password fromdba_users where username='LIU';
USERNAME PASSWORD
------------------------------------------------------------
LIU
Let’s first see Case-sensitive passwordfeature in 11g and then steps to change/restore passwords
SYS@orcl>create user LIU IDENTIFIED BYLIU;
用户已创建。
SYS@orcl>GRANT CONNECT TO LIU;
授权成功。
SYS@orcl>conn liu/liu
ERROR:
ORA-01017: invalid username/password;logon denied
警告:您不再连接到 ORACLE。
@>CONN LIU/LIU
已连接。
LIU@orcl>
This behavior is controlled by“sec_case_sensitive_logon”initialization paramter. If the value is true then it will enforce casesensitive passwords
一、Undo表空间和回滚段
１、Undo段的主要作用
（１）事务回滚
（２）事务恢复(实例恢复，利用回滚来恢复未提交的数据)
（３）读一致性(构造CR)
（４）闪回查询
２、查与undo相关的参数：
sys@OCM> show parameter undo
NAME TYPE VALUE
----------------------------------------------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
从上面可以看出，UNDOTBS1就是当前使用的undo表空间。
３、Undo段:段头、回滚块
gyj@OCM> select * from v$rollname;
USN NAME
---------- ------------------------------
0 SYSTEM
1 _SYSSMU1_592353410$
2 _SYSSMU2_967517682$
3 _SYSSMU3_1204390606$
4 _SYSSMU4_1003442803$
5 _SYSSMU5_538557934$
6 _SYSSMU6_2897970769$
7 _SYSSMU7_3517345427$
8 _SYSSMU8_3901294357$
9 _SYSSMU9_1735643689$
10 _SYSSMU10_4131489474$
gyj@OCM>select blocks,extents from dba_segments wheresegment_name='_SYSSMU10_4131489474$';
BLOCKS EXTENTS
---------- ----------
400 5
gyj@OCM>select tablespace_name,extent_id,file_id,block_id,blocks from dba_extents whereSEGMENT_NAME='_SYSSMU10_4131489474$';
TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
------------------------------ -------------------- ---------- ----------
UNDOTBS1 0 3 272 8
UNDOTBS1 1 3 384 8
UNDOTBS1 2 3 2816 128
UNDOTBS1 3 3 512 128
UNDOTBS1 4 3 2304 128
gyj@OCM> select owner, segment_name, tablespace_name from dba_rollback_segs;
OWNER SEGMENT_NAME TABLESPACE_NAME
------ ------------------------------------------------------------
SYS SYSTEM SYSTEM
PUBLIC _SYSSMU10_4131489474$ UNDOTBS1
PUBLIC _SYSSMU9_1735643689$ UNDOTBS1
PUBLIC _SYSSMU8_3901294357$ UNDOTBS1
PUBLIC _SYSSMU7_3517345427$ UNDOTBS1
PUBLIC _SYSSMU6_2897970769$ UNDOTBS1
PUBLIC _SYSSMU5_538557934$ UNDOTBS1
PUBLIC _SYSSMU4_1003442803$ UNDOTBS1
PUBLIC _SYSSMU3_1204390606$ UNDOTBS1
PUBLIC _SYSSMU2_967517682$ UNDOTBS1
PUBLIC _SYSSMU1_592353410$ UNDOTBS1
４、Undo段中区的状态
（１）active
（２）unexpired
（３）expired
（４）free
gyj@OCM> select * from t1;
ID NAME
---------- ----------
1 AAAAA
2 BBBBB
3 CCCCC
4 DDDDD
5 EEEEE
gyj@OCM> update t1 set name='eeeee'where id=5;
1 row updated.
这时先不要提交，然后通过活动的事务查到正在使用的回滚段。
sys@OCM> selectXIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBASQN,UBAREC,STATUS from v$transaction;
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS
---------- ---------- ---------- ---------- ---------- ---------- -------- ----------
8 18 4308 3 2086 316 2 ACTIVE
sys@OCM> select * from v$rollname whereusn=8;
USN NAME
---------- ------------------------------
8 _SYSSMU8_3901294357$
sys@OCM>select extent_id, bytes, status from dba_undo_extents wheresegment_name='_SYSSMU8_3901294357$';
EXTENT_ID BYTES STATUS
---------- ---------- ---------
0 65536 EXPIRED
1 65536 EXPIRED
2 1048576 ACTIVE
gyj@OCM> update t1 set name='eeeee'where id=5;
1 row updated.
gyj@OCM> commit;
Commit complete.
提交操作，使2号区状态由ACTIVE变为UNEXPIRED
sys@OCM> select extent_id, bytes, statusfrom dba_undo_extents where segment_name='_SYSSMU8_3901294357$';
EXTENT_ID BYTES STATUS
---------- ---------- ---------
0 65536 EXPIRED
1 65536 EXPIRED
2 1048576 UNEXPIRED
至少一个参数没有被指定值。
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbException: 至少一个参数没有被指定值。
总结：
这种情况的出现主要是我们写的SQL语句与数据库字段对应不起来或表中没有该字段引起：
select * from mytable where a='1' and b='1'
但你的mytable没有a字段
解决此问题的方法就是认真的对照SQL语句和数据库表是否对应……
本文链接