Oracle Database Discussions

to: who might have an idea or like to verify the below!!



1. In the area if the option Oracle Data Mining is installed and used we are in the search of additional information or a confirmation. If you have any input it is welcome!
First, as we understand in all versions from 8i, 9i, 10G and 11G the option Data Mining is present as part of Data Warehousing. Would be good to understand if this is correct. Next to that technically it seems per version different tables, schema's have been used to run this option.


Version 8:
no records found on what schema, tables, sources Data Mining uses. We might have overlooked it in the Server reference. Any idea or source what we can use?

Version 9:
ODM. ODM_MINING_MODEL table does not exist if it is not installed. This is an assumption and would be good to have a clarification

prompt Oracle Data Mining
SELECT
value
FROM V$OPTION
WHERE parameter = 'Oracle Data Mining';
SELECT
name,user_name
from ODM_MINING_MODEL;
SELECT
case when count(*)>0 then 'Oracle Data Mining option
used' else '' end
from ODM_MINING_MODEL;

Version 10G:
It seems the source schema and tables are:  dmsys.dm$p_model

Version 11G:
It seems the source schema and tables are: sys.model$


2. Concerning the option Advanced Security we kind of found scripts that should be able to see if this option is installed and in use. They all seem to work on versions  9, 10, 11. It would be good to get a confirmation if our assumptions are correct.

Version 8: 
no records found on what schema, tables, sources Advanced Security uses. We might have overlooked it in the Server reference. Any idea or source what we can use?

Version 9, 10G, 11G:
For network encryption and strong authentication
select count(*) from v$session_connect_info where network_service_banner in 'authentication service, encryption service, crypto_checksumming';

if > 0, network encryption and/or authentication is in use.

To see if TDE column encryption is used
'select count(*) from dba_encrypted_columns;'
returns more than zero rows.

To see if TDE tablespace encryption is used
'select count(*) from dba_tablespaces where encrypted ='YES';'

The scripts for TDE column encryption and TDE tablespace encryption don't need to be converted to 8i and 9i, since these features didn't exist at that time.

TDE column encryption available from: 10gR2 and later
TDE tablespace encryption available from: 11gR1 and later


3. Looking at Label Security we believe on all versions 8-11G the schema LBACSYS is present in the database when the option is installed. The following scripts seem to work, however we could not test it. Again it would be great to see if someone can confirm this.

prompt spatial
select
decode(owner,'OE',owner||' (2 Samples)',owner)
owner,count(*)
from(select distinct owner,table_name
from DBA_TAB_COLUMNS
where data_type='SDO_GEOMETRY' and owner not in ('MDSYS'))
group by owner;
SELECT
count(*),sdo_index_owner
from DBA_SDO_INDEX_METADATA group by sdo_index_owner;
SELECT
case when count(*)>0 then 'Spatial option used' else ''
end
from (select distinct owner,table_name
from DBA_TAB_COLUMNS
where data_type='SDO_GEOMETRY' and owner not in ('MDSYS'));