How to read value from sys_refcursor variable

How to read value from sys_refcursor variable?

Developer often write functions which return type is sys_refcursor.

To directly read the value of the returned function we can use the below method:-

create or replace function TESTCALENDERDAYS return sys_refcursor is
rc sys_refcursor;
begin
open rc for select distinct ADD_MONTHS(SYSDATE, -365) as "day",
            COOL_id, COOL_name,MATION_id
            from QA.PQRS_STATS_VIEW
            union all
            select distinct ADD_MONTHS(SYSDATE, -365)+1 as "day",
            COOL_id, COOL_name,MATION_id
            from QA.PQRS_STATS_VIEW where ADD_MONTHS(SYSDATE, -365)+1 < sysdate;
return rc;
end;


create or replace function TESTCALENDERDAYS return sys_refcursor is
rc sys_refcursor;
begin
open rc for select distinct to_char(ADD_MONTHS(SYSDATE, -365)) as "day",COOL_id, COOL_name,MATION_id from TESTCALENDERDAYS_MV2
            union all
            select distinct to_char(ADD_MONTHS(SYSDATE, -365)+1) as "day",COOL_id, COOL_name,MATION_id
            from TESTCALENDERDAYS_MV2 where ADD_MONTHS(SYSDATE, -365)+1 < sysdate;
return rc;
end;

select to_date(day),COOL_ID,COOL_NAME,MATION_ID from (select * from xmltable('/ROWSET/ROW'
PASSING xmltype(TESTCALENDERDAYS())
columns
DAY date  PATH 'DAY',
COOL_ID PATH 'COOL_ID',
COOL_NAME PATH 'COOL_NAME',
MATION_ID PATH 'MATION_ID'
));

select * from  TESTCALENDERDAYS_MV2 from dual





select TESTF() from dual
select TESTCALENDERDAYS() from dual

select * from (select * from xmltable('/ROWSET/ROW'
PASSING xmltype(TESTF())
columns
ID  PATH 'ID',
COMPANY PATH 'COMPANY'
));

select * from TESTCALENDERDAYS_MV2

CREATE TABLE "SWARNASHIS"."TESTCALENDERDAYS_MV2" 
   ( "day" DATE, 
"COOL_ID" NUMBER(38,0), 
"COOL_NAME" NVARCHAR2(512), 
"MATION_ID" NUMBER(38,0)
   )
   tablespace users

insert into TESTCALENDERDAYS_MV2 values(sysdate-1,11,'ashisswarn',12);
insert into TESTCALENDERDAYS_MV2 values(sysdate-2,12,'ashisswarn',13);
insert into TESTCALENDERDAYS_MV2 values(sysdate-3,13,'ashisswarn',14);
insert into TESTCALENDERDAYS_MV2 values(sysdate-4,14,'ashisswarn',15);
insert into TESTCALENDERDAYS_MV2 values(sysdate-5,15,'ashisswarn',16);
insert into TESTCALENDERDAYS_MV2 values(sysdate-6,16,'ashisswarn',17);
insert into TESTCALENDERDAYS_MV2 values(sysdate-7,17,'ashisswarn',18);
insert into TESTCALENDERDAYS_MV2 values(sysdate-8,18,'ashisswarn',19);
insert into TESTCALENDERDAYS_MV2 values(sysdate-9,19,'ashisswarn',20);
insert into TESTCALENDERDAYS_MV2 values(sysdate-1,11,'ashisswarn',12);
insert into TESTCALENDERDAYS_MV2 values(sysdate-1,11,'ashisswarn',12);
insert into TESTCALENDERDAYS_MV2 values(sysdate-1,11,'ashisswarn',12);
insert into TESTCALENDERDAYS_MV2 values(sysdate-1,11,'ashisswarn',12);
insert into TESTCALENDERDAYS_MV2 values(sysdate-1,11,'ashisswarn',12);

commit


This works for fetching:

select extractvalue(column_value,'/ROW/day') day,
extractvalue(column_value,'/ROW/COOL_ID') COOL_id,
extractvalue(column_value,'/ROW/COOL_NAME') COOL_name,
extractvalue(column_value,'/ROW/MATION_ID') MATION_id
--     , extractvalue(column_value,'/ROW/LAST_NAME') last_name
from table(xmlsequence(TESTCALENDERDAYS()))

====

select day,COOL_id from (select extractvalue(column_value,'/ROW/day') day,
extractvalue(column_value,'/ROW/COOL_ID') COOL_id,
extractvalue(column_value,'/ROW/COOL_NAME') COOL_name,
extractvalue(column_value,'/ROW/MATION_ID') MATION_id
--     , extractvalue(column_value,'/ROW/LAST_NAME') last_name
from table(xmlsequence(TESTCALENDERDAYS()))) where COOL_id

How to find the list of unusable index on the full database:-

set pagesize 9000
set linesize 2000
set long 20000

select dbms_metadata.get_ddl('INDEX', index_name, owner)
from all_indexes
where owner in(SELECT OWNER from (select OWNER,INDEX_NAME,INDEX_TYPE,PARTITION_NAME,SUBPARTITION_NAME, STATUS from  (select OWNER,INDEX_NAME,INDEX_TYPE,NULL AS PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_indexes where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_ind_PARTITIONS where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,NULL as PARTITION_NAME,SUBPARTITION_NAME,STATUS from dba_ind_SUBPARTITIONS where STATUS='UNUSABLE')))
AND INDEX_NAME IN(SELECT INDEX_NAME from (select OWNER,INDEX_NAME,INDEX_TYPE,PARTITION_NAME,SUBPARTITION_NAME, STATUS from  (select OWNER,INDEX_NAME,INDEX_TYPE,NULL AS PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_indexes where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_ind_PARTITIONS where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,NULL as PARTITION_NAME,SUBPARTITION_NAME,STATUS from dba_ind_SUBPARTITIONS where STATUS='UNUSABLE')));

