Explore topic-wise InterviewSolutions in .

TNSNAMES.ORA and SQLNET.ORA

TNSNAMES.ORA and SQLNET.ORA

The ANALYZE COMMAND.

The ANALYZE command.

SHOW ERRORS

SHOW ERRORS

Partitioning is a method of TAKING LARGE tables and indexes and SPLITTING them into smaller, more MANAGEABLE pieces.

Partitioning is a method of taking large tables and indexes and splitting them into smaller, more manageable pieces.

ALTER INDEX <index_name> REBUILD;

ALTER INDEX <index_name> REBUILD;

TURN on FINE GRAIN AUDITING for the table.

Turn on fine grain auditing for the table.

DBA_FREE_SPACE

DBA_FREE_SPACE

DBA_DATA_FILES

DBA_DATA_FILES

ALTER DATABASE DATAFILE &LT;datafile_name> RESIZE <new_size>;

ALTER DATABASE DATAFILE <datafile_name> RESIZE <new_size>;

ALTER TABLESPACE &LT;tablespace_name> ADD DATAFILE <datafile_name> SIZE <size>

ALTER TABLESPACE <tablespace_name> ADD DATAFILE <datafile_name> SIZE <size>

GRANT the CONNECT to the USER.

Grant the CONNECT to the user.

The SYSTEM TABLESPACE.

The SYSTEM tablespace.

You COULD USE Logminer or STREAMS

You could use Logminer or Streams

ALTER SYSTEM SWITCH LOGFILE;

ALTER SYSTEM SWITCH LOGFILE;

USE of V$SESSION and V$SESSION_WAIT

Use of V$SESSION and V$SESSION_WAIT

The JOB QUEUE PROCESSES.

The Job Queue Processes.

PMON

PMON

Materialized views are objects that are reduced SETS of INFORMATION that have been summarized, grouped, or AGGREGATED from BASE tables. They are typically USED in data warehouse or decision support systems.

Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.

In UNIX $ORACLE_HOME/rdbms/audit, in WINDOWS the EVENT VIEWER

In unix $ORACLE_HOME/rdbms/audit, in Windows the event viewer

Buffer CACHE ADVICE, Segment Level STATISTICS, & Timed Statistics.

Buffer Cache Advice, Segment Level Statistics, & Timed Statistics.

Table functions are DESIGNED to return a set of rows through PL/SQL logic but are INTENDED to be used as a normal table or VIEW in a SQL statement. They are also used to pipeline information in an ETL process.

Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as a normal table or view in a SQL statement. They are also used to pipeline information in an ETL process.

SELECT DBTIMEZONE from DUAL;

select DBTIMEZONE from dual;

ORACLE_BASE is the root DIRECTORY for ORACLE. ORACLE_HOME LOCATED beneath ORACLE_BASE is where the oracle products reside.

ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.

You GET this error when you get a snapshot too OLD within rollback. It can usually be solved by increasing the undo retention or increasing the SIZE of rollbacks. You should also look at the logic involved in the application GETTING the error MESSAGE.

You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the application getting the error message.

Use the BUFFER cache ADVISORY over a given WORKLOAD and then query the v$db_cache_advice table. If a CHANGE was necessary then I would use the alter system set db_cache_size command.

Use the buffer cache advisory over a given workload and then query the v$db_cache_advice table. If a change was necessary then I would use the alter system set db_cache_size command.

CREATE a PLAN table with utlxplan.SQL.
Use the EXPLAIN plan SET statement_id = ‘tst1’ into plan_table for a SQL statement
Look at the explain plan with utlxplp.sql or utlxpls.sql

Create a plan table with utlxplan.sql.
Use the explain plan set statement_id = ‘tst1’ into plan_table for a SQL statement
Look at the explain plan with utlxplp.sql or utlxpls.sql

The INST_ID column which indicates the instance in a RAC ENVIRONMENT the information CAME from.

The INST_ID column which indicates the instance in a RAC environment the information came from.

STARTUP NOMOUNT – Instance startup. Control File is read here.

STARTUP MOUNT – The database is MOUNTED. Datafiles are read for the status and CHECKED with control file.

STARTUP OPEN – The database is OPENED. Normal users can access.

STARTUP NOMOUNT – Instance startup. Control File is read here.

STARTUP MOUNT – The database is mounted. Datafiles are read for the status and checked with control file.

STARTUP OPEN – The database is opened. Normal users can access.

ALTER DATABASE backup CONTROL FILE to TRACE.

Alter database backup control file to trace.

A PRIMARY KEY and a FOREIGN key.

A primary key and a foreign key.

A BITMAP INDEX.

A Bitmap index.

FASTER ACCESS to DATA BLOCKS in a table.

Faster access to data blocks in a table.

Both the truncate and DELETE command have the desired outcome of GETTING rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high WATER mark and produces a now rollback. The delete command, on the other HAND, is a DML operation, which will produce a rollback and thus take longer to COMPLETE.

Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete.

In the ALERT LOG.

In the alert log.

USE the DESCRIBE COMMAND or use the dbms_metadata.get_ddl PACKAGE.

Use the describe command or use the dbms_metadata.get_ddl package.

A data block is the smallest unit of LOGICAL storage for a database object. As OBJECTS grow they take chunks of additional storage that are composed of contiguous data blocks. These GROUPINGS of contiguous data blocks are called extents. All the extents that an object takes when grouped TOGETHER are considered the SEGMENT of the database object. Extent may not be continuous.

A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object. Extent may not be continuous.

ISSUE the CREATE SPFILE from PFILE COMMAND.

Issue the create spfile from pfile command.

I WOULD create a TEXT based backup CONTROL FILE, STIPULATING where on disk all the data files where and then issue the recover command with the using backup control file clause.

I would create a text based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause.

A HOT backup is basically taking a backup of the database while it is still up and running and it must be in archive log MODE. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is OCCURRING and you can recover the database to any point in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a SLIGHT performance gain as the database is not cutting archive logs to disk.

A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any point in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.