Oracle 9 - Certified Associate - part 2

Oracle DB: informations about DML and DDL statements, datafiles, tablespaces, indexes, etc...
Informations for Oracle Certified Associate - part 2

Oracle Database fundamentals 2
Here some notes i took about Oracle 9i while reading OCA/OCP: Oracle9i DBA Fundamentals I. Stuff like database structure, names of the DB processes, etc... .

Basics
SQL: Functional programming language (write the desired outcome).
C: Procedural programming language (write process used to get the outcome).
RDBMS: (Relational DB management system) Translates SQL into a process which Oracle can use.
Select statements are processed as follows:

1. Open cursor
2. Search shared pool and see if SQL is there (library cache)
3. Validate SQL syntax
4. Validate tables & columns
5. Acquire locks
6. Check user privileges.
7. Parse statement if SQL does not exist in library cache (gen. expl. plan)
8. Execute statement. The server process executes it and writes into buffer cache
9. Fetch values from cursor & return result to user process
10. Eventually redo point 9 if requested.
     

DML (update + delete) statements are processed as follows:

1. Points 1 to 8 of select
2. Get data from disk into buffer cache
3. Get lock on this data
4. Do DML in buffer cache (dirty blocks are generated)
5. Get lock on undo segment.
6. Write new & old version into undo.
    

DML (insert) statements are processed as follows:

1. Points 1 to 8 of select.
2. Get a block from disk into buffer cache.
3. Insert new row into block.
4. Get a lock on the undo segment.
5. Write old & new version of data into the undo segment.
6. Write redo entry into redo log buffer.
    

Advantage of copying data into buffer cache: 1) Ram faster than HD 2) User doesn't have to wait for HD-write.
Whe a commit is issued:
- Locks on the table and/or rows are released.
- Locks on undo segments are released. When this space is needed again later, these informations are overwritten.
- Redo entry saying that transaction was committed is generated.
SCN: (system change number) Is generated for each transaction.

Logical components of a DB

Tablespace
Segment				Segment
Extent		Extent		Extent		Extent
Block	Block	Block	Block	Block	Block	Block	Block

- Block:

• its size must be a multiple of the OS block size.
• the block size is defined at DB-creation by setting the parm DB_BLOCK_SIZE.

- Extent: group of continuous blocks
- Segment:

• group of extents.
• multiple extents for the same segment can be on different data files of the same tablespace.
• One table has 1 segment (exception: partitioned tables - as well with tablespaces)
• There are 4 types of extent:
  • data
  • index
  • temporary
  • undo

- Tablespace: group of segments.

Physical components of a DB

• data files
• redo log files (*can be multiplexed)
• control files (*can be multiplexed)

1 datafile -> 1 tablespace
1 tablespace -> n datafiles

See pag. 118 for syntax of "create database":

• REUSE: overweites existing files, otherwise error if file already exists.
• LOGFILE: location of online redo files.
• MAXLOGHISTORY: RAC - Max nbr of archived redo log files.
• MAXDATAFILES: Max nbr of data files for each instance.
• DB_FILES: Max data files accessible for each instance.
• NOARCHIVELOG: defaults.
• UNDO TABLESPACE: used only if parm UNDO_MANAGEMENT=AUTO.

Data dictionary
- User SYS is the owner of the data dictionary.
- It is created in the SYSTEM tblspace.
- SQL.BSQ creates:

- CATALOG.SQL:

- CATPROC.SQL: PL/SQL items

Memory structures

