Teamwork Challenges - One of the newer guys on the team asked our project manager for some info. He needed to figure out how to get his scripts logging in to run unattended. Th...
When you drop an object, any privileges on the object that have been granted to roles disappear automatically. You can grant multiple privileges at once if you separate the privileges by commas. You remove privileges by REVOKE FROM. You can remove all privileges with REMOVE ALL PRIVILEGES or simply REVOKE ALL.
PUBLIC is an Oracle account that represents all users. You can grant privileges to PUBLIC. Then all users get that access. If you own an object, you do not need explicit privileges to access it. You get them by default. All grants operate immediately without the need for a COMMIT.
There are a slew of data dictionary views to look at privileges. USER_SYS_PRIVS contains those system privileges related to the user. DBA_SYS_PRIVS has all system privileges.
USER_TAB_PRIVS contains those privileges where you own the object, granted the privilege, or are a grantee of the privilege. ALL_TAB_PRIVS adds privileges through a role or that are granted to PUBLIC and available to you. DBA_TAB_PRIVS has everything.
ALL_TAB_PRIVS_RECD are privileges you have directly, through a role, or that are PUBLIC. There is a SESSION_PRIVS view that I am not too familiar with yet.
There are some old Oracle defined roles that are being phased out. But I think you still need to know them. CONNECT gives you CREATE SESSION. It is for a general user. RESOURCE is a role for application developers. DBA is a role for administrators.
Roles can be granted to roles. There are a number of data dictionary views associated with roles. ROLE_ROLE_PRIVS are the roles that have been granted other roles. ROLE_SYS_PRIVS are system privileges assigned to roles. ROLE_TAV_PRIVS are object privileges granted to roles.
Here is a trivia fact: there are no privileges specifically for indexes. You get the ability to create an index when you have the CREATE TABLE privilege.
Materialized views (MVs) were previously called snapshots. They are like regular views in that there is some SQL run to get the results. However unlike normal views, MVs store the results in a table. They were introduced in Oracle 8. The query used to populate the MV can go against tables, views, or even other MVs. Since the hard work is done when the MV is created, use of them can really speed up your queries.
Other databases have MVs. In Microsoft SQL Server they are called indexed views. And in DB2 they are called materialized query tables. You get the speed increase when you directly use the MVs. There is also a query rewrite technology where the database can use an MV instead of a base table to get speedup. This query rewrite was introduced in Oracle 8i.
There are different strategies to refresh the data in an MV. You can manually do it. Or you could periodically update. Or you could automatically update them when the underlying tables change. That last change gets captured in materialized view logs. There is a fast refresh option that requires specifics around the underlying query:
- No CONNECT BY
- No INTERSECT, MINUS, or UNION ALL
- No aggregate functions
- No joins other than subqueries
- No mismatch columns in a UNION
- Plus some other subquery restrictions
You cannot perform any DDL on an MV. But you can perform DML. It depends on how your MV was set up. You can make it read only. Or you could make it updatable. Do that by specifying FOR UPDATE AS prior to the SELECT. There are even writable MVs. But they are rarely used.
The query under the MV can have an ORDER BY clause. However that only applies to the data from the initial creation. Updates can make the MV out of order. The MV can be built deferred. Then the data can later be added during a refresh. Finally if you have a regular table that acts like an MV, you can turn it into an MV by using the ON PREBUILT TABLE during MV creation.
With flashback, you can look at data at a previous point in time. You can go back to the last DDL that occurred on a table. You are also constrained by the undo retention period specified by the Oracle database parameters. There are a couple types of flashback you can employ:
- FQ - Flashback Query
- FVQ - Flashback Query Version
- FTQ - Flashback Transaction Query
FVQ gives you rows that represent committed versions of data. Note that if you insert/update and then delete rows in a transaction, those operations do not show up in the FVQ. You only see material changes per transaction. You SELECT data whose VERSIONS are between TIMESTAMP values. You can also look between SCN ranges too. FVW does not work on views.
FTQ is running queries against the FLASHBACK_TRANSACTION_QUERY view. Working with this technology involves the global transaction identifier (XID). It is a RAW value tied to the transaction the change was made in. You can look at the value using the RAWTOHEX() function. Basically you want to query the view using some value(s) of the XID.
First up let's talk about the CTAS - Create Table As Select. You create a table and insert data into it all in one SQL statement. Constraints and indexes do not get copied over (except for the NOT NULL constraint). If you SELECT something other than a column, then you need to provide a column alias to define the column name in the new table. Or you could provide the fully qualified list of columns in the CREATE TABLE part.
The CTAS technique can be used to INSERT rows into a table using a subquery. Just SELECT INTO your table. Not need for a VALUES clause. Stick the SELECT subquery right after that.
Multitable insert allows you to, as the name suggests, insert into more than one table with a single SQL statement. This can be an unconditional or conditional insert. For unconditional inserts, you must specify the ALL keyword. Otherwise you can specify the ALL or FIRST keyword for a conditional insert. ALL means all of the WHEN clauses are evaluated. FIRST means only the first WHEN clause to match gets chosen. Multitable inserts do not work on views. Be warned that you cannot put sequences on the subquery for the mutlitable insert. Also watch out because the insert is a single SQL statement, which generates only one sequence value.
The tables in the data dictionary are named in a certain pattern. The normally start with the prefixes USER, ALL, or DBA. For example, USER_TABLES is all tables owned by the current user. ALL_TABLES is all tables the current user has access to. And DBA_TABLE is all the table in the database.
There are V_$ views which have a public synonym which starts with V$. An example is V$SESSION. These views are dynamic performance views. There are also GV_$ views, with public synonyms that start with GV$. These are global dynamic performance views. If you want to join some of these views together, you should first copy their data to temp tables. Then you can query the temp tables. This is because of the dynamic nature of these views based on what is going on in the system.
USER_SYNOYNMS contains private synonyms for the current user. PUBLIC synonyms are in ALL_SYNONYMS and DBA_SYNONYMS. USER_CATALOG has a bit of information on tables, views, synonyms and sequences owned by the current user. USER_OBJECTS has more information on objects owned by the current user.
Some of the most important data dictionary views are USER_TABLES and USER_TAB_COLUMNS. These have corresponding synonyms TABS and COLS. You can find out about your tables and columns in those tables with these views.
USER_SYS_PRIVS has the system privileges granted to the current yser. USER_ROLE_PRIVS has the roles granted to the current user. And USER_TAB_PRIVS has privileges granted on objects that the current user is involved with. "Involved" means is the owner, is granted privilege to, or has granted privilege for.
Finally the USER_CONSTRAINTS view has info on constraints owned by the current user. They are divided up by constraint type:
- P for primary key
- R for foreign key
- U for unique
- C for check and NOT NULL
ROLLUP is a subclass of GROUP BY. It produces subaggregate rows, or aggregates of aggregates. It works well with the SUM aggregate function. But you can use it with any aggregate function. For each group set, you get a single line summary. If the output of a normal GROUP BY is called regular rows, then the extra rows from the ROLLUP are called superaggregate rows.
Next up we have the CUBE. This is like a 3D ROLLUP. As such it is a subclass of GROUP BY. You get all the normal output from ROLLUP. You also get subtotals for the groupings.
Something that helps with formatting ROLLUP and CUBE output is GROUPING. This is a function that returns either 0 or 1. If you do not use ROLLUP or CUBE, GROUPING returns a 0. If you are on a superaggregate row of output, GROUPING returns a 1. Otherwise it returns a 0. You can use the GROUPING in your SELECT to transform the output. For example, you can format some value selected. Or you could replace the text entirely.
The final topic of the day is GROUPING SETS. They work with GROUP BY. They control what groups to display in the output. You pass lists in the GROUPING SETS. A NULL in the list indicates you want a single grand total. The result is almost like separate SQL statements GROUP BY each list, and the whole thing UNION ALL together.
- UNION ALL
I have used UNION and UNION ALL before. Normally I choose UNION unless there is some business restriction that wants me to filter out the duplicates. Here is a trivia fact: INTERSECT will also filter out any duplicates.
You cannot perform set operations on columns of type BLOCK or CLOB. You can only do an ORDER BY at the end of the whole set operation. The ORDER BY can be by position, or reference an alias from the first query.
This might be obvious. But you cannot add a NOT NULL constraint to a column on a table with records in it (which don't meet the constraint). When you create a foreign key column, you can set the constraint as DEFERRABLE. It won't be enforced until a COMMIT has been issues, or until you explicitly set the constaint as IMMEDIATE.
You can create an index on a column right when you are creating the table. The command can reference an existing index name. Or more commonly, it can just stick the index creation SQL right in there with the column definition in parentheses.
Function based indexes allows you to store indexes on the values of a function. That way when SQL uses the function, the optimizer will use the index to speed things up.
FLASHBACK lets you recover objects and data. For example you can do this on a TABLE. You could also do this on DML that has transpired. When you do it on a table, it recovers the table from the DROP TABLE command. You can't restore the state prior to when the last ALTER TABLE was issued. You can restore it to a newly named table. Non-bitmap indexes are recovered. Non foreign key constraints are recovered. And privileges are recovered.
The FLASHBACK works because when you DROP TABLE, it gets saved away. See details by querying the USER_RECYCLEBIN view. However if you issue a PURGE, the dropped table will be removed from the recycle bin and you cannot FLASHBACK recover it.
The FLASHBACK can bring a table back to a point in time defined by:
- Prior to the DROP
- A TIMESTAMP
- An SCN
- A restore point
To use an external table, you must first CREATE DIRECTORY where the file will be located. Then you grant privileges on the directory. Finally you create the table with a special EXTERNAL syntax. You have to specify how the table will be loaded (i.e. SQL*Loader or DATAPUMP). We actually use external tables on our project when we have a file we need to access from database code.
- If a required field in a table is not contained in the view
- SQL to create view has GROUP BY
- SQL to create view has aggregation
- SQL to create view uses DISTINCT
- SQL to create view has more than one table (through a join)
Indexes are objects to make queries run faster. The WHERE clause and ORDER BY clause will specifically benefit from some indexes. They cannot be placed on columns of type LOB or RAW. You automatically get an index for a primary key or a unique column. The optimizer might use an index for almost everything except for NOT EQUALS comparisons. A composite index has two or more columns in it.
Finally synonyms are aliases for database objects. They can be for tables, views, sequences, or even other synonyms. The object referenced does not even have to exist when you create the synonym. But it must by the itme you use it. There are two types of synoyms:
- Single row
- Multiple row
- Multiple column
- NOT IN
- ANY (also known by SOME)
- CHECK constraint
- GROUP BY
- Function based index
- DEFAULT value
- WHEN (from the CASE)
- START WITH / CONNECT BY
WITH allows you to name a subquery block. The named queries act like inline views or temporary tables. The name is valid in all of the statement except in the subquery itself.
- Inner joins match values in all tables
- Outer joins match even if one table does not have a match
- Equijoins do an exact match on values
- Non-equijoins match with comparisons such as "greater than"
Another way to join tables with same column names is to use USING(same_col_name). You cannot prefix the column name with an alias or even a table name. This is similar to the natural join, except that this allows outer joins as well as inner joins.
The self join will join a table with itself. It can be an inner join or an outer join. It can be an equijoin or a non-equijoin.
Finally there is the Cartesian product. It is also called the cross join. This is not very useful. You invoke it with the CROSS JOIN syntax, or SELECT FROM multiple tables without specifying any joins. Be warned that you might get a whole lot of rows with this option.
Now back to scalar functions. An example math scalar function is MOD. It stands for modulus. It is the remainder left over when you divide integers. A similar function is REMAINDER. However REMAINDER will find the closet divisor, possibly going past when evenly divides, and might return a negative number.
How about we talk about timezones? Here are three types:
- UTC - Universal timezone, previously GMT
- Database - the timezone on the server returned by DBTIMEZONE()
- Session - the timezone of the user returned by SESSIONTIMEZONE()
The CURRENT_DATE() and CURRENT_TIMESTAMP() functions returns values in session timezone. The FROM_TZ() produces a TIMESTAMP WITH TIMEZONE. So does TO_TIMESTAMP_TZ(). You can use CAST() to change the type. You can EXTRACT() details of a timezone. And you can use AT TIME ZONE to transform a time to a different timezone.
A view acts like a table. It looks like a table. Behind the scenes, it runs a SELECT statement to produce its results.
Synonyms are aliases for existing objects. Private synonyms are owned by a schema. And public synonyms are owned by a user named PUBLIC.
The WHERE clause has access to all columns in a table, regardless of whether they exists in the SELECT list. The order of precedence, highest to lowest, of logical operators is NOT, AND, then OR.
If BETWEEN is in a WHERE clause, it is inclusive of the two end points given.
The ORDER BY also has access to all columns in the table. It is always the final part in a SQL statement. It can use alias names for sorting. You can also do positional ordering, the first column being number 1. NULL values always have the highest values.
I really have only one interesting fact about LIKE. The % is only expanded after the LIKE keyword. So don't use it on the left side!
The beginner database administration certification seems easy. You just pass a test. But to get to the next level, you need to jump through some hoops. One such hoop is that you need to take a class from Oracle. All formats of training cost the same amount. So if I go this route, I will probably attend a class in person. The problem is that cool classes like hard core performance tuning last five days. And that means they cost $3500. I guess I could get my company to pay. However the price seems a bit high.
Anyway, I am reading up my study guide to pass the SQL Expert certification exam. I learned some details about certain types that cannot be UNIQUE, PRIMARY KEY, or FOREIGN KEY. They are:
- TIMESTAMP WITH TIME ZONE
Next up I learned that there are six types of SQL statements:
- Session control
- System Control
DML is the old INSERT/UPDATE/DELETE. It also encompasses MERGE. I don't use MERGE. It is some kind of combination of the other types of DML, all in one statement. I guess I will be studying that too. TCL is things like COMMIT. I know about ALTER SESSION and ALTER SYSTEM. And I got plenty of experience with embeded SQL by using Pro*C.
Okay. I am up to Chapter 4 in my study guide. 14 more chapters to go and I shall be ready to take the certification test by the end of the month. I will let you know how I fare.
I am an Oracle PL/SQL Developer Certified Associate. The main reason I got certified was that my company said they wanted people to get certified, and they were willing to pay. So I think I bought some training materials. Went in for an in person class. Then took and passed the test.
The certifications desired for my dream job were ones I had not heard of before. Maybe they are new. I really don't pay too much attention to certifications any more. I think it is time for that to change. So I have been studying the OCA Oracle Database SQL Certified Expert Exam Guide. Got an online copy. I am encouraged that I get most of the answers right at the end of each chapter.
There were a lot of basics that I did not have a strong grasp on. For instance I knew object names were capped at 30 characters. I found that limit the hard way. However I did not know objects need to start with a letter. Also did not know the only special characters allowed in the name are $, _, and #.
Speaking of names, there are objects that are grouped together in namespaces. That is, certain objects cannot have the same name in the same namespace. Here are those namespaces:
- user, role, public synonym
- table, view, sequence, private synonym
One strange thing about this site was that it provided statistics on my competition. It did not name any names. But it informed me about the details of the other people who applied for the job I was interested in. Weird.
Here were the different current salaries of the competition:
- ETL Developer
- Oracle Developer
- Project Lead
- Senior Architect
- Senior Developer
- Front end development
- Project Management
What else can I do to improve my rank? Get some more certifications. I am working on that. Expand my areas of expertise. There are a lot of hot technologies in the Oracle Database universe. The real winner might be reaching out to my network. I have worked so long that I know tons of people out there with an Oracle database background.
Then there is the RANK() group function. It will order specified column(s) from first to last. Ties get the same rank, but subsequent numbers are skipped based on how many rows tie.
GROUP BY will put sets together to be treated as a whole. Group functions then operate on these sets. You can combine the ORDER BY with GROUP BY. ORDER BY can contain any expression in the GROUP BY clause, and expression in the SELECT clause, group functions, and USER/SYSDATE/UID.
The HAVING clause will restrict sets of rows from a GROUP BY. It requires GROUP BY. You can use any expression from the GROUP BY, or any group functions in the HAVING clause.
Prior to Oracle 8, large objects were represented by the LONG and LONG RAW types in the database. There could be at most one column of this type per table. The data was stored inline with the other columns in the table.
Now we have two general types of large objects (LOBs): internal and external. The internal variety are stored in the database in tables. Examples of this are the CLOB, NCLOB, and BLOB. I am most familiar with the BLOB. You can manage these objects with the DBMS_LOB built in package.
Then there are external LOBs. An example of this is a BFILE. The data is stored on disk. And the access to the data is read only. I don't recall ever working with external LOBs. Maybe it is time for a small practice project with them?
- Associative Arrays
- Nested Tables
Next you have nested tables. Indexes are numeric and start with 1. These start out as dense collections. But they could get sparse if you DELETE an item. This type of collection can be stored in a database. When you do that, the data for columns of this type is stored out of line. You can initialize the collection with a constructor. And you can define a collection of this type like TYPE my_nested_type IS TABLE OF VARCHAR2(4).
The VARRAY is like the nested table. It also has numeric indexes which start at 1. You can store collections of this type in a table. Unlike nested arrays, columns of this type in a database table have the collection stored inline. The result is better performance than nested tables in database tables. This collection type is always dense. While you can DELETE from a VARRAY, you must DELETE all items in the VARRAY. You can define a collection of this type like TYPE my_varray_type IS VARRAY(10) OF VARCHAR2(4).
Here are some functions that work on collections:
- EXTEND (nested table or VARRAY only)
- TRIM (nested table or VARRAY only)
To start with, you can define a cursor in the declaration section. If it needs any parameters, you pass it a list in parentheses almost like you do a function. Then you OPEN the cursor, passing in any parameters in parentheses. You can then FETCH the cursor into your variables. And before doing a CLOSE on the cursor, you might be checking any of the cursor attributes:
When using a FORALL cursor, there are the additional attributes %BULK_ROWCOUNT and %BULK_EXCEPTIONS. Most of the time when you define an explicit cursor, you have the cursor name that you prepend to the attributes. However you could do an implicit cursor by executing a SELECT statement. In that scenario, you can still check the attribute, but you use the term SQL instead of the cursor name. For example, you can check SQL%ROWCOUNT.
Then there is my good friend the ref cursor. I actually don't use these very frequently. My cursors are created and used in a procedure or function. I don't pass cursors around to other procedures. But if you had to, you could use a ref cursor. It is, as the name implies, a reference to a cursor. There are two types: strong and weak. Strong ref cursors have a fixed return type in the prototype. Weak ref cursors can be used for any SELECT statement. There is also a generic SYS_REFCURSOR which is a weak ref cursor.
The book goes on at length about collections. However that warrants its own separate post.
For example, DUAL is actually a table owned by SYS. I just considered it a virtual scratch pad table area. But it is a real table. It only has one column called DUMMY of type VARCHAR2(1). Furthermore, that table has exactly one row with a value of "X".
I reviewed the syntax to create a package. There is no BEGIN keyword in declaring the specification. Just an END. The package body has an optional BEGIN-END pair. But that is for a one time initialization set of code. It executes once the first time your session accesses the package. I have actually used this feature a time or two.
Now a bit about types. I know that NUMBER is an Oracle type. INTEGER on the other hand, is an Oracle subtype of NUMBER. You can define your own subtypes. Not that I do that much.
There were a bunch of tips on getting better performance. Some were not intuitive. Try to do your operations in a single SQL statement.Avoid the use of implicit conversion. I am all behind that. Implicit conversion just feels like sloppy program to begin with. Do not define your variables as NOT NULL.
Huh? You would think limiting a variable to NOT NULL might help performance. Nope. For those variables, Oracle has to do extra work to keep checking if the thing is getting set to NULL. Use PLS_INTEGER for math operations. And of course, use BULK COLLECT and FORALL when dealing with collections.
Went through a couple iterations until I got the basic table definitions the way I wanted. Then I coded up a procedure to insert records into the tables. Something strange was happening though. The IDs were not getting set. I thought I manually tested the triggers from the SQL*Plus command prompt.
Turns out my trigger became invalid when I kept dropped and recreating the tables. Easy enough to fix. Just recompile the triggers. Then I did some philosophical thinking about my setup. I insert a new row of data. The trigger populates the ID. How do I then find out what that ID is if I want to insert it as a foreign key in some table?
In the old days, I would let my procedures explicitly generate the IDs. That way I had the ID before the INSERT, and I could then use the value I stored in a variable to use as foreign keys. Now that the IDs are set automatically in triggers, I got to query the database after the fact. There has to be a better way to do this. Any ideas?
SQL ErrorWhat? At first I thought something was broken with user creation. Then I paid closer attention to the ORA-65096. Okay. I need a more complicated username. Nope. That was not the problem. Finally I broke down and Googled the subject.
Error signaled by database server
ORA-65096: invalid common user or role name
ORA-06512: at "SYS.PRVTEMX_ADMIN", line 8565
ORA-06512: at "SYS.WRI$_REPT_SECURITY", line 288
ORA-06512: at "SYS.DBMS_REPORT", line 898
ORA-06512: at line 1
Apparently there is some new magic going on with Oracle 12c. The username needs to start with C##. I should have known something was up when the default username in Enterprise Manager when you create a new user is C##.
This has something to do with the Oracle 12c multitenant architecture. That is somehow connected to pluggable databases as well. Those are not terms I am familiar with yet. I did see online that there were some hacks I could apply that would let me name usernames the way I want. Time to get hacking.
APEX is data in a bunch of tables plus some PL/SQL code. Since it is web based, you specify a URL which gets translated to an APEX PL/SQL call. Results are sent back in the form of HTML. Each request uses a separate database session.
There are two models that APEX can run under: the three tier model and the two tier model. Here is the three tier model:
- Web browser
- Oracle HTTP server with mod_plsql
- Oracle database with Oracle APEX
- Web browser
- Oracle database with embedded PLSQL gate plus APEX
There are some sample objects available. One is Human Resources (HR) and another is Order Entry (OE). I think I will just stick with my Oracle database 12c for now. There will be time to play with APEX in the future.
Before I could run over and log in with Enterprise Manger, I decided to click the Password Management button at the end of the database install and configuration. There were a ton of accounts that got created for free with the database.
The only two accounts that were unlocked by default were SYS and SYSTEM. I always wondered what the difference between these two were. They are both real users with schemas. SYS owns the data dictionary tables. I guess SYSTEM is one I should log onto first to create my real daily user?
One thing I do know is that these are unlike SYSDBA and SYSOPER, which are actually roles and not users. The real surprise was the tons of other users that got created by default. I left all those user accounts locked by default. Here are the multitude of accounts:
In Enterprise Manager, I went over to Security|Users to create my first user account. Now we are cooking with gas. I think I am going to have some fun with this database. Already got a few applications that I need written.
This time around it worked. That's when I arrived at Step 1 of 10. Had to decide between a desktop and server class. Well my little laptop is definitely the desktop variety. Then I was given the chance to specify the Windows user for the Oracle home user. I just chose a built-in account. That might be a security risk in the production world. But this is just my throw away database.
There was a drop list choice for the installation type. However the only option in it was the Enterprise edition. Good enough for me. The global database name was defaulted to "orcl.home" which I accepted as is. For some reason I decided to save my response file. You never know when I might want to do an unattended install with the same set of options.
Here were the steps for the database install:
- Copy files
- Setup Oracle base
- Oracle Database configuration
If you want to program with Oracle components, you will need one of the following versions of Microsoft Visual Studio:
- Visual C++ 2010 SP1
- Visual C++ 2012
- Visual C++ 2013 Update 2
- Oracle Call Interface
Oops. The Universal Installer would not run on my machine. It complained that the Java run-time was not found at "bin\jawaw.exe". I was directed to go to http://www.javasoft.com to install the JRE version 1.4 or higher. Umm I thought the installer was supposed to install Oracle's own version of the JRE.
ACCESSIBLE BY will allow you to specify packages that can access your newly defined package. You cannot call this newly defined package directly. But the packages on the "white list" can. This allows you to define helper functions that have restricted usage.
You can now grant roles to program units. That is, grant them to packages, procedures, and functions. Then you can use invoker's rights on them. They can get the privileges they need through the roles. You can bind PL/SQL types such as BOOLEAN to SQL. FETCH FIRST let's you limit the number of rows that return from a query.
BEQUEATH_CURRENT_USER let's you specify a view to have invoker's rights. Invisible columns can be created when you specify INVISIBLE during the DDL. They can only be seen if you explicitly name them. DESCRIBE does not see them. Neither does SELECT *. However %ROWTYPE knows about them. They have no column order. Strangely enough, that can be mandatory columns.
The UTL_CALLSTACK package contains routines previously found in DBMS_UTILITY. Then there is the EXPAND_SQL_TEXT in the DMBS_UTILITY package that expands view queries to their base tables. You can ENABLE_DDL_LOGGING. This is an init param. All DDL will now be logged in an XML file. It can be activated at the database or session level.
Temporary undo can now be stored in a temporary table instead of the undo tablespace. You can now issue a TRUNCATE TABLE CASCADE to get rid of tables plus those that depend on the table you are truncating.
You can use CURRVAL and NEXTVAL in PL/SQL without having to SELECT them from DUAL. You get all kinds of options for specifying parameters in function/procedure calls. You can still do the old positional method. But you can also do the named method. Or you could combine positional and named in what we call a mixed style.
Big news. There is a RESULT_CACHE directive you can put at the end of a function declaration. Results of the query get cached in the SGA. This can speed things up considerably. There are some limitations:
- Does not work in an anonymous block
- Does not work with invoker's rights
- There can be no OUT parameters
- The function cannot access a data dictionary
- The function cannot access a temporary table
- The function cannot access a sequence
- The function cannot access a SQL function
- No parameters can be any type of LOB
- No parameters can be a REF CURSOR
- No parameters can be a collection
- No parameters can be an object
- No parameters can be a record
- Return type cannot be any of these prohibited parameter types either
New types SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE are what you would expect. They have the added restriction that they cannot be NULL. CONTINUE can be used to move to the next iteration in a loop. Compound triggers can put triggers for more than one firing point together. This let's you share code and data between them. They all have to be DML triggers. You can only GOTO code in your own section. Other restriction that make sense are that :NEW can only be changed BEFORE EACH ROW. And :OLD and :NEW cannot be referenced in the before and after statement triggers.
There is a new syntax to specify the parent you inherited a method from. It looks like (SELF AS my_parent).my_method(). FORALL can not reference specific elements in collections. This can be done in the SET and WHERE clauses. LISAGG is a new built in function that does string aggregation. That is, it can turn values from multiple rows into one big concatenated row.
COLLECTION improvements have been made. You can CAST to a specific type of collection. You can order the elements in a COLLECTION. You can specify that the collection has only DISTINCT values in it.
Virtual columns can be added to tables. This let's you store an expression in something that behaves like a column. Syntax involves GENERATE ALWAYS AS ... VIRTUAL during column definition. It can be index. It can be part of a constraint. This makes it act like a view.
Hey. PIVOT is a new keyword to transpose rows to columns. This is good for applications such as reports. It is an aggregate operation. There is a reverse UNPIVOT that transposes columns to rows instead.
Regular expressions can now be used in SQL queries. Here are the functions:
A new UTL_MAIL package sends mail programmatically without having to know the low level details of the SMTP protocol. A new UTL_COMPRESS package lets you zip and unzip data. It works with RAW/BLOB/BFILE types.
There is now conditional compilation of PL/SQL. You can get the current version with DBMS_DB_VERSION. Then you can use conditional $IF expression $THEN statement syntax. There is also $ERROR handling.
DBMS_DDL allows you to hide PL/SQL source code from users. Use the CREATE_WRAPPED keyword. The PLS_INTEGER grows in size to match BINARY_INTEGER. Implicit conversion can now be done between CLOB and NCLOB. Watch out for performance implications though.
DBMS_UTILITY gets the FORMAT_ERROR_BACKTRACE method to print out the call stack during exception processing. COLLECT is a new group function that gathers GROUP records and rturns them in a collection. You can write and read external tables.
Now I am looking around for my next gig. I want it to be a database development job. Specifically I want to be an Oracle developer. So I had better know what is new. I use the term "new" loosely. Well let's pick up where I left off. What was new way back when Oracle 9i came out?
There is a new DBMS_METADATA package that can extract object definition. It will output XML or DDL. Your choice. Then there are the ANSI-standard CASE statements and expressions. The UTL_RAW package can convert to and from NUMBER and BINARY_INTEGER. There is a web version of SQL*Plus called iSQL*Plus.
You can insert a full row into a PL/SQL table now. You can create collections that are indexed by a VARCHAR2. They become hash tables. You can override an object's default constructor with your own code. The TREAT keyword allows you to specify which method you have inherited gets executed.
TIMESTAMP is a new type with fractional seconds. "Table Functions" produce a collection of rows. The result can be queried like you query with SQL. An example of the product is a nested table or VARRAY. Speaking of nested, you can nest collection types now. For example, you can have a PL/SQL table of PL/SQL tables.
You can do bulk fetches with native dynamic SQL (EXECUTE IMMEDIATE). There is a MERGE command which combines insert and update. This is something that is allegedly important for data warehousing.
The XMLTYPE was introduced in Oracle 9iR1. This allows the database to natively support XML. The type is treated as a user defined type with built in functions that operate on it. Therefore you can use it in SQL just like any other user defined type. The SQL can query parts of the XML document. You can use the type for column data types. You can use it in PL/SQL as types. It supports XML Schema.
There are different ways that XMLTYPE can internally store the data. However it is stored, an XML DOM tree structure can be constructed on demand when the need arises. Here are some options on how the XMLTYPE can internally store the data:
- As a CLOB
- Object Relational
- Binary XML
If you want to get a some parts of the XML, you can use the extract() function. Pass it an XPath string. It will give you back an XMLTYPE. Or you can use the extractvalue() function. It too takes an XPath string to specify the piece of XML you want. It however returns a scalar value.
Next there was Oracle Enterprise Manager Grid Control. This allows you to manage many databases, application servers, and other products. The Grid Control resides on a separate server and has its own database. You can monitor Oracle infrastructure. You can also monitor non-Oracle items such as:
- Other databases
- Web servers
- Application servers
- Operating systems
- Oracle Management Servive
- Oracle Management Agent
- Oracle Management Repository.
To simply monitoring, you can combine single components into groups. Then you can monitor the whole group as one. You can automate administrative tasks. You can publish customized reports. These reports can be HTML based. You can manage Oracle patches. The Grid Control is extensible through management plugins.
Data Masking is an add on pack for Enterprise Manager. It allows you to meet data privacy and data protection mandates. It replaces sensitive values with realistic replacements. Thus you can use production like data for non-production uses.
Data Masking can actually scan for sensitive data. It can make sure sensitive data does not leave the production environment. Its operation is also called scrambling or anonymization. The scrubbing is based on rules that you set up or choose from some preset formats.
Here are the data type supported for scrubbing:
- LOB (requires Grid 11gR1)
- Clone production to a staging environment
- Select mask definition in staging
- Clone the masked staging data to a test environment
- Credit card numbers
- ISBN numbers
- UPC codes
- Social Security Numbers
- Phone numbers
- Select catalog role
- Select any dictionary
- Execute privileges on DBMS_CRYPTO
- Clustered tables
- Object tables
- XML tables
- Virtual columns
- Bulk operations
- Disabled logging
- Running in parallel
Spatial defines a number of geometry types:
The basic building block in Spatial is an element. Types, like mentioned before, are point, line, or polygon. Many elements make up a geometry. And a collection of geometries is a layer.
Spatial allows you to perform an area of interest query. There are two tiers to its query model: (1) primary filter, and (2) secondary filter. To help speed querying, a concept of a spatial index has been created. This is a logical index. There are two types. The first, which is the default, is the R-tree. It uses a minimum bounding box. The second is the quadtree. It uses a technique called tessellation. Each rectangle is chopped into smaller rectangles.
The Virtual Private Database (VPD) technology takes security to another level. You get more control. Specifically you decide which rows any type of user is allowed to see. I have written about VPD before. Now I want to talk about Oracle Label Security (OLS). OLS is built upon VPD. But unlike VPD, which requires you to write an access function in PL/SQL, OLS runs "out of the box."
Out of the box means you don't need to do any programming to use OLS. You can set up what you need using Oracle Enterprise Manager. You can create labels. Assign labels to rows. And also assign labels to users. Also you can use Oracle's policy manager tool.
As you can imagine, OLS was originally created to server government applications. Specifically it was meant to address Department of Defense security needs. However OLS must have been a hit. Because now it is also being used in the commercial sector.
The high level idea is that users can have labels associated with them. Specific rows of specific tables can also have labels applied to them. When a table is accessed by a user, the labels of the user and rows are compared. If the labels match, the user gets access. This label information is stored in a new column added to the table for access purposes.
Although I have heard it different ways, more people report that OLS does not come installed by default in the Oracle database. If so, you must use the Universal Installer to add it in. You also need to configure the LBACSYS account that gets installed with OLS. You must unlock it, set its password, and grant it SELECT ANY DICTIONARY.
RMAN backups are stored as backup sets. It can do incremental as well as full backups. RMAN works with a media manager. That would be the system that controls the devices where backups are stored. For example, one media manager might control your tape drive.
An important facet of RMAN operation is the recovery catalog. This is a database schema where RMAN activity is recorded. It tracks the file names of all database files. It stores the location of redo logs. It can be used if the control file is lost.
RMAN is started by running the rman executble. You CONNECT TARGET to log into a database. The user needs SYSDBA privileges, although you do not explicitly state AS SYSDBA. The BACKUP command will start the backup. Unique file names are generated for backup files. The files are proprietary and specific to RMAN only.
You can do a hot backup while the system is running. The database must be in ARCHIVELOG mode. This results in what they call an inconsistent backup because it will require redo logs. Backups can be full or incremental. Incremental backups start at level 0 which is equivalent to a full backup. The VALIDATE command checks whether there is any backup corruption.
You can put RMAN commands in a file. Run it with the @ sign, just like SQL*Plus. Use the LIST BACKUPS for a report. Or use the REPORT command for a more comprehensive report. DELETE will explicitly get rid of old backups. Any backups older than the retention period are automatically deleted. Recovery involves a RESTORE DATABASE along with a RECOVER DATABASE command.
The VPD restrictions apply to select/insert/update/index/delete statements. They do no affect DDL. The security will have an impact on security. There are some types of VPD you can use to lessen the performance hit:
- Static policy - applied once per query
- Context sensitive - applied when context changes
- Dynamic policy - reevaluated every run
You manage the VPD security with calls to the DBMS_RLS package. RLS stands for row level security. There is also a column masking type of VPD. That style will return all rows int he object. But sensitive columns will be returned as NULL values. The column masking technique only applies to SELECT statements.
VPD policies can be managed in groups. One should not perform a SELECT FOR UPDATE on an object managed with VPD. There can be multiple polices on an object. There WHERE clauses implementing the VPD will be joined via AND.
Earlier I said that VPD cannot be bypassed. VPD is not applied in these circumstances:
- DIRECT path exports
- Operations on SYS owned objects
- Operations run by SYS
- Operations run AS SYSDBA
- The user has the EXEMPT ACCESS POLICY privilege
Bulk processing is the term to describe BULK COLLECT and FORALL. The big idea is that there is a PL/SQL engine and a SQL engine in the database. Switching between the two costs you speed due to overhead. So it would be best to make one handoff from the PL/SQL engine to the SQL one.
BULK COLLECT will cause a query to make a single switch into the SQL engine and fill up your colection. FORALL works the other way around, allowing you to do one DML operation for all the items in your collection with just one context switch into the SQL engine.
There is a warning though. Don't do unbounded BULK COLLECT operations. That will eat up a lot of memory in the PGA (Program Global Area). Collections are stored in memory there. You don't want a huge one taking up too much memory. Use the LIMIT statement to cap how many records are grabbed at once in a BULK COLLECT. Experience says you should use a LIMIT of at least 25. It would be preferable to use a LIMIT of around 100. Oracle uses 100 as the default optimization for cursor loops in Oracle 10g and above.
After you do the BULK COLLECT, if there are records, they will start with index 1 in your collection. The rest of the records will be densely populated in the collection. If there are no records, the collection will be empty. The COUNT on your cursor will be zero. There will not be a NO_DATA_FOUND exception raised in this scenario. Similarly you cannot count on %NOTFOUND. Use the COUNT.
Here is a tip for the FORALL. It is not a loop. You don't need a loop command. By default the collection you are doing DML on must be dense. If not, you must use the IN INDICES OF with the FORALL. That only works with Oracle 10g and above. Normally you would not want exceptions in the middle of your FORALL to stop the flow. To prevent that, use the SAVE EXCEPTIONS clause. Then errors will be stored up in the SQL%BULK_EXCEPTIONS pseudo collection.
Data Modeler lets you create, browse, and edit models. Here are some model types it supports:
- Data Type
Why go through the trouble of using a Data Modeler? Some say it is much easier than maintaining scripts manually. It can serve as a documentation tool. Or it can just help you get a handle on an existing database system.
The ERD deals with entities and relationships. It does not show a specific entity. Instead it deals with entity sets. Drawing up an ERD is part of a data modeling technique. In the end, it is used to create a relational database. An ERD is not well suited for unstructured data.
There are different style to draw the ERD:
- Information engineering
- Peter Chen
- Charles Bachman
- James Martin
- Oracle Designer
- Rational Rose
- System Architect
- TOAD Data Modeler
CA ERwin is a data modeler. The name stands for "Entity Relationship" and Microsoft "Windows". The tool was created by Logic Works. It allows you to also do requirements analysis. Of course it lets you do database design. ERwin will let you create a custom information system, a transactional database system, or a datamart. There are four main editions of the software:
- Community - free, subset of features, max 25 objects
- Navigator - read only
- Standard - an enterprise edition
- Workgroups - collaborative
- SQL Server
Once installed, I found the IDE to be very intuitive. It felt like using Microsoft's Visual Studio, where I feel at home. I started a logical modeling project. Generated a bunch of entities chock full of attributes. Save my file off with an "*.etwin" extension. Later I plan to create a physical model and deploy it to an Oracle database. Oh my.
But back to OWB. It is a data warehouse integration solution. The goal is to migrate data from legacy systems and put it into a data warehouse. Sounds like it was just what we needed. OWB has some secondary goal: modeling, profiling, cleansing, and auditing of data.
It was first released in January 2000. OWB 11gR2 is the last released. It will be patched to work with Oracle 12c. But it scheduled to be integrated in Oracle Data Integrator (ODI).
OWB is a repository driven tool. That is, the metadata that describes the ETL is put into a schema. You develop mappings and design process flows with it. It deploys into PL/SQL packages. It runs under Control Center and the OWB runtime. Oracle has to be the target.
The system we were replacing using OWB and a web version of our application went live briefly. It had a bunch of problems. It was slow. It did not implement all of the requirements of the old system. I can't blame these problems on OWB per se. Others have tried to rewrite the huge legacy system without success, and they did not use OWB in their solutions.
EBS stands for E-Business Suite. It is also know as e-BS, EB-Suite, and Applications/Apps. They are a bunch of Oracle business applications. They are each licensed separately. The latest release is Release 12, also known as R12. EBS is huge. It is second only to SAP in sales.
Here are the main groups of apps in EBS:
- Enterprise Resource Planning (ERP)
- Customer Relationship Management (CRM)
- Supply Chain Management (SCM)
- Oracle Forms Server
- Oracle Reports Server
- Apache Web Server
- Oracle Discoverer
There are three main operations in OLAP:
- Drill down
- Slicing and dicing
There is a certain term in OLAP called the OLAP Cube. It is also known as a multidimensional cube or hypercube. It contains numeric facts (called measures) categorized by dimensions. This cube is created from data warehouse schemas. The speed in accessing the cube is from the aggregation of data. For some complex queries, accessing data can be 1000 times faster than querying a normal database.
There are different types of popular OLAP systems:
The dimension table categorizes data. It has less rows than a fact table. But it will probably have a lot of attributes. These attributes are descriptive text values that help answer business questions. They can contain data collected at the lowest level. The data can be aggregated into hierarchies. These tables might take along time to load.
Here are some misc terms. A level is a position within a dimension table hierarchy. A stars query is a join between a fact table and a dimension table. A centipede schema is a star schema with a lot of dimensions.
An entity is a chunk of information. That are things of importance. Entities usually map to a database table. Attributes are components of an entity. They define the unique of the entity. Logical design is the process of identifying entities and attributes. You could use a tool such as Oracle Warehouse Builder or Oracle Designer to conduct logical design.
- Fact Constellation
The snowflake schema is like a star schema. However the dimensional tables are normalized. The fact constellation schema has multiple fact tables. It has shared dimension tables which are large. It is more complicated. The galaxy schema has many fact tables. They have common dimensions. It is the combination of many data marts.