One of the things I read recently showed that you shouldn’t use NUMBER fields to store dates–you should be using the DATE data type.  (As Tom Kyte has recommended, you should pick the data type based on what it is you’re trying to store).

Continue Reading

Example explaining truncates and their effect on the cost of a full table scans, and how to de-allocate space from a table using:

Continue Reading

Trying to Creating a View?

You need the CREATE VIEW system privilege to create a view in your own schema, or the CREATE ANY VIEW system privilege to create a view in someone else’s schema.  These privileges can be inherited through roles.

You also need to be granted SELECT, INSERT, UPDATE, and DELETE on all tables referenced by the view, or the corresponding SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, and DELETE ANY TABLE system privileges, and these privileges may not be obtained through roles.

Additionally, in order to grant other users access to your view, you must have received object privileges to the base tables with the WITH GRANT OPTION, or the appropriate system privileges with the WITH ADMIN OPTION.

Trying to Truncate a Table?

If the table you’re trying to truncate is not in your own schema, you must be granted the DROP ANY TABLE privilege.  This privilege may be inherited through a role.

Trying to do something in a Procedure, Function, or a Trigger?

If the procedure or function is created with definer’s rights (which is the default) then any privilege obtained through a role is “null and void.”  Any privileges you’re trying to take advantage of must be granted to you directly (i.e. not through a role).  For example, if you create a new user and only grant the user the CONNECT and RESOURCE roles, you’ll see that if you login as the newly created user, you’ll be able to create a table; however, if the newly created user tries to create a procedure that creates a table, you’ll get “ORA-01031: insufficient privileges” because you’ve not been granted the CREATE TABLE system privilege directly–it’s been granted to you through a role.  Try granting the CREATE TABLE privilege directly to the owner of the procedure/function.

Triggers always have definer’s rights, so the above is always true for triggers.

If the procedure is created with invoker’s rights, it’s true that an invoker of the function may take advantage of roles; however at compile time the owner of the procedure must have been granted the necessary permissions on the underlying objects if static SQL is being used.  Again, these permissions cannot be inherited through roles–this is necessary for compile-time validation.  One way to avoid this is to use dynamic SQL (execute immediate), so that the dependency is not checked at the function’s compile time.

Are you used to working with a version of Oracle prior to 10.2?

Note that if you’re accustomed to working on Oracle version that’s pre-10.2, you were automatically granted the ALTER SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW, CREATE CLUSTER, CREATE DATABASE LINK, and CREATE SEQUENCE system privileges as part of the CONNECT role, but starting in Oracle 10.2 and beyond, the CONNECT role only has the CREATE SESSION.  So granting someone CONNECT will no longer allow them to create tables, views, etc.  More information here.  Note that using the CONNECT role nowadays is deprecated by Oracle.