• SGA (System Global Area - shared) (Total size defined by parm SGA_MAX_SIZE)
  - It is created at instance startup (STARTUP NOMOUNT is enough).
  - The size is allocated using granules. If the SGA size is < 128MB, the granule size is 4MB, otherwise 16MB.
  - A minimum of 3 granules are allocated: 1 for Shared pool, 1 for buffer cache and 1 for redo log buffer.
  • Shared pool (size defined by parm SHARED_POOL_SIZE)
    • Library cache
      - Divided into a shared (sql parse tree and exec plan) and a private area (bind vars)
    • Dictionary cache
      - contains object definitions.
      - is the only one which keeps infos in a row structure instead of a buffer (block). Referenced sometimes as row cache.
    • Control structs
  • Buffer cache (see V$BUFFER_POOL)
    Where rows are read into or changed/inserted/deleted before writing them to data files.
    • Keep area
      - This data will stay in memory and will not age out.
    • Recycle area
      - Lines in here will be thrown out as soon as possible.
    • Default area
      - This data uses a LRU algorithm do age out data. Data that does not fit into keep and recycle is put in here.
  • Redo log buffer cache
    - Keeps track of changes done with the statements "insert / update / delete / create / alter / drop".
  • Large pool (Optional - size defined by parm LARGE_POOL_SIZE)
    - Used to do backups / restores and for keeping session memory for MTS config
  • Java pool (Optional - size defined by parm JAVA_POOL_SIZE)
• PGA (Program Global Area - private - allocated for each server process)
  - Global temp tables are perhaps allocated in here?
  • Sort area
    - If shared server are used, this is allocated from the SGA (not into )
    - contains as well the locks.
    - size determined by (SORT_AREA_SIZE / SORT_AREA_RETAINED_SIZE)
    - If the sort is bigger than SORT_AREA_SIZE 1) data is divided into groups that fit 2) they are sorted separately 3) then the results are saved in the user's temp tablespace and 4) the final result is generated by merging all the separate sorts.
  • Session info
    
  • - *only if dedicated servers are used. If shared servers are used, it's in the SGA.
  • Stack space (session vars)
    - *only if dedicated servers are used. If shared servers are used, it's in the SGA.

The SGA must fit in "real" memory. To calculate it use DB_CACHE_SIZE, LOG_BUFFER, SHARED_POOL_SIZE.

Background processes

• DBWn (Database writer):
  • Writes dirty buffers to disk from buffer cache.
  • Started as default only 1 DBWn. Max is 10 (0->10). See DB_WRITER_PROCESSES.
  • Writes are done when:
    • server process needs more space in the buffer cache.
    • LGWR wants to write (checkpoint)
    • Every 3 seconds because of a timeout.
    • Nbr of dirty buffers reaches a limit.
  • Writes are done as well when a table is dropped or truncated.
• LGWR (Log writer):
  • reads from redo log buffer to online redo log files.
  • Writes from buffer to disk when:
    • commit
    • when redo buffer is 1/3 full
    • when DBWn writes
    • every 3 seconds
    • when there is 1MB of redo log records
  • Min is 2 online redo log files (writes are done in a circular way).
• CKPT (Checkpoint writer):
  • reduces time needed for instance recovery.
• SMON (System monitor):
  • Does instance or crash recovery at DB-startup, or when files or tablespaces are brought back online.
  • Cleans up temp segments.
  • Coalesces space (only if PCTINCREASE of the tablespace is set to nonzero)
• PMON (Process monitor):
  • Cleans up failed user processes and frees up resources of those processes.
• ARCn (Archiver):
  • Active only if DB is in archivelog mode and automatic archiving is enabled (LOG_ARCHIVE_START = TRUE)
  • Copies online archive log files to other locations.
  • Max is 10
  • Started by LGWR
• RECO (recoverer)
  • Started only if "distributed transactions" is on.
  • Resolves "in-doubt" transactions (?).
• LOCK:
  • Used for inter-instance locking if "real application clusters" are used.
  • RAC is 1DB -> n instances
• QMNn (Queue monitor):
  • Monitors message queues.
  • Max is 10 (see parm AQ_TM_PROCESSES)
• Dnnn (Dispatcher):
  • Create at least 1 dispatcher for each network protocol used in oracle.
• Snnn (Shared server process):
  • Min is value of parm SHARED_SERVERS
  • Max is value of parm MAX_SHARED_SERVERS

Server processes
There are 2 ways of setting them up:

• Dedicated servers
  Every user who connects to Oracle has its own process which reads from disk and puts this into the buffer cache. Theas means more memory & cpu consumption. Each time a connection is established, the system has to create a dedicated server process.