===============

Schema Wise

set pagesize 232
set linesize 232
set feedback off
set echo off
set verify off
select OWNER,INDEX_NAME,INDEX_TYPE,PARTITION_NAME,SUBPARTITION_NAME, STATUS from  (select OWNER,INDEX_NAME,INDEX_TYPE,NULL AS PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_indexes where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_ind_PARTITIONS where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,NULL as PARTITION_NAME,SUBPARTITION_NAME,STATUS from dba_ind_SUBPARTITIONS where STATUS='UNUSABLE') where OWNER='&schema_name';
spool off


===============
Generating the unusable command:

[oracle@xxxx swarn]$ more 2.sql
spool /u01/oracle/swarn/unusable_cmd.sql
set pagesize 232
set linesize 232
set feedback off
set echo off
set verify off
set heading off
select 'alter index '||OWNER||'.'||index_name||' unusable; 'from (select OWNER, INDEX_NAME from  (select OWNER,INDEX_NAME,INDEX_TYPE,NULL AS PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_indexes where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_ind_PARTITIONS where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,NULL as PARTITION_NAME,SUBPARTITION_NAME,STATUS from dba_ind_SUBPARTITIONS where STATUS='UNUSABLE'));
spool off
====================

How to create Bigfile tablespace for EXADATA DB MACHINES and Other high end DW systems

How to create Bigfile tablespace for EXADATA DB MACHINES and Other high end DW systems?

Traditionally we use small file tablespace which can handle data upto a maxsize of 32GB.

However Big file tablespaces are required when we want to create tablespace with more than 32 GB size.

This is very help when we work on Oracle High end Advance Database machine like Exadata and also for various DataWarehouse systems.

CREATE BIGFILE TABLESPACE EDW_DATA_EQBS DATAFILE '+DATABIPRD' SIZE 512G AUTOEXTEND ON; -- done 


CREATE BIGFILE TABLESPACE EDW_DATA_FOUNDATION DATAFILE '+DATABIPRD' SIZE 512G AUTOEXTEND ON;


CREATE BIGFILE TABLESPACE EDW_DATA_DRE DATAFILE '+DATABIPRD' SIZE 512G AUTOEXTEND ON;


CREATE BIGFILE TABLESPACE EDW_METADATA_TOOLS DATAFILE '+DATABIPRD' SIZE 128G AUTOEXTEND ON;




CREATE BIGFILE TEMPORARY TABLESPACE TEMP TEMPFILE '+DATABIPRD' SIZE 256G AUTOEXTEND ON;


CREATE BIGFILE TEMPORARY TABLESPACE TEMP1 TEMPFILE '+DATABIPRD' SIZE 256G AUTOEXTEND ON;


CREATE BIGFILE TEMPORARY TABLESPACE TEMP2 TEMPFILE '+DATABIPRD' SIZE 256G AUTOEXTEND ON;


CREATE BIGFILE TEMPORARY TABLESPACE TEMP3 TEMPFILE '+DATABIPRD' SIZE 256G AUTOEXTEND ON;


CREATE BIGFILE TEMPORARY TABLESPACE TEMP4 TEMPFILE '+DATABIPRD' SIZE 256G AUTOEXTEND ON;



ALTER TABLESPACE EDW_METADATA_TOOLS AUTOEXTEND ON NEXT 8G MAXSIZE 512G;

ALTER TABLESPACE EDW_DATA_EQBS AUTOEXTEND ON NEXT 8G MAXSIZE 1024G;

ALTER TABLESPACE EDW_DATA_FOUNDATION AUTOEXTEND ON NEXT 8G MAXSIZE 1024G;

ALTER TABLESPACE EDW_DATA_DRE AUTOEXTEND ON NEXT 8G MAXSIZE 1024G;



ALTER TABLESPACE TEMP AUTOEXTEND ON NEXT 8G MAXSIZE 512G;

ALTER TABLESPACE TEMP1 AUTOEXTEND ON NEXT 8G MAXSIZE 512G;

ALTER TABLESPACE TEMP2 AUTOEXTEND ON NEXT 8G MAXSIZE 512G;

ALTER TABLESPACE TEMP3 AUTOEXTEND ON NEXT 8G MAXSIZE 512G;

ALTER TABLESPACE TEMP4 AUTOEXTEND ON NEXT 8G MAXSIZE 512G;

How to work in Timesten Database

How to work in Times ten=>

[oracle@bbmpde01 info]$ vi sys.odbc.ini
[oracle@bbmpde01 info]$ cd bin
bash: cd: bin: No such file or directory
[oracle@bbmpde01 info]$ cd ../bin
[oracle@bbmpde01 bin]$ ./ttisql

Copyright (c) 1996, 2015, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.


Command> connect "DSN=cachedb1_1122";
12701: DatabaseCharacterSet attribute required for data store creation. Refer to the TimesTen documentation for information on selecting a character set.
The command failed.
Command> exit
Done.
[oracle@bbmpde01 bin]$ cd ..
[oracle@bbmpde01 tt1122]$ cd info/
[oracle@bbmpde01 info]$ ls
cluster.oracle.ini  DemoDataStore  snmp.ini  sys.odbc.ini  sys.ttconnect.ini  timestend.pid  ttcacheadvisor  ttendaemon.options  tterrors.log  ttmesg.log
[oracle@bbmpde01 info]$ vi sys.odbc.ini
[oracle@bbmpde01 info]$ cd ../bin
[oracle@bbmpde01 bin]$ ./ttisql

Copyright (c) 1996, 2015, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.


