Explore topic-wise InterviewSolutions in .

A RESOURCE CONSUMER group is USED to group together similar users BASED on their resource NEEDS.

A resource consumer group is used to group together similar users based on their resource needs.

Database RESOURCE MANAGER is composed of the following FOUR elements : Resource consumer group, resource plan,resource allocation method,resource plan directive.

Database resource manager is composed of the following four elements : Resource consumer group, resource plan,resource allocation method,resource plan directive.

DBA's can AUTOMATICALLY switch users from one RESOURCE group to another ,based on preset resource usage CRITERIA,and can LIMIT the amount of undo space a resource group can use.

DBA's can automatically switch users from one resource group to another ,based on preset resource usage criteria,and can limit the amount of undo space a resource group can use.

1) The database resource manager enables us to limit the length of time a user session can STAY idle and to automatically TERMINATE long-running SQL statements and user SESSIONS.
2) USING the database resource manager we can set initial login priorities for various consumer groups. 
3) By using the concept of active session pool,we can specify the maximum number of concurrent active sessions for a consumer group-the Database resource manager will automatically queue all the subsequent requests until the currently running sessions complete.

1) The database resource manager enables us to limit the length of time a user session can stay idle and to automatically terminate long-running SQL statements and user sessions.
2) Using the database resource manager we can set initial login priorities for various consumer groups. 
3) By using the concept of active session pool,we can specify the maximum number of concurrent active sessions for a consumer group-the Database resource manager will automatically queue all the subsequent requests until the currently running sessions complete.

Database RESOURCE manager allows us to CREATE resource plans, which specify how much of our resources should go to various consumer groups.We can group users based on their resource REQUIREMENT and we can have the database resource manager allocate a preset amount of resources to these groups.We can easily prioritize our users and jobs.

Database resource manager allows us to create resource plans, which specify how much of our resources should go to various consumer groups.We can group users based on their resource requirement and we can have the database resource manager allocate a preset amount of resources to these groups.We can easily prioritize our users and jobs.

CHAR pads BLANK spaces to the MAXIMUM length.
VARCHAR2 does not pad blank spaces.
For CHAR the maximum length is 255 and 2000 for VARCHAR2.

CHAR pads blank spaces to the maximum length.
VARCHAR2 does not pad blank spaces.
For CHAR the maximum length is 255 and 2000 for VARCHAR2.

Only ONE LONG column is ALLOWED. It is not POSSIBLE to use LONG column in WHERE or ORDER BY clause.

Only one LONG column is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause.

- To modify the DATATYPE of a column the column MUST be empty.
- To ADD a column with NOT NULL constrain, the table must be empty.

- To modify the datatype of a column the column must be empty.
- To add a column with NOT NULL constrain, the table must be empty.

It won't, Because SYSDATE FORMAT contains TIME attached with it.

It won't, Because SYSDATE format contains time attached with it.

SEQUENCE name CURRVAL, sequence name NEXTVAL. It is not possible. Only if you ACCESS NEXT value in the SESSION, current value can be accessed.

Sequence name CURRVAL, sequence name NEXTVAL. It is not possible. Only if you access next value in the session, current value can be accessed.

If changes are MADE to the TABLES and these tables are the BASE tables of a view, then the changes will be reference on the view.

If changes are made to the tables and these tables are the base tables of a view, then the changes will be reference on the view.

Odd NUMBER of records:

EVEN number of records:

Odd number of records:

Even number of records:

SQL> SET NULL ‘N/A’ to RESET SQL> Set NULL.

SQL> Set NULL ‘N/A’ to reset SQL> Set NULL.

12 TRIGGERS.

12 triggers.

A stored procedure is pre-loaded in memory for faster execution. It ALLOWS the DBMS control of PERMISSIONS for security PURPOSES. It also eliminates the need to RECOMPILE components when minor changes occur to the database.

A stored procedure is pre-loaded in memory for faster execution. It allows the DBMS control of permissions for security purposes. It also eliminates the need to recompile components when minor changes occur to the database.

A table SCANS.

A table scans.

-TABLES
-VIEWS
-INDEXES
-SYNONYMS
-SEQUENCES
-TABLESPACES etc.

-TABLES
-VIEWS
-INDEXES
-SYNONYMS
-SEQUENCES
-TABLESPACES etc.

-SELECT.
-CONNECT.
-RESOURCES.

-SELECT.
-CONNECT.
-RESOURCES.

RENAME is a PERMANENT NAME given to a table or column whereas Alias is a temporary name given to a table or column which do not exist once the SQL STATEMENT is executed.

Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column which do not exist once the SQL statement is executed.

Outer Join--Its a join CONDITION USED where you can query all the rows of one of the TABLES in the join condition EVEN though they don?t satisfy the join condition.

