My Oracle Support Banner

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

To view full details, 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 a vibrant support community of peers and Oracle experts.