Command> connect "DSN=cachedb1_1122";
15105: User CACHEADM requesting database creation is not the instance administrator.  Please verify user id and password. Only the instance administrator can create a database.
The command failed.
Command>
Command> exit
Done.
[oracle@bbmpde01 bin]$ id
uid=1101(oracle) gid=54321(oinstall) groups=54321(oinstall),1000(oracle),54322(dba)
[oracle@bbmpde01 bin]$ cd ..
[oracle@bbmpde01 tt1122]$ ls
3rdparty  bin  doc  include  info  lib  mibs  network  nls  oraclescripts  PERL  plsql  quickstart  quickstart.html  README.html  startup  support  ttcacheadv  ttclasses  ttoracle_home  welcome.html
[oracle@bbmpde01 tt1122]$ cd info/
[oracle@bbmpde01 info]$ ls
cluster.oracle.ini  DemoDataStore  snmp.ini  sys.odbc.ini  sys.ttconnect.ini  timestend.pid  ttcacheadvisor  ttendaemon.options  tterrors.log  ttmesg.log
[oracle@bbmpde01 info]$ more sys.odbc.ini
# Copyright (c) 1999, 2014, Oracle and/or its affiliates. All rights reserved.

#########################################################################
#
# The following are the default values for connection attributes.
# In the Data Sources defined below, if the attribute is not explicitly
# set in its entry, TimesTen 11.2.2 uses the defaults as
# specified below.  For more information on these connection attributes,
# see the accompanying documentation.
#
# Lines in this file beginning with # or ; are treated as comments.
# In _attribute_=_value_ lines, the _value_ consists of everything
# after the = to the end of the line, with leading and trailing white
# space removed.
#
#########################################################################
#
#    ** attribute **           ** default **
#
# Data store attributes
#       DataStore                 (no default)
#       DatabaseCharacterSet      (no default)
#       Description               (no default)
#       Driver                    (no default)
#       LogDir                    (Directory containing db transaction log
#                                  files)
#       Preallocate               (0 - do not preallocate disk space for the
#                                  datastore should be preallocated )
#       RangeIndexType            (1 - Range indexes are T-tree indexes)
#       ReplicationApplyOrdering  (0 - automatic parallel replication)
#       ReplicationParallelism    (1)
#       Temporary                 (0 - do not create a permanent datastore)
#       TypeMode                  (0 - Oracle types)
#
# First connection attributes
#       AutoCreate                (1)
#       CkptFrequency             (600)
#       CkptLogVolume             (0 - off)
#       CkptRate                  (0 - rate not limited)
#       Connections               (The lesser of 2000 or the number of
#                                  semaphores specified in the SEMMSL kernel
#                                  parameter)
#       ForceConnect              (0 - connection disallowed)
#       LogAutoTruncate           (1 - continue after log is truncated)
#       LogBufMB                  (64 - measured in MB)
#       LogBufParallelism         (4)
#       LogFileSize               (64 - measured in MB )
#       LogFlushMethod            (1 - Write data to transaction log files
#                                  using buffered writes.  Use explicit sync
#                                  operations as needed to sync log data to
#                                  disk)
#       LogPurge                  (1 - remove unneeded transaction log files)
#       MemoryLock                (0 - Linux, Solaris and Windows 64-bit
#                                  platforms only)
#       Overwrite                 (0 - do not overwrite the existing datastore)
#       PermSize                  (32 - measured in MB)
#       ReceiverThreads           (1)
#       RecoveryThreads           (1 - the number of threads used to rebuild
#                                  indexes during recovery)
#       CkptReadThreads           (1 - the number of threads used to read
#                                  checkpoint files at startup)
#       TempSize                  (default is derived from PermSize - measured
#                                  in MB)
#
#
# General connection attributes
#       CommitBufferSizeMax       (16 - measured in KB)
#       ConnectionName            (process argv[0])
#       DDLCommitBehavior         (0 - Oracle-style DDL commits)
#       DDLReplicationAction      (INCLUDE)
#       DDLReplicationLevel       (2 - Replication of objects enabled)
#       Diagnostics               (1 - generate base level diagnostics )
#       DuplicateBindMode         (0 - Oracle-style binding)
#       DurableCommits            (0 - do not force log to disk on transaction
#                                  commits)
#       Isolation                 (1 - read-committed)
#       LockLevel                 (0 - row-level locking)
#       LockWait                  (10 seconds)
#       MatchLogOpts              (0 - Values of Logging & LogPurge are not
#                                  ignored)
#       PermWarnThreshold         (90 - pecentage at which warnings should be
#                                  issued)
#       PrivateCommands           (0 - share commands between connections)
#       PWD                       (no default)
#       PWDCrypt                  (no default)
#       QueryThreshold            (0 - do not return an error nor throw an SNMP
#                                  trap if the query times out before executing)
#       ReplicationTrack          (no default)
#       SQLQueryTimeout           (0 - time limit in seconds for executing SQL
#                                  queries)
#       TempWarnThreshold         (90 - percentage at which out-of-memory
#                                  warnings should be issued)
#       UID                       (operating system user ID)
#       WaitForConnect            (1 - wait until connection to the datastore
#                                  is possible)
#
#
# NLS general connection attributes
#       ConnectionCharacterSet    (if DatabaseCharacterSet == TIMESTEN8
#                                  then TIMESTEN8 else US7ASCII)
#       NLS_LENGTH_SEMANTICS      (BYTE - default length semantic configuration)
#       NLS_NCHAR_CONV_EXCP       (0 - do not report data loss for data
#                                  conversion between NCHAR/NVARCHAR data and
#                                  CHAR/VARCHAR data)
#       NLS_SORT                  (BINARY - the collating sequence to use for
#                                  linguistic comparisons)
#
#
# PL/SQL first connection attibutes
#       PLSQL                     (default is installation dependent.  If
#                                  enabled at install time, default is 1)
#
#    The following Attributes are significant only when PLSQL=1
#       PLSQL_MEMORY_ADDRESS      (platform specific default; value is entered
#                                 as Hex: 20000000 means 0x20000000)
#       PLSQL_MEMORY_SIZE         (32 - measured in MB)
#
#
# PL/SQL general connection attibutes
#    The following Attributes are significant only when PLSQL=1
#       PLSQL_OPTIMIZE_LEVEL      (2)
#       PLSQL_CCFLAGS             (default is "")
#       PLSQL_CONN_MEM_LIMIT      (100 - measured in MB)
#       PLSCOPE_SETTINGS          (IDENTIFIERS:NONE)
#       PLSQL_TIMEOUT             (30 - measured in seconds)
#
#
# Oracle TimesTen Application-Tier Database Cache first connection attributes
#       CacheAWTMethod            (1 - plsql)
#
#
# Oracle TimesTen Application-Tier Database Cache database attributes
#       CacheAWTParallelism       (1 - no parallelism)
#       CacheGridEnable           (1 - All cache groups in the data store are
#                                  defined as members of a cache grid)
#       CacheGridMsgWait          (60 - seconds to wait for a cache grid
#                                  message from a remote member)
#
#
# Oracle TimesTen Application-Tier Database Cache database attributes
#       DynamicLoadEnable         (1 - Enable dynamic load of Oracle data to
#                                  dynamic cache groups for the current
#                                  connection)
#       DynamicLoadErrorMode      (0 - do not return an error on a transparent
#                                  dynamic load behavior)
#       OracleNetServiceName      (no default)
#       OraclePWD                 (no default)
#       PassThrough               (0 - SQL not passed through to Oracle)
#       RACCallback               (1 - Install the TAF and FAN callbacks)
#
#
# TimesTen Client connection attributes
#       TCP_Port                  (no default)
#       TCP_Port2                 (no default)
#       TTC_FailoverPortRange     (no default)
#       TTC_Server                (no default)
#       TTC_Server2               (no default)
#       TTC_Server_DSN            (no default)
#       TTC_Server_DSN2           (no default)
#       TTC_Timeout               (60 - seconds the client waits for a
#                                  connection)
#
#
# TimesTen Server connection attributes
#       MaxConnsPerServer         (1)
#       ServersPerDSN             (1)
#       ServerStackSize           (128 on 32-bit systems, 256 on 64-bit systems,
#                                  measured in MB)
#
#########################################################################


