Monday, June 30, 2008

CHANGE_TABLES

CHANGE_TABLES describes existing change tables. The user must have the SELECT_CATALOG_ROLE privilege to access this view. This view is intended for use by Change Data Capture publishers.

Column

Datatype

NULL

Description

CHANGE_TABLE_SCHEMA

VARCHAR2(30)

NOT NULL

Change table owner

CHANGE_TABLE_NAME

VARCHAR2(30)

NOT NULL

Change table name

CHANGE_SET_NAME

VARCHAR2(30)

NOT NULL

The change set this table belongs to

SOURCE_SCHEMA_NAME

VARCHAR2(30)

NOT NULL

The table owner in the source system

SOURCE_TABLE_NAME

VARCHAR2(30)

NOT NULL

The name of the corresponding table in the source system

CREATED

DATE

NOT NULL

Date on which this change table was created

CREATED_SCN

NUMBER

System change number (SCN) when this table was created

CAPTURED_VALUES

VARCHAR2(1)

NOT NULL

Indicates the values O (old), N (new), or B (both)

PUB_ID

NUMBER

NOT NULL

Publication ID for subscribers

database_properties

DATABASE_PROPERTIES lists Permanent database properties.

Column

Datatype

NULL

Description

PROPERTY_NAME

VARCHAR2(30)

NOT NULL

Property name

PROPERTY_VALUE

VARCHAR2(4000)

Property value

DESCRIPTION

VARCHAR2(4000)

Property description

SELECT *
FROM database_properties
ORDER BY 1;


PROPERTY_NAME

PROPERTY_VALUE

DESCRIPTION

DBTIMEZONE

-07:00

DB time zone

DEFAULT_PERMANENT_TABLESPACE

USERS

Default Permanent Tablespace ID

DEFAULT_TBS_TYPE

SMALLFILE

Default tablespace type

DEFAULT_TEMP_TABLESPACE

TEMP

ID of default temporary tablespace

DICT.BASE

2

dictionary base tables version #

EXPORT_VIEWS_VERSION

8

Export views revision #

GLOBAL_DB_NAME

testdb.mycom.com

Global database name

NLS_CALENDAR

GREGORIAN

Calendar system

NLS_CHARACTERSET

UTF8

Character set

NLS_COMP

BINARY

NLS comparison

NLS_CURRENCY

$

Local currency

NLS_DATE_FORMAT

DD-MON-RR

Date format

NLS_DATE_LANGUAGE

AMERICAN

Date language

NLS_DUAL_CURRENCY

$

Dual currency symbol

NLS_ISO_CURRENCY

AMERICA

ISO currency

NLS_LANGUAGE

AMERICAN

Language

NLS_LENGTH_SEMANTICS

BYTE

NLS length semantics

NLS_NCHAR_CHARACTERSET

UTF8

NCHAR Character set

NLS_NCHAR_CONV_EXCP

FALSE

NLS conversion exception

NLS_NUMERIC_CHARACTERS

.,

Numeric characters

NLS_RDBMS_VERSION

10.2.0.2.0

RDBMS version for NLS parameters

NLS_SORT

BINARY

Linguistic definition

NLS_TERRITORY

AMERICA

Territory

NLS_TIMESTAMP_FORMAT

DD-MON-RR HH.MI.SSXFF AM

Time stamp format

NLS_TIMESTAMP_TZ_FORMAT

DD-MON-RR HH.MI.SSXFF AM TZR

Timestamp with timezone format

NLS_TIME_FORMAT

HH.MI.SSXFF AM

Time format

NLS_TIME_TZ_FORMAT

HH.MI.SSXFF AM TZR

Time with timezone format

Oracle RDBMS history over the years

