Oracle 12c Upgrade: 21 – SQL Enhancements

Card Set Information

Author:
Tralala
ID:
297940
Filename:
Oracle 12c Upgrade: 21 – SQL Enhancements
Updated:
2015-03-09 03:54:46
Tags:
SQL Enhancements
Folders:
Oracle 12c Upgrade
Description:
Oracle 12c Upgrade: 21 – SQL Enhancements
Show Answers:

Home > Flashcards > Print Preview

The flashcards below were created by user Tralala on FreezingBlue Flashcards. What would you like to do?


  1. What is the max size for VARCHAR2, NVARCHAR2 and RAW?
    32,767 bytes
  2. What are extended character data type columns?
    • VARCHAR2 and NVARCHAR2 over 4000 bytes
    • RAW over 2000 bytes
  3. How are extended character data type columns stored?
    Out of line, like LOBs
  4. What parameter allows extended character data type columns?
    MAX_STRING_SIZE=EXTENDED
  5. In order to use extended character data type columns, what must be done?
    • shutdown database
    • startup upgrade
    • alter system set max_string_size = extended;
    • @ORACLE_HOME/rdbms/admin/utl32k.sql
    • shutdown immediate;
    • startup
  6. What are the possible values of the parameter MAX_STRING_SIZE?
    • STANDARD
    • EXTENDED
  7. Once extended character data type columns, can existing tables have their column sizes changed?
    • Yes, but may cause chaining in VARCHAR2
    • Index on such columns have to be rebuilt
    • Oracle recommend migrating to a new table
  8. Describe Database Migration Assistant for Unicode.
    • DMU, a new GUI tool to migrate databases from standard character sets to Unicode
    • Replaces CCSSCAN and CSALTER
    • Reduces downtime and has better error handling and failure recovery capabilities
  9. Where are Database Migration Assistant for Unicode executables found?
    $ORACLE_HOME/dmu/dmu/bin/
  10. When COMPATIBLE is set to > 12.0.0.0 what is the default parameter for DB_SECUREFILE?
    PREFERRED
  11. When COMPATIBLE is set to < 12.0.0.0 what is the default parameter for DB_SECUREFILE?
    • PERMITTED
    • In 12c, are BASICFILES still allowed?
    • Can still create BASICFILES if you manually specify BASICFILE in the LOB storage clause
  12. In 12c, what "replaces" Top-N queries?
    The ROW_LIMITING clause
  13. What two keywords form the ROW_LIMITING clause?
    • OFFSET - Rows to skip before returning result
    • FETCH - Number of rows to return
  14. In ROW_LIMITING, if OFFSET is omitted, what is it assumed to be?
    0, ie start at first row
  15. In ROW_LIMITING, where does ORDER BY appear
    • Before ROW_LIMITING clauses
    • ORDER BY ID
    • FETCH FIRST 5 ROWS ONLY
  16. How can the rows returned by fetch be defined
    • Explicitly as number of rows - FETCH FIRST n ROWS
    • As a percentage - FETCH FIRST n PERCENT
  17. What does FETCH FIRST 5 ROWS WITH TIES do?
    • Fetches the first 5 rows and all the values that match the lowest value
    • ie 15,15 11, 8, 4, 4, 4, 4
  18. In ROW_LIMITING, what's the difference between FETCH FIRST and FETCH NEXT?
    None, they do the same thing
  19. What is basic syntax for a ROW_LIMITING sql?
    • ….
    • OFFSET n ROWS
    • FETCH FIRST n ROWS;
    • or
    • OFFSET n ROWS
    • FETCH FIRST n PERCENT;
  20. What are the compression clauses BASIC and FOR OLTP replaced with in 12c?
    • BASIC- ROW STORE COMPRESS BASIC
    • FOR OLTP - ROW STORE COMPRESS ADVANCED
  21. In 12c can compression clauses BASIC and FOR OLTP still be used?
    Yes, for backward compatibility
  22. In 12c, what has OLTP compression been renamed to?
    Advanced Row Compression

What would you like to do?

Home > Flashcards > Print Preview