[ODBC Data Sources]
TT_1122=TimesTen 11.2.2 Driver
sampledb_1122=TimesTen 11.2.2 Driver
cachedb1_1122=TimesTen 11.2.2 Driver
repdb1_1122=TimesTen 11.2.2 Driver
repdb2_1122=TimesTen 11.2.2 Driver
sampledbCS_1122=TimesTen 11.2.2 Client Driver
cachedb1CS_1122=TimesTen 11.2.2 Client Driver
repdb1CS_1122=TimesTen 11.2.2 Client Driver
repdb2CS_1122=TimesTen 11.2.2 Client Driver

#####################################################################
# Instance-Specific System Database
#
# A predefined instance-specific database reserved for system use.
# It provides a well-known database for use when a connection
# is required to execute commands.
#
#####################################################################

[TT_1122]
Driver=/u01/oracle/products/timesten/TimesTen/tt1122/lib/libtten.so
DataStore=/u01/oracle/products/timesten/TimesTen/tt1122/info/TT_1122
DatabaseCharacterSet=US7ASCII

#####################################################################
# Data source for Sample programs
#
# This database is used by the Quick Start sample programs.
# The sample database (sampledb) must be created prior to
# running the sample programs. The PermSize and TempSize attributes
# can be adjusted depending on the options used in running the
# sample benchmark programs.
#
#####################################################################

[sampledb_1122]
Driver=/u01/oracle/products/timesten/TimesTen/tt1122/lib/libtten.so
DataStore=/u01/oracle/products/timesten/TimesTen/tt1122/info/DemoDataStore/sampledb_1122
PermSize=40
TempSize=32
PLSQL=1
DatabaseCharacterSet=US7ASCII

#####################################################################
# Sample Data source for Quick Start Oracle TimesTen Application-Tier Database Cache tutorial
#
# This database is used by the Quick Start Oracle TimesTen Application-Tier Database Cache tutorial.
# Before using the cachedb1 DSN, uncomment both the
# DatabaseCharacterSet and OracleNetServiceName attributes and insert
# the appropriate values for the database character set of your
# Oracle database and the TNS service name for your Oracle database
#####################################################################

[cachedb1_112]
Driver=/u01/oracle/products/timesten/TimesTen/tt1122/lib/libtten.so
DataStore=/u01/oracle/products/timesten/TimesTen/tt1122/info/DemoDataStore/cachedb1_1122
PermSize=40
TempSize=32
PLSQL=1
#DatabaseCharacterSet=AL32UTF8
#OracleNetServiceName=MyOrclDB

#####################################################################
# Sample Data source for Quick Start Replication tutorial
#
# This database is used by the Quick Start Active Standby tutorial
#####################################################################

[repdb1_1122]
Driver=/u01/oracle/products/timesten/TimesTen/tt1122/lib/libtten.so
DataStore=/u01/oracle/products/timesten/TimesTen/tt1122/info/DemoDataStore/repdb1_1122
PermSize=40
TempSize=32
PLSQL=1
DatabaseCharacterSet=AL32UTF8

#####################################################################
# Sample Data source for Quick Start Replication tutorial
#
# This database is used by the Quick Start Active Standby tutorial
#####################################################################

[repdb2_1122]
Driver=/u01/oracle/products/timesten/TimesTen/tt1122/lib/libtten.so
DataStore=/u01/oracle/products/timesten/TimesTen/tt1122/info/DemoDataStore/repdb2_1122
PermSize=40
TempSize=32
PLSQL=1
DatabaseCharacterSet=AL32UTF8