• Shared servers (MTS - multithreaded server)
  Many users are served by one or few server processes.
  usr process -> dispatcher (perhaps more than 1) -> queue -> shared server process
  usr process -> -> shared server process
  The "dispatchers / queues / shared server processes" exist always - don't need 2b created when somebody connects (see "Snnn" background process).

Where is the user session information?
Shared servers are used -> control structs of shared pool (MTS architecture).
Dedicated servers are used -> control structs of PGA.

OFA (Optimal / Oracle flexible architecture)
Advantages:
- Good performance.
- Flexible, so that db growth is easy.
- Encourages consistent file-name conventions. Easy to distinguish files & associate them to their tblspaces.
- Keeps contents of tblspace separate (e.g. idx & tblspace are separate). Fragmentation & i/o contention are low.
- Supports multiple "oracle_home" => multiple oracle releases (7,8,9,10) can run concurrently.
- Possible to have 1 listener for multiple "oracle_home".
- Admin infos of each db is separate.

When I create the DB, two loginIDs are created... ID Initial password SYS change_on_install SYSTEM manager ...and as well the role "DBA" (has all admin privileges).

2 authentication systems:

• OS
  - Set "remote_login_passwordfile" to "none" (default)
  - The unix user must be a member of the oracle OSDBA or OSOPER unix groups. In Unix, usually it is the "DBA" group.
  - The sysoper (or osoper) privilege can perform startup, shutdown, ecc... (pag. 81).
  - The sysdba (or osdba) privilege can perform everything that osoper/sysoper can do, plus create database, all system privileges with "admin option" and time-based recovery.
  - Can connect by issuing "connect / as sysdba" (or "as sysoper").
  - No user is needed in the Oracle DB.
• Password file
  - The user needs to have the appropriate privileges in Oracle.
  - Do the following to setup a pwdfile authentication:

1. Create a pwdfile and define a pwd for the SYS user:
   "orapwd file=<filename> password=<pwd> (password that SYS will have) [entries=<maxusers>]
2. Set "remote_login_passwordfile"
   - The pwds for the users SYS and INTERNAL can always be defined.
   - Exclusive: - File can be used only for 1 instance.
   - Can add more users to the file that will have SYSDBA or SYSOPER
   - Shared: - File can be used for multiple instances.
   - No other users than SYS and INTERNAL can be specified.
3. Grant to the users the SYSDBA/SYSOPER privileges -> they're added to the pwdfile.
   - When I connect to the DB using the SYSDBA privilege I connect to the SYS schema.
   - When I connect to the DB using the SYSOPER privilege I connect ot the PUBLIC schema.
   - See view V$PW(D)FILE_USERS to know who has SYSDBA/SYSOPER privileges and is in the password file.
   - Start/Stop an instance -> SYSDBA/SYSOPER privilege is needed.
   - Objects created by people logging in as sysdba or sysoper will be owned by SYS.

Starting a DB

1. Start the instance
2. Mount the DB
3. Open the DB for normal use (default)

Command "startup"	Command "alter"	Remarks
startup nomount		Only views that read from the SGA can be accessed.
startup mount	alter database mount	Control files are opened.
startup open read only	alter database open read only	No redo infos are generated
startup open	alter database open
startup force		DB wasn't shut down properly. It will shutdown the DB and restart it if it's already running. It's like shutdown abort + startup open
startup restrict	alter database <enable/disable> restricted session	Allow only users with "restricted session" privilege. Already connected users stay connected.

 

Command "shutdown"	Remarks
shutdown normal	Waits until all users disconnect.
shutdown transactional	Waits until all active transactions are committed or rolled back. No new trans are allowded.
shutdown immediate	Immediately rolls back all transactions.
shutdown abort	will NOT rollback active transactions -> instance recovery during startup is needed
startup force	shutdown abort + startup open.

Files
Parameter

