151) How can i change the password for my username?
alter user user_name idenified by new_password;
so, if you are logged in as scott and want to change your password to lion, you can use
alter user scott identified by lion;
152) How can I retrieve a specified number of highest- or lowest-value columns with a single query
oracle refers to this as an “TOP-N Query”. Think of this as a two-step process. In the first step, you create a view with an ORDER BY clause that orders records in ascending or descending values of a specified column. You then select the top required number of records from this view using ROWNUM. You could combine both of these steps in a single query by selecting the specified number of records from an in-line view whose records are ordered in ascending or descending order of the required column. Thus, to retrieve the three employees in the EMP table with the
highest salaries, you could write the following query:
SQL> SELECT ename, sal
FROM (SELECT ename, sal
FROM emp
ORDER BY sal DESC)
WHERE ROWNUM <= 3;
To retrieve the two departments in the EMP/DEPT tables with lowest total salaries you could write the following query:
SELECT d.dname, e.total_sal
FROM (SELECT deptno, sum(sal) total_sal FROM emp
GROUP BY deptno
ORDER BY sum(sal) ASC) e, dept d WHERE d.deptno = e.deptno
AND ROWNUM <= 2;
153) What is a ROWID and Why do I need that
ROWID is the physical address (location) of the row on the disk. This is the fastest way to access a row in a table
154) How does Oracle store the DATE datatype internally?
Oracle stores data in a proprietary format. It uses 7 bytes for Data storage and 1 byte is used for the length data.
The following is the mapping of the oracle’s date type
Byte 1 -> Century
Byte 2 -> Year
Byte 3 -> Month
Byte 4 -> Day
Byte 5 -> Hour
Byte 6 -> Minute
Byte 7 -> Second
155) what is an oracle precompiler?
An Oracle Precompiler is a programming tool that allows you to embed SQL statements in a high-level source program. The precompiler accepts the source program as input, translates the embedded SQL statements into standard Oracle runtime library calls, and generates a modified source program that you can compile, link, and execute in the usual way. examples are
Pro*C Precompiler for C, Pro*Cobol for Cobol and SQLJ for Java etc.
156) What is a dual table and why is it used?
Dual is a table that contains a dummy column with a row ‘X’. it is used to satisfy the requirements of a SQL syntax containing a From clause.
157) What do locks actually do?
Locks are the mechanism that prevent more than one user at a time making changes to the database. There are several types of locks that define their scope.
1. tablelevel lock- allows only that user holding the lock to change any piece of row data in a table. Row share, Row exclusive, share, share row exclusive and exclusive are the several modes of a table lock.
2. Row-level lock- It allows the user to change one or two rows of data in a table.
Note: Any row in a table that is not held by a row-level lock can be changed by another user.
158) Why is the query not fetching the records for the date I wanted?
For example, if your query is
SELECT EMP_NAME, SSN FROM EMP WHERE HIER_DATE =
TO_DATE(’03-NOV-1999′,’DD-MON-YYYY’);
Oracle assumes the hours, minutes and seconds to 12:00:00, So it tries to fetch the records matched on that date at 1200 hours 00 minutes and 00 seconds.
If the record is inserted at 11:02:34 hours, it will not be fetched. If you need all the records for a particular date, use the date
conversion functions, like
SELECT EMP_NAME, SSN FROM EMP WHERE
TO_CHAR(HIER_DATE,’DD-MON-YYYY’) = ’03-NOV-1999′;
Remember that if you have an Index on the date field the query optimizer may not use it;
How do I drop an unused column ? Any restrictions if so what are they?
As of Oracle 8i, it is possible to drop a column or UNUSED Column from a Table. This feature drops the column from a table and releases any space back to the segment. It will also drop any columns that were previously marked as being unused. This clause also causes any indexes, constraints and statistics on this column to be dropped.
Alter table shippers drop column ( c9 );
Alter table shippers unused columns;
Restrictions. Oracle does not permit combination drop and set unused clauses in the same statement. Also an error will occur if drop column is attempted from an object type table or a nested table or a partitioning key column or a parent key column. Another good restriction is that a column cannot be dropped from any of the tables owned by SYS.
159) How can I protect my PL/SQL source code?
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 and available.
The syntax is: wrap iname=myscript.sql oname=xxxx.yyy
160) Can one use dynamic SQL within PL/SQL? OR Can you use a DDL in a procedure? How?
From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic SQL statements.
Ex: 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;
161) What is nls_date_format?
It is an national Language Set date format. It is used to change the date format and new_time() function to convert a timestamp to a new time zone. I want to use sub selects give me an example of what is a good case for sub selects Use subselects when you want to get detailed information based on group values.
162) What are these v$, x$ tables and views.
These are tables that are intented for the dba to manage performance related information. These are also referred to as dynamic performance views and give the dba’s enough information to see whats happening in the database at a particular point in time
163) How can I see what tables I have access to and what tables I own
Oracle Data Dictionary provides 3 levels of views user,all and dba views. These views provide information based on your privileges in the database.
User_tables, User_indexes.. etc., provide information about the
objects you own.
All_Tables, All_indexes… etc., provide information about the
objects you have access to.
Dba_tables, dba_indexes.. etc., provide information about all the
objects in the database but are only available to dba’s.
Example :
To see what tables I own…
select table_name from user_tables;
To see what tables I have access to and who the owner is…select
owner, table_name from user_tables;
164) How can I use a large rollback segment ( rblarge )to avoid the snapshot too old error
You can use the following command
set transaction use rollback segment rblarge;
This has to be the first statement of the transaction and you need to reissue this statement whenever you end the transaction, i.e, most of us forget to re issue the command after a commit or rollback.
165) which of the following is better to find the number of rows in a table
1. count(1)
2. count(*)
3. count(rowid)
the answer is
1. and 3.
The function count() executes faster when given a criteria/value which is quicker to resolve in the sql processing mechanism.Thus, an integer or rowid are faster to resolve than an ‘*’which is a wild card symbol for all the colums in a table and hence it is more time consuming.
166) What is a ROWID and Why do I need that
ROWID is the physical address (location) of the row on the disk. This is the fastest way to access a row in a table
Note :The rowid format in oracle 8 is different from rowid format of oracle7
167) What is a Cartesian product?
A cartesian product is a result from a select statement that returns all the data from both the tables . The result of a cartesian product is due to the unspecifying the where clause for the join select statement
168. To see current user name
Sql> show user;
169. Change SQL prompt name
SQL> set sqlprompt “123techguru > “
123techguru >
170. Switch to DOS prompt
SQL> host
172. How do I eliminate the duplicate rows ?
SQL> delete from table_name
where rowid not in (select max(rowid) from table
group by duplicate_values_field_name);
173.How do I display row number with records?
To achive this use rownum pseudocolumn with query, like
SQL> SQL> select rownum, ename from emp;
Output:
1 Scott
2 Millor
3 Jiyo
4 Smith
174. Display the records between two range
select rownum, empno, ename from emp where rowid in(Select rowid from EMP where rownum <=&upto
minus
Select rowid from EMP where rownum<&Start);
Enter value for upto: 10
Enter value for Start: 7
ROWNUM EMPNO ENAME
——— ——— ———-
1 7782 CLARK
2 7788 SCOTT
3 7839 KING
4 7844 TURNER
175. Oracle cursor:
Implicit & Explicit cursors
Oracle uses work areas called private SQL areas to create SQL statements. PL/SQL construct to identify each and every work are used, is called as Cursor. For SQL queries returning a single row, PL/SQL declares all implicit cursors. For queries that returning more than one row, the cursor needs to be explicitly declared.
176. Explicit Cursor attributes
There are four cursor attributes used in Oracle cursor_name%Found, cursor_name%NOTFOUND, ursor_name %ROWCOUNT, cursor_name%ISOPEN
177. Implicit Cursor attributes
Same as explicit cursor but prefixed by the word SQL
SQL%Found, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN
Tips :
1. Here SQL%ISOPEN is false, because oracle automatically closed the implicit cursor after executing SQL statements.
2. All are Boolean attributes.
178. Find out 9th highest salary from emp table
SELECT DISTINCT (a.sal)
FROM EMP A
WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B
WHERE a.sal <=b. sal);
Enter value for n: 9
179. To view installed Oracle version information
SQL> select banner from v$version;
180. Display the number value in Words
SQL> select sal, (to_char(to_date(sal,’j’), ‘jsp’)) from emp;
181. Display Odd/ Even number of records
Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
1
3
5
Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
2
4
6
182. Which date function returns number value?
months_between
183. Any three PL/SQL Exceptions?
Too_many_rows, No_Data_Found, Value_Error, Zero_Error,Others
184. What are PL/SQL Cursor Exceptions?
Cursor_Already_Open, Invalid_Cursor
185. Other way to replace query result null value with a text
SQL> Set NULL ‘N/A’
to reset SQL> Set NULL ‘’
186. What are the more common pseudo-columns?
SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM
187. What is the output of SIGN function?
1 for positive value,
0 for Zero,
-1 for Negative value.
188. What is the maximum number of triggers, can apply to a single table?
12 triggers.
189. How would you determine the time zone under which a database was operating?
select DBTIMEZONE from dual;
190. Explain the use of setting GLOBAL_NAMES equal to TRUE.
Setting GLOBAL_NAMES dictates how you might connect to a database. This variable is either TRUE or FALSE and if it is set to TRUE it enforces database links to have the same name as the remote database to which they are linking.
191. What command would you use to encrypt a PL/SQL application?
WRAP
192. Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any values to the calling application, a function will return a single value. A package on the other hand is a collection of functions and procedures that are grouped together based on their commonality to a business function or application.
193. Explain the use of table functions.
Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as a normal table or view in a SQL statement. They are also used to pipeline information in an ETL process.
194. Name three advisory statistics you can collect.
Buffer Cache Advice, Segment Level Statistics, & Timed Statistics
195. Where in the Oracle directory tree structure are audit traces placed?
In unix $ORACLE_HOME/rdbms/audit, in Windows the event viewer
196. Explain materialized views and how they are used.
Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.
197.When a user process fails, what background process cleans up after it?
PMON
198. What background process refreshes materialized views?
The Job Queue Processes.
199. How would you determine what sessions are connected and what resources they are waiting for?
Use of V$SESSION and V$SESSION_WAIT
200. Describe what redo logs are.
Redo logs are logical and physical structures that are designed to hold all the changes made to a database and are intended to aid in the recovery of a database.
201. How would you force a log switch?
ALTER SYSTEM SWITCH LOGFILE;
202. What does coalescing a tablespace do?
Coalescing is only valid for dictionary-managed tablespaces and defragments space by combining neighboring free extents into large single extents.
203. What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
A temporary table space is used for temporary objects such as sort structures while permanent tablespaces are used to store those objects meant to be used as the true objects of the database.
204. Name a tablespace automatically created when you create a database.
The SYSTEM tablespace.
205. When creating a user, what permissions must you grant to allow them to connect to the database?
Grant the CONNECT to the user.
206. How do you add a data file to a tablespace?
ALTER TABLESPACE <tablespace_name> ADD DATAFILE <datafile_name> SIZE <size>
207. How do you resize a data file?
ALTER DATABASE DATAFILE <datafile_name> RESIZE <new_size>;
208. What view would you use to look at the size of a data file?
DBA_DATA_FILES
209. What view would you use to determine free space in a tablespace?
DBA_FREE_SPACE
210. How would you determine who has added a row to a table?
Turn on fine grain auditing for the table.
211. How can you rebuild an index?
ALTER INDEX <index_name> REBUILD;
212. Explain what partitioning is and what its benefit is.
Partitioning is a method of taking large tables and indexes and splitting them into smaller, more manageable pieces.
213. You have just compiled a PL/SQL package but got errors, how would you view the errors?
SHOW ERRORS
214. How can you gather statistics on a table?
The ANALYZE command.
215. How can you enable a trace for a session?
Use the DBMS_SESSION.SET_SQL_TRACE
or
Use ALTER SESSION SET SQL_TRACE = TRUE;
216. What is the difference between the SQL*Loader and IMPORT utilities?
These two Oracle utilities are used for loading data into the database. The difference is that the import utility relies on the data being produced by another Oracle utility EXPORT while the SQL*Loader utility allows data to be loaded that has been produced by other utilities from different data sources just so long as it confirms to ASCII formatted or delimited files.
217. Name two files used for network connection to a database.
TNSNAMES.ORA and SQLNET.ORA