An /*+ append */ hint, when added to an insertion, tells Oracle you’d like to do a direct path insert instead of a conventional path insert. Most people use the append hint to speed up an insert; however, direct path inserts aren’t always faster than conventional inserts.
I recently worked on a project where I have an interval-partitioned table that I insert into daily. After the insert, I wanted to gather statistics on the partition that I’d just inserted to. I thought back through some recent reading and remembered that the ROWID of an object contains the following elements:
- Letters 1-6 = the OBJECT_ID
- Letters 7-9 = the relative FILE_ID
- Letters 10-15 = the BLOCK within the file
- Letters 16-18 = the ROW within the block
I knew there was a package called DBMS_ROWID (although I had never used it) but I guessed there would be a procedure that I might could acquire an OBJECT_ID for a given ROWID.
And I was right.
Example explaining truncates and their effect on the cost of a full table scans, and how to de-allocate space from a table using:
alter table x enable row movement;
alter table x shrink space compact; -- moves rows around
alter table x shrink space; -- drops the high water mark
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.