This is the html version of the DOC file http://www.inf.fu-berlin.de/~mscholz/ocp-testerkl%84rungen.doc.
G o o g l e automatically generates html versions of DOC documents as we crawl the web.

Google is not affiliated with the authors of this page nor responsible for its content.

These search terms have been highlighted:

cannot

drop

user

currently

connected

Generelle Fragen:

Parameterfile

Initsid.ora

Controlfile

Predefined roles (exp_full_database)

Was ist Optimal (rollback segment)

Initrans ? (index)

SchwÃ¤chen

Managing Tables

Reorganizing Data

Disable a constraint using this command:

ALTER TABLE table_name DISABLE CONSTRAINT table_name_pk;

Use the CASCADE option to disable a primary key referenced by a foreign key so that the foreign key is

disabled before the primary key is disabled.

The current log sequence number and checkpoint information used to recover a database are stored in the control file.

DBA_SEGMENTS displays the size and storage settings for all the segments in the database.

The initialization parameter LOG_ARCHIVE_START indicates whether automatic archiving is enabled when an instance starts. A value of TRUE indicates that archiving is automatic. The default value is FALSE, and the filled redo log files must be manually archived. Execute the ARCHIVE LOG LIST command to determine if automatic archiving is enabled.

ORACLE_HOME -> specifies where the Oracle software will be installed

ORACLE_SID -> specifies the instance name

PATH -> search path

ORA_NLS33 -> required for character sets other than US7ASCII

Trace files record information about the background processes. This file is useful when trying to determine the cause of a failure. Tracing can be enabled or disabled by altering the SQL_TRACE initialization parameter.

