ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB" ORA-01950: no privileges on

收到凌晨邮件告警 

  Errors in file /opt/oracle/diag/rdbms/xxxx/xxxx2/trace/ixxxx2_j000_5002.trc:
  ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB"
  ORA-01950: no privileges on tablespace 'SYSAUX'
  ORA-06512: at "DBSNMP.BSLN_INTERNAL", line 2073
  ORA-06512: at line 1

查看dbsnmp  用户的系统权限

  

SQL> select grantee,privilege from dba_sys_privs where grantee='DBSNMP';

GRANTEE                        PRIVILEGE
------------------------------ ----------------------------------------
DBSNMP                         CREATE PROCEDURE
DBSNMP                         SELECT ANY DICTIONARY
DBSNMP                         CREATE TABLE

ORA-01950: no privileges on tablespace tips

Oracle Error Tips by Donald Burleson(S. Karam)

 
The Oracle docs note this on the ora-01950 error:
ORA-01950: no privileges on tablespace "string"
 
Cause: User does not have privileges to allocate an extent in the specified tablespace.
Action: Grant the user the appropriate system privileges or grant the user space resource on the tablespace.

On the ITtoolbox Forum, a confrontation concerning ORA-01950 has been posted.   A replier offers information, by asking the user to solve ORA-01950 by either:

ALTER USER <username> QUOTA 100M ON <tablespace name> 
 
GRANT UNLIMITED TABLESPACE TO <username>
And to also make sure the user has been granted Connect, Resources roles incase the user was not given Create table privileges.

ORA-01950: no privileges on tablespace xxxx

案例场景：

新建了一个表空间后TBS_MARKET_DAT，希望将归档的数据放置在这个表空间。


SQL> CREATE TABLESPACE TBS_MARKET_DAT
  2  DATAFILE '/u04/oradata/gps/bookt_data_arch_01.dbf'
  3  SIZE 100M
  4  AUTOEXTEND ON
  5  NEXT 10M
  6  MAXSIZE 4G
  7  EXTENT MANAGEMENT LOCAL
  8  SEGMENT SPACE MANAGEMENT AUTO ONLINE ;
用户A（默认表空间为TBS_ODS_DAT）创建表时指定表存储在表空间为TBS_MARKET_DAT时，遭遇ORA-01950: no privileges on tablespace 'TBS_MARKET_DAT'

 

原因分析：

   如下所示，用户没有权限在指定的表空间分配空间。只需要将表空间的权限授予给该用户即可。

[oracle@DB-Server ~]$ oerr ora 01950
01950, 00000, "no privileges on tablespace '%s'"
// *Cause:  User does not have privileges to allocate an extent in the
//          specified tablespace.
// *Action: Grant the user the appropriate system privileges or grant the user
//          space resource on the tablespace.

解决方法：

 

方法1: 授予用户对该表空间的UNLIMITED配额

 

SQL> ALTER USER ETL QUOTA UNLIMITED ON TBS_MARKET_DAT;

User altered.

SQL>

 

方法2: 重新授权resource角色给用户之后，便可以创建表格

 

SQL> GRANT RESOURCE TO ETL;

类似另外案例

Errors in file /wload/test/app/oracle/diag/rdbms/TEST/TEST/trace/TEST_j000_7733378.trc:
ORA-12012: error on auto execute of job “SYS”.”BSLN_MAINTAIN_STATS_JOB”
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at “DBSNMP.BSLN_INTERNAL”, line 2073
ORA-06512: at line 1
Sat Mar 24 23:01:18 2012

Error from trace file

*** 2012-03-24 23:00:13.943
*** SESSION ID:(23.6871) 2012-03-24 23:00:13.943
*** CLIENT ID:() 2012-03-24 23:00:13.943
*** SERVICE NAME:(SYS$USERS) 2012-03-24 23:00:13.943
*** MODULE NAME:(DBMS_SCHEDULER) 2012-03-24 23:00:13.943
*** ACTION NAME:(BSLN_MAINTAIN_STATS_JOB) 2012-03-24 23:00:13.943

ORA-12012: error on auto execute of job “SYS”.”BSLN_MAINTAIN_STATS_JOB”
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at “DBSNMP.BSLN_INTERNAL”, line 2073
ORA-06512: at line 1

Cause

Table DBSNMP.BSLN_BASELINES contains inconsistent information that is causing the job to fail.

Check the job status

SQL> select log_date,status from dba_scheduler_job_run_details where job_name=’BSLN_MAINTAIN_STATS_JOB’;

LOG_DATE STATUS
————————————————————————— ——————————
10-MAR-12 11.00.13.501250 PM +00:00 FAILED
03-MAR-12 11.00.10.603238 PM +00:00 FAILED
17-MAR-12 11.00.14.793987 PM +00:00 FAILED
24-MAR-12 11.00.13.980545 PM +00:00 FAILED

Role of BSLN_MAINTAIN_STATS_JOB

This job runs the BSLN_MAINTAIN_STATS_PROG program on the BSLN_MAINTAIN_STATS_SCHED schedule.  The program BSLN_MAINTAIN_STATS_PROG will keep the default baseline’s statistics up-to-date

Check the DBSNMP.BSLN_BASELINES table

SQL> select * from DBSNMP.BSLN_BASELINES;

DBID INSTANCE_NAME BASELINE_ID BSLN_GUID TI A STATUS LAST_COMP
———- —————- ———– ——————————– — – —————- ———
1166314350 FTEST 0 4AC774574F6C7D60D4ADF390356098C1 NX Y ACTIVE 27-NOV-10
1166314350 TEST 0 CEF9782DC11EE02FCB9E1D821B49FB3F HX Y ACTIVE 27-NOV-10
So on our database we should just have the entry for TEST so we need to delete the entry for FTEST.

Delete the inconsistent entry

SQL> delete from DBSNMP.BSLN_BASELINES where INSTANCE_NAME=’FTEST’;

1 row deleted.
SQL> select * from DBSNMP.BSLN_BASELINES;

DBID INSTANCE_NAME BASELINE_ID BSLN_GUID TI A STATUS LAST_COMP
———- —————- ———– ——————————– — – —————- ———
1166314350 TEST 0 CEF9782DC11EE02FCB9E1D821B49FB3F HX Y ACTIVE 27-NOV-10

Now re-run the job.

SQL> exec dbms_scheduler.run_job(‘BSLN_MAINTAIN_STATS_JOB’,false);

PL/SQL procedure successfully completed.

The job has now successfully run.

SQL> select log_date,status from dba_scheduler_job_run_details where job_name=’BSLN_MAINTAIN_STATS_JOB’;

LOG_DATE STATUS
————————————————————————— ——————————
10-MAR-12 11.00.13.501250 PM +00:00 FAILED
29-MAR-12 01.11.43.054124 PM +01:00 SUCCEEDED
03-MAR-12 11.00.10.603238 PM +00:00 FAILED
17-MAR-12 11.00.14.793987 PM +00:00 FAILED
24-MAR-12 11.00.13.980545 PM +00:00 FAILED

http://t.askmaclean.com/thread-1159-1-1.html