#####################################################################
#
# New data source definitions can be added below.
#
#####################################################################

[cachedb1_1122]
Driver=/u01/oracle/products/TimesTen/tt1122/lib/libtten.so
DataStore=/u01/oracle/products/TimesTen/tt1122/info/DemoDataStore/cachedb1_1122
PermSize=40
TempSize=32
PLSQL=1
UID=cacheadm
PWD=...................
OraclePWD=...........cacheadm
DatabaseCharacterSet=AL32UTF8
OracleNetServiceName=OFSDB


########################################################################
# This following sample definitions should be in the .odbc.ini file
# that is used for the TimesTen 11.2.2 Client.
# The Server Name is set in the TTC_SERVER attribute.
# The Server DSN is set in the TTC_SERVER_DSN attribute.
#########################################################################


[sampledbCS_1122]
TTC_SERVER=ttLocalHost_tt1122
TTC_SERVER_DSN=sampledb_1122

[cachedb1CS_1122]
TTC_SERVER=ttLocalHost_tt1122
TTC_SERVER_DSN=cachedb1_1122

[repdb1CS_1122]
TTC_SERVER=ttLocalHost_tt1122
TTC_SERVER_DSN=repdb1_1122

[repdb2CS_1122]
TTC_SERVER=ttLocalHost_tt1122
TTC_SERVER_DSN=repdb2_1122

[oracle@bbmpde01 info]$
[oracle@bbmpde01 info]$
[oracle@bbmpde01 info]$ cd ../bin
[oracle@bbmpde01 bin]$ pwd
/u01/oracle/products/timesten/TimesTen/tt1122/bin
[oracle@bbmpde01 bin]$ ./ttisql

Copyright (c) 1996, 2015, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.


Command> connect "DSN=sampledb_1122";
Connection successful: DSN=sampledb_1122;UID=oracle;DataStore=/u01/oracle/products/timesten/TimesTen/tt1122/info/DemoDataStore/sampledb_1122;DatabaseCharacterSet=US7ASCII;ConnectionCharacterSet=US7ASCII;DRIVER=/u01/oracle/products/timesten/TimesTen/tt1122/lib/libtten.so;PermSize=40;TempSize=32;TypeMode=0;
(Default setting AutoCommit=1)
Command> exit
Disconnecting...
Done.
[oracle@bbmpde01 bin]$ cd ../info/
[oracle@bbmpde01 info]$ ls
cluster.oracle.ini  DBI5ac22dcf.0  DemoDataStore  snmp.ini  sys.odbc.ini  sys.ttconnect.ini  timestend.pid  ttcacheadvisor  ttendaemon.options  tterrors.log  ttmesg.log
[oracle@bbmpde01 info]$ vi sys.odbc.ini
[oracle@bbmpde01 info]$ cd bin
bash: cd: bin: No such file or directory
[oracle@bbmpde01 info]$ cd ../bin
[oracle@bbmpde01 bin]$ ./ttisql

Copyright (c) 1996, 2015, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.


Command> connect "DSN=cachedb1_1122";
  830: Cannot create data store file. OS-detected error: No such file or directory
The command failed.
Command> exit
Done.
[oracle@bbmpde01 bin]$ cd ../info/
[oracle@bbmpde01 info]$ vi sys.odbc.ini
[oracle@bbmpde01 info]$ cd ../bin
[oracle@bbmpde01 bin]$ ./ttisql

Copyright (c) 1996, 2015, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.


Command> connect "DSN=cachedb1_1122";
Connection successful: DSN=cachedb1_1122;UID=oracle;DataStore=/u01/oracle/products/timesten/TimesTen/tt1122/info/DemoDataStore/cachedb1_1122;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;DRIVER=/u01/oracle/products/timesten/TimesTen/tt1122/lib/libtten.so;PermSize=40;TempSize=32;TypeMode=0;OracleNetServiceName=OFSDB;
(Default setting AutoCommit=1)
Command> CREATE USER testuser IDENTIFIED BY mypsswrd;

User created.

Command> drop user testuser;

User dropped.

Command> CREATE USER cacheadm IDENTIFIED BY cacheadm;

User created.

Command> GRANT ADMIN, DDL TO cacheadm
       > ;
15111: Invalid privilege: DDL.  Roles are not supported.
The command failed.
Command> GRANT ADMIN to cacheadm;
Command>
Command>
Command>
Command>
Command>
Command> connect "DSN=cachedb1_1122";
Connection successful: DSN=cachedb1_1122;UID=oracle;DataStore=/u01/oracle/products/timesten/TimesTen/tt1122/info/DemoDataStore/cachedb1_1122;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;DRIVER=/u01/oracle/products/timesten/TimesTen/tt1122/lib/libtten.so;PermSize=40;TempSize=32;TypeMode=0;OracleNetServiceName=OFSDB;
(Default setting AutoCommit=1)
con1: Command> GRANT ADMIN to cacheadm;
15140: GRANT failed: User CACHEADM already has system privilege ADMIN
The command failed.
con1: Command>
con1: Command>
con1: Command>
con1: Command> connect cacheadm
             > ;
