Disable PK constraint.
alter table TBL1 disable constraint PK_TBL1 ;

Delete PK index.
alter table TBL1 drop index PK_TBL1 ;

Create PK index.
create unique index “PK_TBL1” on “TBL1” (“INSPECTORID”, “DUTYID”, “INSPID”)
tablespace “TBLSPCINDX”
pctfree 10 initrans 2 maxtrans 255
storage
(
initial 64K
next 0K
minextents 1
maxextents 2147483645
pctincrease 0
freelists 1
freelist groups 1
)
nologging;

Enable PK constraint.
alter table “TBL1” enable constraint “PK_TBL1” ;

Oracle Architecture

The Oracle Instance

The memory structures and server processes that do the work in the database.

The System Global Area is a shared block of memory for Oracle’s use. At a minimum contains:

– Redo Log buffer: short term storage for redo information so it can be written to the redo logs.

– Shared Pool: further broken down into the library cache, which holds recently parsed and executed code, and the data dictionary cache, which store recently used object definitions.

– Database buffer cache: Oracle’s work area for executing SQL.

The instance also houses the processes

– System Monitor: Opening database, maintain connection between instance and database

– Database Writer: writes to the database files (writes as little as possible. Minimizing disk I/O for performance)

– Process Monitor: Monitors user sessions

– Log Writer: writes to the redo logs (writes as close to real time as possible. Ideally save all changes.)

– Checkpoint: ensure instance is synchronized with the database from time to time.

– Archiver: writes archived redo logs

The Oracle Database

The database refers to the physical files on the os that contain the data and data dictionary. At the minimum the database requires datafiles, control files, and redo logs.

Parameter File: Holds the parameters to start the instance

Password File: Encrypted file that holds the sysdba password. Allows sys to log on regardless of the state of the database.

Datafiles: Core of the database, the files that hold the data.

Control Files: Holds all the parameters used to connect the instance and database. For Example, pointers to the rest of the database (redo logs, datafiles…) and various data to maintain database integrity (scn and timestamp). Often multiplexed to allow recovery from file corruption.

Redo Log: maintains all changes made to the data over a given period of time or until the log is full. Often multiplexed to allow recovery from file corruption.

Archived Redo Logs: Copies of filled redo logs kept for recovery purposes.

(Special thanks to “Josh” for this information)

SELECT ‘alter index MYPROJ.’ || index_name || ‘ rebuild tablespace MYPROJINDX;’
FROM dba_indexes
WHERE owner = ‘MYPROJ’
AND tablespace_name != ‘MYPROJINDX’
ORDER BY index_name;

1. Log into the oracle instance using SQL*Plus or other oracle scripting tool as SYSTEM user.

2. Create roles SCHEMA_DEVELOPER and SCHEMA_USER.

CREATE ROLE “SCHEMA_DEVELOPER” NOT IDENTIFIED;

GRANT CREATE SESSION TO “SCHEMA_DEVELOPER”;
GRANT SELECT ANY DICTIONARY TO “SCHEMA_DEVELOPER”;
GRANT ALTER SESSION TO “SCHEMA_DEVELOPER”;
GRANT CREATE CLUSTER TO “SCHEMA_DEVELOPER”;
GRANT CREATE DATABASE LINK TO “SCHEMA_DEVELOPER”;
GRANT CREATE PROCEDURE TO “SCHEMA_DEVELOPER”;
GRANT CREATE PUBLIC SYNONYM TO “SCHEMA_DEVELOPER”;
GRANT CREATE SEQUENCE TO “SCHEMA_DEVELOPER”;
GRANT CREATE TABLE TO “SCHEMA_DEVELOPER”;
GRANT CREATE TRIGGER TO “SCHEMA_DEVELOPER”;
GRANT CREATE VIEW TO “SCHEMA_DEVELOPER”;
GRANT DROP PUBLIC SYNONYM TO “SCHEMA_DEVELOPER”;

CREATE ROLE “SCHEMA_USER” NOT IDENTIFIED;

GRANT CREATE SESSION TO “SCHEMA_USER”;
GRANT SELECT ANY DICTIONARY TO “SCHEMA_USER”;

3. Create the tablespaces (make sure to update the filepath for the datafile according to the server you are working with). Variables are enclosed in curley braces {}. Use this script as a guide:

CREATE TABLESPACE “COMMONDATA”
LOGGING
DATAFILE ‘{PATH_TO_DATAFOLDER}COMMONDATA01.DBF’
SIZE 10M REUSE
AUTOEXTEND ON
NEXT 50M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;

CREATE TABLESPACE “COMMONINDX”
NOLOGGING
DATAFILE ‘{PATH_TO_DATAFOLDER}COMMONINDX01.DBF’
SIZE 5M REUSE
AUTOEXTEND ON
NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;

4. Create the user and role using this script as a guide. (Note: if you are using Oracle 10g or higher you will need to remove lines that set quota unlimited on temp).

CREATE USER “COMMON”
profile “DEFAULT”
identified by “{COMMON_PASSWORD}”
default tablespace “COMMONDATA”
temporary tablespace “TEMP”
quota unlimited on COMMONDATA
quota unlimited on COMMONINDX
account UNLOCK;

CREATE ROLE “COMMON_TABLE_USER” NOT IDENTIFIED;

5. Open a command prompt window and import a copy of the common schema from a .dmp file. If you dont have a .dmp file find another instance with the common schema and export it. Use these scripts as a guide:

For importing the schema:

imp common/{COMMON_PASSWORD}@{INSTANCE_NAME} file=common.dmp log=commonimp.log fromuser=common touser=common

For exporting the schema:

exp common/{COMMON_PASSWORD}@{INSTANCE_NAME} file=common.dmp log=commonexp.log

7. Run the following scripts one at a time to generate grant scripts for the roles needed.
Select the statements that were created by these scripts and run them in order to grant the correct priveleges:

SELECT ‘grant SELECT, REFERENCES on “COMMON”.’ || table_name “Grant Privileges”, ‘TO ‘ || role || ‘;’ “To Role”
FROM dba_tables
, dba_roles
WHERE owner = ‘COMMON’
AND table_name = any (select table_name from dba_tables where table_name like ‘CL_%’)
AND role = any (select role from dba_roles where role like ‘COMMON%’)
ORDER BY role, table_name;

col “Grant Privileges” format a50
SELECT ‘grant SELECT, INSERT, UPDATE on “COMMON”.’ || table_name “Grant Privileges”, ‘TO ‘ || role || ‘;’ “To Role”
FROM dba_tables
, dba_roles
WHERE owner = ‘COMMON’
AND table_name = any (select table_name from dba_tables where table_name like ‘CT_%’)
AND role = any (select role from dba_roles where role like ‘COMMON%’)
ORDER BY role, table_name;

8. Grant schema role permissions:

GRANT SCHEMA_DEVELOPER TO COMMON;
GRANT COMMON_TABLE_USER TO SCHEMA_DEVELOPER;
GRANT COMMON_TABLE_USER TO SCHEMA_USER;