Few Demantra Tables Not Using DEMANTRA User Default Tablespace Get ORA-01950: no privileges on tablespace (Doc ID 2278563.1)

Last updated on JUNE 20, 2017

Applies to:

Oracle Demantra Demand Management - Version 12.2.5.1 and later
Information in this document applies to any platform.

Symptoms

On : 12.2.5.1 version, Technical

ACTUAL BEHAVIOR
---------------
Customer installed Demantra 12.2.5.1 and used DEMANTRA_SALES_DATA as default tablespace for DEMANTRA schema.

They are getting frequent tabelspace privileges issues referring to APPS_TS_TX_DATA tablespace as follows.
ORA-01950: no privileges on tablespace 'APPS_TS_TX_DATA'

They did some research and found that 55 tables of DEMANTRA schema are using APPS_TS_TX_DATA default tablespace instead of DEMANTRA_SALES_DATA tablespace which is selected during the Installation.

TSOAPSD1> select default_tablespace, temporary_tablespace,username from dba_users where username='DEMANTRA';

DEFAULT_TABLESPACE TEMPORARY_TABLESPACE USERNAME
------------------------------ DEMANTRA_SALES_DATA TEMP DEMANTRA

TSOAPSD1> select distinct tablespace_name,count(*) from dba_tables where owner='DEMANTRA' group by tablespace_name;

TABLESPACE_NAME COUNT(*)
----------
APPS_TS_TX_DATA 55
DEMANTRA_SALES_DATA 1113
  23

Following 55 tables are not using default tablespace.

TSOAPSD1> select owner,table_name,TABLESPACE_NAME from dba_tables where owner='DEMANTRA' and tablespace_name='APPS_TS_TX_DATA';

