These notes are from Exam Cram Oracle 9i Fundamentals 1 by April Wells.
Tablespace contains segments which contain extents which contain data blocks which contain OS blocks. Extents are specific to a data file. Segments can span data files.
Oracle Universal Installer can be run non-interactively using a template so that installs can be completely scripted. ./runInstaller -responsefile filename -silent
The password file allows access to administrative roles sysdba / sysoper to an OS user without divulging the adminsitrative passwords.
Oracle Enterprise Manager can be used to control a fleet of servers using Intelligent Agents and the Oracle Management Server schema. Each managed node requires one Intelligent Agent which communicates with OMS.
Oracle Flexible Architecture would have:
ORACLE_BASE the topmost directory,
ORACLE_HOME set to ORACLE_BASE/product/release/
Add ORACLE_HOME/bin to the PATH.
Set ORA_NLS33 to ORACLE_HOME/nls/data if using character set other than US7ASCII.
Add ORACLE_HOME/lib to LD_LIBRARY_PATH.
Startup: nomount mount open restrict recover force
If an instance is running, STARTUP FORCE shuts it down with mode ABORT before restarting it.
STARTUP NOMOUNT can be used to create a new controlfile.
Oracle Managed File parameters: DB_CREATE_FILE_DEST DB_CREATE_ONLINE_LOG_DEST_n
When using OMF, alter database add logfile; will create logfile with size of 100MB.
Startup nomount then create database.
CREATE DATABASE runs sql.bsq to create data dictionary base tables.
Install data dictionary views with ORACLE_HOME/rdbms/admin/catalog.sql.
Install PLSQL with catproc.sql.
To convert ORACLE_HOME/dbs/initSID.ora to spfile: create spfile from pfile;
alter system enable restricted session; Leaves sessions connected but new sessions can only be established by DBA and those with RESTRICTED SESSION privilege.
If not specified with startup pfile=filename, then starts with ORACLE_HOME/dbs/spfileSID.ora or spfile.ora or initSID.ora.
alter session set sql_trace=true
Show data dictionary tables: SELECT * FROM DICTIONARY [DICT CATALOG CAT]
Show data dictionary columns: SELECT * FROM DICT_COLUMS
V$ are all dynamic performance views.
DBA_SEGMENTS and DBA_EXTENTS both have column BLOCKS.
Block header contains: block address, row directory, table directory, transaction slots.
The table directory is the portion of the data block contains information about the table having rows in this block.
Blocks have a header that consist of:
* Block type
1=undo segment header, 2=undo segment block, 5=data segment header, 7=temporary table, 11=data file header, 14=Unlimited rollback segment header, 15=Unlimited deferred rollback segment header, 16=Unlimited data segment header, 17=Unlimited data segment header with FL groups, 18=Extent map block
* Block format
Is it an oracle 7 or oracle 8 or 9 or 10 formatted block?
* Relative database address
* SCN sequence number
* Check value
Is used for integrity checking at the block level if db_block_checksum is set.
The tail is not at the beginning of a block but at its end: it is used as a block consistency check. That is, the value must be equal to SCN + block type + SCN sequence number.
AL32UTF8 varying width multi byte character set.
AL16UTF16 fixed width multi byte character set.
UTF-8 unicode standard.
UTF8 Oracle implementation of unicode.
Database character set may not be fixed width multi byte.
National character set may be fixed width multi byte.
When developing a packaged application, UTF-16 and nvarchar data types will provide most portability to any customer site.
NLS_LANGUAGE determines: NLS_DATE_LANGUAGE NLS_SORT.
Set ORA_NLS33 to $ORACLE_HOME/ocommon/nls/admin/data
Default sorting method is binary.
System Change Number is stored in all data files and control files and redo log files.
alter database open resetlogs; This creates a new incarnation of the database and affects recovery.
create controlfile...; Create control file from scratch with information you provide. You need to know names of all data files.
alter database backup controlfile to '/path/name';
alter database backup controlfile to trace;
FAST_START_MTTR_TARGET sets upper bound on recovery time by causing more frequent check points.
alter system switch logfile;
alter database clear [unarchived] logfile group
V$LOGHISTORY Archived logs.
V$LOGHIST Online logs.
alter system archive log all; Does what the archiver process does.
CREATE TABLESPACE BLOCKSIZE needs a configured subcache to use - DB_[2 4 8 16 32]K_CACHE_SIZE. Temporary tablespace can not use nonstandard block size.
Tablespaces that can never be offline: system, default temporary, undo with active segments.
TEMPORARY; might need recovery
IMMEDIATE; will need recovery
FOR RECOVER; allows tablespace point in time recovery
READ ONLY; still will allow objects to be dropped
READ WRITE; undoes READ ONLY
Temporary tablespace tuning should set UNIFORM EXTENT SIZE to multiple of SORT_AREA_SIZE.
BACKUP CONTROLFILE does not record temporary tablespace temp files.
CREATE CONTROLFILE does not allow specifying temporary tablespace.
Set a default temporary tablespace: alter database default temporary tablespace
To drop a tablespace for sure: drop tablespace
Read consistency is implemented through undo and system change numbers.
SET TRANSACTION READ ONLY; provides transaction level repeatable read.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; provides transaction level repeatable read during read/write.
Types of undo: system, non-system, deferred. Deferred occurs when tablespace is offline for recovery.
Init.ora settings for system managed undo: undo_management [auto, manual] undo_tablespace=
How to change the undo tablespace: alter system set undo_tablespace=
When one instance of RAC is looking for a previous SCN in undo, does it look in it's undo then ask all other instances if it is available in their undo?
alter system set undo retention=
Monitor via v$undostat.
Index organized table overflow holds columns that don't fit or that are excluded (PCTTHRESHOLD INCLUDING).
CHAR(n) 1-2000 bytes, blank padded.
VARCHAR2(n) 0-4000 bytes.
DATE 7 bytes.
TIMESTAMP Fraction of second, up to 9 digits, default is 6.
BLOB When greater than 4000 bytes, a locator points to out of line storage.
VARRAY Stores a list, manimum size is set.
create or replace directory
grant read on directory
dbms_redefinition.can_redef_table to verify if it can be online redefined.
Create interim table.
Bitmap index may never be unique.
Primary key constraint may not contain nulls.
Unique constraint may contain nulls.
$ORACLE_HOME/rdbms/admin/utlexcpt.sql creates EXCEPTIONS table.
PASSWORD_LOCK_TIME number of days account will be locked after FAILED_LOGIN_ATTEMPTS
PASSWORD_REUSE_TIME days before password may be reused. PASSWORD_REUSE_MAX number of new passwords before reuse of previous password. These are mutually exclusive - use one or the other.
PASSWORD_LIFE_TIME days to expire password.
Oracle-supplied password verify_function is enabled by running utlpwdmg.sql. It changes the default profile.
ALTER RESOURCE COST changes the weighted values.
DBMS_RESOURCE_MANAGER package use requires privilege
ADMINISTER_RESOURCE_MANAGER, which is in DBA role.
Resource plans are created in the pending area. The resource plan guides resources to consumer groups.
Create consumer group. There may be more than one consumer group per plan. Oracle provides consumer groups default_consumer_group and other_groups.
Plan directive specifies how resource are allocated to consumer groups.
execute dbms_resource_manager.create_plan_directive; This must always include the OTHER_GROUPS catch-all.
Individual users needs privileges to set initial group and to switch groups. It is convenient to grant the privileges to PUBLIC.
Activate the resource manager: alter system set resource_manager_plan=<>;
DBA_RSRC_PLANS lists all resource plans.
V$RSRC_PLAN lists currently active plans.
DBA_RSRC_PLAN_DIRECTIVES lists all resource plan directives.
To move the audit trail out of the system tablespace:
alter table aud$ move tablespace <>'
create index <> on aud$(sessionid, ses$tid) tablespace <>;
Users with role SYSOPER can
Extended ROWID is ten bytes encoded in base 64. Restricted ROWID is six bytes encoded in base 16.