- $ORACLE_HOME/DBS is its default location.
- init<sid>.ora is the default name.
- Contains u.a. the dbname, the location of control file, location archived log files, if ARCx process has to be started, location of dump & trace files.
- spfile must be created from a pfile.
- v$parameter -> current session
- v$system_parameter -> system parameters
- use "alter system/session" to change them.

alter system set <parm>=<value> scope =	spfile	Takes effect only after next restart.
	memory	Takes effect immediately but is not saved.
	both

- parm. CORE_DUMP_DEST: available only in unix
- parm. USER_DUMP_DEST: writes deadlocks, user session errors.
- parm. BACKGROUND_DUMP_DEST: writes the alert log file ("alter_<sid>.log") which lists:

• non-default init parms
• DB startups & shutdowns
• DB archiving & recoveries
• modifications to tablespaces, rollback segments, data files.

- parm. DB_CREATE_FILE_DEST & DB_CREATE_ONLINE_LOG_DEST_n (OMF is active if at least 1 of them is specified.

• DB_CREATE_FILE_DEST: Default location for new datafiles (file will be "ora_[?].dbf"). Can be changed with "alter system".
• DB_CREATE_ONLINE_LOG_DEST_n (max is 5): Locations of online redo log files ("*.log") and control files ("*.ctl"). Can be changed with "alter system".
  • If only one of the directories is there, control files will be saved in there. Don't know what happens, when all 3 are specified (probably db_create_online... will be used as it is probably multiplexed (a minimum of 2 is required for rollback round_robin).
  • No control file is required to create the db. They're created when I create the db.
  • Recommanded are 2 control files on 2 disks.

- parm. CONTROL_FILES: Default location for the control files.

• It contains:
  • db name (a control file belongs 2 only 1 db)
  • db creation timestamp.
  • data files name, location, on/offline status. If offline, its scn.
  • redo log files name & location and redo log archive infos (updated by ARCn).
  • tblspace names
  • current log sequence number (updated in the controlfile by LGWR)
  • current checkpoint nbr (updated by CKPT).
  • start & and of undo segments.
  • RMAN info
• Its size is determined by:
  • MAXDATAFILES
  • MAXINSTANCES
  • MAXLOGFILES
  • MAXLOGMEMBERS
  • MAXLOGHISTORY
• Oracle recommends a minimum of 2 control files (max is 8). There are 2 ways of multiplexing them: init.ora & spfile.
  • init.ora:
    1. shutdown db
    2. cp or mv files
    3. change parm "CONTROL_FILES"
    4. restart db (If the db is not created yet, it will create & multiplex all ctrlfiles specified in "CONTROL_FILES". The 1st file of the list is used for reading - others are just multiplexed.)
    • spfile:
    1. "alter system set control_files = <filename> scope=spfile"
    2. "shutdown normal" (shutdown the db)
    3. copy an existing file to the new location
    4. startup (start the instance)(OMF-managed control files are created if "CONTROL_FILES" is not mentioned, but DB_CREATE_ONLINE_LOG_DEST_n.)
• Re/create control files (if I want to change "MAX" limits, db name or I lost old control files):
  1. Prepare names of data files and redo log files.
  2. Prepare script as pag. 149 (See option NORESETLOGS (missing datafiles are showed after restart as "MISSINGnnnn" in v$datafile. Media recovery will reinsert them), as they're already existing and shouldn't be changed). The script can be prepared as well with "alter database backup controlfile to trace" (file must be edited because comments have to be deleted before being able to use it) or "alter database backup controlfile to <filename> reuse".
  3. Shutdown db
  4. startup nomount
  5. Run script
  6. alter database open
  7. Shutdown db and take backup.
   
• Query control file information:
  • names and status of controlfiles: v$controlfile
  • names of controlfiles: show parameter control_files

Redo log

• LGWR writes when:
  • A user commits (a SCN is assigned to the redo records)
  • Redo log buffer is 1/3 full
  • There is ~1MB of changed data in the buffer
• Each db has its own online redo groups (RAC: oracle keeps track to which instance the entries belong).
• Statements:
  • create / alter:

Statement	Statement	Remark
create database xxx	logfile [<group 1>] [fname] size 10M, logfile [<group 2>] [fname] size 10M;	Redo log group 1+ 2 are created.
create database x logfile	[<group 1>] ('/blah1a.log', '/blah1b.log') size 10M, [<group 2>] ('/blah2a.log', '/blah2b.log') size 10M	Redo log groups 1 + 2 are multiplexed.
alter database add logfile	(group 3) ('/blah3a.log', 'blah3b.log') size 10M
alter database add logfile	'/blah4a.log' reuse	Reuses an existing file. Uses size of the already existing file.
alter database add logfile	member '/blah4b.log' to group 4;
alter database add logfile	member '/blah4b.log' to ('/blah4a.log');	Searches in which group those members are used and adds it to that group.

 

• drop groups:

1. Check in V$LOG if the group is being used ("active"). If yes, issue "alter system switch logfile".
2. alter database drop logfile group x.
3. delete file from disk manually
   • drop & re-add:
     1. alter database clear logfile group x
   • Rename members:
     1. Shutdown db (do full backup).
     2. cp / rename files
     3. startup mount
     4. alter database rename file <old> to <new>;
     5. alter database open;
     6. Backup ctrl file.

Log switch:

• The file actively written is called the "current" log file.
• Log files required to recover the instance are called "active". The others are "inactive".
• Log switches are mentioned in the alert log file.
• Log switches occur:
  • When the log is switched (file is full) => log sequence nbr is allocated to the new file.
  • alter system switch logfile;
• Log switches cause a checkpoint:
  • Header of data files & ctrl files updated by CKPT.
  • Blocks are written to disk by DBWn.
  • A checkpoint happens when:
    • There is a log switch (redo log file is full).
    • Shutdown
    • Tblspace is put to read-only or backup mode.
    • Tblspace or datafile is taken offline.
    • alter system checkpoint;
    • alter system switch logfile;
    • by parm FAST_START_MTTR_TARGET (says how long a db recovery has to take).
      Set "log_checkpoints_to_alert = true" to trace them.

Archive logs

• They're copied by ARCn to a different location. LGWR waits for ARCn.

Statement (the first OR the second)	Statement	Remarks
alter system set	LOG_ARCHIVE_DEST	<= mandatory
	LOG_ARCHIVE_DUPLEX_DEST	(optional)
alter system set	LOG_ARCHIVE_DEST_n <mandatory/optional> [reopen <time seconds to wait>]	Max is 10. mandatory means that it has absolutely to write in there. reopen means it retries if it is busy.

Remark: LOG_ARCHIVE_MIN_SUCCEED_DEST is ignored if less than "n".
 

• Set archivelog:

1. Shutdown db.
2. Change parms.
3. startup mount;
4. alter database archivelog;
5. alter database open;

• Unset archivelog:

1. Shutdown db.
2. startup mount;
3. alter database noarchivelog;
4. alter database open;
   Archivemode info: "archive log list" or v$database

Tablespaces and data files

• The datafiles I specify upon db creation are assigned to the system tablespace.
• For dictionary-managed tables, if its extents are allocated or freed, undo infos are generated as the dictionary tables are changed. No undo for locally managed tables.
• (OMF) If I create a tablespace without giving filenames, the value of DB_CREATE_FILE_DEST will be used.
• Parameters:
  • Default value for INITIAL and NEXT:

Parm	Default value	Minimum value
INITIAL	5 db blocks	2 blocks if it's dict.managed. 3 blocks if it's locally managed.
NEXT	5 db blocks	3 db blocks.

 

• PCTINCREASE

• MINEXTENTS:

• LOGGING (redo log):

• SEGMENT SPACE MANAGEMENT:

• Locally managed tblspaces cannot have the parms:
  • default storage
  • temporary
  • minimum extent
• SEGMENT SPACE MANAGEMENT:
  • AUTO
    - Bitmaps are used.
    - Parameters that are ignored: FREELISTS / FREELIST GROUPS / PCTUSED.
   
• Transform the type of tblspace (extent allocation method (local or dict.-managed cannot be changed):
  • Dictionary-managed tblspaces (if they're empty):
    permanent <=> temporary
  • Locally managed tblspaces:
    permanent <= temporary
• Statements for taking a tablespace offline (sqls are rolled back when they're online again):

Statement	Remark
alter tablespace x offline normal	All datafiles must be online.
alter tablespace x offline temporary	Media recovery might be needed for offline datafiles. A checkpoint on datafiles is done.
alter tablespace x offline immediate	No checkpoint -> media recovery is needed

• Statement for taking a tablespace online: alter tablespace x online;
   

Blocks
Parameters for data blocks
- PCTFREE:

• Default = 10

- PCTUSED:

• Default = 40
• Cannot be used with index

- INITRANS:

• Default = 1 (data) or 2 (indexes & clusters)
• It's the number of concurrent DML transactions in the worst case.
• If too low, space will be allocated in the block itself and not in the header and will never be released.

- MAXTRANS:

• Max number of concurrent transactions.

Extents
- If locally managed:

• Storage parms are ignored.
• Uniform (e.g. "extent management local uniform size 512k") or variable (managed by oracle)
  - Are made of contiguous blocks.

Segments
- v$sort_segment: Displays only infos of segments saved in temporary tblspaces.
- A partitioned table has more than 1 segment.
Undo segments

• Undo is created before update / delete / insert (with rowid) rows.
• Operations done in the undo segments are saved as well in the redo logs.
• Segments are freed with transaction end, but not destroyed (see snapshot too old error).
• The undo segment for system tblspace is always created at db creation.
• Automatic undo management:
  • undo_management: auto (manual is possible) (cannot be dynamically altered)
  • undo_tablespace: default is SYS_UNDOTBS (can be dynamically altered)
• Only 1 tblspace at a time can be used.
• "create undo tablespace x datafile y size 15M"
• "alter system set undo_tablespace=x"
• Transactions have to be complete in order to drop the undo tblspace.
• Parm UNDO_RETENTION (seconds) specifies for how long freed undo segments have 2b kept.
• Error snapshot too old -> increase UNDO_RETENTION and eventually increase tblspace size.
• DBA_ROLLBAC_SEGS

Tables
- "segment space management auto" => following are not used: PSTUSED / FREELISTS / FREELIST GROUP
- Oracle recommends: FREELISTS = INITRANS
- In partitioned tables, each partition gets a segment.
- In partitioned tables, each partition can be put on a different partition.
- In partitioned tables, each partition can be set as read-only.
- Temporary tables:

• "create global temporary table on commit <delete rows /preserve rows>" -> is created in the user temp space.
• "delete rows" means that it is transaction based.
• "preserver rows" means that it is session based (rows will disappear after a disconnect + connect).
  - "drop any table" privilege required to drop other user's tables.
  - Set columns as "unused" makes the column invisible in the table definition.
  - find migrated rows: pag. 297

Indexes
- Bitmap index stores rowids for null values.
- PCTUSED cannot be used.
- INITRANS should be higher than the one of the table.
- Stats have to be gathered in order to use function-based indexes.
- RBO doesn't use function-based indexes. Only CBO.
- "disable validate": Indexes that enforce the contraint is switched off. No DML is allowded.
- Constraint check :

• The method cannot be changed with "alter table". Constraints have to be dropped and recreated.
• "deferrable initially <deferred / immediate>":
  - "deferrable": user can change the behaviour (default is not deferrable).
  - "initially deferred": do check at the end of the transaction.
  - "initially immediate": do check at the end of the sql (default).
• Set constraints: for the current transaction.
• "alter table xxx modify constraint initially <deferred / immediate": fixed in the table

User management
- The profile "default is created with the db (has "unlimited" for everything).
- Resource limits are enabled with "resource_limit = true" parameter (default is false).
- Parameters:
(*When limit is reached user can commit/rollback the previous transactions -> Afterwards he has to disconnect. The failed SQL is rolled back)

• CPU_PER_SESSION: 1/100 sec.
• CPU_PER_CALL: (for single sql) 1/100 sec
• LOGICAL_READS_PER_SESSION: data blocks from memory & disk.
• LOGICAL_READS_PER_CALL
• PRIVATE_SGA: Bytes (or KB or MB)

• CONNECT_TIME: minutes (total). Afterwards usr is disconnected & rollback.
• IDLE_TIME: minutes (continuous idle). Afterwards usr is disconnected & rollback.
• FAILED_LOGIN_ATTEMPTS: max consecutive failed logins -> account locked.
• PASSWORD_LOCK_TIME: days
• PASSWORD_LIFE_TIME: days -> dba has to reset the password.
• PASSWORD_GRACE_TIME: days before password expiration that a warning is issued.
• PASSWORD_REUSE_TIME: days a pwd cannot be used again after changing it.
• or
• PASSWORD_REUSE_MAX: number of pwd changes before a pwd can be used again.
• PASSWORD_VERIFY_FUNCTION: function to be used for pwd verification.
  - Days can be specified as a fraction (e.g. 1/24 means 1 hour).
  - "alter user xxx account unlock"
  Composite limit
  - Value 0 means "not to be considered".
  - Following values can be set:
  - CPU_PER_SESSION
  - LOGICAL_READS_PER_SESSION
  - CONNECT_TIME
  - PRIVATE_SGA
  - To modify the costs: "alter resource cost <which one> <value>". The ones not modified won't change.
  - To add composite limit to a profile: "alter profile xxx composite_limit 100000".
  - Example: LOGICAL_READS_PER_SESSION 10 / CONNECT_TIME 2 => (500'000x10)+(10x2) =5'000'020
  - To change other resources in the profile: "alter profile xxx limit <what> <how>"
  - Assign profile to users: "alter user yyy profile xxx".
  - Views:
  - DBA_PROFILES
  - USER_RESOURCE_LIMITS
  - USER_PASSWORD_LIMITS
  - RESOURCE_COST

- Minimal settings:
-> User has to have a space quota (not for temp. tblspaces).

- ORA_ENCRYPT_LOGIN: Eventually setz it to true - but only on the client machine. - DBLINK_ENCRYPT_LOGIN: Eventually setz it to true - but only on the client machine. - OS_AUTHENT_PREFIX:

- REMOTE_OS_AUTHENT (default is false): can connect from any machine, if this is set to true!!!!!
- LICENSE_MAX_SESSIONS: when the limit is reached, connect only with "restricted session" privilege is possible.
- ALL_USERS: user creation date and ID.
- DBA_USERS: encrypted pwd
- To save a password which is already encrypted: "create user xxx identified by values 'jkfsduerum'"
- Shutdown & startup is done by connecting as follows: "connect <username> as sysdba".

Privileges
- PUBLIC: it's a user group. All users belong to it.
- User and a role cannot have the same name.
- DML can be granted on a column.
- Even with the DBA privilege, I have to have "with grant option" to grant to other users.
- Revoke of only "with admin privilege" is not possible. Revoke the whole privilege and reassign it.
- Not possible to revoke single column privileges => revoke table and reassign.
- Granted SYSTEM "with admin option" is NOT revoked from other people when original is revoked.
- Granted OBJECT "with admin option" IS revoked from other people when original is revoked.

Audit
- Audit table is SYS.AUD$
- Enable auditing AUDIT_TRAIL=TRUE (or DB) => inserted into table.
- Enable auditing AUDIT_TRAIL=OS => inserted into os file.
- Types of auditing:

- AUDIT / NOAUDIT.
- Users with admin privileges, when they connect, are audited in the os files.

Globalization support
- Default is US7ASCII (7 bytes -> 128 values)
- "alter database character set x":

- Parm. national character set: used in NCHAR / NVARCHAR / NCLOB
- Parm. national character set can be:

- AF32UTF8 / UTF8 / UTFE:

- NLS_SORT: language used in order to perform sorting.
- NCHAR can use only UTF8 & AL16UTF16.\\