Question:

[Oracle] DBA’s (SQL e SQL*Plus)

1. How can you pass variables into a SQL routine?
2. You want to include carriage return / linefeed into your output from a SQL script. How can you do this?
3. How do you call a PL/SQL procedure in SQL?
4. How do you execute a host OS command from within SQL?
5. You want to use SQL to generate SQL. What is it called and give an example.
6. What SQL * PLUS command is used to format output from a select? 
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)?
8. What special Oracle feature allows you to specify how the CBO treats SQL functions?
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?
10. What is a Cartesian product?
11. You are joining a local and remote table and the network manager complains about network traffic involved. Hodo you reduce the amount of traffic?
12. What is the default ordering in the ORDER BY statement?
13. What is TKPROF and how is it used?
14. What is EXPLAIN plan and how is it used?
15. How do you set the number of lines per page? The width?
16. How do you prevent output from coming to the screen?
17. How do you prevent Oracle from giving you informational messages during and after a SQL statement execution?
18. How do you generate file output from SQL?

Answers:

1. By the use of & or && symbol. For passing in variable numbers can be used &1, &2. To be prompted for a specific variable, place ampersand variable into the code itself: SELECT * FROM dba_tables WHERE owner = &owner_name; The use of double ampersand tells Oracle to reuse the variable for subsequent times, unless ACCEPT is used to get value from the user.
2. The best way is to use the CHR() function (CHR(10) as a return / linefeed and the concatenation function. Another method is to use the return / linefeed as a part of a quoted string.
3. By using EXECUTE or wrap the call in a BEGIN END block and treat it as an anonymous block.
4. By using “!” or”HOST” command
5. This is called dynamic SQL. An example:

   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;

6. This is done with the COLUMN. 
7. The only column you can group by is the COUNT(item_no), the rest are aggregate functions.
8. You can use hints – FIRST ROWS, ALL_ROWS, RULE, USING INDEX, STAR.
9. If you use MIN / MAX function against your ROWID, then select against the proposed primary key you can squeeze out the ROWID of duplicate rows quickly.

   SELECT ROWID FROM emp e
   WHERE e.ROWID > (SELECT MIN(x.ROWID)
   FROM emp x
   WHERE x.emp_n0 = e.emp_no);

   In a situation if multiple columns make up the proposed key, they all must be used in the WHERE clause.
10. A Cartesian product is a result of an unrestricted of 2 or more tables.
11. Push the processing of the remote data to the remote server by using a view to preselect information for the join. This will result in only data needed for the join being sent across the network.
12. Ascending
13. The TKPROF is a tuning tool used to determine the execution time for SQL statements. Use it first by setting TIMED_STATISTICS parameter to TRUE and then setting the entire instance SQL_TRACE to on or just for the session with an ALTER SESSION command. Once that is done you run TKPROF and generate a readable report with an explain plan.
14. The EXPLAIN plan is used to tune SQL statements. You have to have the EXPLAIN_TABLE generated for the user you are generating the explain plan for. This is done with the utlxplan.sql. Once the EXPLAIN_TABLE exists, you run the explain command with the statement to be explained. The explain table then is queried to see the execution plan.
15. The SET command in SQL * PLUS is used to control the number of lines generated per pager 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.
16. The SET option of TERMOUT controls output to the screen. Setting TERMOUT OFF turns off the screen output.
17. The SET option FEEDBACK and VERIFY can be set to OFF.
18. By use of the SPOOL command.