1. Explain the difference between a hot backup and a cold backup
and the benefits associated with each.
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.
2. You have just had to restore from backup and do not have any
control files. How would you go about bringing up this database?
I would create a text based backup control
file, stipulating اشتراط where on disk all the data files
were and then issue the recover command with the using backup control file
clause.
3. How do you switch from an init.ora file to a spfile? Issue
the create spfile from pfile command.
4. Explain the difference between a data block, an extent and a
segment.
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.
5. Give two examples of how you might determine the structure of
the table DEPT.
Use the describe command or use the dbms_metadata.get_ddl
package.
SELECT DBMS_METADATA.GET_DDL('TABLE','MERCHANT_MACHINES') FROM dual;
6. Where would you look for errors from the database engine? In the alert log.
7. Compare and contrast TRUNCATE and DELETE for a table.
Both the truncate and delete command delete
rows in a table. The differ is that truncate command is a DDL (Data
Definition Language ) operation and just
moves the high water mark and produces few rollback data. The delete command,
on the other hand, is a DML, which will produce rollback data and thus take
longer to complete.
8. Give the reasoning behind using an index. Faster access to data blocks in a table.
9. Give the two types of tables involved in producing a star
schema and the type of data they hold.
Fact tables and dimension tables. A fact
table contains measurements while dimension tables will contain data that will
help describe the fact tables.
10. What type of index should you use on a fact table? A Bitmap
index.
11. Give some examples of the types of database contraints you
may find in Oracle and indicate their purpose.
·
A Primary or Unique Key can
be used to enforce uniqueness on one or more columns.
·
A Referential Integrity
Contraint can be used to enforce a Foreign Key relationship between two tables.
·
A Not Null constraint - to
ensure a value is entered in a column
·
A Value Constraint - to
check a column value against a specific set of values.
12. A table is classified as a parent table and you want to drop
and re-create it. How would you do this without affecting the children tables?
Disable the foreign key constraint to the
parent, drop the table, re-create the table, enable the foreign key constraint.
13. Explain the difference between ARCHIVELOG mode and
NOARCHIVELOG mode and the benefits and disadvantages to each.
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.
14. What command would you use to create a backup control file? Alter database backup control file to
trace.
15. Give the stages of instance startup to a usable state where
normal users may access it.
STARTUP NOMOUNT - Instance startup
STARTUP MOUNT - The database is mounted
STARTUP OPEN - The database is opened
16. What column differentiates the V$ views to the GV$ views and
how?
V$ is for single instance. gv$ is for RAC; all instances.
Oracle RAC allows multiple computers to run Oracle RDBMS software
simultaneously while accessing a single database,
thus providing clustering. In a non-RAC Oracle database, a
single instance accesses a single database. The database
consists of a collection of data files, control files, and redo logs
located on disk. The instance comprises the collection of
Oracle-related memory and operating
system processes that run on a computer system.
17. How would you go about generating an EXPLAIN plan?
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
18. How would you go about increasing the buffer cache hit ratio?
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.
19. Explain an ORA-01555.
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.
20. Explain the difference between $ORACLE_HOME and $ORACLE_BASE.
ORACLE_BASE - The home directory for the Oracle software
(e.g. /u01/app/oracle/product/10.2.1) with subdirectories like:
bin – rdbms – sqlplus - etc.
ORACLE_HOME - Below ORACLE_BASE, the files for each database: Oracle Interview
Questions
1. How would you determine the time zone under which a database
was operating?
SELECT dbtimezone FROM DUAL; SELECT TZ_OFFSET('America/Menominee') FROM
DUAL;
select sessiontimezone from dual;
2. Explain the use of setting GLOBAL_NAMES equal to TRUE.
It ensure the use of consistent naming
conventions for databases and links in a networked environment.
3. What command would you use to encrypt a PL/SQL application? WRAP
4. Explain the difference between a FUNCTION, PROCEDURE and
PACKAGE.
They are all named PL/SQL blocks.
Function must return a value. Can be called
inside a query.
Procedure may or may not return value.
Package is the collection of functions,
procedures, variables which can be logically grouped together.
9. When a user process fails, what background process cleans up
after it? PMON
10. What background process refreshes materialized views? Job Queue Process (CJQ)
11. How would you determine what sessions are connected and what
resources they are waiting for?
v$session,v$session_wait
13. How would you force a log switch? alter system switch
logfile;
15. What does coalescing a tablespace do?
Coalesce simply takes contigous free extents and makes them into a
single bigger free extent.
16. What is the difference between a TEMPORARY tablespace and a
PERMANENT tablespace?
TEMP tablespace gets cleared once the
transaction is done where as PERMANENT tablespace retails the data.
17. Name a tablespace automatically created when you create a
database. SYSTEM
18. When creating a user, what permissions must you grant to
allow them to connect to the database?
Grant create session to username;
19. How do you add a data file to a tablespace?
alter tablespace USERS add datafile
'/ora01/oradata/users02.dbf' size 50M;
20. How do you resize a data file?
alter database datafile
'/ora01/oradata/users02.dbf' resize 100M;
21. What view would you use to look at the size of a data file?
dba_data_files
22. What view would you use to determine free space in a
tablespace?
dba_free_space
select
* from dba_free_space where TABLESPACE_NAME = 'BASIC_DATA';
23. How would you determine who has added a row to a table?
By implementing an INSERT trigger for logging
details during each INSERT operation on the table
select
* from all_objects ao where ao.OBJECT_TYPE = 'TABLE' and ao.OWNER = 'ALPHA'
24. How can you rebuild an index? ALTER INDEX index_name REBUILD;
25. Explain what partitioning is and what its benefit is.
A table partition is also a table segment,
and by using partitioning technique we can enhance performance of table access.
26. You have just compiled a PL/SQL package but got errors, how
would you view the errors? show
errors
27. How can you gather statistics on a table? exec dbms_stats.gather_table_stats
EXEC
DBMS_STATS.gather_table_stats('ALPHA', 'test2');
Also, remember to analyze all associated
indexes on that table using dbms_stats.gather_index_stats
28. How can you enable a trace for a session? alter session
set sql_trace='TRUE';
29. What is the difference between the SQL*Loader and IMPORT utilities?
SQL*LOADER loads external data which is in OS
files to oracle database tables while IMPORT utility imports data only which is
exported by EXPORT utility of oracle database.
30. Name two files used for network connection to a database.
TNSNAMES.ORA and SQLNET.ORA
2. What is a mutating table error and how can you get around it?
This happens with triggers. It occurs because the trigger is trying to update a
row it is currently using. The usual fix involves either use of views or
temporary tables so the database is selecting from one while updating the
other.
3. Describe the use of %ROWTYPE and %TYPE in PL/SQL Expected
answer: %ROWTYPE allows you to associate a variable with an entire table
row. The %TYPE associates a variable with a single column type.
4. What packages (if any) has Oracle provided for use by
developers? Expected answer: Oracle provides the DBMS_ series of packages.
There are many which developers should be aware of such as DBMS_SQL, DBMS_PIPE
The
5. Describe the use of PL/SQL tables Expected answer: 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 8 they will be able to be of the %ROWTYPE designation, or RECORD.
DBMS_PIPE
package lets two or more sessions in the same instance communicate.,
DBMS_TRANSACTION, DBMS_LOCK The DBMS_LOCK
package provides an interface
to Oracle Lock Management services., DBMS_ALERT DBMS_ALERT
supports asynchronous notification of database
events (alerts)., DBMS_OUTPUT The DBMS_OUTPUT
package enables you to send
messages from stored procedures, DBMS_JOB DBMS_JOB
subprograms schedule and manage jobs in the job queue., DBMS_UTILITY The DBMS_UTILITY package provides various utility subprograms,
DBMS_DDL This package provides access to some SQL data definition language
(DDL) statements from stored procedures. It also provides special
administration operations that are not available as Data Definition Language
statements (DDLs). , UTL_FILE With the UTL_FILE
package, PL/SQL programs can
read and write operating system text files. UTL_FILE
provides a restricted version of operating system stream file I/O.. 5. Describe the use of PL/SQL tables Expected answer: 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 8 they will be able to be of the %ROWTYPE designation, or RECORD.
6. When is a declare statement needed ? The DECLARE
statement is used in PL/SQL anonymous blocks such as with stand alone,
non-stored PL/SQL procedures. It must come first in a PL/SQL stand alone file
if it is used.
7. In what order should a open/fetch/loop set of commands in a
PL/SQL block be implemented if you use the %NOTFOUND cursor variable in
the exit when statement? Why? Expected answer: 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.
8. What are SQLCODE and SQLERRM and why are they important for
PL/SQL developers? Expected answer: SQLCODE returns the value of the error
number for the last error encountered. The SQLERRM returns the actual error
message for the last error encountered. They can be used in exception handling
to report, or, store in an error log table, the error that occurred in the
code. These are especially useful for the WHEN OTHERS exception.
DECLARE
name employees.last_name%TYPE;
v_code NUMBER;
v_errm VARCHAR2(64);
BEGIN
SELECT last_name INTO name FROM employees WHERE employee_id = 1000;
EXCEPTION
WHEN OTHERS THEN
v_code := SQLCODE;
v_errm := SUBSTR(SQLERRM, 1 , 64);
DBMS_OUTPUT.PUT_LINE('The error code is ' || v_code || '- ' || v_errm);
END;
/
9. How can you find within a PL/SQL block, if a cursor is open?
Use the %ISOPEN cursor status variable.
10. How can you generate debugging output from PL/SQL? Use
the DBMS_OUTPUT package. command, but this only shows errors. The DBMS_OUTPUT
package can be used to show intermediate results from loops and the status of
variables as the procedure is executed. The new package UTL_FILE can also be
used.
11. What are the types of triggers? BEFORE, AFTER, ROW,
TABLE, INSERT, UPDATE, DELETE and ALL key words: BEFORE ALL ROW INSERT AFTER
ALL ROW INSERT BEFORE INSERT AFTER INSERT etc.
1. A tablespace has a table with 30 extents in it. Is this bad? Why
or why not.
Multiple extents in and of themselves aren?t bad. However if you
also have chained rows this can hurt performance.
2. How do you set up tablespaces during an Oracle installation?
You should always attempt to use the Oracle Flexible Architecture
standard or another partitioning scheme to ensure proper separation of SYSTEM,
ROLLBACK, REDO LOG, DATA, TEMPORARY and INDEX segments.
3. You see multiple fragments in the SYSTEM tablespace, what should
you check first?
Ensure that users don?t have the SYSTEM tablespace as their
TEMPORARY or DEFAULT tablespace assignment by checking the DBA_USERS view.
4. What are some indications that you need to increase the
SHARED_POOL_SIZE parameter? Poor data
dictionary or library cache hit ratios, getting error ORA-04031. Another
indication is steadily decreasing performance with all other tuning parameters
the same.
5. What is the general guideline for sizing db_block_size and
db_multi_block_read for an application that does many full table scans?
Oracle almost always reads in 64k chunks. The two should have a
product equal to 64 or a multiple of 64.
6. What is the fastest query method for a table Fetch by rowid
7. Explain the use of TKPROF? What initialization parameter should
be turned on to get full TKPROF output?
The tkprof tool is a tuning tool used to determine cpu and execution
times for SQL statements. You use it by first setting timed_statistics to true
in the initialization file and then turning on tracing for either the entire
database via the sql_trace parameter or for the session using the ALTER SESSION
command. Once the trace file is generated you run the tkprof tool against the
trace file and then look at the output from the tkprof tool. This can also be
used to generate explain plan output.
8. When looking at v$sysstat you see that sorts (disk) is high. Is
this bad or good? If bad -How do you correct it?
If you get excessive disk sorts this is bad. This indicates you need
to tune the sort area parameters in the initialization files. The major sort
are parameter is the SORT_AREA_SIZe parameter.
9. When should you increase copy latches? What parameters control
copy latches
When you get excessive contention for the copy latches as shown by
the "redo copy" latch hit ratio. You can increase copy latches via
the initialization parameter LOG_SIMULTANEOUS_COPIES to twice the number of
CPUs on your system.
10. Where can you get a list of all initialization parameters for
your instance? How about an indication if they are default settings or have
been changed
You can look in the init.ora file for an indication of manually set
parameters. For all parameters, their value and whether or not the current
value is the default value, look in the v$parameter view.
11. Describe hit ratio as it pertains to the database buffers. What
is the difference between instantaneous and cumulative hit ratio and which
should be used for tuning
The hit ratio is a measure of how many times the database was able
to read a value from the buffers verses how many times it had to re-read a data
value from the disks. A value greater than 80-90% is good, less could indicate
problems. If you simply take the ratio of existing parameters this will be a
cumulative value since the database started. If you do a comparison between
pairs of readings based on some arbitrary time span, this is the instantaneous
ratio for that time span. Generally speaking an instantaneous reading gives
more valuable data since it will tell you what your instance is doing for the
time it was generated over.
12. Discuss row chaining, how does it happen? How can you reduce it?
How do you correct it
Row chaining occurs when a VARCHAR2 value is updated and the length
of the new value is longer than the old value and won?t fit in the remaining
block space. This results in the row chaining to another block. It can be
reduced by setting the storage parameters on the table to appropriate values.
It can be corrected by export and import of the effected table.
1. Give one method for transferring a table from one schema to
another:
There are several possible methods, export-import, CREATE TABLE...
AS SELECT, or COPY.
2. What is the purpose of the IMPORT option IGNORE? What is it?s
default setting 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.
3. You have a rollback segment in a version 7.2 database that has
expanded beyond optimal, how can it be restored to optimal Use the ALTER TABLESPACE ..... SHRINK
command.
4. If the DEFAULT and TEMPORARY tablespace clauses are left out of a
CREATE USER command what happens? Is this bad or good? Why
The user is assigned the SYSTEM tablespace as a default and
temporary tablespace. This is bad because it causes user objects and temporary
segments to be placed into the SYSTEM tablespace resulting in fragmentation and
improper table placement (only data dictionary objects and the system rollback
segment should be in SYSTEM).
5. What are some of the Oracle provided packages that DBAs should be
aware of
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. They may also try to answer with the UTL*.SQL or CAT*.SQL
series of SQL procedures. These can be viewed as extra credit but aren?t part
of the answer.
6. What happens if the constraint name is left out of a constraint
clause
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.
7. What happens if a tablespace clause is left off of a primary key
constraint clause
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.
8. What is the proper method for disabling and re-enabling a primary
key constraint
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.
9. What happens if a primary key constraint is disabled and then
enabled without fully specifying the index clause
The index is created in the user?s 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.
10. (On UNIX) When should more than one DB writer process be used?
How many should be used
If the UNIX system being used is capable of asynchronous IO then only
one is required, if the system is not capable of asynchronous IO then up to
twice the number of disks used by Oracle number of DB writers should be
specified by use of the db_writers initialization parameter.
11. You are using hot backup without being in archivelog mode, can
you recover in the event of a failure? Why or why not
You can?t use hot backup without being in archivelog mode. So no,
you couldn?t recover.
12. What causes the "snapshot too old" error? How can this
be prevented or mitigated
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.
13. How can you tell if a database object is invalid By checking the
status column of the DBA_, ALL_ or USER_OBJECTS views, depending upon whether
you own or only have permission on the view or are using a DBA account.
13. A user is getting an ORA-00942 error yet you know you have
granted them permission on the table, what else should you check
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 balls to the object (create synonym emp for scott.emp;)
14. A developer is trying to create a view and the database won?t
let him. He has the "DEVELOPER" role which has the "CREATE
VIEW" system privilege and SELECT grants on the tables he is using, what
is the problem
You need to verify the developer has direct grants on all tables
used in the view. You can?t create a stored object with grants given through
views.
15. If you have an example table, what is the best way to get sizing
data for the production table implementation
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.
16. How can you find out how many users are currently logged into
the database? How can you find their operating system id
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.
17. A user selects from a sequence and gets back two values, his
select is: SELECT pk_seq.nextval FROM dual;What is the problem Somehow two
values have been inserted into the dual table. This table is a single row,
single column table that should only have one value in it.
18. How can you determine if an index needs to be dropped and
rebuilt
Run the ANALYZE INDEX command on the index to validate its structure
and then calculate the ratio of LF_BLK_LEN/LF_BLK_LEN+BR_BLK_LEN and if it
isn?t near 1.0 (i.e. greater than 0.7 or so) then the index should be rebuilt.
Or if the ratio BR_BLK_LEN/ LF_BLK_LEN+BR_BLK_LEN is nearing 0.3.
1. How can variables be passed to a SQL routine
By use of the & symbol. For passing in variables the numbers 1-8
can be used (&1, &2,...,&8) to pass the values after the command
into the SQLPLUS session. To be prompted for a specific variable, place the
ampersanded variable in the code itself: "select * from dba_tables where
owner=&owner_name;" . Use of double ampersands tells SQLPLUS to
resubstitute the value for each subsequent use of the variable, a single
ampersand will cause a reprompt for the value unless an ACCEPT statement is
used to get the value from the user.
2. You want to include a carriage return/linefeed in your output
from a SQL script, how can you do this
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.
3. How can you call a PL/SQL procedure from SQL By use of the EXECUTE (short form EXEC)
command.
4. How do you execute a host operating system command from within SQL
By use of the exclamation ball "!" (in UNIX and some other
OS) or the HOST (HO) command.
5. You want to use SQL to build SQL, what is this called and give an
example
This is called dynamic SQL. An example would be: set lines 90 pages
0 termout off feedback off verify off spool drop_all.sql select ?drop
user ?||username||? cascade;? from dba_users where username not in
("SYS?,?SYSTEM?); spool off Essentially you are looking to see that they
know to include a command (in this case DROP USER...CASCADE;) and that you need
to concatenate using the ?||? the values selected from the database.
6. What SQLPlus command is used to format output from a select This is best done with the COLUMN command.
7. You want to group the following set of select returns, what can
you group on
Max(sum_of_cost), min(sum_of_cost), count(item_no), item_no The only
column that can be grouped on is the "item_no" column, the rest have
aggregate functions associated with them.
8. What special Oracle feature allows you to specify how the cost
based system treats a SQL statement
The COST based system allows the use of HINTs to control the
optimizer path selection. If they can give some example hints such as FIRST
ROWS, ALL ROWS, USING INDEX, STAR, even better.
9. You want to determine the location of identical rows in a table
before attempting to place a unique index on the table, how can this be done
Oracle tables always have one guaranteed unique column, the rowid
column. If you use a min/max function against your rowid and then select
against the proposed primary key you can squeeze out the rowids of the
duplicate rows pretty quick. For example: select rowid from emp e where e.rowid
> (select min(x.rowid) from emp x where x.emp_no = e.emp_no); In the
situation where multiple columns make up the proposed key, they must all be
used in the where clause.
10. What is a Cartesian product
A Cartesian product is the result of an unrestricted join of two or more
tables. The result set of a three table Cartesian product will have x * y * z
number of rows where x, y, z correspond to the number of rows in each table
involved in the join.
Joins
A join is a query that combines rows from
two or more tables, views, or materialized views. Oracle Database performs a
join whenever multiple tables appear in the
FROM
clause of the query. The select list of the query can select any columns from
any of these tables. If any two of these tables have a column name in common,
then you must qualify all references to these columns throughout the query with
table names to avoid ambiguity.Join Conditions
Most join queries contain at least one join
condition, either in the
FROM
clause or in the WHERE
clause. The join condition compares two columns, each from a different table.
To execute a join, Oracle Database combines pairs of rows, each containing one
row from each table, for which the join condition evaluates to TRUE
. The columns in the join conditions
need not also appear in the select list.
To execute a join of three or more tables, Oracle first joins two of
the tables based on the join conditions comparing their columns and then joins
the result to another table based on join conditions containing columns of the
joined tables and the new table. Oracle continues this process until all tables
are joined into the result. The optimizer determines the order in which Oracle
joins tables based on the join conditions, indexes on the tables, and, any
available statistics for the tables.
IA
WHERE
clause
that contains a join condition can also contain other conditions that refer to
columns of only one table. These conditions can further restrict the rows
returned by the join query.
Note:
You cannot specify LOB columns in the
WHERE
clause if
the WHERE
clause contains the join condition. The use of
LOBs in WHERE
clauses is also subject to other restrictions. See
Oracle
Database SecureFiles and Large Objects Developer's Guide for more
information.Equijoins
An equijoin is a join with a join condition
containing an equality operator. An equijoin combines rows that have equivalent
values for the specified columns. Depending on the internal algorithm the
optimizer chooses to execute the join, the total size of the columns in the
equijoin condition in a single table may be limited to the size of a data block
minus some overhead. The size of a data block is specified by the
initialization parameter
DB_BLOCK_SIZE
.
See Also:
Self Joins
A self join is a join of a table to itself.
This table appears twice in the
FROM
clause and is followed by table aliases that qualify column names in the join
condition. To perform a self join, Oracle Database combines and returns rows of
the table that satisfy the join condition.Cartesian Products
If two tables in a join query have no join condition, then Oracle
Database returns their Cartesian product. Oracle
combines each row of one table with each row of the other. A Cartesian product
always generates many rows and is rarely useful. For example, the Cartesian
product of two tables, each with 100 rows, has 10,000 rows. Always include a
join condition unless you specifically need a Cartesian product. If a query
joins three or more tables and you do not specify a join condition for a
specific pair, then the optimizer may choose a join order that avoids producing
an intermediate Cartesian product.
Inner Joins
An inner join (sometimes called a simple join) is a join of two or more tables that returns
only those rows that satisfy the join condition.
Outer Joins
An outer join extends the result of a simple
join. An outer join returns all rows that satisfy the join condition and also
returns some or all of those rows from one table for which no rows from the
other satisfy the join condition.
·
To write a query that
performs an outer join of tables A and B and returns all rows from A (a left outer join), use the
LEFT
[OUTER
] JOIN
syntax in the FROM
clause, or apply the outer join
operator (+) to all columns of B in the join condition in the WHERE
clause. For all rows in A that
have no matching rows in B, Oracle Database returns null for any select list
expressions containing columns of B.
·
To write a query that
performs an outer join of tables A and B and returns all rows from B (a right outer join), use the
RIGHT
[OUTER
] JOIN
syntax in the FROM
clause, or apply the outer join
operator (+) to all columns of A in the join condition in the WHERE
clause. For all rows in B that
have no matching rows in A, Oracle returns null for any select list expressions
containing columns of A.
·
To write a query that
performs an outer join and returns all rows from A and B, extended with nulls
if they do not satisfy the join condition (a full outer join),
use the
FULL
[OUTER
] JOIN
syntax in the FROM
clause.
You cannot compare a column with a subquery in the
WHERE
clause of any outer join,
regardless which form you specify.
You can use outer joins to fill gaps in sparse data. Such a join is called a partitioned outer join and is formed using the query_partition_clause
of the join_clause syntax. Sparse data is data that does
not have rows for all possible values of a dimension such as time or
department. For example, tables of sales data typically do not have rows for
products that had no sales on a given date. Filling data gaps is useful in
situations where data sparsity complicates analytic computation or where some
data might be missed if the sparse data is queried directly.
See Also:
·
join_clause for more information about using outer
joins to fill gaps in sparse data
·
Oracle Database Data Warehousing Guide for a complete
discussion of group outer joins and filling gaps in sparse data
Oracle recommends that
you use the
FROM
clause OUTER
JOIN
syntax rather than the Oracle join operator. Outer join queries that use the
Oracle join operator (+) are subject to the following rules and restrictions,
which do not apply to the FROM
clause OUTER
JOIN
syntax:
·
You cannot specify the (+)
operator in a query block that also contains
FROM
clause join syntax.
·
The (+) operator can appear
only in the
WHERE
clause or,
in the context of left-correlation (when specifying the TABLE
clause) in the FROM
clause, and can be applied only to
a column of a table or view.
·
If A and B are joined by
multiple join conditions, then you must use the (+) operator in all of these
conditions. If you do not, then Oracle Database will return only the rows
resulting from a simple join, but without a warning or error to advise you that
you do not have the results of an outer join.
·
The (+) operator does not
produce an outer join if you specify one table in the outer query and the other
table in an inner query.
·
You cannot use the (+)
operator to outer-join a table to itself, although self joins are valid. For
example, the following statement is not valid:
· -- The following statement is not valid:
· SELECT employee_id, manager_id
· FROM employees
· WHERE employees.manager_id(+) = employees.employee_id;
However, the following self join is
valid:
SELECT e1.employee_id, e1.manager_id, e2.employee_id
FROM employees e1, employees e2
WHERE e1.manager_id(+) = e2.employee_id
ORDER BY e1.employee_id, e1.manager_id, e2.employee_id;
·
The (+) operator can be
applied only to a column, not to an arbitrary expression. However, an arbitrary
expression can contain one or more columns marked with the (+) operator.
·
A
WHERE
condition containing the (+)
operator cannot be combined with another condition using the OR
logical operator.
·
A
WHERE
condition cannot use the IN
comparison condition to compare a
column marked with the (+) operator with an expression.
If the
WHERE
clause contains a condition that compares a column from table B with a
constant, then the (+) operator must be applied to the column so that Oracle
returns the rows from table A for which it has generated nulls for this column.
Otherwise Oracle returns only the results of a simple join.
In a query that performs outer joins of more than two pairs of
tables, a single table can be the null-generated table for only one other
table. For this reason, you cannot apply the (+) operator to columns of B in
the join condition for A and B and the join condition for B and C. Refer to SELECT
for the syntax for an outer join.
Antijoins
An antijoin returns rows from the left side of the predicate for
which there are no corresponding rows on the right side of the predicate. It
returns rows that fail to match (
NOT
IN
) the subquery on the
right side.
See Also:
Semijoins
A semijoin returns rows that match an
EXISTS
subquery without duplicating rows from the left
side of the predicate when multiple rows on the right side satisfy the criteria
of the subquery.
Semijoin and antijoin transformation cannot be done if the subquery
is on an
OR
branch of the WHERE
clause.
INNER Versus OUTER Joins
•
In SQL:1999, the join of two tables returning only matched rows is
called an inner join.
•
A join between two tables that returns the results of the inner join as
well as the unmatched rows from the left (or right) tables is called a left (or
right) outer join.
•
A join between two tables that returns the results of an inner join as
well as the results of a left and right join is a full outer join.
11. You are joining a local and a remote table, the network manager
complains about the traffic involved, how can you reduce the network traffic
Push the processing of the remote data to the remote instance by using a view
to pre-select the information for the join. This will result in only the data
required for the join being sent across.
11. What is the default ordering of an ORDER BY clause in a SELECT
statement
Ascending
12. What is tkprof and how is it used
The tkprof tool is a tuning tool used to determine cpu and execution
times for SQL statements. You use it by first setting timed_statistics to true
in the initialization file and then turning on tracing for either the entire
database via the sql_trace parameter or for the session using the ALTER SESSION
command. Once the trace file is generated you run the tkprof tool against the
trace file and then look at the output from the tkprof tool. This can also be
used to generate explain plan output.
13. What is explain plan and how is it used
The EXPLAIN PLAN command is a tool to tune SQL statements. To use it
you must have an explain_table generated in the user you are running the
explain plan for. This is created using the utlxplan.sql script. Once the
explain plan table exists you run the explain plan command giving as its
argument the SQL statement to be explained. The explain_plan table is then
queried to see the execution plan of the statement. Explain plans can also be
run using tkprof.
14. How do you set the number of lines on a page of output? The width
The SET command in SQLPLUS is used to control the number of lines
generated per page and the width of those lines, for example SET PAGESIZE 60
LINESIZE 80 will generate reports that are 60 lines long with a line width of
80 characters. The PAGESIZE and LINESIZE options can be shortened to PAGES and
LINES.
15. How do you prevent output from coming to the screen
The SET option TERMOUT controls output to the screen. Setting
TERMOUT OFF turns off screen output. This option can be shortened to TERM.
16. How do you prevent Oracle from giving you informational messages during and after a SQL statement execution
The SET options FEEDBACK and VERIFY can be set to OFF.
17. How do you generate file output from SQL
By use of the SPOOL comm
1. What is a CO-RELATED SUBQUERY
A CO-RELATED SUBQUERY is one that has a correlation name as table or
view designator in the FROM clause of the outer query and the same correlation
name as a qualifier of a search condition in the WHERE clause of the subquery.
eg
SELECT field1 from table1 X
WHERE field2>(select avg(field2) from table1 Y
where
field1=X.field1);
(The subquery in a correlated subquery is revaluated for every row of the table or view named in the outer query.)
2. What are various joins used while writing SUBQUERIES
Self join-Its a join foreign key of a table references the same
table.
Outer Join--Its a join condition used where One can query all the
rows of one of the tables in the join condition even though they don't satisfy
the join condition.
Equi-join--Its a join condition that retrieves rows from one or more
tables in which one or more columns in one table are equal to one or more
columns in the second table.
3. What are various constraints used in SQL
NULL NOT NULL CHECK DEFAULT
4. What are different Oracle database objects
TABLES VIEWS INDEXES SYNONYMS SEQUENCES TABLESPACES etc
5. What is difference between Rename and Alias
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.
6. What is a view
A view is stored procedure based on one or more tables, its a
virtual table.
7. What are various privileges that a user can grant to another user
SELECT CONNECT RESOURCE
8. What is difference between UNIQUE and PRIMARY KEY constraints
A table can have only one PRIMARY KEY whereas there can be any
number of UNIQUE keys. The columns that compose PK are automatically define NOT
NULL, whereas a column that compose a UNIQUE is not automatically defined to be
mandatory must also specify the column is NOT NULL.
9. Can a primary key contain more than one columns
Yes
10. How you will avoid duplicating records in a query
By using DISTINCT
11. What is difference between SQL and SQL*PLUS
SQL*PLUS is a command line tool where as SQL and PL/SQL language
interface and reporting tool. Its a command line tool that allows user to type
SQL commands to be executed directly against an Oracle database. SQL is a
language used to query the relational database(DML,DCL,DDL). SQL*PLUS commands
are used to format query result, Set options, Edit SQL commands and PL/SQL.
12. Which datatype is used for storing graphics and images
LONG RAW data type is used for storing BLOB's (binary large
objects).
13. How will you delete duplicating rows from a base table
DELETE FROM table_name A WHERE rowid>(SELECT min(rowid) from
table_name B where B.table_no=A.table_no);
CREATE TABLE new_table AS SELECT DISTINCT * FROM old_table;
DROP old_table RENAME new_table TO old_table DELETE FROM table_name
A WHERE rowid NOT IN (SELECT MAX(ROWID) FROM table_name GROUP BY column_name)
14. What is difference between SUBSTR and INSTR
SUBSTR returns a specified portion of a string eg SUBSTR('BCDEF',4)
output BCDE INSTR provides character position in which a pattern is found in a
string.
eg INSTR('ABC-DC-F','-',2) output 7 (2nd occurence of '-')
15. There is a string '120000 12 0 .125' ,how you will find the
position of the decimal place
INSTR('120000 12 0 .125','.',1) output 13
16. There is a '%' sign in one field of a column. What will be the
query to find it.
'\' Should be used before '%'.
17. When you use WHERE clause and when you use HAVING clause
HAVING clause is used when you want to specify a condition for a
group function and it is written after GROUP BY clause The WHERE clause is used
when you want to specify a condition for columns, single row functions except
group functions and it is written before GROUP BY clause if it is used.
18. Which is more faster - IN or EXISTS
EXISTS is more faster than IN because EXISTS returns a Boolean value
whereas IN returns a value.
Appropriate answer will be....
Result of the subquery is small Then "IN" is typicaly more
appropriate. and Result of the subquery is big/large/long Then
"EXIST" is more appropriate.
19. What is a OUTER JOIN
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 dont satisfy
the join condition.
20. How you will avoid your query from using indexes
SELECT * FROM emp Where emp_no+' '=12345;
i.e you have to concatenate the column name with space within codes
in the where condition.
SELECT /*+ FULL(a) */ ename, emp_no from emp where emp_no=1234;
i.e using HINTS
Oracle Interview Questions
1. What is a pseudo column. Give some examples
It is a column that is not an actual column in the table.
eg USER, UID, SYSDATE, ROWNUM, ROWID, NULL, AND LEVEL.
Suppose customer table is there having different columns like
customer no, payments.What will be the query to select top three max payments.
For top N queries, see http://www.orafaq.com/forum/mv/msg/160920/472554/102589/#msg_472554
post
2. What is the purpose of a cluster.
Oracle does not allow a user to specifically locate tables, since
that is a part of the function of the RDBMS. However, for the purpose of
increasing performance, oracle allows a developer to create a CLUSTER. A
CLUSTER provides a means for storing data from different tables together for
faster retrieval than if the table placement were left to the RDBMS.
3. What is a cursor.
Oracle uses work area to execute SQL statements and store processing
information PL/SQL construct called a cursor lets you name a work area and
access its stored information A cursor is a mechanism used to fetch more than
one row in a Pl/SQl block.
4. Difference between an implicit & an explicit cursor.
PL/SQL declares a cursor implicitly for all SQL data manipulation
statements, including quries that return only one row. However,queries that
return more than one row you must declare an explicit cursor or use a cursor
FOR loop.
Explicit cursor is a cursor in which the cursor name is explicitly
assigned to a SELECT statement via the CURSOR...IS statement. An implicit
cursor is used for all SQL statements Declare, Open, Fetch, Close. An explicit
cursors are used to process multirow SELECT statements An implicit cursor is
used to process INSERT, UPDATE, DELETE and single row SELECT. .INTO statements.
5. What are cursor attributes
%ROWCOUNT %NOTFOUND %FOUND %ISOPEN
6. What is a cursor for loop.
Cursor For Loop is a loop where oracle implicitly declares a loop
variable, the loop index that of the same record type as the cursor's record.
7. Difference between NO DATA FOUND and %NOTFOUND
NO DATA FOUND is an exception raised only for the SELECT....INTO
statements when the where clause of the querydoes not match any rows. When the
where clause of the explicit cursor does not match any rows the %NOTFOUND
attribute is set to TRUE instead.
8. What a SELECT FOR UPDATE cursor represent.
SELECT......FROM......FOR......UPDATE[OF column-reference][NOWAIT]
The processing done in a fetch loop modifies the rows that have been retrieved
by the cursor. A convenient way of modifying the rows is done by a method with
two parts: the FOR UPDATE clause in the cursor declaration, WHERE CURRENT OF
CLAUSE in an UPDATE or declaration statement.
9. What 'WHERE CURRENT OF ' clause does in a cursor.
LOOP
SELECT num_credits INTO v_numcredits FROM classes
WHERE dept=123 and course=101;
UPDATE students
SET current_credits=current_credits+v_numcredits
WHERE CURRENT OF X;
END LOOP
COMMIT;
END;
10. What is use of a cursor variable? How it is defined.
A cursor variable is associated with different statements at run
time, which can hold different values at run time. Static cursors can only be
associated with one run time query. A cursor variable is reference type(like a
pointer in C). Declaring a cursor variable: TYPE type_name IS REF CURSOR RETURN
return_type type_name is the name of the reference type,return_type is a record
type indicating the types of the select list that will eventually be returned
by the cursor variable.
11. What should be the return type for a cursor variable.Can we use
a scalar data type as return type.
The return type for a cursor must be a record type.It can be
declared explicitly as a user-defined or %ROWTYPE can be used. eg TYPE
t_studentsref IS REF CURSOR RETURN students%ROWTYPE
12. How you open and close a cursor variable.Why it is required.
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.
13. How you were passing cursor variables in PL/SQL 2.2.
In PL/SQL 2.2 cursor variables cannot be declared in a package.This
is because the storage for a cursor variable has to be allocated using Pro*C or
OCI with version 2.2,the only means of passing a cursor variable to a PL/SQL
block is via bind variable or a procedure parameter.
14. Can cursor variables be stored in PL/SQL tables.If yes how.If
not why.
No, a cursor variable points a row which cannot be stored in a
two-dimensional PL/SQL table.
15. Difference between procedure and function.
Functions are named PL/SQL blocks that return a value and can be
called with arguments procedure a named block that can be called with
parameter. A procedure all is a PL/SQL statement by itself, while a Function
call is called as part of an expression.
16. What are different modes of parameters used in functions and
procedures.
IN OUT INOUT
17. What is difference between a formal and an actual parameter
The variables declared in the procedure and which are passed, as
arguments are called actual, the parameters in the procedure declaration.
Actual parameters contain the values that are passed to a procedure and receive
results. Formal parameters are the placeholders for the values of actual
parameters
18. Can the default values be assigned to actual parameters.
Yes
19. Can a function take OUT parameters.If not why.
Yes. A function return a value, but can also have one or more OUT
parameters. it is best practice, however to use a procedure rather than a
function if you have multiple values to return.
20. What is syntax for dropping a procedure and a function .Are
these operations possible.
Drop Procedure procedure_name
Drop Function function_name
21. What are ORACLE PRECOMPILERS.
Using ORACLE PRECOMPILERS ,SQL statements and PL/SQL blocks can be
contained inside 3GL programs written in C,C++,COBOL,PASCAL, FORTRAN,PL/1 AND
ADA. The Precompilers are known as Pro*C,Pro*Cobol,... This form of PL/SQL is
known as embedded pl/sql,the language in which pl/sql is embedded is known as
the host language. The prcompiler translates the embedded SQL and pl/sql
ststements into calls to the precompiler runtime library.The output must be
compiled and linked with this library to creater an executable.
22. What is OCI. What are its uses.
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
23. Difference between database triggers and form triggers.
a) Data base trigger(DBT) fires when a DML operation is performed on
a data base table.Form trigger(FT) Fires when user presses a key or navigates
between fields on the screen b) Can be row level or statement level No
distinction between row level and statement level. c) Can manipulate data
stored in Oracle tables via SQL Can manipulate data in Oracle tables as well as
variables in forms. d) Can be fired from any session executing the triggering
DML statements. Can be fired only from the form that define the trigger. e) Can
cause other database triggers to fire.Can cause other database triggers to
fire,but not other form triggers.
24. What is an UTL_FILE.What are different procedures and functions
associated
with it. UTL_FILE is a package that adds the ability to read and
write to operating system files Procedures associated with it are FCLOSE,
FCLOSE_ALL and 5 procedures to output data to a file PUT, PUT_LINE, NEW_LINE,
PUTF, FFLUSH.PUT, FFLUSH.PUT_LINE,FFLUSH.NEW_LINE. Functions associated with it
are FOPEN, ISOPEN.
25. Can you use a commit statement within a database trigger.
No
26. What is the maximum buffer size that can be specified using the
DBMS_OUTPUT.ENABLE function?
1,000,000
Oracle Interview Questions
1. When looking at the estat events report you see that you are
getting busy buffer waits. Is this bad? How can you find what is causing it
Buffer busy waits could indicate contention in redo, rollback or
data blocks. You need to check the v$waitstat view to see what areas are
causing the problem. The value of the "count" column tells where the
problem is, the "class" column tells you with what. UNDO is rollback
segments, DATA is data base buffers.
2. If you see contention for library caches how can you fix it
Increase the size of the shared pool.
3. If you see statistics that deal with "undo" what are they really talking about
Rollback segments and associated structures.
4. If a tablespace has a default pctincrease of zero what will this cause (in relationship to the smon process)
The SMON process won?t automatically coalesce its free space
fragments.
5. If a tablespace shows excessive fragmentation what are some methods to defragment the tablespace? (7.1,7.2 and 7.3 only)
In Oracle 7.0 to 7.2 The use of the 'alter session set events
'immediate trace name coalesce level ts#';? command is the easiest way to
defragment contiguous free space fragmentation. The ts# parameter corresponds
to the ts# value found in the ts$ SYS table. In version 7.3 the ?alter
tablespace coalesce;? is best. If the free space isn?t contiguous then export,
drop and import of the tablespace contents may be the only way to reclaim
non-contiguous free space.
6. How can you tell if a tablespace has excessive fragmentation
If a select against the dba_free_space table shows that the count of
a tablespaces extents is greater than the count of its data files, then it is
fragmented.
7. You see the following on a status report: redo log space requests 23 redo log space wait time 0 Is this something to worry about? What if redo log space wait time is high? How can you fix this Since the wait time is zero, no. If the wait time was high it might indicate a need for more or larger redo logs.
8. What can cause a high value for recursive calls? How can this be fixed
A high value for recursive calls is cause by improper cursor usage,
excessive dynamic space management actions, and or excessive statement
re-parses. You need to determine the cause and correct it By either relinking
applications to hold cursors, use proper space management techniques (proper
storage and sizing) or ensure repeat queries are placed in packages for proper
reuse.
9. If you see a pin hit ratio of less than 0.8 in the estat library cache report is this a problem? If so, how do you fix it
This indicate that the shared pool may be too small. Increase the
shared pool size.
10. If you see the value for reloads is high in the estat library cache report is this a matter for concern
Yes, you should strive for zero reloads if possible. If you see
excessive reloads then increase the size of the shared pool.
11. You look at the dba_rollback_segs view and see that there is a large number of shrinks and they are of relatively small size, is this a problem? How can it be fixed if it is a problem
A large number of small shrinks indicates a need to increase the
size of the rollback segment extents. Ideally you should have no shrinks or a
small number of large shrinks. To fix this just increase the size of the
extents and adjust optimal accordingly.
12. You look at the dba_rollback_segs view and see that you have a large number of wraps is this a problem
A large number of wraps indicates that your extent size for your
rollback segments are probably too small. Increase the size of your extents to
reduce the number of wraps. You can look at the average transaction size in the
same view to get the information on transaction size.
Oracle Interview Questions
1. You have just started a new instance with a large SGA on a busy
existing server. Performance is terrible, what should you check for
The first thing to check with a large SGA is that it isn?t being
swapped out.
2. What OS user should be used for the first part of an Oracle installation (on UNIX)
You must use root first.
3. When should the default values for Oracle initialization parameters be used as is
Never
4. How many control files should you have? Where should they be located
At least 2 on separate disk spindles. Be sure they say on separate
disks, not just file systems.
5. How many redo logs should you have and how should they be configured for maximum recoverability
You should have at least three groups of two redo logs with the two
logs each on a separate disk spindle (mirrored by Oracle). The redo logs should
not be on raw devices on UNIX if it can be avoided.
6. You have a simple application with no "hot" tables (i.e. uniform IO and access requirements). How many disks should you have assuming standard layout for SYSTEM, USER, TEMP and ROLLBACK tablespaces
At least 7, see disk configuration answer above.
7. Describe third normal form
Something like: In third normal form all attributes in an entity are
related to the primary key and only to the primary key
8. Is the following statement true or false:
"All relational databases must be in third normal form"
False. While 3NF is good for logical design most databases, if they have more
than just a few tables, will not perform well using full 3NF. Usually some
entities will be denormalized in the logical to physical transfer process.
9. What is an ERD
An ERD is an Entity-Relationship-Diagram. It is used to show the
entities and relationships for a database logical model.
10. Why are recursive relationships bad? How do you resolve them
A recursive relationship (one where a table relates to itself) is
bad when it is a hard relationship (i.e. neither side is a "may" both
are "must") as this can result in it not being possible to put in a
top or perhaps a bottom of the table (for example in the EMPLOYEE table you
couldn?t put in the PRESIDENT of the company because he has no boss, or the
junior janitor because he has no subordinates). These type of relationships are
usually resolved by adding a small intersection entity.
11. What does a hard one-to-one relationship mean (one where the relationship on both ends is "must")
Expected answer: This means the two entities should probably be made
into one entity.
12. How should a many-to-many relationship be handled
By adding an intersection entity table
13. What is an artificial (derived) primary key? When should an artificial (or derived) primary key be used
A derived key comes from a sequence. Usually it is used when a
concatenated key becomes too cumbersome to use as a foreign key.
Oracle Interview Questions
1. When should you consider denormalization
Whenever performance analysis indicates it would be beneficial to do
so without compromising data integrity.
2. How can you determine if an Oracle instance is up from the operating system level
There are several base Oracle processes that will be running on
multi-user operating systems, these will be smon, pmon, dbwr and lgwr. Any
answer that has them using their operating system process showing feature to
check for these is acceptable. For example, on UNIX a ps -ef|grep dbwr will
show what instances are up.
3. Users from the PC clients are getting messages indicating : ORA-06114: (Cnct err, can't get err txt. See Servr Msgs & Codes Manual)
What could the problem be The instance name is probably incorrect in their connection string.
4. Users from the PC clients are getting the following error stack: ERROR: ORA-01034: ORACLE not available ORA-07318: smsget: open error when opening sgadef.dbf file. HP-UX Error: 2: No such file or directory
What is the probable cause The Oracle instance is shutdown that they are trying to access, restart the instance.
5. How can you determine if the SQLNET process is running for SQLNET V1? How about V2
For SQLNET V1 check for the existence of the orasrv process. You can
use the command "tcpctl status" to get a full status of the V1 TCPIP
server, other protocols have similar command formats. For SQLNET V2 check for
the presence of the LISTENER process(s) or you can issue the command
"lsnrctl status".
6. What file will give you Oracle instance status information? Where is it located
The alert.ora log. It is located in the directory specified by the
background_dump_dest parameter in the v$parameter table.
7. Users aren?t being allowed on the system. The following message is received: ORA-00257 archiver is stuck. Connect internal only, until freed What is the problem The archive destination is probably full, backup the archive logs and remove them and the archiver will re-start.
8. Where would you look to find out if a redo log was corrupted assuming you are using Oracle mirrored redo logs
There is no message that comes to the SQLDBA or SRVMGR programs
during startup in this situation, you must check the alert.log file for this information.
9. You attempt to add a datafile and get: ORA-01118: cannot add anymore datafiles: limit of 40 exceeded What is the problem and how can you fix it When the database was created the db_files parameter in the initialization file was set to 40. You can shutdown and reset this to a higher value, up to the value of MAX_DATAFILES as specified at database creation. If the MAX_DATAFILES is set to low, you will have to rebuild the control file to increase it before proceeding.
10. You look at your fragmentation report and see that smon hasn?t coalesced any of you tablespaces, even though you know several have large chunks of contiguous free extents. What is the problem
Check the dba_tablespaces view for the value of pct_increase for the tablespaces. If pct_increase is zero, smon will not coalesce their free space.
11. Your users get the following error: ORA-00055 maximum number of DML locks exceeded What is the problem and how do you fix it The number of DML Locks is set by the initialization parameter DML_LOCKS. If this value is set to low (which it is by default) you will get this error. Increase the value of DML_LOCKS. If you are sure that this is just a temporary problem, you can have them wait and then try again later and the error should clear.
12. You get a call from you backup DBA while you are on vacation. He has corrupted all of the control files while playing with the ALTER DATABASE BACKUP CONTROLFILE command. What do you do
As long as all datafiles are safe and he was successful with the
BACKUP controlfile command you can do the following: CONNECT INTERNAL STARTUP
MOUNT (Take any read-only tablespaces offline before next step ALTER DATABASE
DATAFILE .... OFFLINE;) RECOVER DATABASE USING BACKUP CONTROLFILE ALTER
DATABASE OPEN RESETLOGS; (bring read-only tablespaces back online) Shutdown and
backup the system, then restart If they have a recent output file from the
ALTER DATABASE BACKUP CONTROL FILE TO TRACE; command, they can use that to
recover as well. If no backup of the control file is available then the
following will be required: CONNECT INTERNAL STARTUP NOMOUNT CREATE CONTROL
FILE .....; However, they will need to know all of the datafiles, logfiles, and
settings for MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES for the database
to use the command.
ORACLE SQL PL/SQL Interview Questions
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What are the various types of queries ? Answer: The types of queries are: Normal Queries Sub Queries Co-related queries Nested queries Compound queries
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What is a transaction ?
Answer: A transaction is a set of SQL statements between any two COMMIT and ROLLBACK statements.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What is implicit cursor and how is it used by Oracle ? Answer: An implicit cursor is a cursor which is internally created by Oracle.It is created by Oracle for each individual SQL.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Which of the following is not a schema object : Indexes, tables, public synonyms, triggers and packages ? Answer: Public synonyms
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What is PL/SQL? Answer: PL/SQL is Oracle's Procedural Language extension to SQL.The language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance), and so, brings state-of-the-art programming to the Oracle database server and a variety of Oracle tools.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Is there a PL/SQL Engine in SQL*Plus? Answer: No.Unlike Oracle Forms, SQL*Plus does not have a PL/SQL engine.Thus, all your PL/SQL are send directly to the database engine for execution.This makes it much more efficient as SQL statements are not stripped off and send to the database individually.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Is there a limit on the size of a PL/SQL block?
Answer: Currently, the maximum parsed/compiled size of a PL/SQL block is 64K and the maximum code size is 100K.You can run the following select statement to query the size of an existing package or procedure. SQL> select * from dba_object_size where name = 'procedure_name'
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Can one read/write files from PL/SQL? Answer: Included in Oracle 7.3 is a UTL_FILE package that can read and write files.The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=...parameter).
Before Oracle 7.3 the only means of writing a file was to use
DBMS_OUTPUT with the SQL*Plus SPOOL command.
DECLARE fileHandler UTL_FILE.FILE_TYPE; BEGIN fileHandler := UTL_FILE.FOPEN('/home/oracle/tmp', 'myoutput','W'); UTL_FILE.PUTF(fileHandler, 'Value of func1 is %sn', func1(1)); UTL_FILE.FCLOSE(fileHandler); END;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
How can I protect my PL/SQL source code?
Answer: PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code.This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original).This way you can distribute software without having to worry about exposing your proprietary algorithms and methods.SQL*Plus and SQL*DBA will still understand and know how to execute such scripts.Just be careful, there is no "decode" command available. The syntax is: wrap name=myscript.sql
oname=xxxx.yyy
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Can one use dynamic SQL within PL/SQL? OR Can you use a DDL in a procedure ? How ? Answer: From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic SQL statements. Eg: CREATE OR REPLACE PROCEDURE DYNSQL AS cur integer; rc integer; BEGIN cur := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(cur,'CREATE TABLE X (Y DATE)',
DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur); DBMS_SQL.CLOSE_CURSOR(cur); END;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What are the various types of Exceptions ?
Answer: User defined and Predefined Exceptions.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Can we define exceptions twice in same block ? Answer: No.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What is the difference between a procedure and a function ? Answer: Functions return a single variable by value whereas procedures do not return any variable by value.Rather they return multiple variables by passing variables by reference through their OUT parameter.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Can you have two functions with the same name in a PL/SQL block ? Answer: Yes.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Can you have two stored functions with the same name ? Answer: Yes.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Can you call a stored function in the constraint of a table ? Answer: No.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What are the various types of parameter modes in a procedure ? Answer: IN, OUT AND INOUT.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What is Over Loading and what are its restrictions ? Answer: OverLoading means an object performing different functions depending upon the no.of parameters or the data type of the parameters passed to it.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Can functions be overloaded ? Answer: Yes.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Can 2 functions have same name & input parameters but differ only by return datatype Answer: No.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What are the constructs of a procedure, function or a package ?
Answer: The constructs of a procedure, function or a package are :
variables and constants
cursors exceptions
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Why Create or Replace and not Drop and recreate procedures ?
Answer: So that Grants are not dropped.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Can you pass parameters in packages ? How ? Answer: Yes.You can pass parameters to procedures or functions in a package.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What are the parts of a database trigger ? Answer: The parts of a trigger are:
A triggering event or statement
A trigger restriction A trigger action
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What are the various types of database triggers ?
Answer: There are 12 types of triggers, they are combination of :
Insert, Delete and Update Triggers.
Before and After Triggers. Row and Statement Triggers.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What is the advantage of a stored procedure over a database trigger ?
Answer: We have control over the firing of a stored procedure but we have no control over the firing of a trigger.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What is the maximum no.of statements that can be specified in a trigger statement ? Answer: One.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Can views be specified in a trigger statement ? Answer: No
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What are the values of :new and :old in Insert/Delete/Update Triggers ? Answer: INSERT : new = new value, old = NULL DELETE : new = NULL, old = old value UPDATE : new = new value, old = old value
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What are cascading triggers? What is the maximum no of cascading
triggers at a time?
Answer: When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading.Max = 32.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What are mutating triggers ? Answer: A trigger giving a SELECT on the table on which the trigger is written.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What are constraining triggers ? Answer: A trigger giving an Insert/Updat e on a table having referential integrity constraint on the triggering table.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Describe Oracle database's physical and logical structure ?
Answer:
Physical : Data files, Redo Log files, Control file.
Logical : Tables, Views, Tablespaces, etc.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Can you increase the size of a tablespace ? How ?
Answer: Yes, by adding datafiles to it.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Can you increase the size of datafiles ? How ? Answer: No (for Oracle 7.0) Yes (for Oracle 7.3 by using the Resize clause )
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What is the use of Control files ? Answer: Contains pointers to locations of various data files, redo log files, etc.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What is the use of Data Dictionary ? Answer: It Used by Oracle to store information about various physical and logical Oracle structures e.g.Tables, Tablespaces, datafiles, etc
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What are the advantages of clusters ? Answer: Access time reduced for joins.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What are the disadvantages of clusters ? Answer: The time for Insert increases.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Can Long/Long RAW be clustered ? Answer: No.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Can null keys be entered in cluster index, normal index ? Answer: Yes.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Can Check constraint be used for self referential integrity ? How ? Answer: Yes.In the CHECK condition for a column of a table, we can reference some other column of the same table and thus enforce self referential integrity.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What are the min.extents allocated to a rollback extent ? Answer: Two
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What are the states of a rollback segment ? What is the difference between partly available and needs recovery ? Answer: The various states of a rollback segment are :
ONLINE
OFFLINE PARTLY AVAILABLE NEEDS RECOVERY INVALID.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What is the difference between unique key and primary key ?
Answer: Unique key can be null; Primary key cannot be null.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
An insert statement followed by a create table statement followed by rollback ? Will the rows be inserted ? Answer: No.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Can you define multiple savepoints ? Answer: Yes.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Can you Rollback to any savepoint ? Answer: Yes.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What is the maximum no.of columns a table can have ? Answer: 254.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What is the significance of the & and && operators in PL SQL ? Answer: The & operator means that the PL SQL block requires user input for a variable.The && operator means that the value of this variable should be the same as inputted by the user previously for this same variable
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Can you pass a parameter to a cursor ? Answer: Explicit cursors can take parameters, as the example below shows.A cursor parameter can appear in a query wherever a constant can appear.
CURSOR c1 (median IN NUMBER) IS
SELECT job, ename FROM emp WHERE sal > median;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What are the various types of RollBack Segments ?
Answer: The types of Rollback sagments are as follows :
Public Available to all instances
Private Available to specific instance
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Can you use %RowCount as a parameter to a cursor ?
Answer: Yes
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Is the query below allowed : Select sal, ename Into x From emp Where ename = 'KING' (Where x is a record of Number(4) and Char(15)) Answer: Yes
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Is the assignment given below allowed : ABC = PQR (Where ABC and PQR are records) Answer: Yes
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Is this for loop allowed : For x in &Start..&End Loop Answer: Yes
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
How many rows will the following SQL return : Select * from emp Where rownum < 10; Answer: 9 rows
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
How many rows will the following SQL return : Select * from emp Where rownum = 10; Answer: No rows
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Which symbol preceeds the path to the table in the remote database ? Answer: @
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Are views automatically updated when base tables are updated ? Answer: Yes
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Can a trigger written for a view ? Answer: No
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
If all the values from a cursor have been fetched and another fetch is issued, the output will be : error, last record or first record ? Answer: Last Record
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A table has the following data : [[5, Null, 10]].What will the average function return ? Answer: 7.5
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Is Sysdate a system variable or a system function? Answer: System Function
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Consider a sequence whose currval is 1 and gets incremented by 1 by using the nextval reference we get the next number 2.Suppose at this point we issue an rollback and again issue a nextval.What will the output be ? Answer: 3
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Definition of relational DataBase by Dr.Codd (IBM)? Answer: A Relational Database is a database where all data visible to the user is organized strictly as tables of data values and where all database operations work on these tables.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What is Multi Threaded Server (MTA) ? Answer: In a Single Threaded Architecture (or a dedicated server configuration) the database manager creates a separate process for each database user.But in MTA the database manager can assign multiple users (multiple user processes) to a single dispatcher (server process), a controlling process that queues request for work thus reducing the databases memory requirement and resources.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Which are initial RDBMS, Hierarchical & N/w database ? Answer:
RDBMS - R system
Hierarchical - IMS N/W - DBTG
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Difference between Oracle 6 and Oracle 7
Answer:
ORACLE 7 ORACLE
6
Cost based optimizer Rule based optimizer Shared SQL Area SQL area allocated for each user Multi Threaded Server Single Threaded Server Hash Clusters Only B-Tree indexing Roll back Size Adjustment No provision Truncate command No provision Distributed Database Distributed Query Table replication & snapshots No provision Client/Server Tech No provision
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What is Functional Dependency?
Answer: Given a relation R, attribute Y of R is functionally dependent on attribute X of R if and only if each X-value has associated with it precisely one -Y value in R
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What is Auditing ? Answer: The database has the ability to audit all actions that take place within it. a) Login attempts, b) Object Accesss, c) Database Action Result of Greatest(1,NULL) or Least(1,NULL) NULL
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
While designing in client/server what are the 2 imp.things to be considered ? Answer: Network Overhead (traffic), Speed and Load of client server
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What are the disadvantages of SQL ? Answer: Disadvantages of SQL are :
Cannot drop a field
Cannot rename a field Cannot manage memory Procedural Language option not provided Index on view or index on index not provided View updation problem
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
When to create indexes ?
Answer: To be created when table is queried for less than 2% or 4% to 25% of the table rows.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
How can you avoid indexes ? Answer: To make index access path unavailable Use FULL hint to optimizer for full table scan Use INDEX or AND-EQUAL hint to optimizer to use one index or set to indexes instead of another. Use an expression in the Where Clause of the SQL.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What is the result of the following SQL : Select 1 from dual UNION
Select 'A' from dual;
Answer: Error
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Can database trigger written on synonym of a table and if it can be then what would be the effect if original table is accessed. Answer: Yes, database trigger would fire.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Can you alter synonym of view or view ? Answer: No
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Can you create index on view Answer: No.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What is the difference between a view and a synonym ? Answer: Synonym is just a second name of table used for multiple link of database.View can be created with many tables, and with virtual columns and with conditions.But synonym can be on view.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What's the length of SQL integer ? Answer: 32 bit length
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What is the difference between foreign key and reference key ? Answer: Foreign key is the key i.e.attribute which refers to another table primary key. Reference key is the primary key of table referred by another table.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Can dual table be deleted, dropped or altered or updated or inserted ? Answer: Yes
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
If content of dual is updated to some value computation takes place or not ? Answer: Yes
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
If any other table same as dual is created would it act similar to dual? Answer: Yes
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
For which relational operators in where clause, index is not used ? Answer: <> , like '%...' is NOT functions, field +constant, field||''
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Assume that there are multiple databases running on one machine.How can you switch from one to another ? Answer: Changing the ORACLE_SID
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What are the advantages of Oracle ? Answer: Portability : Oracle is ported to more platforms than any of its competitors, running on more than 100 hardware platforms and 20 networking protocols. Market Presence : Oracle is by far the largest RDBMS vendor and spends more on R & D than most of its competitors earn in total revenue.This market clout means that you are unlikely to be left in the lurch by Oracle and there are always lots of third party interfaces available. Backup and Recovery : Oracle provides industrial strength support for on-line backup and recovery and good software fault tolerence to disk failure.You can also do point-in-time recovery. Performance : Speed of a 'tuned' Oracle Database and application is quite good, even with large databases.Oracle can manage > 100GB databases. Multiple database support : Oracle has a superior ability to manage multiple databases within the same transaction using a two-phase commit protocol.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What is a forward declaration ? What is its use ? Answer: PL/SQL requires that you declare an identifier before using it.Therefore, you must declare a subprogram before calling it.This declaration at the start of a subprogram is called forward declaration.A forward declaration consists of a subprogram specification terminated by a semicolon.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What are actual and formal parameters ? Answer: Actual Parameters : Subprograms pass information using parameters.The variables or expressions referenced in the parameter list of a subprogram call are actual parameters.For example, the following procedure call lists two actual parameters named emp_num and amount: Eg.raise_salary(emp_num, amount);Formal Parameters : The variables declared in a subprogram specification and referenced in the subprogram body are formal parameters.For example, the following procedure declares two formal parameters named emp_id and increase: Eg.PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS current_salary REAL;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What are the types of Notation ?
Answer: Position, Named, Mixed and Restrictions.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What all important parameters of the init.ora are supposed to be increased if you want to increase the SGA size ? Answer: In our case, db_block_buffers was changed from 60 to 1000 (std values are 60, 550 & 3500) shared_pool_size was changed from 3.5MB to 9MB (std values are 3.5, 5 & 9MB) open_cursors was changed from 200 to 300 (std values are 200 & 300) db_block_size was changed from 2048 (2K) to 4096 (4K) {at the time of database creation}. The initial SGA was around 4MB when the server RAM was 32MB and The new SGA was around 13MB when the server RAM was increased to 128MB.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
If I have an execute privilege on a procedure in another users schema, can I execute his procedure even though I do not have privileges on the tables within the procedure ? Answer: Yes
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What are various types of joins ? Answer: Types of joins are:
Equijoins
Non-equijoins self join outer join
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What is a package cursor ?
Answer: A package cursor is a cursor which you declare in the package specification without an SQL statement.The SQL statement for the cursor is attached dynamically at runtime from calling procedures.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
If you insert a row in a table, then create another table and then say
Rollback.In this case will the row be inserted ?
Answer: Yes.Because Create table is a DDL which commits automatically as soon as it is executed.The DDL commits the transaction even if the create statement fails internally (eg table already exists error) and not syntactically. |
Yasser
To know the name of database:
select * from global_name;
select name from v$database;
To know the version of database:
SELECT version
FROM v$instance;
To check the privilege of current user:
select * from session_privs;
To unlock user:
ALTER USER user_name ACCOUNT UNLOCK;
Operators:
SQL> select id from test1;
ID
---------
1
1
1
2
3
4
6 rows selected.
SQL> select id from test2;
ID
---------
4
5
SQL> select id from test1
2 union
3 select id from test2;
ID
---------
1
2
3
4
5
select id from test1
union all
select id from test2
ID
---------
1
1
1
2
3
4
4
5
select id from test1
intersect
select id from test2
ID
---------
4
select id from test1
minus
select id from test2
ID
---------
1
2
3
No comments:
Post a Comment