execute dbms_logmnr_d.set_tablespace与ORA-01950

在客户现场配置streams环境

以sys用户执行execute dbms_logmnr_d.set_tablespace时一直提示下面错误：

ERROR at line 1:
ORA-01950: no privileges on tablespace 'TBS_STREAMS'
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 233
ORA-06512: at line 1

仔细分析之后觉得不是sys用户缺少权限而是system缺少...

[@more@]

SQL> execute dbms_logmnr_d.set_tablespace('tbs_streams');
BEGIN dbms_logmnr_d.set_tablespace('tbs_streams'); END;

*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'TBS_STREAMS'
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 233
ORA-06512: at line 1

SQL> grant unlimited tablespace to system;

Grant succeeded.

SQL> execute dbms_logmnr_d.set_tablespace('tbs_streams');

PL/SQL procedure successfully completed.

SQL> show user
USER is "SYS"
SQL>
--=========================
SQL> exec dbms_logmnr_d.set_tablespace('tbs_streams');

PL/SQL 过程已成功完成。

--执行之后查询表空间'tbs_streams'里的segment也确实证明他们是属于system用户而不属于sys用户。

SQL> col segment_name format a30
SQL> select owner,segment_name from dba_segments where tablespace_name='tbs_streams';

OWNER SEGMENT_NAME
------------------------------ ------------------------------
SYSTEM LOGMNR_SESSION_EVOLVE$
SYSTEM LOGMNR_SESSION_EVOLVE$_PK
SYSTEM LOGMNR_HEADER1$
SYSTEM LOGMNR_HEADER2$
SYSTEM LOGMNR_UID$
SYSTEM LOGMNR_UID$_PK
SYSTEM LOGMNRC_DBNAME_UID_MAP
SYSTEM LOGMNRC_DBNAME_UID_MAP_PK
SYSTEM LOGMNR_DICTSTATE$
SYSTEM LOGMNR_DICTIONARY$
SYSTEM LOGMNR_OBJ$

OWNER SEGMENT_NAME
------------------------------ ------------------------------
SYSTEM LOGMNR_USER$
SYSTEM LOGMNRC_GTLO
SYSTEM LOGMNRC_GTLO_PK
SYSTEM LOGMNRC_GTCS
SYSTEM LOGMNRC_GTCS_PK
SYSTEM LOGMNRC_GSII
SYSTEM LOGMNRC_GSII_PK
SYSTEM LOGMNR_TAB$
SYSTEM LOGMNR_COL$
SYSTEM LOGMNR_ATTRCOL$
SYSTEM LOGMNR_TS$

OWNER SEGMENT_NAME
------------------------------ ------------------------------
SYSTEM LOGMNR_IND$
SYSTEM LOGMNR_TABPART$
SYSTEM LOGMNR_TABSUBPART$
SYSTEM LOGMNR_TABCOMPART$
SYSTEM LOGMNR_TYPE$
SYSTEM LOGMNR_COLTYPE$
SYSTEM LOGMNR_ATTRIBUTE$
SYSTEM LOGMNR_LOB$
SYSTEM LOGMNR_CDEF$
SYSTEM LOGMNR_CCOL$
SYSTEM LOGMNR_ICOL$

OWNER SEGMENT_NAME
------------------------------ ------------------------------
SYSTEM LOGMNR_LOBFRAG$
SYSTEM LOGMNR_INDPART$
SYSTEM LOGMNR_INDSUBPART$
SYSTEM LOGMNR_INDCOMPART$
SYSTEM LOGMNRP_CTAS_PART_MAP
SYSTEM LOGMNRP_CTAS_PART_MAP_PK
SYSTEM LOGMNRP_CTAS_PART_MAP_I
SYSTEM LOGMNR_I1OBJ$
SYSTEM LOGMNR_I1USER$
SYSTEM LOGMNR_I1TAB$
SYSTEM LOGMNR_I1TS$

OWNER SEGMENT_NAME
------------------------------ ------------------------------
SYSTEM LOGMNR_I1TABPART$
SYSTEM LOGMNR_I1TABSUBPART$
SYSTEM LOGMNR_I1TABCOMPART$
SYSTEM LOGMNR_I1IND$
SYSTEM LOGMNR_I1COL$
SYSTEM LOGMNR_I2COL$
SYSTEM LOGMNR_I1ATTRCOL$
SYSTEM LOGMNR_I1TYPE$
SYSTEM LOGMNR_I1COLTYPE$
SYSTEM LOGMNR_I1LOB$
SYSTEM LOGMNR_I1CDEF$

OWNER SEGMENT_NAME
------------------------------ ------------------------------
SYSTEM LOGMNR_I1CCOL$
SYSTEM LOGMNR_I1ICOL$
SYSTEM LOGMNR_I1LOBFRAG$
SYSTEM LOGMNR_I1INDPART$
SYSTEM LOGMNR_I1INDSUBPART$
SYSTEM LOGMNR_I1INDCOMPART$
SYSTEM LOGMNR_I2TABPART$
SYSTEM LOGMNR_I2INDPART$
SYSTEM LOGMNR_I2TABCOMPART$
SYSTEM LOGMNR_I2TABSUBPART$
SYSTEM LOGMNR_I2IND$

OWNER SEGMENT_NAME
------------------------------ ------------------------------
SYSTEM LOGMNR_I2TAB$
SYSTEM LOGMNRC_I2GTLO
SYSTEM LOGMNRC_I3GTLO
SYSTEM LOGMNR_LOG$
SYSTEM LOGMNR_LOG$_PK
SYSTEM LOGMNR_LOG$_FLAGS
SYSTEM LOGMNR_LOG$_FIRST_CHANGE#
SYSTEM LOGMNR_PROCESSED_LOG$
SYSTEM LOGMNR_PROCESSED_LOG$_PK
SYSTEM LOGMNR_SPILL$
SYSTEM SYS_IL0000005765C00008$$

OWNER SEGMENT_NAME
------------------------------ ------------------------------
SYSTEM SYS_LOB0000005765C00008$$
SYSTEM LOGMNR_SPILL$_PK
SYSTEM LOGMNR_AGE_SPILL$
SYSTEM SYS_IL0000005769C00008$$
SYSTEM SYS_LOB0000005769C00008$$
SYSTEM LOGMNR_AGE_SPILL$_PK
SYSTEM LOGMNR_RESTART_CKPT_TXINFO$
SYSTEM SYS_IL0000005773C00011$$
SYSTEM SYS_LOB0000005773C00011$$
SYSTEM LOGMNR_RESTART_CKPT_TXINFO$_PK
SYSTEM LOGMNR_ERROR$

OWNER SEGMENT_NAME
------------------------------ ------------------------------
SYSTEM LOGMNR_RESTART_CKPT$
SYSTEM LOGMNR_RESTART_CKPT$_PK
SYSTEM SYS_IL0000005778C00012$$
SYSTEM SYS_LOB0000005778C00012$$
SYSTEM SYS_IL0000005778C00009$$
SYSTEM SYS_LOB0000005778C00009$$
SYSTEM LOGMNR_FILTER$

已选择95行。

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19602/viewspace-1053299/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19602/viewspace-1053299/