Outer Join--Its a join condition used where you can query all the rows of one of the tables in the join condition even though they don?t satisfy the join condition.

ORACLE Call Interface is a method of accesing database from a 3GL program. Uses--No 
precompiler is REQUIRED,PL/SQL blocks are executed like other DML statements. 
The OCI LIBRARY provides 
--functions to parse SQL statemets 
--bind input variables 
--bind output variables 
--EXECUTE statements 
--fetch the results 

Oracle Call Interface is a method of accesing database from a 3GL program. Uses--No 
precompiler is required,PL/SQL blocks are executed like other DML statements. 
The OCI library provides 
--functions to parse SQL statemets 
--bind input variables 
--bind output variables 
--execute statements 
--fetch the results 

OPEN cursor VARIABLE FOR SELECT...Statement.
CLOSE cursor variable In order to associate a cursor variable with a particular SELECT statement. OPEN SYNTAX is USED. In order to free the resources used for the QUERY CLOSE statement is used.

OPEN cursor variable FOR SELECT...Statement.
CLOSE cursor variable In order to associate a cursor variable with a particular SELECT statement. OPEN syntax is used. In order to free the resources used for the query CLOSE statement is used.

DELETE from table_name where ROWID not in (SELECT max(rowid) from table group by 
duplicate_values_field_name); or delete duplicate_values_field_name dv from table_name.
where rowid <(select MIN(rowid) from table_name TB where ta.dv=tb.dv); 

delete from table_name where rowid not in (select max(rowid) from table group by 
duplicate_values_field_name); or delete duplicate_values_field_name dv from table_name.
where rowid <(select min(rowid) from table_name tb where ta.dv=tb.dv); 

EXISTS is more FASTER than IN because EXISTS RETURNS a Boolean value WHEREAS IN returns a value.

EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.

The best METHOD is to use the CHR() FUNCTION (CHR(10) is a return/linefeed) and the CONCATENATION function "||". Another method, although it is hard to document and isn?t always portable is to use the return/linefeed as a PART of a quoted STRING.

The best method is to use the CHR() function (CHR(10) is a return/linefeed) and the concatenation function "||". Another method, although it is hard to document and isn?t always portable is to use the return/linefeed as a part of a quoted string.

There are several ways. One is to look at the V$SESSION or v$PROCESS views. Another way is to check the current_logins parameter in the v$sysstat view. Another if you are on UNIX is to do a "ps -ef|grep oracle|wc -l? COMMAND, but this only works against a single instance installation.

There are several ways. One is to look at the v$session or v$process views. Another way is to check the current_logins parameter in the v$sysstat view. Another if you are on UNIX is to do a "ps -ef|grep oracle|wc -l? command, but this only works against a single instance installation.

The best way is to analyze the table and then USE the data PROVIDED in the DBA_TABLES VIEW to get the average row LENGTH and other pertinent data for the calculation. The quick and dirty way is to look at the number of blocks the table is actually using and ratio the number of rows in the table to its number of blocks against the number of EXPECTED rows.

The best way is to analyze the table and then use the data provided in the DBA_TABLES view to get the average row length and other pertinent data for the calculation. The quick and dirty way is to look at the number of blocks the table is actually using and ratio the number of rows in the table to its number of blocks against the number of expected rows.

You need to check that the USER has specified the FULL NAME of the object (SELECT empid FROM scott.EMP; instead of SELECT empid FROM emp;) or has a synonym that points to the object (CREATE SYNONYM emp FOR scott.emp;).

You need to check that the user has specified the full name of the object (SELECT empid FROM scott.emp; instead of SELECT empid FROM emp;) or has a synonym that points to the object (CREATE SYNONYM emp FOR scott.emp;).

This is caused by large or long RUNNING transactions that have either WRAPPED onto their own rollback space or have had another transaction write on part of their rollback space. This can be PREVENTED or mitigated by breaking the transaction into a set of smaller transactions or increasing the size of the rollback SEGMENTS and their EXTENTS.

This is caused by large or long running transactions that have either wrapped onto their own rollback space or have had another transaction write on part of their rollback space. This can be prevented or mitigated by breaking the transaction into a set of smaller transactions or increasing the size of the rollback segments and their extents.

You can't use hot backup WITHOUT being in ARCHIVELOG mode. So no, you couldn't recover.

You can't use hot backup without being in archivelog mode. So no, you couldn't recover.

The index is created in the users DEFAULT TABLESPACE and all SIZING information is LOST. Oracle doesn't store this information as a part of the constraint definition, but only as part of the index definition, when the constraint was disabled the index was dropped and the information is gone.