To kill a user's session, query V$SESSION to retrieve the user's session ID (sid) and serial number (serial#).

Use this statement to kill a user's session:

ALTER SYSTEM KILL SESSION 'sid, serial#'

When an active session is killed, the user receives this message:

ORA-00028: your session has been killed

When an inactive session is killed, the message is not returned immediately. The STATUS column in V$SESSION is marked as killed until the user attempts to use the session again.

The LOAD parameter in the SQL*Loader command specifies the number of records to load.

The SET ROLE command can be used to enable and disable a role.

Disabled Novalidate -> New and existing data may violate the constraint.

Disabled Validate -> Modification of the constraint column is not allowed.

Enabled Novalidate -> Only existing data may violate the constraint.

Enabled Validate -> All data in the table is guaranteed to adhere to the constraint.

Query DBA_OBJECTS to display all the objects in the database. If errors occur during compilation of objects, the STATUS column in DBA_OBJECTS is set to INVALID. Compiled objects that are available for use are marked VALID.

In this example, the view would be marked as INVALID because the table no longer exists.

Use the Export/Import utilities to:

1. Move data from one user to another user, from one database to another database, from one table to another table or from one tablespace to another tablespace.

2. Migrate data to a different operating system or version of Oracle

3. Create a logical backup.

4. Create test data.

5. Reorganize tables.

DBA_CONSTRAINTS displays the names and types of all the constraints in the database and their current status.

The ORAPWD utility is used to create a password file. This is the correct syntax for the command:

orapwd file=filename password=password entries=entries

filename is the password file

password is the password for SYS and INTERNAL

entries is the maximum number of distinct DBAs

After the VERIFY_FUNCTION is enabled and the DEFAULT profile is altered, a user must change their password using these guidelines:

1. The new password must differ from the old password by at least three characters.

2. The password must have at least 4 characters.

3. The password cannot be equal to the username.

4. The password must have at least one alpha, one numeric, and one special character.

The Optimal Flexible Architecture is a set of guidelines created by Oracle that provide a template for configuring Oracle systems. The OFA recommends that database files be placed in directories directly below the Oracle directory. Database files include datafiles, control files, and redo log files. Product files are grouped by purpose and Oracle Server version and then placed in directories directly below the Oracle directory.

Oracle will automatically maintain the control files specified by the CONTROL_FILES initialization parameter. Oracle recommends at least two control files and you can specify up to eight. If you lose a control file, you can copy an existing control file, update the parameter file, and restart an instance.

Use the DROP USER command with the CASCADE option to drop all the user objects before dropping the user. A user with objects must be dropped with the CASCADE option. You cannot drop a user that is currently connected.

Since the CREATE DATABASE command uses the CONTROLFILE REUSE option, the control files listed in the parameter will be reused. For this command to execute successfully, the control files listed in the parameter file must exist.

The ALERT file stores information about block corruption errors, internal errors, and the non-default initialization parameters used at instance start up. The ALERT file also records information about database start up, shut down, archiving, and recovery.

When transporting a tablespace, it is not necessary to drop or disable the indexes. However, bitmap indexes cannot be transported. All the tablespace data is moved when a tablespace is transported. The TRANSPORT_TABLESPACE parameter is required to create the tablespace metadata for the transport.

To sort a column based on a different language for a session, use this command:

ALTER SESSION SET NLS_SORT=language command;

This is useful when the data is sorted in a different schema than the output required.

Stored PL/SQL program units are created and stored in the data dictionary. Stored PL/SQL program units include procedures, functions, triggers, and packages.

The SYSTEM rollback segments record changes made to objects in the SYSTEM tablespace.

Private rollback segments are acquired by an instance when they are named in the parameter file or brought online using the ALTER ROLLBACK SEGMENT command.

Public rollback segments are in the pool of rollback segments.

Deferred rollback segments are created by the Oracle Server when a tablespace is brought offline. The Oracle Server will drop these segments when they are no longer needed.

To set up password file authentication:

1. Use ORAPWD on UNIX and Windows NT to create a password file.

2. Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE (for only one instance) or SHARED (for multiple instances). If you use the SHARED option, the password file will only recognize users SYS and INTERNAL.

3. When connecting to the database, you must provide a valid username and password. This is an example connect string:

CONNECT internal/oracle

When using the password utility to create a password file, you must specify a file name and location, a password for the SYS and INTERNAL users, and the maximum number of DBAs using the password file.

The control file records the names and locations of the datafiles and redo log files. The control file is updated when a datafile or redo log file is added, renamed, or dropped. The control file should be backed up immediately after any database structure changes.

DBA_FREE_SPACE displays the tablespace name, relative file number, file ID, block ID, and blocks. A query of this view is useful when you are going to create a new object or you know that a segment is about to extend, and you want to make sure that there is enough space in the containing tablespace.

Users who have been granted the SELECT_CATALOG_ROLE have SELECT privileges on the data dictionary views.

The TRUNCATE command deletes all the rows in the table and releases all the unused space. The corresponding indexes will also be truncated. If the REUSE option is used with the TRUNCATE command, the data is deleted from the table and its corresponding indexes, but no space is deallocated. If the DROP option is used with the TRUNCATE command, the extents specified by MINEXTENTS are retained. The DROP clause is the default.

A table cannot be truncated that is referenced by a foreign key.

The TABLE keyword specifies the name of the table to be exported in table mode. When a table is exported in table mode, the table definition, data, constraints, and grants on the table are included. Indexes and triggers are included if the export is performed by a privileged user.

Because index entries are smaller than the rows they index, there are usually more entries per block so the INITRANS value should be higher than the corresponding table.

Use the ALTER SESSION command to an individual NLS characteristic for a session. NLS parameters can be specified as initialization parameters on the server and as environmental variables on the client. NLS parameters can be used to define language-dependent conventions and territory-dependent conventions.

When a table is created, it is allocated at least one extent as specified by the INITIAL extent parameter at the segment or the tablespace level.

A role can be temporarily revoked from a user by disabling the role. The DEFAULT ROLE NONE option makes all the roles granted to a user nondefault roles. A user may have multiple default roles. By default, all of a user's roles are enabled when a user logs on.

2. Test

Fragen

- V$Session

• Validate Enterprise DBA Part 1A: Architecture and Administration - Maintaining Data Integrity

• Schema ?

utl*sql creates the additional views and tables needed for the database utilities.

prvt*.plb is an administrative script and provides wrapped package code.

dbms*.sql defines the database package specifications.

cat*.sql provides catalog and data dictionary information.

Join V$SESSION and V$SORT_USAGE to display the username, session address, tablespace, contents, extents, and blocks for active sorts.

DBA_EXTENTS displays the owner, segment name, and extent ID. It displays the name of location and size of each extent in a segment.

Profiles can be used for account locking, password aging and expiration, password history, and password complexity verification. CPU time, I/O operations, idle time, connect time, memory space, and concurrent sessions can be controlled using profiles. Password changes are accomplished using the ALTER USER command.

When assigning a COMPOSITE_LIMIT to a profile, Oracle calculates a weighted sum for these resource limits:

PRIVATE_SGA

CPU_PER_SESSION

CONNECT_TIME

LOGICAL_READS_PER_SESSION

To specify the weights assigned to each resource, use the ALTER RESOURCE COST command.

When planning your database configuration, you should:

1. Maintain at least two control files on separate disks.

2. Multiplex the groups of redo log files.

3. Separate datafiles based on their:

a. Propensity for fragmentation

b. Disk contention

c. Required administrative tasks

The control file records the names and locations of all the datafiles and redo log files. If you take a datafile offline and rename it, you must update the control file to bring the datafile back online. To update the control file, use the ALTER DATABASE RENAME FILE command.

The ANY keyword in a system privilege grants a user the privilege in any schema if the 07_DICTIONARY_ACCESSIBILITY parameter is set to TRUE. If the parameter is set to FALSE, then the user can access objects in any schema except the SYS schema.

A triggering event is an event that causes a trigger to fire. Triggering events are SQL statements, database events, or user events. The most commonly used triggering events by database administrators are:

1. An instance startup or shutdown

2. A specific error message or any error message

3. A user logon or logoff

4. A CREATE, ALTER, or DROP statement in any schema

Use the INSERT INTO SELECT command to perform a direct-load insert. This command can be used to copy data from one table to another table in the same database. The buffer cache is bypassed to speed up the insert. During a direct-load insert, users can be concurrently modifying existing data in the table. Data is loaded above the high water mark. During a direct-load insert, all indexes are maintained and all enabled constraints are enforced.

To deallocate all unused space in an index above the high water mark, use this command:

ALTER INDEX index DEALLOCATE UNUSED;

To deallocate unused space while keeping a specified number of bytes above the high water mark, use this command:

ALTER TABLE table DEALLOCATE UNUSED KEEP integer K/M;

If the size specified in the KEEP option is below MINEXTENTS, MINEXTENTS will be kept.

The order of import:

1. Type definitions

2. Table definitions

3. Table data

4. Table indexes

5. Integrity constraints, procedures, and views

5. Bitmap, functional, and domain indexes

Setting the COMPRESS Export parameter to 'Y' creates an initial extent the size of the current segment when the data is imported. Setting the value to 'N' will retain the size of the current extents.

Use the Export/Import utilities to:

1. Move data from one user to another user, from one database to another database, from one table to another table or from one tablespace to another tablespace.

2. Migrate data to a different operating system or version of Oracle

3. Create a logical backup.

4. Create test data.

6. Reorganize tables.

During conventional path loads:

1. COMMITs are used to make changes permanent.

2. Redo log entries are always generated.

3. All enabled constraints are enforced.

4. INSERT triggers fire.

5. Clustered tables can be loaded.

7. Other users can make changes to the tables being loaded.

If a transaction fails because there is insufficient space in a rollback segment, either the rollback segment has reached its MAXEXTENTS value or the the tablespace is out of space. To extend the tablespace, add a datafile or enable the AUTOEXTEND option for an existing datafile. To increase the MAXEXTENTS value for the rollback segment, use the ALTER ROLLBACK SEGMENT command with the STORAGE clause or drop and recreate the rollback segment with a larger extent sizes.

When you issue a CREATE DATABASE command that fails, you should:

1. Shut down the instance.

2. Delete any files created by the failed CREATE DATABASE command.

3. Correct the error in the CREATE DATABASE command.

4. Restart an instance.

ALTER, DELETE, EXECUTE, INDEX, INSERT, REFERENCES, SELECT, and UPDATE are object privileges.

When shutting down the database using the ABORT option, all current users are disconnected and pending transactions are not committed or rolled back. This method is the fastest way to shut down the database because the instance is terminated without performing a checkpoint or closing the files. Because the database will require instance recovery at the next STARTUP, you should only attempt this method after NORMAL and IMMEDIATE have failed.

3. Test

A snapshot too old error occurs when the Oracle Server cannot create a read consistent image of the data when a query is executed. The error is returned when a query is issued on a row with a lock, but before the query is finished the transaction commits and the rollback segment is overwritten. Reduce the chances of snapshot too old errors by creating rollback segments with high MINEXTENTS values, larger extent sizes, and high OPTIMAL values.

The sql.bsq script creates the base data dictionary tables. Because most of the information in these tables is encoded, most database users do not directly access these tables.

The catalog.sql script creates the data dictionary views to decode and summarize the data in the base tables. Synonyms are created on the views by the script to allow users easy access to the views.

Oracle Instance -> SGA, Background Processes

Oracle Database -> Control Files, Redo Log Files, Datafiles

A temporary segment is created in a PERMANENT tablespace when a user assigned to a PERMANENT tablespace for sorting issues a statement that requires more sort space than is available in memory.

V$SORT_SEGMENT will display information about TEMPORARY tablespaces with sort segments.

Use the ALTER INDEX REBUILD command to move an index to a different tablespace, remove deleted entries, change a reverse key index to a normal B-tree index or a normal B-tree index to a reverse key index, or to modify the index parameters. Indexes can be rebuilt online using the ONLINE keyword.

SYS and SYSTEM are automatically created and granted the DBA role when a database is created. The data dictionary base tables and views are created in the SYS schema. Additional tables are views are created in the SYSTEM schema which are used by Oracle tools. Oracle recommends that you create an additional user with the DBA role for DBA tasks.

The utlpwdmg.sql script enables the VERIFY_FUNCTION function and alters the DEFAULT profile.

When you query V$LOG, the status for a redo log group can be:

UNUSED - The group has never been written to.

CURRENT - The group is the current redo log group.

ACTIVE - The group is online and needed for instance recovery, but is not being written to.

INACTIVE - The group is online, but is not needed for instance recovery.

CLEARING - The log is being recreated as an empty log.

CLEARING_CURRENT - The current log file is being cleared of a closed thread.

The default value for PCTUSED is 40%. The default value for PCTFREE is 10%. Block space utilization parameters are set at the segment level. The MAXTRANS parameter determines the maximum number of concurrent transactions allowed on a block or index block. The INITRANS parameter determines the minimum number of transactions that can concurrently make changes to a block.

You can increase the size of a tablespace by:

1. adding a datafile using the ALTER TABLESPACE ADD DATAFILE command

2. resizing a datafile using the ALTER DATABASE DATAFILE RESIZE command

Use the ALTER INDEX REBUILD command to move an index to a different tablespace, remove deleted entries, change a reverse key index to a normal B-tree index or a normal B-tree index to a reverse key index, or to change the index parameters. Indexes can be rebuilt online using the ONLINE keyword, and can be coalesced using the COALESCE keyword.

DBA_TS_QUOTAS displays user tablespace quotas, number of bytes and blocks used, and the maximum number of bytes and blocks allowed.

Before issuing the CREATE DATABASE command:

1. Connect as SYSDBA using either a password file or by O/S authentication.

2. Create a parameter file.

Do not use the default values for:

DB_NAME

CONTROL_FILES

DB_BLOCK_SIZE

3. Create a parameter.

3. Start up an instance in NOMOUNT state.

he NLS_TERRITORY initialization parameter defines the values used to determine the territory-dependent conventions. It is used to determine the default date format and the local currency symbol. It determines the default values for the NLS_CURRENCY, NLS_ISO_CURRENCY, NLS_DATE_FORMAT, and NLS_NUMERIC_CHARACTER parameter

Set database checkpoint intervals by setting the LOG_CHECKPOINT_INTERVAL, LOG_CHECKPOINT_TIMEOUT, and FAST_START_IO_TARGET initialization parameters.

Set the FAST_START_IO_TARGET parameter to a small value to provide better instance recovery by ensuring that fewer blocks need to be recovered. This parameter is only available in the Enterprise Edition.

4. Test

Fragen:

• Passwort Reuse Max oder Max Reuse

• SQL Loader lernen (direct path etc.)

• Was ist Minextents

• Export/Import Utilities

The DBMS_TTS package is created by the dbmsplts.sql script that is executed by the catproc.sql script. The DBMS_TTS.TRANSPORT_SET_CHECK procedure is used to verify that a tablespace is self-contained before being transported. The procedure populates the TRANSPORT_SET_VIOLATIONS view. The

DBMS_TTS.ISSELFCONTAINED returns a value of TRUE if a transportable set is self-contained. The dependencies between the objects in the tablespace to be transported and the objects in the target database must be resolved by the user.

When a database is created, the Oracle server will allocate space to the control file based on the values of MAXLOGMEMBERS, MAXLOGFILES, MAXDATAFILES, MAXLOGHISTORY, and MAXINSTANCES in the CREATE statement. Change the values of the parameters by recreating the control file using the CREATE CONTROLFILE command.

System Change Numbers are assigned by the Oracle Server when a transaction commits. This provides a means for Oracle to perform consistency checks. Oracle assigns new SCN values by incrementing the previous SCN to provide a unique number independent of the operating system date and time.

The syntax to add a redo log group:

ALTER DATABASE prod

ADD LOGFILE GROUP 2 ('/disk2/log2a.rdo',

'/disk3/log3b.rdo') SIZE 1M;

If you specify the GROUP option, you must use an integer value. If you do not use the GROUP option, the Oracle Server will automatically generate a value. Use the SIZE option if you are creating a new file. Use the REUSE option if the file already exists.

To issue the command, you need the ALTER SYSTEM privilege. To place the database in RESTRICTED SESSION, start up the database using the STARTUP RESTRICT command or by issuing the ALTER SYSTEM command with the ENABLE RESTRICTED SESSION option. Only users with the RESTRICTED SESSION privilege will be able to connect to the database if the database is started in restricted mode. To disable RESTRICTED SESSION, use the ALTER SYSTEM command with the DISABLE RESTRICTED SESSION option.

The NLS_SORT parameter can be used with the NLS_UPPER, NLS_LOWER, NLS_INITCAP, and NLSSORT functions.

Use the ALTER INDEX REBUILD command to move an index to a different tablespace, remove deleted entries, change a reverse key index to a normal B-tree index or a normal B-tree index to a reverse key index, or to change the index parameters. Indexes can be rebuilt online using the ONLINE keyword, and can be coalesced using the COALESCE keyword.

Writes to the datafiles are deferred to reduce disk contention and I/O. Oracle's Fast COMMIT allows LGWR to record only the changes and SCN in the redo log files. The size of the transaction does not affect the time required to write to the redo log files. These writes are sequential and faster than writing entire blocks to the datafiles. Writes to the datafiles occur independently of the COMMIT.

The correct sequence of steps to move a datafile that is not in the SYSTEM tablespace is:

1. Take the tablespace offline.

2. Move the files using O/S commands.

3. Issue the ALTER TABLESPACE command.

5. Bring the tablespace back online.

Use SQL*Loader to load data from external files into Oracle database tables. SQL*Loader allows one or more input files, records to be combined, variable or fixed length fields, and any data format. Bypassing the database buffer cache is optional. Parallel direct loads allow concurrent direct load sessions to load data into a single table.

When using direct path load, no redo is generated for NOARCHIVELOG databases or ARCHIVELOG databases when the logging option is disabled. Direct path loads cannot be used to load clustered tables. Direct path load does not allow other users to make changes to the tables being loaded.

When password file authenticated is enabled, V$PWFILE_USERS will display the names of users with the SYSDBA or SYSOPER privileges.

NLS_INSTANCE_PARAMETERS displays the values of the NLS initialization parameters listed in the parameter file.

The control file used by SQL*Loader specifies the format of the input and output and any conditions that may determine which data is loaded from the input files.

A user must be assigned a tablespace quota to store objects. A user does not need quota in a temporary tablespace or tablespaces for rollback segments. If a user is not assigned a tablespace, the user's default tablespace is the SYSTEM tablespace. If a user is assigned a quota of UNLIMITED on a tablespace, the MAX_BLOCKS column in the DBA_TS_QUOTA view will return a value of -1.

Query SESSION_PRIVS to display all the privileges available to a user in the current session through granted privileges and enabled roles.

Query DBA_SYS_PRIVS to display all the system privileges granted to users and roles.

Query DBA_TAB_PRIVS to display all the object privileges granted to a specific user.

To perform a parallel direct-load insert, use the PARALLEL hint in the INSERT INTO SELECT command. The parallel direct-load insert uses parallel query slaves to insert the data. The data is written to temporary segments until the transaction commits.

V$CONTROLFILE_RECORD_SECTION displays information stored in the control file. The MAXDATAFILES parameter is recorded in the control file.

Temporary segments are based on the default storage parameters of the tablespace. For the TEMPORARY tablespace, INITIAL and NEXT should be equal to each other and a multiple of SORT_AREA_SIZE plus DB_BLOCK_SIZE to reduce the possibility of fragmentation. PCTINCREASE should always be equal to zero.

Because PCTINCREASE is always zero, the second and all other extents will always be the size of NEXT.

A role can be granted to a user or another role. A role cannot be granted to itself.

A role can be enabled or disabled for an individual user granted the role.

Roles are not in a schema, can require a password, and can have both system and object privileges.