1970 -- Dr. Edgar Codd publishes his theory of relational data modeling.
1977 -- Software Development Laboratories (SDL) formed by Larry Ellison, Bob Miner, Ed Oates and Bruce Scott with $2,000 of startup cash. Larry and Bob come from Ampex where they were working on a CIA project code-named "Oracle." Bob and Bruce begin work on the database.
1978 -- The CIA is the first customer, yet the product is not released commercially as of yet. SDL changes its name to Relational Software Inc. (RSI).
1979 -- RSI ships the first commercial version, Version 2 (there is no V1 shipped on fears that people won't buy a first version of software) of the database written in Assembler Language. The first commercial version of the software is sold to Wright-Patterson Air Force Base. It is the first commercial RDBMS on the market.
1981 -- The first tool, Interactive Application Facility (IAF), which is a predecessor to Oracle's future SQL*Forms tool, is created.
1982 -- RSI changes its name to Oracle Systems Corporation (OSC) and then simplifies the name to Oracle Corporation.
1983 -- Version 3, written in C (which makes it portable) is shipped. Bob Miner writes half, while also supporting the Assembler-based V2, and Bruce Scott writes the other half. It is the first 32-bit RDBMS.
1984 -- Version 4 released. First tools released, (IAG-genform, IAG-runform, RPT). First database with read consistency. Oracle ported to the PC.
1985 -- Version 5 and 5.1 are released. First Parallel Server database on VMS/VAX.
1986 -- Oracle goes public March 12 (the day before Microsoft and eight days after Sun). The stock opens at $15 and closes at $20.75. Oracle Client/Server is introduced; first client/server database. Oracle5.1 is released.
1987 -- Oracle is the largest DBMS company. Oracle Applications group started. First SMP (symmetrical multi-processing) database introduced.
1987 -- Oracle's Rich Niemiec along with Brad Brown and Joe Trezzo working at Oracle (now at TUSC) implement the first production client/server application running Oracle on a souped-up 286 running 16 concurrent client/server users for NEC Corporation.
1988 -- Oracle V6 released. First row-level locking. First hot database backup. Oracle moves from Belmont to Redwood Shores. PL/SQL introduced.
1992 -- Oracle V7 is released.
1993 -- Oracle GUI client/server development tools introduced. Oracle Applications moved from character mode to client/server.
1994 -- Bob Miner, the genius behind the Oracle database technology, dies of cancer.
1995 -- First 64-bit database.
1996 -- Oracle7.3 released.
1997 -- Oracle8 is introduced. Oracle Application Server is introduced. Applications for the Web introduced. Oracle is the first Web database. Oracle BI tools like Discoverer are introduced for data warehousing. Tools have native Java support.
1998 -- First major RDBMS (Oracle8) ported to Linux. Applications 11 shipped. Oracle is the first database with XML support.
1999 -- Oracle8i released. Integrates Java/XML into development tools. Oracle is the first database with native Java support.
2000 -- Oracle9i Application Server released; it becomes the first database with middle-tier cache. Launches E-Business Suite, wireless database with OracleMobile, Oracle9i Application Server Wireless and Internet File System (iFS).
2001 -- Oracle9i (9.1) released. Oracle is the first database with Real Application Clusters (RAC).
2002 -- Oracle9i Release 2 (9.2) released.
2003 -- Oracle at France Telecom is No. 1 on Winter Group's Top 10 in DB size at 29TB.
2003 -- Oracle10g comes out -- Grid-focused, Encrypted Backups, Auto-Tuning and ASM.
2005 -- Oracle RAC at Amazon hits the Winter Group's Top 10 in DB size at 25TB.
2005 -- Oracle buys PeopleSoft (includes JD Edwards), Oblix (Identity Management), Retek (Retail) $630M, TimesTen (in-memory DB) and Innobase (InnoDB Open Source).
2006 -- Oracle buys Siebel for $5.8B, Sleepycat Software (Open Source) and Stellant (Content Management). Oracle with an Open Source push offers "unbreakable" support for Red Hat Linux.
2006 -- Oracle10g Release2 comes out in fall.
2007 -- Oracle buys Hyperion for $3.3B.
2007 -- Oracle 11g comes out (predicted based on prior releases).

Oracle system privileges

Note: Oracle 11gR1

Data Dictionary Objects Related To System Privileges

all_sys_privs

session_privs

user_sys_privs

dba_sys_privs

system_privilege_map



Administer

  • Administer Any SQL Tuning Set
  • Administer Database Trigger (database level trigger)
  • Administer Resource Manager
  • Administer SQL Management Object
  • Administer SQL Tuning Set
  • Flashback Archive Administrator
  • Grant Any Object Privilege
  • Grant Any Privilege
  • Grant Any Role
  • Manage Scheduler
  • Manage Tablespace

Advanced Queuing

  • Dequeue Any Queue
  • Enqueue Any Queue
  • Manage Any Queue


Advisor Framework

  • Advisor
  • Administer SQL Tuning Set
  • Administer Any SQL Tuning Set
  • Administer SQL Management Object
  • Alter Any SQL Profile
  • Create Any SQL Profile
  • Drop Any SQL Profile


Alter Any Privileges

  • Alter Any Cluster
  • Alter Any Cube
  • Alter Any Cube Dimension
  • Alter Any Dimension
  • Alter Any Evaluation Context
  • Alter Any Index
  • Alter Any Indextype
  • Alter Any Library
  • Alter Any Materialized View
  • Alter Any Mining Model
  • Alter Any Operator
  • Alter Any Outline
  • Alter Any Procedure
  • Alter Any Role
  • Alter Any Rule
  • Alter Any Rule Set
  • Alter Any Sequence
  • Alter Any SQL Profile
  • Alter Any Table
  • Alter Any Trigger
  • Alter Any Type


Alter Privileges

  • Alter Database
  • Alter Profile
  • Alter Resource Cost
  • Alter Rollback Segment
  • Alter Session
  • Alter System
  • Alter Tablespace
  • Alter User

Analyze Privileges

  • Analyze Any
  • Analyze Any Dictionary

Audit Privileges

  • Audit Any
  • Audit System

Backup Privileges

  • Backup Any Table

Change Privilege

  • Change Notification

Clusters

  • Alter Any Cluster
  • Create Cluster
  • Create Any Cluster
  • Drop Any Cluster

Comment Privileges

  • Comment Any Mining Model
  • Comment Any Table

Contexts

  • Create Any Context
  • Drop Any Context


Create Any Privileges

  • Create Any Cluster
  • Create Any Context
  • Create Any Cube
  • Create Any Cube Build Process
  • Create Any Cube Dimension
  • Create Any Dimension
  • Create Any Directory
  • Create Any Evaluation Context
  • Create Any Index
  • Create Any Indextype
  • Create Any Job
  • Create Any Library
  • Create Any Materialized View
  • Create Any Measure Folder
  • Create Any Mining Model
  • Create Any Operator
  • Create Any Outline
  • Create Any Procedure
  • Create Any Rule
  • Create Any Rule Set
  • Create Any Sequence
  • Create Any SQL Profile
  • Create Any Synonym
  • Create Any Table
  • Create Any Trigger
  • Create Any Type
  • Create Any View


Create Privileges

  • Create Cluster
  • Create Cube
  • Create Cube Build Process
  • Create Cube Dimension
  • Create Database Link
  • Create Dimension
  • Create Evaluation Context
  • Create External Job
  • Create Indextype
  • Create Job
  • Create Library
  • Create Materialized View
  • Create Measure Folder
  • Create Mining Model
  • Create Operator
  • Create Procedure
  • Create Profile
  • Create Public Database Link
  • Create Public Synonym
  • Create Role
  • Create Rollback Segment
  • Create Rule
  • Create Rule Set
  • Create Sequence
  • Create Session
  • Create Synonym
  • Create Table
  • Create Tablespace
  • Create Trigger
  • Create Type
  • Create User
  • Create View

Database

  • Alter Database
  • Alter System
  • Audit System

Database Links

  • Create Database Link
  • Create Public Database Link
  • Drop Public Database Link

Debug

  • Debug Any Procedure
  • Debug Connect Session

Delete

  • Delete Any Cube Dimension
  • Delete Any Measure Folder
  • Delete Any Table

Dimensions

  • Alter Any Dimension
  • Create Any Dimension
  • Create Dimension
  • Drop Any Dimension

Directories

  • Create Any Directory
  • Drop Any Directory


Drop Any Privileges

  • Drop Any Cluster
  • Drop Any Context
  • Drop Any Cube
  • Drop Any Cube Build Process
  • Drop Any Cube Dimension
  • Drop Any Dimension
  • Drop Any Directory
  • Drop Any Evaluation Context
  • Drop Any Index
  • Drop Any Indextype
  • Drop Any Library
  • Drop Any Materialized View
  • Drop Any Measure Folder
  • Drop Any Mining Model
  • Drop Any Operator
  • Drop Any Outline
  • Drop Any Procedure
  • Drop Any Role
  • Drop Any Rule
  • Drop Any Rule Set
  • Drop Any Sequence
  • Drop Any SQL Profile
  • Drop Any Synonym
  • Drop Any Table
  • Drop Any Trigger
  • Drop Any Type
  • Drop Any View

Drop Privileges

  • Drop Profile
  • Drop Public Database Link
  • Drop Public Synonym
  • Drop Rollback Segment
  • Drop Tablespace
  • Drop User

Evaluation Context

  • Alter Any Evaluation Context
  • Create Any Evaluation Context
  • Create Evaluation Context
  • Drop Any Evaluation Context
  • Execute Any Evaluation Context

Execute Any Privileges

  • Execute Any Class
  • Execute Any Evaluation Context
  • Execute Any Indextype
  • Execute Any Library
  • Execute Any Operator
  • Execute Any Procedure
  • Execute Any Program
  • Execute Any Rule
  • Execute Any Rule Set
  • Execute Any Type

Export & Import

  • Export Full Database
  • Import Full Database

Fine Grained Access Control

  • Exempt Access Policy

File Group

  • Manage Any File Group
  • Manage File Group
  • Read Any File Group

Flashback

  • Flashback Any Table
  • Flashback Archive Administrator

Force

  • Force Any Transaction
  • Force Transaction

Indexes

  • Alter Any Index
  • Create Any Index
  • Drop Any Index

Indextype

  • Alter Any Indextype
  • Create Any Indextype
  • Create Indextype
  • Drop Any Indextype
  • Execute Any Indextype

Insert

  • Insert Any Cube Dimension
  • Insert Any Measure Folder
  • Insert Any Table

Job Scheduler

  • Create Any Job
  • Create External Job
  • Create Job
  • Execute Any Class
  • Execute Any Program
  • Manage Scheduler

Libraries

  • Alter Any Library
  • Create Any Library
  • Create Library
  • Drop Any Library
  • Execute Any Library

Locks

  • Lock Any Table

Materialized Views

  • Alter Any Materialized View
  • Create Any Materialized View
  • Create Materialized View
  • Drop Any Materialized View
  • Flashback Any Table
  • Global Query Rewrite
  • On Commit Refresh
  • Query Rewrite

Mining Models

  • Alter Any Mining Model
  • Comment Any Mining Model
  • Create Any Mining Model
  • Create Mining Model
  • Drop Any Mining Model
  • Select Any Mining Model

OLAP Cubes

  • Alter Any Cube
  • Create Any Cube
  • Create Cube
  • Drop Any Cube
  • Select Any Cube
  • Update Any Cube

OLAP Cube Build

  • Create Any Cube Build Process
  • Create Cube Build Process
  • Drop Any Cube Build Process
  • Update Any Cube Build Process

OLAP Cube Dimensions

  • Alter Any Cube Dimension
  • Create Any Cube Dimension
  • Create Cube Dimension
  • Delete Any Cube Dimension
  • Drop Any Cube Dimension
  • Insert Any Cube Dimension
  • Select Any Cube Dimension
  • Update Any Cube Dimension

OLAP Cube Measure Folders

  • Create Any Measure Folder
  • Create Measure Folder
  • Delete Any Measure Folder
  • Drop Any Measure Folder
  • Insert Any Measure Folder

Operator

  • Alter Any Operator
  • Create Any Operator
  • Create Operator
  • Drop Any Operator
  • Execute Any Operator

Outlines

  • Alter Any Outline
  • Create Any Outline
  • Drop Any Outline

Procedures

  • Alter Any Procedure
  • Create Any Procedure
  • Create Procedure
  • Drop Any Procedure
  • Execute Any Procedure

Profiles

  • Alter Profile
  • Create Profile
  • Drop Profile

Query Rewrite

  • Global Query Rewrite
  • Query Rewrite

Refresh

  • On Commit Refresh

Resumable

  • Resumable

Roles

  • Alter Any Role
  • Create Role
  • Drop Any Role
  • Grant Any Role

Rollback Segment

  • Alter Rollback Segment
  • Create Rollback Segment
  • Drop Rollback Segment

Scheduler

  • Manage Scheduler

Select

  • Select Any Cube
  • Select Any Cube Dimension
  • Select Any Dictionary
  • Select Any Mining Model
  • Select Any Sequence
  • Select Any Table
  • Select Any Transaction

Sequence

  • Alter Any Sequence
  • Create Any Sequence
  • Create Sequence
  • Drop Any Sequence
  • Select Any Sequence

Session

  • Alter Resource Cost
  • Alter Session
  • Create Session
  • Restricted Session

Synonym

  • Create Any Synonym
  • Create Public Synonym
  • Create Synonym
  • Drop Any Synonym
  • Drop Public Synonym

Sys Privileges

  • SYSDBA
  • SYSOPER

Tablespace

  • Alter Tablespace
  • Create Tablespace
  • Drop Tablespace
  • Manage Tablespace
  • Unlimited Tablespace

Table

  • Alter Any Table
  • Backup Any Table
  • Comment Any Table
  • Create Any Table
  • Create Table
  • Delete Any Table
  • Drop Any Table
  • Flashback Any Table
  • Insert Any Table
  • Lock Any Table
  • Select Any Table
  • Update Any Table

Trigger

  • Administer Database Trigger
  • Alter Any Trigger
  • Create Any Trigger
  • Create Trigger
  • Drop Any Trigger

Types

  • Alter Any Type
  • Create Any Type
  • Create Type
  • Drop Any Type
  • Execute Any Type
  • Under Any Type

Update

  • Update Any Cube
  • Update Any Cube Build Process
  • Update Any Cube Dimension
  • Update Any Table

Under

  • Under Any Table
  • Under Any Type
  • Under Any View

User

  • Alter User
  • Become User
  • Create User
  • Drop User

View

  • Create Any View
  • Create View
  • Drop Any View
  • Flashback Any Table
  • Merge Any View
  • Under Any View


Granting System Privileges

Grant A Privilege

GRANT TO ;

GRANT create table TO usera;


Revoking System Privileges

Revoke A Single Privilege

REVOKE FROM ;

REVOKE create table FROM usera;