OWNER TABLE_NAME TABLESPACE_NAME
----------------------------------- DEMANTRA BAL_BM APPS_TS_TX_DATA
DEMANTRA BAL_BM_DIFFERENCES APPS_TS_TX_DATA
DEMANTRA BIIO_IMP_PROMO_DATA APPS_TS_TX_DATA
DEMANTRA BIIO_IMP_PROMO_DATA_ERR APPS_TS_TX_DATA
DEMANTRA BIIO_LAUNCH_MGMT_MDP APPS_TS_TX_DATA
DEMANTRA BIIO_LAUNCH_MGMT_MDP_ERR APPS_TS_TX_DATA
DEMANTRA BIIO_LAUNCH_MGMT_PROMO APPS_TS_TX_DATA
DEMANTRA BIIO_LAUNCH_MGMT_PROMO_ERR APPS_TS_TX_DATA
DEMANTRA BIIO_LAUNCH_MGMT_SALES APPS_TS_TX_DATA
DEMANTRA BIIO_LAUNCH_MGMT_SALES_ERR APPS_TS_TX_DATA
DEMANTRA BIIO_LAUNCH_MGMT_SALES_TMP APPS_TS_TX_DATA
DEMANTRA BIIO_OTHER_PLAN_DATA APPS_TS_TX_DATA
DEMANTRA BIIO_OTHER_PLAN_DATA_ERR APPS_TS_TX_DATA
DEMANTRA BIIO_POPULATION APPS_TS_TX_DATA
DEMANTRA BIIO_POPULATION_ERR APPS_TS_TX_DATA
DEMANTRA BIIO_POPULATION_LAUNCH APPS_TS_TX_DATA
DEMANTRA BIIO_POPULATION_LAUNCH_ERR APPS_TS_TX_DATA
DEMANTRA BIIO_PROMOTION APPS_TS_TX_DATA
DEMANTRA BIIO_PROMOTION_ERR APPS_TS_TX_DATA
DEMANTRA BIIO_PROMOTION_LAUNCH APPS_TS_TX_DATA
DEMANTRA BIIO_PROMOTION_LAUNCH_ERR APPS_TS_TX_DATA
DEMANTRA BIIO_PROMOTION_STATUS APPS_TS_TX_DATA
DEMANTRA BIIO_PROMOTION_STATUS_ERR APPS_TS_TX_DATA
DEMANTRA BIIO_PURGE_IB_HISTORY APPS_TS_TX_DATA
DEMANTRA BIIO_PURGE_IB_HISTORY_ERR APPS_TS_TX_DATA
DEMANTRA BIIO_SIEBEL_POPULATION APPS_TS_TX_DATA
DEMANTRA BIIO_SIEBEL_POPULATION_ERR APPS_TS_TX_DATA
DEMANTRA BIIO_SIEBEL_PROMOTION APPS_TS_TX_DATA
DEMANTRA BIIO_SIEBEL_PROMOTION_ERR APPS_TS_TX_DATA
DEMANTRA CDP_PARAMETERS_DAILY APPS_TS_TX_DATA
DEMANTRA DB_HEALTH_DETAILED_LOG APPS_TS_TX_DATA
DEMANTRA DB_PATCH_LOG APPS_TS_TX_DATA
DEMANTRA DB_UPGRADE_LOG APPS_TS_TX_DATA
DEMANTRA INIT_PARAMS_216 APPS_TS_TX_DATA
DEMANTRA INIT_PARAMS_217 APPS_TS_TX_DATA
DEMANTRA LOG_ADD_MISSING_DEFAULT_ROWS APPS_TS_TX_DATA
DEMANTRA LOG_ADD_MISSING_ENG_COLS APPS_TS_TX_DATA
DEMANTRA LOG_BUILD_TEMP_DATES_TABLE APPS_TS_TX_DATA
DEMANTRA LOG_CAL_MONTH_DB_UPGRADE APPS_TS_TX_DATA
DEMANTRA LOG_CAL_MONTH_SPLIT_SET_QUEUES APPS_TS_TX_DATA
DEMANTRA LOG_CREATE_GL_MATRIX APPS_TS_TX_DATA
DEMANTRA LOG_DATA_CLEANUP APPS_TS_TX_DATA
DEMANTRA LOG_DATA_MODEL APPS_TS_TX_DATA
DEMANTRA LOG_ENCRYPTION APPS_TS_TX_DATA
DEMANTRA LOG_HTTP APPS_TS_TX_DATA
DEMANTRA LOG_MDP_MATRIX_ADD_LUD_COLS APPS_TS_TX_DATA
DEMANTRA LOG_METADATA APPS_TS_TX_DATA
DEMANTRA LOG_NOTIFICATION APPS_TS_TX_DATA
DEMANTRA LOG_SCAN_DATA_TABLE_COLUMNS APPS_TS_TX_DATA
DEMANTRA LOG_SET_MODULE APPS_TS_TX_DATA
DEMANTRA LOG_SYNC_GL_MATRIX APPS_TS_TX_DATA
DEMANTRA LOG_UPGRADE_TO_CAL_MONTH APPS_TS_TX_DATA
DEMANTRA POPU_ATTR_VALID_FILTER_LEVELS APPS_TS_TX_DATA
DEMANTRA SIMULATION_SETTING APPS_TS_TX_DATA
DEMANTRA SPLIT_DATA_EXCLUDE_LIST APPS_TS_TX_DATA

55 rows selected.


The parameters in the sys_params table also point to the right values

PNAME PVAL
----------------------------------------------------------------------------------------------------
simulationspace DEMANTRA_SIM_DATA
simulationindexspace DEMANTRA_SIM_IDX
sales_data_engine_space DEMANTRA_ENGINE_IDX
sales_data_engine_index_space DEMANTRA_ENGINE_DATA
tablespace DEMANTRA_SALES_DATA

 

EXPECTED BEHAVIOR
-----------------------
Expect that Demantra tables use the default tablespscae defined during installation for all Demantra tables

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Do a demantra install and define default tables spaces for demantra tables
2. Access the application and use it


BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot access all of the functionality due to permission issues on the tablespace

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms