The flashcards below were created by user
on FreezingBlue Flashcards.
What is the max size for VARCHAR2, NVARCHAR2 and RAW?
What are extended character data type columns?
- VARCHAR2 and NVARCHAR2 over 4000 bytes
- RAW over 2000 bytes
How are extended character data type columns stored?
Out of line, like LOBs
What parameter allows extended character data type columns?
In order to use extended character data type columns, what must be done?
- shutdown database
- startup upgrade
- alter system set max_string_size = extended;
- shutdown immediate;
What are the possible values of the parameter MAX_STRING_SIZE?
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
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
Where are Database Migration Assistant for Unicode executables found?
When COMPATIBLE is set to > 188.8.131.52 what is the default parameter for DB_SECUREFILE?
When COMPATIBLE is set to < 184.108.40.206 what is the default parameter for DB_SECUREFILE?
- In 12c, are BASICFILES still allowed?
- Can still create BASICFILES if you manually specify BASICFILE in the LOB storage clause
In 12c, what "replaces" Top-N queries?
The ROW_LIMITING clause
What two keywords form the ROW_LIMITING clause?
- OFFSET - Rows to skip before returning result
- FETCH - Number of rows to return
In ROW_LIMITING, if OFFSET is omitted, what is it assumed to be?
0, ie start at first row
In ROW_LIMITING, where does ORDER BY appear
- Before ROW_LIMITING clauses
- ORDER BY ID
- FETCH FIRST 5 ROWS ONLY
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
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
In ROW_LIMITING, what's the difference between FETCH FIRST and FETCH NEXT?
None, they do the same thing
What is basic syntax for a ROW_LIMITING sql?
- OFFSET n ROWS
- FETCH FIRST n ROWS;
- OFFSET n ROWS
- FETCH FIRST n PERCENT;
What are the compression clauses BASIC and FOR OLTP replaced with in 12c?
- BASIC- ROW STORE COMPRESS BASIC
- FOR OLTP - ROW STORE COMPRESS ADVANCED
In 12c can compression clauses BASIC and FOR OLTP still be used?
Yes, for backward compatibility
In 12c, what has OLTP compression been renamed to?
Advanced Row Compression