Midterm Review Flashcards
Name 4 default tablespaces created with Oracle.
- User
* stores all user table data
* administrator set up
* many user tablespaces (each have a different name) - System SYSAUX
* stores the data dictionary for the entire database
* the database schema, user names, passwords, user schemas … - Undo (Rollback)
* stores any data as it was prior to a transaction start - Temporary
* stores and sorts data when creating indexes, performing calculations, joining tables …
What are the “physical” components of Oracle?
Physical (the disk files):
Datafiles: the schema & the data
Redo log files: the files with copy of all committed + uncommitted transactions
Control files: the oracle configuration files (so software starts up correctly)
Software files: the oracle programs
When is a redo entry is written to the redo file?
When a commit occurs.
What does RAID stand for?
Redundant Array of Inexpensive Devices
Describe a “transaction”.
“A transaction is a logical unit of work that contains one or more SQL statements”
Describe a “ Oracle Instance”.
“…the set of Oracle Database background processes that operate on the stored data and the shared allocated memory that those processes use to do their work.”
What are 3 components of the SGA (System Global Area)?
- Database Buffer Cache
- Shared Pool: - Library and Data Dictionary Cache
- Redo Log Buffer
What might a user schema contain?
Tables
Views
Indexes
Stored procedures
What are some of the more commonly used data types within Oracle?
- Char
- Varchar2
- Number
- Date
- Long char
- Raw or longraw
- Blob
- ROWID
What are 4 role types within Oracle?
- DBA
- CONNECT
- RESOURCE
- EXP_FULL_DATABASE, IMP_FULL_DATABASE
SQL as a language can be catagorized into 4 language structures. What are these?
-
Data Definition Language (DDL) statements are used to define the database structure or schema.
Example CREATE, DROP -
Data Manipulation Language (DML) statements are used for managing data within schema objects.
Some examples: SELECT,UPDATE, INSERT, DELETE -
Data Control Language (DCL) statements.
Example: GRANT,REVOKE - **Transaction Control Language (TCL) **– statements to control transactions
e.g. COMMIT, ROLLBACK
What does MDSYS stand for?
Multi-dimensional Data SYStem
What are 5 elements of MDSYS.SDO_GEOMETRY?
- SDO_GTYPE - Number, indicates point, line, polygon
- SDO_SRID - Number, reference to coordinate system or Null
- SDO_POINT_TYPE - Only for points, coordinate pair, or Null
- SDO_ELEM_INFO - Define how coordinates are listed. Contains Ordinate offset, Element type & Interpretation, or Null for points
- SDO_ORDINATE - The list of coordinates, or Null for points
In order to create spatial data in Oracle five steps are taken.
- Create a table with a column of type MDSYS.SDO_GEOMETRY
- Load data to the table
- Register the table with the database
- Validate the geometry
- Index the data
Describe the following geometry.
ETYPE: 1
INTERP: 1
A single point.
