Troubleshooting Guide (TSG) - ORA-01552: Cannot Use System Rollback Segment for Non-System Tablespace (Doc ID 1579215.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 24-Aug-2017***
PURPOSE
The Purpose of this document is to have a checklist for troubleshooting ORA-01552 error when using Manual Undo Management .
本文档的目的是提供一个清单，用于排查使用"Manual Undo Management"时出现的ORA-01552错误
ORA-01552: cannot use system rollback segment for non-system tablespace
This Error can only be Raised if UNDO_MANAGEMENT=manual , while executing any DML or DDL statements for tablespaces other than the SYSTEM tablespace.
仅当UNDO_MANAGEMENT=manual时，对除SYSTEM表空间以外的表空间执行任何DML或DDL语句时，才会引发此错误
TROUBLESHOOTING STEPS
Most Frequent Causes 最常见的原因
1- having only one rollback segment online 只有一个online回滚段
The Most Frequent Cause is having only one online rollback segment exists in the database which is the SYSTEM rollback segment , Which can be Confirmed from the following :
最常见的原因是数据库中只有一个online回滚段，即SYSTEM回滚段，可以从以下内容中进行确认
SQL> select segment_name, status,tablespace_name from dba_rollback_segs; SEGMENT_NAME STATUS TABLESPACE_NAME ------------------------------ ---------------- ------------------------------ SYSTEM ONLINE SYSTEM _SYSSMU11_508543045$ OFFLINE UNDOTBS2 _SYSSMU12_1326925045$ OFFLINE UNDOTBS2 _SYSSMU13_882757287$ OFFLINE UNDOTBS2 _SYSSMU14_3664063346$ OFFLINE UNDOTBS2 _SYSSMU15_1230784178$ OFFLINE UNDOTBS2 _SYSSMU16_3220246718$ OFFLINE UNDOTBS2 _SYSSMU17_3340471171$ OFFLINE UNDOTBS2 _SYSSMU18_1158896232$ OFFLINE UNDOTBS2 _SYSSMU19_1570777968$ OFFLINE UNDOTBS2 _SYSSMU20_226770654$ OFFLINE UNDOTBS2
In This Case The issue can be fixed by one of the following solutions : 在这种情况下，可以通过以下解决方法之一解决此问题
1) Switch to Automatic Undo Management 切换到自动Undo管理
1- SQL> alter system set undo_management=auto scope=spfile; System altered. 2- Restart the database. SQL> Shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup
or
2) Create new Rollback segment 创建新的回滚段
SQL> Create Rollback segment <RBS_name> Tablespace <TBS_name> ;
Note : Creating rollback segments is allowed in Tablespaces with manual segment space management other wise error ORA-30574 will be Raised .
注意：在具有手动段空间管理的表空间中允许创建回滚段，否则将引发ORA-30574错误
ORA-30574: Cannot create rollback segment in tablespace with AUTO segment space management
Tablespaces Applicable for creating new rollback segments can be Identified using :
可以使用以下方式标识适用于创建新回滚段的表空间
select TABLESPACE_NAME, CONTENTS from dba_tablespaces where SEGMENT_SPACE_MANAGEMENT = 'MANUAL' and CONTENTS <> 'TEMPORARY' and TABLESPACE_NAME <> 'SYSTEM';
or
3) online existing Rollback segment online现有的回滚段
SQL> alter rollback segment "RBS_name" online;
Note : this command may rise error ORA-30017: segment 'RBS_name' is not supported in MANUAL Undo Management mode
注意：此命令可能会出现错误ORA-30017：在 MANUAL Undo Management 模式下不支持段 'RBS_name'
which is expected to occur if this rollback segment were previously created in AUTO Undo Management mode . 如果先前在AUTO Undo Management模式下创建了该回退段，则可能会发生此错误
2- using standby Database opened in read only 使用备用数据库以只读方式打开
The Other Frequent Cause for Error ORA-01552 is Trying to Execute DML or DDL statements while being connected to standby Database opened in read only mode ,
错误的另一个常见原因，ORA-01552是在连接到以只读模式打开的备用数据库时尝试执行DML或DDL语句
The read only mode prevents the Use of DML or DDL statements , only Querying the Data are allowed . 只读模式阻止使用DML或DDL语句，仅允许查询数据
This can be confirmed using : 可以使用以下方法确认
SQL> select NAME,DATABASE_ROLE,OPEN_MODE from v$database;
Note : select for update is DML statement which is not allowed in the read-only database.
注意：select for update是DML语句，在只读数据库中不允许The Solution in This Case is to Open the standby in the read-write mode , or Do Not use DML or DDL statements in standby Database in read only mode.
在这种情况下，解决方案是以读写模式打开备用数据库，或者以只读模式在备用数据库中不使用DML或DDL语句
3- User Defined Triggers 用户定义的触发器
User Defined Triggers Are usually used for Auditing or other certain purpose , which are implemented by the Application or the DBA ,
用户定义的触发器通常用于审核或其他特定目的，由应用程序或DBA实施
If The error are in the Form of : 如果错误的形式为
ORA-00604: error occurred at recursive SQL level 1 ORA-01552: cannot use system rollback segment for non-system tablespace '<Tablespace_Name>'
ORA-00604 is an indication that the root cause of the ORA-01552 is a recursive statement issued by by a trigger on the database or the application level .
ORA-00604 表示ORA-01552的根本原因是由数据库或应用程序级别上的触发器发出的递归语句
Also enabling errorstack trace for ORA-1552 will show that the current SQL statement is Different than the statement returns the ORA-01552 when Run , for example :
同时为 ORA-1552 启用错误堆栈跟踪将显示当前SQL语句不同于Run时该语句返回ORA-01552，例如
SQL> CREATE ROLLBACK SEGMENT rbs_upgrade TABLESPACE UNDO STORAGE ( INITIAL 100M NEXT 100M MAXEXTENTS UNLIMITED ); CREATE ROLLBACK SEGMENT rbs_upgrade * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'
While enabling errorstack trace for ORA-1552 shows : 为ORA-1552启用错误堆栈跟踪时，显示
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS' Current SQL statement for this session: INSERT INTO KHANS.AUDIT_INFO VALUES (USER, SYSDATE, :B3 ,ORA_SYSEVENT,ORA_DICT_OBJ_OWNER,ORA_DICT_OBJ_NAME,:B2 ,:B1 )
This statement is the action of the Trigger when it fires. 该语句是触发器触发时的动作
Also the Call stack will contain one of the following functions which indicates the use of Triggers :
而且，调用堆栈将包含以下函数之一，这些函数指示触发器的使用
kktextrg kkttrex kktexeevt0 kktfrddltrg kkxtexe kxtex1
To Identify which Trigger is causing this You can use the following :
要确定引起该事件的触发器，您可以使用以下方法
The Column TRIGGER_BODY in DBA_TRIGGERS Table is LONG Datatype , which will result in error ORA-00932 when trying to use it in Like clause ,
DBA_TRIGGERS表中的TRIGGER_BODY列为LONG数据类型，当在Like子句中尝试使用它时，将导致错误ORA-00932
SQL> select OWNER,TRIGGER_NAME from DBA_TRIGGERS where STATUS='ENABLED' and TRIGGER_BODY like upper('%AUDIT_INFO%');
select OWNER,TRIGGER_NAME from DBA_TRIGGERS where STATUS='ENABLED' and TRIGGER_BODY like upper('%AUDIT_INFO%')
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG
Note : AUDIT_INFO is sample keyword from the ORA-01552 error stack Current SQL Statement .
注意：AUDIT_INFO是来自ORA-01552错误堆栈“当前SQL语句”的示例关键字
so We need first to create a function to be able to use this column in like clause :
因此，我们首先需要创建一个函数，以便能够在like子句中使用此列
create or replace function TRIG_BODY( p_owner in varchar2,p_trigger_name in varchar2) return varchar2 as l_cursor integer default dbms_sql.open_cursor; l_n number; l_long_val varchar2(4000); l_long_len number; l_buflen number := 4000; l_curpos number := 0; begin dbms_sql.parse( l_cursor, 'select trigger_body from DBA_TRIGGERS where owner = :x and trigger_name=:y', dbms_sql.native ); dbms_sql.bind_variable( l_cursor, ':x', p_owner ); dbms_sql.bind_variable( l_cursor, ':y', p_trigger_name ); dbms_sql.define_column_long(l_cursor, 1); l_n := dbms_sql.execute(l_cursor); if (dbms_sql.fetch_rows(l_cursor)>0) then dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos , l_long_val, l_long_len ); end if; dbms_sql.close_cursor(l_cursor); return l_long_val; end TRIG_BODY; / create public synonym TRIG_BODY for TRIG_BODY; --为TRIG_BODY创建公用同义词TRIG_BODY grant execute on TRIG_BODY to public; Please execute this SQL to Identify which Trigger is causing this : --请执行此SQL以确定引起此情况的触发器 select owner,trigger_name,TRIGGER_TYPE,TRIGGERING_EVENT from DBA_TRIGGERS where TRIG_BODY(owner,trigger_name) like '%AUDIT_INFO%' and status='ENABLED';
Note : AUDIT_INFO is sample keyword from the ORA-01552 error stack Current SQL Statement .
注意：AUDIT_INFO是来自ORA-01552错误堆栈“当前SQL语句”的示例关键字
The Trigger can be disabled by : 可以通过以下方式禁用触发器
SQL> alter trigger <OWNER>.<TRIGGER_NAME> disable;
4- hidden parameter "_rollback_segment_count" 隐含参数 "_rollback_segment_count"
The issue can also be caused from hidden parameter "_rollback_segment_count" ,
you can check the current value by : 您可以通过以下方式检查当前值
select a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value", a.KSPPDESC "Describtion" from x$ksppi a, x$ksppcv b, x$ksppsv c where a.indx = b.indx and a.indx = c.indx and a.ksppinm like '%_rollback_segment_count%';
You can see a similar following messages in the alert log file : 您可以在alert log file中看到类似的以下消息
SMON: number of AUM segments currently onlined: 0SMON: online AUM segments being raised from 0 to 20
Please remove the line for "_rollback_segment_count" from the initialization parameter ,
请从初始化参数中删除"_rollback_segment_count"
or execute :
SQL> alter system reset "_rollback_segment_count" ;
then restart the database . 然后重新启动数据库
Other Exceptional Cases where the ORA-01552 can occur 可能发生ORA-01552的其他特殊情况
There are some Other Exceptional Cases where the ORA-01552 can occur :
在ORA-01552可能发生的其他一些特殊情况下
1- While creating undo tablespace or creating Rollback segment 在创建undo tablespace或Rollback segment时
SQL> create undo tablespace undotbs2 datafile '/dev/vx/rdsk/test/undotbs1' size 3069m; * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01552: cannot use system rollback segment for non-system tablespace 'TESTING' ORA-06512: at line 2 SQL> create rollback segment r1 tablespace system; * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01552: cannot use system rollback segment for non-system tablespace TESTING' ORA-06512: at line 2
The original Undo tablespace was offline dropped and the database was open after a fake recovery , also the CDC (Change Data Capture) trigger is enabled.
原始的Undo tablespace已offline删除，并且在虚假恢复后打开了数据库，还启用了CDC（更改数据捕获）触发器
10046 trace or Error stack trace will show that the create undo tablespace is failing due to CDC trigger.
10046跟踪或错误堆栈跟踪将显示由于CDC触发器导致创建undo tablespace失败
The Solution is to Disable the CDC trigger and then create the undo tablespace .
解决方案是禁用CDC触发器，然后创建undo tablespace
To disable the CDC trigger follow the below steps : 要禁用CDC触发器，请执行以下步骤
Set the following parameter in the initialization parameter file then restart the database : --在初始化参数文件中设置以下参数，然后重新启动数据库 "_system_trig_enabled"=false SQL> conn / as sysdba SQL> ALTER TRIGGER sys.cdc_alter_ctable_before DISABLE; SQL> ALTER TRIGGER sys.cdc_create_ctable_after DISABLE; SQL> ALTER TRIGGER sys.cdc_create_ctable_before DISABLE; SQL> ALTER TRIGGER sys.cdc_drop_ctable_before DISABLE;
Once the undo tablespace is created successfully CDC triggers can be enabled and the system should be restarted with "_system_trig_enabled"=true
成功创建undo tablespace后，就可以启用CDC触发器，并且应该使用"_system_trig_enabled"=true重新启动系统
2- Running Oracle8 Migration Utility 运行Oracle8迁移实用程序
The following error occurs when trying to run the Oracle8 Migration Utility: 尝试运行Oracle8 Migration Utility时发生以下错误
ORA-00604: error occurred at recursive SQL level 1 ORA-01552: cannot use system rollback segment for non-system tablespace 'TOOLS' ORA-02002: error while writing to audit trail
It is also possible that the ORA-2002 may not be present, however, it is the same problem.
也可能不存在ORA-2002，但这是相同的问题
The errors occur when the "mig" executable attempts to run the "#^connect (migrate/migrate)" callout in "$ORACLE_HOME/rdbms/admin/migrate.bsq" file.
当"mig"可执行文件尝试运行"$ORACLE_HOME/rdbms/admin/migrate.bsq"文件中的 "#^connect (migrate/migrate)"标注时，将发生错误
The errors occur only when the initialization parameter AUDIT_TRAIL=DB or if AUDIT_TRAIL=TRUE, and the SYS.AUD$ table (the database audit trail) is located in a tablespace other than SYSTEM.
仅当初始化参数AUDIT_TRAIL = DB或AUDIT_TRAIL = TRUE且SYS.AUD$表（数据库审核跟踪）位于SYSTEM以外的表空间中时，才会发生错误。
The Migration Utility takes all non-SYSTEM rollback segments OFFLINE when it runs but in order to update a table outside the SYSTEM tablespace, a non-SYSTEM rollback segment must be used.
迁移实用程序在运行时使所有non-SYSTEM rollback segments OFFLINE，但是为了更新SYSTEM表空间之外的表，必须使用non-SYSTEM rollback segment
NOTE: Moving the "SYS.AUD"$ table to a non-SYSTEM tablespace is NOT supported.
注意：不支持将 "SYS.AUD"$表移动到非SYSTEM表空间
Temporarily disabling auditing will prevent attempted updates to the "SYS.AUD$" table.
暂时禁用审核将防止尝试对"SYS.AUD$"表进行更新。
You need to temporarily disable database auditing by doing the following:
您需要通过执行以下操作来临时禁用数据库审核
1. Shutdown the database. When the Migration Utility fails, it leaves the database running.
1. 关闭数据库。当迁移实用程序失败时，它将使数据库保持运行状态
2. Edit the "init.ora file" by setting AUDIT_TRAIL=NONE.
2. 编辑 "init.ora file"设置AUDIT_TRAIL = NONE
3. Rerun the Migration Utility.
3. 重新运行迁移实用程序
3- While Login to Read-Only Standby Database using Oracle Application (EBS) 使用Oracle应用程序（EBS）登录到只读备用数据库时
ORA-01552: cannot use system rollback segment for non-system tablespace 'APPLSYSD'
While SQLPLUS connection to read-only standby database are working fine without any issue.
虽然SQLPLUS与只读备用数据库的连接正常，没有显示任何问题
Enabling errorstack trace for ORA-1552 shows : 为ORA-1552启用错误堆栈跟踪显示
ksedmp: internal or fatal error ORA-01552: cannot use system rollback segment for non-system tablespace 'APPLSYSD' Current SQL statement for this session: SELECT SESSION_NUMBER FROM FND_USER WHERE USER_ID = :B1 FOR UPDATE OF SESSION_NUMBER, LAST_LOGON_DATE
Note : select for update is DML statement which is not allowed in the read-only database.
注意：select for update是DML语句，在只读数据库中不允许
This seems to be recursive statement by a trigger on the database or the application level .
这似乎是数据库或应用程序级别上的触发器的递归语句
The issue can be fixed by one of the following solutions :
可以通过以下解决方法之一解决此问题
1-Change the application code if you can, This is not possible every time as code is not available in some cases.
1-如果可以，请更改应用程序代码，由于某些情况下代码不可用，因此每次都不可能
OR
2-Open the standby in the read-write mode 以读写模式打开备用服务器
OR
3-Do not try connect from application when standby is in read-only mode.
3-当备用计算机处于只读模式时，请勿尝试从应用程序连接
4- Using Clone Database 使用克隆数据库
If this is a clone database then this will happen when attempting any data modification outside of the system tablespace. Only the system rollback segment can be online in a clone database.
如果这是一个克隆数据库，则在系统表空间之外尝试任何数据修改时都会发生这种情况。在克隆数据库中，只有系统回滚段可以在线
If this cloned database is being used for tablespace point in time recovery then this operation is not allowed.
如果将此克隆数据库用于表空间时间点恢复，则不允许执行此操作
If the non-system tablespace has AUTO segment space management, then create an undo tablespace.
如果非系统表空间具有AUTO段空间管理，则创建一个undo tablespace
5- While installing Oracle Communications ASAP
ORA-01552: cannot use system rollback segment for non-system tablespace 'ASAP_DATA'
This error occurs because when ASAP's Oracle database was created, all the non system rollback segments where offline.
发生此错误的原因是，在创建ASAP的Oracle数据库时，所有非系统回滚段都处于offline状态
Note : The ASAP installation will have to be redone. 注意：必须重新执行ASAP安装
These errors are found in the ASAP installation file CreateDatabase.log:这些错误可在ASAP安装文件CreateDatabase.log中找到
'Loading tables/IDENTITY.i' CHECK (opt5_type = null or opt5_type like '-_'), * ERROR at line 18: 'Loading tables/alrm_cent.i' CREATE TABLE tbl_alarm_log ( * ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'ASAP_DATA'
In This Case The issue can be fixed by one of the following solutions , in this Link .
在这种情况下，可以通过上文中'1- having only one rollback segment online'的解决方案之一解决此问题
6- Bulk Load Catalog Items in Oracle Exchange Oracle Exchange中的批量装入目录项
Bulk load catalog item job ends with failed status 批量装入目录项目作业以失败状态结束
ORA-01552: cannot use system rollback segment for non-system tablespace 'USER_DATA' LDR-00100: Database Error <ERROR> SYS:LDR-00100:Database Error java.sql.SQLException: ORA-01552: cannot use system rollback segment for non- system tablespace 'USER_DATA' at java.lang.Throwable.<init>(Compiled Code) at java.lang.Exception.<init>(Compiled Code) at java.sql.SQLException.<init>(Compiled Code) at oracle.jdbc.dbaccess.DBError.throwSqlException(Compiled Code) at oracle.apps.pom.loader.LoaderApplication.main(Compiled Code)
The Cause is having only one online rollback segment exists in the database which is the SYSTEM rollback segment
原因是数据库中只有一个在线回滚段，即SYSTEM回滚段
In This Case The issue can be fixed by one of the following solutions , in this Link .
在这种情况下，可以通过上文中'1- having only one rollback segment online'的解决方案之一解决此问题
7- Creating DBConsole with EMCA Fails With Error ORA-01552 使用EMCA创建DBConsole失败，错误为ORA-01552
While creating DBConsole via Enterprise Manager Configuration Assistant (EMCA), the repository creation fails with the below errors in emca_repos_config_timestamp.log
通过企业管理器配置助手（EMCA）创建DBConsole时，存储库创建失败，并在emca_repos_config_timestamp.log中显示以下错误
ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP' ORA-06512: at "SYS.DBMS_STATS", line 13591 ORA-06512: at "SYS.DBMS_STATS", line 13898 ORA-06512: at "SYS.DBMS_STATS", line 16240 ORA-06512: at "SYS.DBMS_STATS", line 16282 ORA-06512: at "SYS.DBMS_REGISTRY_SYS", line 953 ORA-06512: at "SYS.DBMS_REGISTRY", line 561
The Cause is having only one online rollback segment exists in the database which is the SYSTEM rollback segment
原因是数据库中只有一个在线回滚段，即SYSTEM回滚段
In This Case The issue can be fixed by one of the following solutions , in this Link .
在这种情况下，可以通过上文中'1- having only one rollback segment online'的解决方案之一解决此问题
Then Recreate the DBConsole using command. 然后使用命令重新创建DBConsole
<ORACLE_HOME>bin/emca -config dbcontrol db -repos create
Refer Note 278100.1 How To Drop, Create And Recreate DB Control In A 10g Database
参考 Note 278100.1 如何在10g数据库中删除，创建和重新创建DB控件
8- Error While Executing Catbundle Script 执行Catbundle脚本时出错
Running catbundle.sql script fails with below error: 运行catbundle.sql脚本失败，并出现以下错误
450 DBMS_OUTPUT.PUT_LINE('Exception - patch NOT installed');
451 DBMS_OUTPUT.PUT_LINE(SQLERRM);
452 :scriptFile := :rdbmsAdminDir || 'nothing.sql'; -- We need to run something
453 END;
454 /
Current bundle installed: (0) - None
Starting bundle ID: 1
Processing components and files for bundle 1: CPUJan2012
Processing components and files for bundle 2: CPUApr2012
Processing components and files for bundle 3: CPUJul2012
Processing components and files for bundle 4: CPUOct2012
Exception - patch NOT installed
ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'
Entry has not been added in registry$history UNDO_MANAGEMENT should be set to AUTO as shown below:
尚未在registry$history中添加条目，UNDO_MANAGEMENT应该设置为AUTO，如下所示：
1. Set undo_management to AUTO.
SQL> alter system set undo_management=auto scope=spfile;
2. Bounce back the database.
SQL> startup force ;
3. Execute the catbundle script.
cd $ORACLE_HOME/rdbms/admin/ SQL> @catbundle.sql cpu apply
4. Verify registry$history and catbundle log from $ORACLE_HOME/cfgtoollogs/catbundle/catbundle_CPU_<dbname>_GENERATE_<timestamp>.log.
从catbundle日志 $ORACLE_HOME/cfgtoollogs/catbundle/catbundle_CPU_<dbname>_GENERATE_<timestamp>.log 验证registry$history
9- While logon to physical standby database 登录物理备用数据库时
The Following error occurs While logon to physical standby database in read only mode.
在只读模式下登录物理备用数据库时发生以下错误
ORA-604: error occurred at recursive SQL level 1 ORA-1552: cannot use system rollback segment for non-system tablespace
The issue is caused by Logon Trigger On Primary Database , which got created On the Physical Standby ,
此问题是由在主数据库上创建的主数据库上的登录触发器引起的
Following error occurs when logon trigger attempt to inert data in physical standby database .
当登录触发器尝试对物理备用数据库中的数据进行惰性化时，将发生以下错误
In This Case The issue can be fixed by one of the following solutions :
在这种情况下，可以通过以下解决方法之一解决此问题
1- Disable the LOGON Trigger on the standby database :
1-在备用数据库上禁用LOGON触发器
SQL> alter trigger <trigger_name> disable;
or
2- Rewrite the on-logon trigger by considering the database role so in case of physical standby logon trigger will not try execute any DML in database.
2- 通过考虑数据库角色来重写登录触发器，以便在物理备用登录触发器的情况下不会尝试在数据库中执行任何DML
CREATE OR REPLACE TRIGGER log_audit_sess_info
AFTER LOGON on database
declare
db_role varchar2(30);
begin
select database_role
into db_role
from v$database;
If db_role <> 'PHYSICAL STANDBY' then
insert into ora_audit.log_audit_sess_info
select sid,audsid, username,nvl(SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER'), SYS_CONTEXT('USERENV','OS_USER')) osuser,machine,
SYS_CONTEXT('USERENV','IP_ADDRESS') terminal,program,module,logon_time
from v$session
where sid in (SELECT VS.SID
FROM V$SESSION VS, V$PROCESS VP
WHERE VS.PADDR = VP.ADDR
AND VS.USERNAME IS NOT NULL
AND VS.STATUS = 'ACTIVE'
AND VS.SID IN (SELECT SID
FROM V$MYSTAT));
end if;
end;
/
10- When Starting The Database 启动数据库时
When trying to startup a database with a pending Transaction (2PC), startup will fail if index I_PENDING_TRANS1 for the PENDING_TRANS$ table is not located on the SYSTEM tablespace.
尝试使用挂起的事务（2PC）启动数据库时，如果SYSTEM表空间中未找到PENDING_TRANS$表的索引I_PENDING_TRANS1，则启动将失败
ORA-00604: error occurred at recursive SQL level 1 ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'
To verify that you have encountered this issue, add the following event to your init<SID>.ora file and try to startup the database once again.
要验证您是否遇到此问题，请将以下事件添加到init<SID>.ora文件中，然后尝试再次启动数据库
event="1552 trace name errorstack level 10"
This event will generate a trace file to your user_dump_dest directory after startup.
该事件将在启动后生成到您的user_dump_dest目录的跟踪文件
The failing SQL statement in the trace file with the ORA-1552 error should be:
具有ORA-1552错误的跟踪文件中失败的SQL语句应为
Current SQL statement for this session: insert into pending_trans$ (local_tran_id, global_tran_fmt, global_oracle_id, global_foreign_id, tran_comment, state, status, heuristic_dflt, session_vector, reco_vector, fail_time, reco_time, top_db_user, top_os_user, top_os_host, top_os_terminal, global_commit#, type#) values (:1, :2, :3, :4 :5, :6, 'P', :7, :8, :8, SYSDATE, SYSDATE, :9, :10, :11, :12,:13,:14)
Solution Description 解决方案说明
--------------------
In order to resolve the issue, the following steps are necessary.
为了解决该问题，必须执行以下步骤
1. Startup the DB with all rollback segments offline. 在所有回滚段都offline的情况下启动数据库
Please contact Oracle Support to get help with the syntax to accomplish this, since all rollback segments MUST BE offline for this to succeed.
请与Oracle Support人员联系以获得有关完成此操作的语法的帮助，因为所有回滚段都必须offline才能成功
2. Drop the index for PENDING_TRANS$.
SQL> drop index I_PENDING_TRANS1;
3. Recreate the index in the SYSTEM tablespace. 在SYSTEM表空间中重新创建索引
SQL> create unique index I_PENDING_TRANS1 on PENDING_TRANS$(local_tran_id) tablespace SYSTEM;
4. Shutdown the DB.
5. Startup the DB with all rollback segments available. 在所有回滚段可用的情况下启动数据库
When a distributed transaction is pending, Oracle inserts a record in the PENDING_TRANS$ table. This table does have one index, I_PENDING_TRANS1, and the index must be updated.
当分布式事务挂起时，Oracle在PENDING_TRANS$表中插入一条记录。该表确实具有一个索引I_PENDING_TRANS1，并且该索引必须更新。
This operation cannot be performed when opening the database if the index is not located on the SYSTEM tablespace because of the following.
如果由于以下原因索引未位于SYSTEM表空间上，则在打开数据库时无法执行此操作
a) Only the SYSTEM rollback segment is online. 仅SYSTEM回滚段处于联机状态
b) No operation can be performed on a non-SYSTEM tablespace using the SYSTEM rollback segment.无法使用SYSTEM回滚段对非SYSTEM表空间执行任何操作
The root cause of this problem is the fact of having index I_PENDING_TRANS1 in a tablespace other than SYSTEM.
此问题的根本原因是在除SYSTEM以外的表空间中具有索引I_PENDING_TRANS1的事实
REFERENCES
NOTE:1390501.1 - How to Use a LONG Column Datatype in 'LIKE' or 'ORDER BY' Clauses?
NOTE:278100.1 - How To Drop, Create And Recreate Database Control (dbconsole) Web Site in Releases 10g and 11g
NOTE:1923576.1 - Multiple SMON/AUM Related Messages In Alert Log File