S1000: Error opening ODBCINI file /var/TimesTen/sys.odbc.ini: Permission denied
The command failed.
none: Command> exit
Disconnecting from cachedb1_1122...
Disconnecting from con1...
Done.
[oracle@bbmpde01 bin]$ cd /var/
[oracle@bbmpde01 var]$ ls
account  cache  crash  cvs  db  empty  games  lib  local  lock  log  mail  mpi-selector  nis  opt  preserve  run  spool  TimesTen  tmp  www  yp
[oracle@bbmpde01 var]$ cd TimesTen/
bash: cd: TimesTen/: Permission denied
[oracle@bbmpde01 var]$ ls
account  cache  crash  cvs  db  empty  games  lib  local  lock  log  mail  mpi-selector  nis  opt  preserve  run  spool  TimesTen  tmp  www  yp
[oracle@bbmpde01 var]$ ls -lrt
total 84
drwxr-xr-x.  2 root root 4096 Nov  1  2011 preserve
drwxr-xr-x.  2 root root 4096 Nov  1  2011 opt
drwxr-xr-x.  2 root root 4096 Nov  1  2011 nis
drwxr-xr-x.  2 root root 4096 Nov  1  2011 local
drwxr-xr-x.  2 root root 4096 Nov  1  2011 games
drwxr-xr-x.  2 root root 4096 Oct 11  2013 cvs
lrwxrwxrwx.  1 root root   10 Feb 20  2014 mail -> spool/mail
drwxr-xr-x.  3 root root 4096 Feb 20  2014 empty
drwxr-xr-x.  2 root root 4096 Feb 20  2014 account
drwxr-xr-x.  3 root root 4096 Jul 23  2014 yp
drwxr-xr-x. 17 root root 4096 Jul 23  2014 cache
drwxr-xr-x.  4 root root 4096 Jul 23  2014 db
drwxr-xr-x   3 root root 4096 Jul 23  2014 mpi-selector
drwxr-xr-x.  2 root root 4096 Sep 30  2014 crash
drwxr-xr-x   7 root root 4096 Aug 24  2015 www
drwxr-xr-x. 14 root root 4096 May  4  2017 spool
drwxr-xr-x. 39 root root 4096 May  8  2017 lib
drwxr-xr-x. 31 root root 4096 Feb  2 10:58 run
drwxr-x---   3 root root 4096 Mar  6 11:04 TimesTen
drwxr-xr-x. 15 root root 4096 Apr  1 03:11 log
drwxrwxr-x.  5 root lock 4096 Apr  2 03:48 lock
drwxrwxrwt.  3 root root 4096 Apr  2 10:26 tmp
[oracle@bbmpde01 var]$ date
Mon Apr  2 16:38:44 EAT 2018
[oracle@bbmpde01 var]$ pwd
/var
[oracle@bbmpde01 var]$ more /var/TimesTen/sys.odbc.ini
/var/TimesTen/sys.odbc.ini: Permission denied
[oracle@bbmpde01 var]$
[oracle@bbmpde01 var]$
[oracle@bbmpde01 var]$ exit
exit
[Dbhavsar@bbmpde01 etc]$ sudo su - root
[sudo] password for vvvvvsar:
[root@bbmpde01 ~]# cd /var
[root@bbmpde01 var]# ls -lrt
total 84
drwxr-xr-x.  2 root root 4096 Nov  1  2011 preserve
drwxr-xr-x.  2 root root 4096 Nov  1  2011 opt
drwxr-xr-x.  2 root root 4096 Nov  1  2011 nis
drwxr-xr-x.  2 root root 4096 Nov  1  2011 local
drwxr-xr-x.  2 root root 4096 Nov  1  2011 games
drwxr-xr-x.  2 root root 4096 Oct 11  2013 cvs
lrwxrwxrwx.  1 root root   10 Feb 20  2014 mail -> spool/mail
drwxr-xr-x.  3 root root 4096 Feb 20  2014 empty
drwxr-xr-x.  2 root root 4096 Feb 20  2014 account
drwxr-xr-x.  3 root root 4096 Jul 23  2014 yp
drwxr-xr-x. 17 root root 4096 Jul 23  2014 cache
drwxr-xr-x.  4 root root 4096 Jul 23  2014 db
drwxr-xr-x   3 root root 4096 Jul 23  2014 mpi-selector
drwxr-xr-x.  2 root root 4096 Sep 30  2014 crash
drwxr-xr-x   7 root root 4096 Aug 24  2015 www
drwxr-xr-x. 14 root root 4096 May  4  2017 spool
drwxr-xr-x. 39 root root 4096 May  8  2017 lib
drwxr-xr-x. 31 root root 4096 Feb  2 10:58 run
drwxr-x---   3 root root 4096 Mar  6 11:04 TimesTen
drwxr-xr-x. 15 root root 4096 Apr  1 03:11 log
drwxrwxr-x.  5 root lock 4096 Apr  2 03:48 lock
drwxrwxrwt.  3 root root 4096 Apr  2 10:26 tmp
[root@bbmpde01 var]# chown oracle:oinstall TimesTen
[root@bbmpde01 var]# chmod 777 TimesTen/
[root@bbmpde01 var]# chmod 777 -R TimesTen/
[root@bbmpde01 var]# chown oracle:oinstall TimesTen -R
[root@bbmpde01 var]# cd TimesTen/
[root@bbmpde01 TimesTen]# ls
sys.odbc.ini  sys.ttconnect.ini  tt1122
[root@bbmpde01 TimesTen]# ls -lrt
total 12
-rwxrwxrwx 1 oracle oinstall  441 Mar  6 11:04 sys.ttconnect.ini
-rwxrwxrwx 1 oracle oinstall 1000 Mar  6 11:04 sys.odbc.ini
drwxrwxrwx 2 oracle oinstall 4096 Mar  6 11:04 tt1122
[root@bbmpde01 TimesTen]# more sys.odbc.ini
# Copyright (c) 1999, 2011, Oracle and/or its affiliates. All rights reserved.

########################################################################
# This following sample definitions should be in the .odbc.ini file
# that is used for the TimesTen 11.2.2 Client.
# The Server Name is set in the TTC_SERVER attribute.
# The Server DSN is set in the TTC_SERVER_DSN attribute.
#########################################################################

