Oracle Class Setup
Version 12.2.0.1

General Information
Description This page contains the SQL statements that can be used in a 12cR1 Oracle database to create an environment compatible with running library code samples and demos. Some of the code in this section is repeated in the library but this page is designed to bring specific pieces together into a coherent structure to allow you to learn about and work with the new database CDB/PDB architecture.

The first instruction assumes you have already followed Oracle's published documentation and installed a 12cR1 database on Windows though everything here is compatible with a Linux or other install as well except for pathing. When doing so you created a CDB with a single PDB named PDBDEV that contains Oracle's sample schemas such as HR, OE, PM, SH, and SCOTT. A second PDB is created as part of the setup, as indicated below, into which our class user, UWCLASS, is created.
Edition Enterprise
Version 12.2.0.1.0
 
As ROOT
These alterations are recommended assuming the indicated conditions are met -- if the server has > 16GB RAM and SGA will be larger than 5GB configure HugePages
memory_target=0
memory_max_target=0
sga_target=<integer>           -- size based on your server's memory
pga_aggregate_target=<integer>
-- size based on your server's memory
run the script hugepages_settings.sh
-- copy the shell scripts output which will look something like this:
vm.nr_hugepages = 9999 to the last line of sysctl.conf
/etc/sysctl –p
-- validate the change has taken affect
grep Huge /proc/meminfo
 
As SYSDBA
Modify GLOGIN.SQL Find the file {ORACLE_HOME}/sqlplus/admin/glogin.sql and rename it to glogin.bak. Create a new file in the same directory named glogin.sql with the as follows contents.

set arraysize 250
set define off
set linesize 121
set long 1000000
set pagesize 45
set serveroutput on
set trim on
set trimspool on

col argument_name format a30
col cluster_name format a30
col col_name format a30
col column_name format a30
col constraint_name format a30
col container_name format a30
col data_type format a30
col db_link format a30
col directory_name format a30
col directory_path format a30
col edition_name format a30
col file_name format a60
col granted_role format a30
col grantee format a30
col host_name format a20
col index_name format a30
col iot_name format a30
col max_lag_time format a12
col name format a30
col object_name format a30
col object_type format a25
col owner format a25
col owner_name format a25
col package_name format a30
col param_name format a25
col partition_name format a30
col pdb format a20
col procedure_name format a30
col queue_table format a30
col role format a30
col schedule_name format a30
col segment_name format a30
col service_name format a30
col subobject_name format a30
col synonym_name format a30
col table_name format a30
col table_type format a30
col triggering_event format a35
col type_name format a30
col type_owner format a30
col type_subname format a30
col username format a30
col value format a30

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

set hist on

-- if *nix add this line too
define _editor=vi
Modify SQL*Plus (Windows only)
  1. After opening SQL*Plus in a terminal window right click on the window title: Select PROPERTIES
  2. On the Options tab page make sure the edit options "QuickEdit Mode" and "Insert Mode" are both selected
  3. On the Layout tab page set the Screen Buffer Size width to at least 161 and the height to at least 1000
  4. Set the Window size width to 201 and the height to 50
SQL*Plus Logon / as sysdba
Verify you are in the root of your Container Database (CDB) -- you must see the following:

SQL> sho con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> sho con_id

CON_ID
------------------------------
1
Create a view for monitoring the status of the containers inside your CDB CREATE OR REPLACE VIEW pdb_status AS
SELECT v.name, v.open_mode, NVL(v.restricted, 'n/a') "RESTRICTED", d.status
FROM v$pdbs v, dba_pdbs d
WHERE v.guid = d.guid
ORDER BY v.create_scn;

SQL> SELECT * FROM pdb_status;

-- you must see the following
NAME                           OPEN_MODE  RES STATUS
------------------------------ ---------- --- ------
PDB$SEED                       READ ONLY  NO  NORMAL
PDBDEV                         READ WRITE NO  NORMAL


-- if PDBDEV has an OPEN_MODE of MOUNT do the following:

SQL> ALTER PLUGGABLE DATABASE PDBDEV OPEN;
Log into the PDBDEV PDB SQL> SELECT name
   2 FROM v$active_services
   3 ORDER BY 1;

NAME
------------------------------
SYS$BACKGROUND
SYS$USERS
orabase
orabaseXDB
pdbdev


-- listener changes ... then stop and restart
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = c:\app\oracle\product\12.1.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:c:\app\oracle\product\12.1.0\dbhome_1\bin\oraclr12.dll")
    )
    (SID_DESC =
      (SID_NAME = PDBDEV)
      (ORACLE_HOME = c:\app\oracle\product\12.1.0\dbhome_1)
    )
    (SID_DESC =
      (SID_NAME = PDBTEST)
      (ORACLE_HOME = c:\app\oracle\product\12.1.0\dbhome_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = PERRITO4)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )

