oracle

Olá pessoal!

Seguem as respostas corretas do nosso questionário sobre SQL e SQL*Plus.

Se você discordar de alguma resposta, fique a vontade para debater em nossos comentários abaixo!

  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.

Milton Bastos é DBA Oracle e Desenvolvedor PL/SQL, dividido entre Apucarana/PR e Curitiba/PR. Certificações: OCA (Oracle 11g DBA Certified Associate), Oracle Database 11g Data Warehousing Certified Implementation Specialist, Oracle Database 11g Sales Specialist Assessment, Oracle Database Appliance PreSales Specialist Assessment, Oracle Database Appliance Sales Specialist Assessment