[ODBC Data Sources]
#sampledbCS_1122=TimesTen 11.2.2 Client Driver
#cachedb1CS_1122=TimesTen 11.2.2 Client Driver
#repdb1CS_1122=TimesTen 11.2.2 Client Driver
#repdb2CS_1122=TimesTen 11.2.2 Client Driver

#[sampledbCS_1122]
#TTC_SERVER=biappdev01.ebsafrica.com
#TTC_SERVER_DSN=sampledb_1122

#[cachedb1CS_1122]
#TTC_SERVER=biappdev01.ebsafrica.com
#TTC_SERVER_DSN=cachedb1_1122

#[repdb1CS_1122]
#TTC_SERVER=biappdev01.ebsafrica.com
#TTC_SERVER_DSN=repdb1_1122

#[repdb2CS_1122]
#TTC_SERVER=biappdev01.ebsafrica.com
#TTC_SERVER_DSN=repdb2_1122

[root@bbmpde01 TimesTen]# cd tt1122/
[root@bbmpde01 tt1122]# ls
[root@bbmpde01 tt1122]# ls -lrt
total 0
[root@bbmpde01 tt1122]# ls -lrt
total 0
[root@bbmpde01 tt1122]# exit
logout
[Dbhavsar@bbmpde01 etc]$ su oracle
Password:
[oracle@bbmpde01 etc]$ cd /u01/oracle/products/timesten/TimesTen/tt1122/info
[oracle@bbmpde01 info]$ cd bin
bash: cd: bin: No such file or directory
[oracle@bbmpde01 info]$ cd ../bin
[oracle@bbmpde01 bin]$ ./ttisql

Copyright (c) 1996, 2015, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.


Command> connect cacheadm;
IM002: Data source name not found and no default driver specified
The command failed.
Command> connect "DSN=cachedb1_1122";
Connection successful: DSN=cachedb1_1122;UID=oracle;DataStore=/u01/oracle/products/timesten/TimesTen/tt1122/info/DemoDataStore/cachedb1_1122;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;DRIVER=/u01/oracle/products/timesten/TimesTen/tt1122/lib/libtten.so;PermSize=40;TempSize=32;TypeMode=0;OracleNetServiceName=OFSDB;
(Default setting AutoCommit=1)
Command>
Command>
Command> connect cacheadm;
IM002: Data source name not found and no default driver specified
The command failed.
none: Command>
none: Command>
none: Command>
none: Command> connect cacheadm/cacheadm
             > ;
S1000: Data Source Name must not consist solely of blanks or contain "[]{}(),;?*=!@\/" characters
The command failed.
none: Command>
none: Command>
none: Command>
none: Command>
none: Command> connect "DSN=cachedb1_1122";
Connection successful: DSN=cachedb1_1122;UID=oracle;DataStore=/u01/oracle/products/timesten/TimesTen/tt1122/info/DemoDataStore/cachedb1_1122;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;DRIVER=/u01/oracle/products/timesten/TimesTen/tt1122/lib/libtten.so;PermSize=40;TempSize=32;TypeMode=0;OracleNetServiceName=OFSDB;
(Default setting AutoCommit=1)
con1: Command>
con1: Command>
con1: Command>
con1: Command>  connect "DSN=cachedb1_1122";
Connection successful: DSN=cachedb1_1122;UID=oracle;DataStore=/u01/oracle/products/timesten/TimesTen/tt1122/info/DemoDataStore/cachedb1_1122;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;DRIVER=/u01/oracle/products/timesten/TimesTen/tt1122/lib/libtten.so;PermSize=40;TempSize=32;TypeMode=0;OracleNetServiceName=OFSDB;
(Default setting AutoCommit=1)
con2: Command>
con2: Command>
con2: Command> grant admin to cacheadm;
15140: GRANT failed: User CACHEADM already has system privilege ADMIN
The command failed.
con2: Command>
con2: Command>
con2: Command>
con2: Command>
con2: Command>
con2: Command>
con2: Command>
con2: Command> grant DDL to cacheadm;
15111: Invalid privilege: DDL.  Roles are not supported.
The command failed.
con2: Command>
con2: Command>
con2: Command>
con2: Command> exit
Disconnecting from cachedb1_1122...
Disconnecting from con1...
Disconnecting from con2...
Done.
[oracle@bbmpde01 bin]$ cd ../info/
[oracle@bbmpde01 info]$ ls
cluster.oracle.ini  DBI5ac22dcf.0  DBI5ac230a1.2  DemoDataStore  snmp.ini  sys.odbc.ini  sys.ttconnect.ini  timestend.pid  ttcacheadvisor  ttendaemon.options  tterrors.log  ttmesg.log
[oracle@bbmpde01 info]$ vi sys.odbc.ini
[oracle@bbmpde01 info]$ cd ../bin
[oracle@bbmpde01 bin]$ ./ttisql

Copyright (c) 1996, 2015, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.


Command> connect "DSN=cachedb1_1122";
Connection successful: DSN=cachedb1_1122;UID=cacheadm;DataStore=/u01/oracle/products/timesten/TimesTen/tt1122/info/DemoDataStore/cachedb1_1122;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;DRIVER=/u01/oracle/products/timesten/TimesTen/tt1122/lib/libtten.so;PermSize=40;TempSize=32;TypeMode=0;OracleNetServiceName=OFSDB;
(Default setting AutoCommit=1)
Command> exit
Disconnecting...
Done.
[oracle@bbmpde01 bin]$
[oracle@bbmpde01 bin]$
[oracle@bbmpde01 bin]$
[oracle@bbmpde01 bin]$
[oracle@bbmpde01 bin]$
[oracle@bbmpde01 bin]$ cd ../infOracleId=swarn
bash: cd: ../infOracleId=swarn: No such file or directory
[oracle@bbmpde01 bin]$ cd ../info
[oracle@bbmpde01 info]$ vi sys.odbc.ini
[oracle@bbmpde01 info]$ cd ../bin/
[oracle@bbmpde01 bin]$ ./ttisql

