收到淩晨郵件告警
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'
原因分析：
如下所示，使用者沒有權限在指定的表空間配置設定空間。隻需要将表空間的權限授予給該使用者即可。
[[email protected] ~]$ 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