The index is created in the users default tablespace and all sizing information is lost. Oracle doesn't store this information as a part of the constraint definition, but only as part of the index definition, when the constraint was disabled the index was dropped and the information is gone.

You USE the ALTER TABLE COMMAND for both. However, for the enable clause you must specify the USING INDEX and TABLESPACE clause for PRIMARY keys.

You use the ALTER TABLE command for both. However, for the enable clause you must specify the USING INDEX and TABLESPACE clause for primary keys.

This results in the INDEX that is automatically generated being placed in then users DEFAULT tablespace. SINCE this will usually be the same tablespace as the table is being created in, this can cause serious performance problems.

This results in the index that is automatically generated being placed in then users default tablespace. Since this will usually be the same tablespace as the table is being created in, this can cause serious performance problems.

The Oracle system will use the default NAME of SYS_Cxxxx where xxxx is a system generated NUMBER. This is bad since it makes tracking which table the constraint BELONGS to or what the constraint does HARDER.

The Oracle system will use the default name of SYS_Cxxxx where xxxx is a system generated number. This is bad since it makes tracking which table the constraint belongs to or what the constraint does harder.

Oracle provides a number of PACKAGES in the form of the DBMS_ packages owned by the SYS user. The packages used by DBAS MAY INCLUDE: DBMS_SHARED_POOL, DBMS_UTILITY, DBMS_SQL, DBMS_DDL, DBMS_SESSION, DBMS_OUTPUT and DBMS_SNAPSHOT.

Oracle provides a number of packages in the form of the DBMS_ packages owned by the SYS user. The packages used by DBAs may include: DBMS_SHARED_POOL, DBMS_UTILITY, DBMS_SQL, DBMS_DDL, DBMS_SESSION, DBMS_OUTPUT and DBMS_SNAPSHOT.

USE the ALTER TABLESPACE ..... SHRINK COMMAND.

Use the ALTER TABLESPACE ..... SHRINK command.

The IMPORT IGNORE option tells import to ignore "ALREADY exists" errors. If it is not specified the tables that already EXIST will be SKIPPED. If it is specified, the ERROR is ignored and the tables data will be INSERTED. The default value is N.

The IMPORT IGNORE option tells import to ignore "already exists" errors. If it is not specified the tables that already exist will be skipped. If it is specified, the error is ignored and the tables data will be inserted. The default value is N.

There are SEVERAL possible METHODS, export-import, CREATE TABLE... AS SELECT, or COPY.

There are several possible methods, export-import, CREATE TABLE... AS SELECT, or COPY.

OPEN then FETCH then LOOP followed by the EXIT when. If not specified in this ORDER will result in the final RETURN being done twice because of the way the %NOTFOUND is handled by PL/SQL.

OPEN then FETCH then LOOP followed by the exit when. If not specified in this order will result in the final return being done twice because of the way the %NOTFOUND is handled by PL/SQL.

PL/SQL TABLES are scalar arrays that can be referenced by a binary integer. They can be used to HOLD VALUES for use in later queries or calculations. In ORACLE they will be ABLE to be of the %ROWTYPE designation, or RECORD.

PL/SQL tables are scalar arrays that can be referenced by a binary integer. They can be used to hold values for use in later queries or calculations. In Oracle they will be able to be of the %ROWTYPE designation, or RECORD.

%ROWTYPE allows you to associate a VARIABLE with an entire table ROW. The %TYPE ASSOCIATES a variable with a single column type.

%ROWTYPE allows you to associate a variable with an entire table row. The %TYPE associates a variable with a single column type.

Candidate should mention USE of DECLARE statement, a FUNCTION must return a value while a procedure doesn't have to. Also ONE can use function in Select SQL statement but not procedure.

Candidate should mention use of DECLARE statement, a function must return a value while a procedure doesn't have to. Also one can use function in Select Sql statement but not procedure.

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.
STARTUP MOUNT - The database is MOUNTED.
STARTUP OPEN - The database is opened.

STARTUP NOMOUNT - Instance startup.
STARTUP MOUNT - The database is mounted.
STARTUP OPEN - The database is opened.

ALTER database backup CONTROL file to TRACE.

Alter database backup control file to trace.

ARCHIVELOG mode is a mode that you can put the database in for creating a BACKUP of all transactions that have occurred in the database so that you can RECOVER to any POINT in time. NOARCHIVELOG mode is basically the ABSENCE of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point in time. NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive LOG and thus increases the performance of the database slightly.

ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any point in time. NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point in time. NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.

DISABLE the foreign key CONSTRAINT to the PARENT, DROP the table, re-create the table, enable the foreign key constraint.

Disable the foreign key constraint to the parent, drop the table, re-create the table, enable the foreign key constraint.