Copyright (c) 1996, 2015, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.


Command> connect "DSN=cachedb1_1122";
Connection successful: DSN=cachedb1_1122;UID=cacheadm;DataStore=/u01/oracle/products/timesten/TimesTen/tt1122/info/DemoDataStore/cachedb1_1122;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;DRIVER=/u01/oracle/products/timesten/TimesTen/tt1122/lib/libtten.so;OracleId=swarn;PermSize=40;TempSize=32;TypeMode=0;OracleNetServiceName=OFSDB;
(Default setting AutoCommit=1)
Command> select * from dual;
< X >
1 row found.
Command>

How to resolve ORA-12012 or ORA-06508 or ORA-04067 or ORA-06512

During one of the  recent cloud migration I was facing lot of below error in alert logs:
========================================
PDB1(4):Errors in file /u01/app/oracle/diag/rdbms/TESTprd1/TESTPRD1/trace/TESTPRD1_j001_19167.trc:
ORA-12012: Fehler beim autom Ausführen von Job 3
ORA-04067: Ausführung nicht erfolgreich, package body "SYSMAN.EMD_MAINTENANCE" ist nicht vorhanden
ORA-06508: PL/SQL: aufgerufene Programmeinheit : "SYSMAN.EMD_MAINTENANCE" konnte nicht gefunden werden

===========================

Alert log is getting filled up with the above error causing DB down.

==
Reason: This is mainly because of broken Jobs in the Oracle scheduler.

Action taken:

SQL> select ERROR_NUMBER,MESSAGE from PDB_PLUG_IN_VIOLATIONS;

SQL> Select job,what,BROKEN,SCHEMA_USER from dba_jobs;

       JOB WHAT                                                                B SCHEMA_USER
---------- ------------------------------------------------------------------- - --------------------------------------------------------------------------------------------------------------------------------
         3 EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();                        N SYSMAN
      4001 wwv_flow_cache.purge_sessions(p_purge_sess_older_then_hrs => 24);   Y APEX_030200
      4002 wwv_flow.push_queue(wwv_flow_platform.get_preference('SMTP)      N APEX_030200
           T_ADDRESS'),wwv_flow_platform.get_preference('SMTP_HOST_PORT'));

        21 PA_VERTEILUNG.VERTEILE_DATEN;                                       Y CLOUDIT
        24 PA_REORG.REORGANISATION;                                            Y CLOUDIT
        22 PA_BERECHNUNG.ABMW;                                                 Y CLOUDIT
        23 PA_BERECHNUNG.DOSISKONTO;                                           Y CLOUDIT

7 rows selected.

Check for N

SQL>
SQL> exec sys.dbms_ijob.broken(3,TRUE);

PL/SQL procedure successfully completed.

SQL> exec sys.dbms_ijob.broken(4002,TRUE);

PL/SQL procedure successfully completed.

SQL> Select job,what,BROKEN,SCHEMA_USER from dba_jobs;

       JOB WHAT                                                                B SCHEMA_USER
---------- ------------------------------------------------------------------- - --------------------------------------------------------------------------------------------------------------------------------
         3 EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();                        Y SYSMAN
      4001 wwv_flow_cache.purge_sessions(p_purge_sess_older_then_hrs => 24);   Y APEX_030200
      4002 wwv_flow_mail.push_queue(wwv_flow_platform.get_preference('SMTP_HOS Y APEX_030200
           T_ADDRESS'),wwv_flow_platform.get_preference('SMTP_HOST_PORT'));

        21 PA_VERTEILUNG.VERTEILE_DATEN;                                       Y CLOUDIT
        24 PA_REORG.REORGANISATION;                                            Y CLOUDIT
        22 PA_BERECHNUNG.ABMW;                                                 Y CLOUDIT
        23 PA_BERECHNUNG.DOSISKONTO;                                           Y CLOUDIT

7 rows selected.

SQL>
SQL>
SQL>
SQL> exec sys.dbms_ijob.remove(4001);

PL/SQL procedure successfully completed.

SQL> exec sys.dbms_ijob.remove(4002);

PL/SQL procedure successfully completed.

SQL>  exec sys.dbms_ijob.remove(3);

PL/SQL procedure successfully completed.

SQL>
SQL> Select job,what,BROKEN,SCHEMA_USER from dba_jobs;

       JOB WHAT                                                                B SCHEMA_USER
---------- ------------------------------------------------------------------- - --------------------------------------------------------------------------------------------------------------------------------
        21 PA_VERTEILUNG.VERTEILE_DATEN;                                       Y CLOUDIT
        24 PA_REORG.REORGANISATION;                                            Y CLOUDIT
        22 PA_BERECHNUNG.ABMW;                                                 Y CLOUDIT
        23 PA_BERECHNUNG.DOSISKONTO;                                           Y CLOUDIT

SQL> exec sys.dbms_ijob.remove(21);

PL/SQL procedure successfully completed.

SQL> exec sys.dbms_ijob.remove(22);

PL/SQL procedure successfully completed.

SQL> exec sys.dbms_ijob.remove(23);

PL/SQL procedure successfully completed.

SQL> Select job,what,BROKEN,SCHEMA_USER from dba_jobs;

       JOB WHAT                                                                B SCHEMA_USER
---------- ------------------------------------------------------------------- - --------------------------------------------------------------------------------------------------------------------------------
        24 PA_REORG.REORGANISATION;                                            Y CLOUDIT

SQL>  exec sys.dbms_ijob.remove(24);

PL/SQL procedure successfully completed.

SQL>

Issue resolved:
SQL>
SQL> Select job,what,BROKEN,SCHEMA_USER from dba_jobs;

no rows selected

No more error in alert logs now..

Restart the DB and reschedule the jobs.