ADR_BASE_LISTENER = C:\app\oracle

-- it may be necessary to configure listener.ora and tnsnames.ora to 127.0.0.1
-- and restart the listener ... check in final release add an entry into tnsnames.ora file


PDBDEV =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDBDEV)
      )
  )
Create a new PDB: ORADEV -- create pdb attributes method
CREATE PLUGGABLE DATABASE oradev
ADMIN USER devdba IDENTIFIED BY devdba
FILE_NAME_CONVERT = ('\pdbseed\', '\oradev\')
STORAGE (MAXSIZE 400M MAX_SHARED_TEMP_SIZE 100M)
DEFAULT TABLESPACE oradata DATAFILE 'c:\app\oracle\oradata\orabase\oradev\oradata01.dbf' SIZE 100M;

SELECT * FROM pdb_status;

ALTER PLUGGABLE DATABASE oradev OPEN;

SELECT * FROM pdb_status;
Create another new PDB: ORATEST -- create pdb clone method
CREATE PLUGGABLE DATABASE pdbtest FROM pdbdev
FILE_NAME_CONVERT = ('\pdbdev\', '\pdbtest\');
CREATE PLUGGABLE DATABASE pdbtest FROM pdbdev
*
ERROR at line 1:
ORA-65081: database or pluggable database is not open in read only mode


ALTER PLUGGABLE DATABASE pdbdev CLOSE;

SELECT * FROM pdb_status;

ALTER PLUGGABLE DATABASE pdbdev OPEN READ ONLY;

SELECT * FROM pdb_status;

CREATE PLUGGABLE DATABASE pdbtest FROM pdbdev
FILE_NAME_CONVERT = ('\pdbdev\', '\pdbtest\');

ALTER PLUGGABLE DATABASE pdbdev CLOSE;
ALTER PLUGGABLE DATABASE ALL OPEN;

SELECT * FROM pdb_status;
Create tablespace and UW class user in all three PDBs ALTER SESSION SET CONTAINER = pdbdev;

SELECT sys_context('USERENV', 'CON_NAME') FROM dual;


-- make sure PDBDEV is open and, if not, ALTER PLUGGABLE DATABASE OPEN;

ALTER PLUGGABLE DATABASE SET DEFAULT BIGFILE TABLESPACE;

SELECT file_name FROM dba_data_files;

-- if the following exception is raised (red), change the storage maxsize for the PDB as
-- shown and repeat the tablespace creation. If no exception continue to the next step

CREATE TABLESPACE uwdata
DATAFILE 'c:\app\oracle\oradata\orabase\pdbdev\uwdata01.dbf' SIZE 100M
LOGGING FORCE LOGGING ONLINE PERMANENT BLOCKSIZE 8K
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE uwdata
*
ERROR at line 1:
ORA-65114: space usage in container is too high


SELECT file_name, bytes/1024/1024 AS MB
FROM dba_data_files;

FILE_NAME                                               MB
--------------------------------------------------- ------
C:\APP\ORACLE\ORADATA\ORABASE\PDBDEV\UNDOTBS01.DBF     100
C:\APP\ORACLE\ORADATA\ORABASE\PDBDEV\SYSAUX01.DBF      360
C:\APP\ORACLE\ORADATA\ORABASE\PDBDEV\SYSTEM01.DBF      250
C:\APP\ORACLE\ORADATA\ORABASE\PDBDEV\USERS01.DBF         5

ALTER PLUGGABLE DATABASE pdbdev STORAGE (MAXSIZE 500M);

SELECT file_name, bytes/1024/1024 AS MB
FROM dba_data_files;

FILE_NAME                                                     MB
----------------------------------------------------- ----------
C:\APP\ORACLE\ORADATA\ORABASE\PDBDEV\UNDOTBS01.DBF         100
C:\APP\ORACLE\ORADATA\ORABASE\PDBDEV\SYSAUX01.DBF          360
C:\APP\ORACLE\ORADATA\ORABASE\PDBDEV\SYSTEM01.DBF          250
C:\APP\ORACLE\ORADATA\ORABASE\PDBDEV\USERS01.DBF             5
C:\APP\ORACLE\ORADATA\ORABASE\PDBDEV\UWDATA01.DBF          250

/*
ALTER PROFILE UWDEFAULT
composite_limit                   1
sessions_per_user                 1
cpu_per_session                   1
cpu_per_call                      1
logical_reads_per_session         1
logical_reads_per_call            1
idle_time                         1
connect_time                      1
private_sga                       1
failed_login_attempts             1
password_life_time                1
password_reuse_time            9999
password_reuse_max                0
password_verify_function       ora12c_strong_verify_function;
password_lock_time             unlimited
password_grace_time               0
inactive_account_time             1
container=current;


CREATE PROFILE pdbdba
failed_login_attempts       3
inactive_account_time      30
password_life_time         60
password_reuse_max          0
password_reuse_time      9999
password_verify_function ora12c_strong_verify_function;
*/


CREATE USER uwclass
IDENTIFIED BY uwclass
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
QUOTA 0 ON SYSTEM
QUOTA 0 ON SYSAUX
QUOTA UNLIMITED ON uwdata
PROFILE default
ACCOUNT UNLOCK
ENABLE EDITIONS
CONTAINER=CURRENT;

GRANT create session TO uwclass;
GRANT create cluster TO uwclass;
GRANT create database link TO uwclass;
GRANT create operator TO uwclass;
GRANT create procedure TO uwclass;
GRANT create role TO uwclass;
GRANT create sequence TO uwclass;
GRANT create synonym TO uwclass;
GRANT create table TO uwclass;
GRANT create trigger TO uwclass;
GRANT create type TO uwclass;
GRANT create view TO uwclass;
GRANT execute ON dbms_lock TO uwclass;
GRANT select any dictionary TO uwclass;
Build Sample Schemas -- create the user scott with password tiger
-- someone at Oracle didn't take the time to test their code and the script
-- {$ORACLE_HOME/rdbms/admin/scott.sql will fail in a PDB because the scott
-- logon is invalid by any criterion. Therefore here's how you reinstall scott.

CREATE USER scott
IDENTIFIED BY tiger
TEMPORARY TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMTED ON users;

GRANT create session, create table TO scott;

-- open the scott.sql script in an editor and copy the CREATE TABLE and INSERT
-- statements into SQL*Plus and execute them. Now you have scott and Oracle
-- should hang its head in shame. The script utlsamp.sql is equally deplorable.

-- https://github.com/oracle/db-sample-schemas/releases/tag/v12.2.0.1
Revoke unnecessary grants to PUBLIC SELECT 'REVOKE SELECT ON ' || table_name || ' FROM PUBLIC;' AS RUN_SCRIPT
FROM dba_tab_privs
WHERE grantee = 'PUBLIC'
AND table_name LIKE 'DBA%'
ORDER BY 1;

RUN_SCRIPT
-------------------------------------------------------------
REVOKE SELECT ON DBA_AUTO_SEGADV_CTL FROM PUBLIC;
REVOKE SELECT ON DBA_AUTO_SEGADV_SUMMARY FROM PUBLIC;
REVOKE SELECT ON DBA_COL_PENDING_STATS FROM PUBLIC;
REVOKE SELECT ON DBA_COL_USAGE_STATISTICS FROM PUBLIC;
REVOKE SELECT ON DBA_DBFS_HS_FIXED_PROPERTIES FROM PUBLIC;
REVOKE SELECT ON DBA_EDITIONING_VIEW_COLS FROM PUBLIC;
REVOKE SELECT ON DBA_EDITIONING_VIEW_COLS_AE FROM PUBLIC;
REVOKE SELECT ON DBA_EXPRESSION_STATISTICS FROM PUBLIC;
REVOKE SELECT ON DBA_FLASHBACK_ARCHIVE FROM PUBLIC;
REVOKE SELECT ON DBA_FLASHBACK_ARCHIVE_TABLES FROM PUBLIC;
REVOKE SELECT ON DBA_FLASHBACK_ARCHIVE_TS FROM PUBLIC;
REVOKE SELECT ON DBA_HEAT_MAP_SEGMENT FROM PUBLIC;
REVOKE SELECT ON DBA_HEAT_MAP_SEG_HISTOGRAM FROM PUBLIC;
REVOKE SELECT ON DBA_IND_PENDING_STATS FROM PUBLIC;
REVOKE SELECT ON DBA_JAVA_CLASSES FROM PUBLIC;
REVOKE SELECT ON DBA_SDO_MAPS FROM PUBLIC;
REVOKE SELECT ON DBA_SDO_STYLES FROM PUBLIC;
REVOKE SELECT ON DBA_SDO_THEMES FROM PUBLIC;
REVOKE SELECT ON DBA_SR_PARTN_OPS FROM PUBLIC;
REVOKE SELECT ON DBA_SR_STLOG_STATS FROM PUBLIC;
REVOKE SELECT ON DBA_SYNC_CAPTURE_TABLES FROM PUBLIC;
REVOKE SELECT ON DBA_TAB_HISTGRM_PENDING_STATS FROM PUBLIC;
REVOKE SELECT ON DBA_TAB_PENDING_STATS FROM PUBLIC;
REVOKE SELECT ON DBA_TAB_STAT_PREFS FROM PUBLIC;
REVOKE SELECT ON DBA_TSTZ_TABLES FROM PUBLIC;
REVOKE SELECT ON DBA_XMLSCHEMA_LEVEL_VIEW FROM PUBLIC;

-- bold faced REVOKES, below, are based on specific known security threats

set pagesize 300
 
SELECT UNIQUE 'REVOKE EXECUTE ON ' || table_name || ' FROM PUBLIC;' AS RUN_SCRIPT
FROM dba_tab_privs dtp
WHERE dtp.grantee = 'PUBLIC'
AND dtp.privilege = 'EXECUTE'
AND dtp.type = 'PACKAGE'
AND ((dtp.table_name LIKE 'DBMS%') OR (dtp.table_name LIKE 'UTL%'))
ORDER BY 1;

RUN_SCRIPT
-------------------------------------------------------------
EVOKE EXECUTE ON DBMS_ADDM FROM PUBLIC;
REVOKE EXECUTE ON DBMS_ADVISOR FROM PUBLIC;
REVOKE EXECUTE ON DBMS_APPLICATION_INFO FROM PUBLIC;
REVOKE EXECUTE ON DBMS_APP_CONT_PRVT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_AQJMS FROM PUBLIC;
REVOKE EXECUTE ON DBMS_AQ_EXP_CMT_TIME_TABLES FROM PUBLIC;
REVOKE EXECUTE ON DBMS_AQ_EXP_DEQUEUELOG_TABLES FROM PUBLIC;
REVOKE EXECUTE ON DBMS_AQ_EXP_HISTORY_TABLES FROM PUBLIC;
REVOKE EXECUTE ON DBMS_AQ_EXP_INDEX_TABLES FROM PUBLIC;
REVOKE EXECUTE ON DBMS_AQ_EXP_QUEUES FROM PUBLIC;
REVOKE EXECUTE ON DBMS_AQ_EXP_QUEUE_TABLES FROM PUBLIC;
REVOKE EXECUTE ON DBMS_AQ_EXP_SIGNATURE_TABLES FROM PUBLIC;
REVOKE EXECUTE ON DBMS_AQ_EXP_SUBSCRIBER_TABLES FROM PUBLIC;
REVOKE EXECUTE ON DBMS_AQ_EXP_TIMEMGR_TABLES FROM PUBLIC;
REVOKE EXECUTE ON DBMS_AQ_IMP_INTERNAL FROM PUBLIC;
REVOKE EXECUTE ON DBMS_AQ_INV FROM PUBLIC;
REVOKE EXECUTE ON DBMS_ASSERT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_AUTO_REPORT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_AUTO_TASK FROM PUBLIC;
REVOKE EXECUTE ON DBMS_AW FROM PUBLIC;
REVOKE EXECUTE ON DBMS_AW_EXP FROM PUBLIC;
REVOKE EXECUTE ON DBMS_AW_STATS FROM PUBLIC;
REVOKE EXECUTE ON DBMS_AW_XML FROM PUBLIC;
REVOKE EXECUTE ON DBMS_CDC_ISUBSCRIBE FROM PUBLIC;
REVOKE EXECUTE ON DBMS_CDC_SUBSCRIBE FROM PUBLIC;
REVOKE EXECUTE ON DBMS_CLOBUTIL FROM PUBLIC;
REVOKE EXECUTE ON DBMS_COMPRESSION FROM PUBLIC;
REVOKE EXECUTE ON DBMS_CREDENTIAL FROM PUBLIC;
REVOKE EXECUTE ON DBMS_CRYPTO_TOOLKIT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_CSX_INT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_CSX_INT2 FROM PUBLIC;
REVOKE EXECUTE ON DBMS_CUBE FROM PUBLIC;
REVOKE EXECUTE ON DBMS_CUBE_ADVISE FROM PUBLIC;
REVOKE EXECUTE ON DBMS_CUBE_ADVISE_SEC FROM PUBLIC;
REVOKE EXECUTE ON DBMS_CUBE_EXP FROM PUBLIC;
REVOKE EXECUTE ON DBMS_CUBE_LOG FROM PUBLIC;
REVOKE EXECUTE ON DBMS_CUBE_UTIL FROM PUBLIC;
REVOKE EXECUTE ON DBMS_DATAPUMP FROM PUBLIC;
REVOKE EXECUTE ON DBMS_DATA_MINING FROM PUBLIC;
REVOKE EXECUTE ON DBMS_DATA_MINING_TRANSFORM FROM PUBLIC;
REVOKE EXECUTE ON DBMS_DB_VERSION FROM PUBLIC;
REVOKE EXECUTE ON DBMS_DDL FROM PUBLIC;
REVOKE EXECUTE ON DBMS_DEBUG FROM PUBLIC;
REVOKE EXECUTE ON DBMS_DEBUG_JDWP FROM PUBLIC;
REVOKE EXECUTE ON DBMS_DEBUG_JDWP_CUSTOM FROM PUBLIC;
REVOKE EXECUTE ON DBMS_DESCRIBE FROM PUBLIC;
REVOKE EXECUTE ON DBMS_DIMENSION FROM PUBLIC;
REVOKE EXECUTE ON DBMS_DM_MODEL_EXP FROM PUBLIC;
REVOKE EXECUTE ON DBMS_DM_MODEL_IMP FROM PUBLIC;
REVOKE EXECUTE ON DBMS_EDITIONS_UTILITIES FROM PUBLIC;
REVOKE EXECUTE ON DBMS_EPG FROM PUBLIC;
REVOKE EXECUTE ON DBMS_ERRLOG FROM PUBLIC;
REVOKE EXECUTE ON DBMS_EXPORT_EXTENSION FROM PUBLIC;
REVOKE EXECUTE ON DBMS_FBT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_FILE_GROUP_EXP FROM PUBLIC;
REVOKE EXECUTE ON DBMS_FILE_GROUP_IMP FROM PUBLIC;
REVOKE EXECUTE ON DBMS_FREQUENT_ITEMSET FROM PUBLIC;
REVOKE EXECUTE ON DBMS_GOLDENGATE_EXP FROM PUBLIC;
REVOKE EXECUTE ON DBMS_GOLDENGATE_IMP FROM PUBLIC;
REVOKE EXECUTE ON DBMS_GSM_NOPRIV FROM PUBLIC;
REVOKE EXECUTE ON DBMS_HEAT_MAP FROM PUBLIC;
REVOKE EXECUTE ON DBMS_HIERARCHY FROM PUBLIC;
REVOKE EXECUTE ON DBMS_HS_PARALLEL FROM PUBLIC;
REVOKE EXECUTE ON DBMS_ILM FROM PUBLIC;
REVOKE EXECUTE ON DBMS_INDEX_UTL FROM PUBLIC;
REVOKE EXECUTE ON DBMS_INMEMORY FROM PUBLIC;
REVOKE EXECUTE ON DBMS_ITRIGGER_UTL FROM PUBLIC;
REVOKE EXECUTE ON DBMS_JAVA FROM PUBLIC;
REVOKE EXECUTE ON DBMS_JAVASCRIPT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_JOB FROM PUBLIC;
REVOKE EXECUTE ON DBMS_JSON FROM PUBLIC;
REVOKE EXECUTE ON DBMS_LCR FROM PUBLIC;
REVOKE EXECUTE ON DBMS_LDAP FROM PUBLIC;
REVOKE EXECUTE ON DBMS_LDAP_UTL FROM PUBLIC;
REVOKE EXECUTE ON DBMS_LOB FROM PUBLIC;
REVOKE EXECUTE ON DBMS_LOBUTIL FROM PUBLIC;
REVOKE EXECUTE ON DBMS_LOGREP_EXP FROM PUBLIC;
REVOKE EXECUTE ON DBMS_LOGREP_IMP FROM PUBLIC;
REVOKE EXECUTE ON DBMS_LOGSTDBY_CONTEXT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_MACOLS_SESSION FROM PUBLIC;
REVOKE EXECUTE ON DBMS_MACSEC_ROLES FROM PUBLIC;
REVOKE EXECUTE ON DBMS_MDX_ODBO FROM PUBLIC;
REVOKE EXECUTE ON DBMS_METADATA FROM PUBLIC;
REVOKE EXECUTE ON DBMS_METADATA_DIFF FROM PUBLIC;
REVOKE EXECUTE ON DBMS_MVIEW_STATS FROM PUBLIC;
REVOKE EXECUTE ON DBMS_NETWORK_ACL_UTILITY FROM PUBLIC;
REVOKE EXECUTE ON DBMS_OBFUSCATION_TOOLKIT FROM PUBLIC;

REVOKE EXECUTE ON DBMS_OBJECTS_UTILS FROM PUBLIC;
REVOKE EXECUTE ON DBMS_ODCI FROM PUBLIC;
REVOKE EXECUTE ON DBMS_OUTPUT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_PARALLEL_EXECUTE FROM PUBLIC;
REVOKE EXECUTE ON DBMS_PART FROM PUBLIC;
REVOKE EXECUTE ON DBMS_PCLXUTIL FROM PUBLIC;
REVOKE EXECUTE ON DBMS_PICKLER FROM PUBLIC;
REVOKE EXECUTE ON DBMS_PLSQL_CODE_COVERAGE FROM PUBLIC;
REVOKE EXECUTE ON DBMS_PREDICTIVE_ANALYTICS FROM PUBLIC;
REVOKE EXECUTE ON DBMS_PREPROCESSOR FROM PUBLIC;
REVOKE EXECUTE ON DBMS_PROFILER FROM PUBLIC;
REVOKE EXECUTE ON DBMS_PSP FROM PUBLIC;
REVOKE EXECUTE ON DBMS_RANDOM FROM PUBLIC;
REVOKE EXECUTE ON DBMS_REFRESH FROM PUBLIC;
REVOKE EXECUTE ON DBMS_REPORT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_RESCONFIG FROM PUBLIC;
REVOKE EXECUTE ON DBMS_RESOURCE_MANAGER FROM PUBLIC;
REVOKE EXECUTE ON DBMS_RESOURCE_MANAGER_PRIVS FROM PUBLIC;
REVOKE EXECUTE ON DBMS_RESULT_CACHE_API FROM PUBLIC;
REVOKE EXECUTE ON DBMS_RMGR_GROUP_EXPORT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_RMGR_PACT_EXPORT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_RMGR_PLAN_EXPORT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_RMIN FROM PUBLIC;
REVOKE EXECUTE ON DBMS_ROWID FROM PUBLIC;
REVOKE EXECUTE ON DBMS_RULE FROM PUBLIC;
REVOKE EXECUTE ON DBMS_RULEADM_INTERNAL FROM PUBLIC;
REVOKE EXECUTE ON DBMS_RULE_ADM FROM PUBLIC;
REVOKE EXECUTE ON DBMS_RULE_EXP_EV_CTXS FROM PUBLIC;
REVOKE EXECUTE ON DBMS_RULE_EXP_RULES FROM PUBLIC;
REVOKE EXECUTE ON DBMS_RULE_EXP_RULE_SETS FROM PUBLIC;
REVOKE EXECUTE ON DBMS_RULE_EXP_UTLI FROM PUBLIC;
REVOKE EXECUTE ON DBMS_RULE_IMP_OBJ FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SCHEDULER FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SCHED_ATTRIBUTE_EXPORT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SCHED_CHAIN_EXPORT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SCHED_CLASS_EXPORT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SCHED_CONSTRAINT_EXPORT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SCHED_CREDENTIAL_EXPORT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SCHED_EXPORT_CALLOUTS FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SCHED_FILE_WATCHER_EXPORT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SCHED_JOB_EXPORT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SCHED_PROGRAM_EXPORT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SCHED_SCHEDULE_EXPORT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SCHED_WINDOW_EXPORT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SCHED_WINGRP_EXPORT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SCN FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SESSION FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SNAPSHOT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SNAPSHOT_UTL FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SODA_DOM FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SPACE FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SPD FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SPM FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SQL FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SQLDIAG FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SQLPA FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SQLTUNE FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SQLTUNE_UTIL2 FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SQL_MONITOR FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SQL_TRANSLATOR FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SQL_TRANSLATOR_EXPORT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_STANDARD FROM PUBLIC;
REVOKE EXECUTE ON DBMS_STATS FROM PUBLIC;
REVOKE EXECUTE ON DBMS_STATS_ADVISOR FROM PUBLIC;
REVOKE EXECUTE ON DBMS_STAT_FUNCS FROM PUBLIC;
REVOKE EXECUTE ON DBMS_STAT_FUNCS_AUX FROM PUBLIC;
REVOKE EXECUTE ON DBMS_STREAMS FROM PUBLIC;
REVOKE EXECUTE ON DBMS_STREAMS_PUB_RPC FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SUMMARY FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SUM_RWEQ_EXPORT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SYNC_REFRESH FROM PUBLIC;
REVOKE EXECUTE ON DBMS_TF FROM PUBLIC;
REVOKE EXECUTE ON DBMS_TRACE FROM PUBLIC;
REVOKE EXECUTE ON DBMS_TRANSACTION FROM PUBLIC;
REVOKE EXECUTE ON DBMS_TRANSFORM_EXIMP FROM PUBLIC;
REVOKE EXECUTE ON DBMS_TYPES FROM PUBLIC;
REVOKE EXECUTE ON DBMS_UTILITY FROM PUBLIC;
REVOKE EXECUTE ON DBMS_WARNING FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XA FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XDB FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XDBNFS FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XDBRESOURCE FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XDBUTIL_INT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XDBZ FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XDBZ0 FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XDB_CONFIG FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XDB_CONSTANTS FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XDB_CONTENT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XDB_PRINT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XDB_REPOS FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XDB_VERSION FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XEVENT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XLSB FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XMLDOM FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XMLGEN FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XMLINDEX FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XMLINDEX0 FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XMLPARSER FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XMLQUERY FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XMLSAVE FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XMLSCHEMA FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XMLSCHEMA_ANNOTATE FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XMLSCHEMA_INT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XMLSCHEMA_LSB FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XMLSTORAGE_MANAGE FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XMLSTORE FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XMLTRANSLATIONS FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XPLAN FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XQUERY FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XQUERYINT FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XSLPROCESSOR FROM PUBLIC;
REVOKE EXECUTE ON DBMS_XS_SESSIONS FROM PUBLIC;
REVOKE EXECUTE ON DBMS_ZHELP_IR FROM PUBLIC;
REVOKE EXECUTE ON UTL_CALL_STACK FROM PUBLIC;
REVOKE EXECUTE ON UTL_COLL FROM PUBLIC;
REVOKE EXECUTE ON UTL_COMPRESS FROM PUBLIC;
REVOKE EXECUTE ON UTL_ENCODE FROM PUBLIC;
REVOKE EXECUTE ON UTL_FILE FROM PUBLIC;
REVOKE EXECUTE ON UTL_GDK FROM PUBLIC;
REVOKE EXECUTE ON UTL_HTTP FROM PUBLIC;
REVOKE EXECUTE ON UTL_I18N FROM PUBLIC;
REVOKE EXECUTE ON UTL_IDENT FROM PUBLIC;
REVOKE EXECUTE ON UTL_INADDR FROM PUBLIC;
REVOKE EXECUTE ON UTL_LMS FROM PUBLIC;
REVOKE EXECUTE ON UTL_MATCH FROM PUBLIC;
REVOKE EXECUTE ON UTL_NLA FROM PUBLIC;
REVOKE EXECUTE ON UTL_RAW FROM PUBLIC;
REVOKE EXECUTE ON UTL_REF FROM PUBLIC;
REVOKE EXECUTE ON UTL_SMTP FROM PUBLIC;
REVOKE EXECUTE ON UTL_TCP FROM PUBLIC;
REVOKE EXECUTE ON UTL_URL FROM PUBLIC;

set pagesize 45

SELECT 'REVOKE SELECT ON ' || table_name || ' FROM PUBLIC;' AS RUN_SCRIPT
FROM dba_tab_privs
WHERE grantee = 'PUBLIC'
AND table_name LIKE 'ALL%'
ORDER BY 1;

REVOKE SELECT ON ALL_ALL_TABLES FROM PUBLIC;
REVOKE SELECT ON ALL_DB_LINKS FROM PUBLIC;
REVOKE SELECT ON ALL_EDITIONING_VIEWS_AE FROM PUBLIC;
REVOKE SELECT ON ALL_ENCRYPTED_COLUMNS FROM PUBLIC;
REVOKE SELECT ON ALL_JAVA_ARGUMENTS FROM PUBLIC;
REVOKE SELECT ON ALL_OBJECTS FROM PUBLIC;
REVOKE SELECT ON ALL_OBJECTS_AE FROM PUBLIC;
REVOKE SELECT ON ALL_OPERATORS FROM PUBLIC;
REVOKE SELECT ON ALL_OPERATOR_COMMENTS FROM PUBLIC;
REVOKE SELECT ON ALL_PROCEDURES FROM PUBLIC;
REVOKE SELECT ON ALL_SOURCE FROM PUBLIC;
REVOKE SELECT ON ALL_SOURCE_AE FROM PUBLIC;
Alter init params ALTER SYSTEM SET global_names = TRUE CONTAINER = CURRENT SCOPE = BOTH;
ALTER SYSTEM SET remote_login_passwordfile = NONE  CONTAINER = ALL SCOPE = SPFILE;
ALTER SYSTEM SET sec_max_failed_login_attempts = 3 CONTAINER = ALL SCOPE = SPFILE;
ALTER SYSTEM SET sec_protocol_error_further_action = 1 CONTAINER = ALL SCOPE = SPFILE;
ALTER SYSTEM SET sec_protocol_error_trace_action = log CONTAINER = CURRENT SCOPE = BOTH;
ALTER SYSTEM SET use_large_pages = TRUE CONTAINER = ALL SCOPE = SPFILE;


-- restart the database so these changes take effect immediately
-- the alteraction for sec_protocol_error_trace_action needs to performed individually for each container
Multiplex Redo Logs ALTER DATABASE ADD LOGFILE MEMBER
'c:\app\oracle\recovery_area\orabase\REDO01b.LOG' TO GROUP 1;

ALTER DATABASE ADD LOGFILE MEMBER
'c:\app\oracle\recovery_area\orabase\REDO02b.LOG' TO GROUP 2;

ALTER DATABASE ADD LOGFILE MEMBER
'c:\app\oracle\recovery_area\orabase\REDO03b.LOG' TO GROUP 3;

ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;

SELECT group#, status
FROM v$log;

SELECT group#, status
FROM v$logfile;
Enhance AWR -- make the SYSAUX tablespace autoextendable if it is not.
ALTER DATABASE DATAFILE 'C:\APP\ORACLE\ORADATA\ORABASE2\SYSAUX01.DBF' AUTOEXTEND ON;

-- get the database's dbid
SELECT dbid
FROM v$database;

SELECT retention, snap_interval, topnsql
FROM wrm$_wr_control
WHERE dbid = (SELECT dbid FROM v$database);

-- set retention to 30 days, interval to 20 minutes, and topnsql to 50,000
exec dbms_workload_repository.modify_snapshot_settings((24*60*31), 20, 50000, 428676178);

SELECT retention, snap_interval, topnsql
FROM wrm$_wr_control
WHERE dbid = (SELECT dbid FROM v$database);
Collect stats -- collect system stats
SELECT pname, pval1
FROM aux_stats$
WHERE sname = 'SYSSTATS_MAIN';

exec dbms_stats.gather_system_stats('INTERVAL', 15);

SELECT pname, pval1
FROM aux_stats$
WHERE sname = 'SYSSTATS_MAIN';


-- fixed object stats
exec dbms_stats.gather_fixed_objects_stats;

-- gather dictionary stats
exec dbms_stats.gather_dictionary_stats;
Secure the file system (perform actions as root)
Location Command  
cd $ORACLE_BASE/ chmod -R 600 audit  
cd $ORACLE_BASE/diag/rdbms/orabase/orabase chmod -R 700 trace  
cd $ORACLE_HOME/network/admin chmod 644 listener.ora sqlnet.ora  
cd $ORACLE_HOME/bin chmod 750 sqlplus SQL*Plus command line utility
cd $ORACLE_HOME/bin chmod 750 exp imp Database export and import
cd $ORACLE_HOME/bin chmod 750 expdp impdp Datapump export and import
cd $ORACLE_HOME/bin chmod 750 orapwd dg4pwd Password file generators
cd $ORACLE_HOME/bin chmod 750 sqlldr SQL*Loader
cd $ORACLE_HOME/bin chmod 750 dbca dbua netca netmgr lsnrctl  
cd $ORACLE_HOME/bin chmod 750 kfed kfod  
cd $ORACLE_HOME/bin chmod 750 oraenv Oracle environment control
cd $ORACLE_HOME/bin chmod 750 owm Oracle Wallet Manager
cd $ORACLE_HOME/bin chmod 750 wrap PL/SQL code wrap utility
Secure the Listener -- add the following lines to the listener.ora file at $ORACLE_HOME/network/admin
ADMIN_RESTRICTIONS_LISTENER_NAME = ON
LOGGING_LISTENER = ON
SECURE_CONTROL_LISTENER = (TCPS, IPC)
SECURE_PROTOCOL_LISTENER = (TCPS, IPC)
SECURE_REGISTER_LISTENER = (TCP, IPC)
TRACE_DIRECTORY_LISTENER = /app/oracle/product/12.2.0/db_1/network/trace
TRACE_FILE_LISTENER = listener.trc


-- add the following lines to the sqlnet.ora file at $ORACLE_HOME/network/admin
ALLOW_LOGON_VERSION = 12
TRACE_DIRECTORY_CLIENT = ORACLE_BASE/diag/clients/user_oracle
TRACE_DIRECTORY_SERVER = ORACLE_BASE/diag/tnslsnr
Secure the Database -- prevent access from remotely created db links
-- later, when running db link demos, you will need to allow connections

exec dbms_distributed_trust_admin.deny_all;
Set Stats Collection Preferences exec dbms_stats.set_database_prefs('CASCADE', 'DBMS_STATS.AUTO_CASCADE');
exec dbms_stats.set_database_prefs('DEGREE','2');
Create Non-Installation Roles conn sys@pdbdev as sysdba

-- role for autotrace
@?/sqlplus/admin/plustrce.sql

-- role for tkprof
@?/rdbms/admin/utltkprf.sql

GRANT plustrace TO uwclass;
GRANT tkprofer TO uwclass;
 
As UWCLASS
Create Class Schema Objects conn uwclass/uwclas@pdbdev

Create the airplanes tables: Click Here

-- save this file as
c:\temp\airplanes.sql

Create the servers and serv_inst tables: Click Here

-- save this file as
c:\temp\servers.sql

Create the postal_code table: Click Here

-- save this file as
c:\temp\postal_code.sql

-- logged on as uwclass create the airplanes table
SQL> @c:\temp\airplanes.sql

-- logged on as uwclass create the servers and serv_inst tables
SQL> @c:\temp\servers.sql

-- logged on as uwclass create the postal_code table
SQL> @c:\temp\postal.sql

Related Topics
Container Database
SQL*Plus
Tables
Tablespaces
Users
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved