trunc function

Use the following syntax that returns the start of the previous month. The sql uses the add_months function, which moves back x number of months, currently set to 12.

select trunc(to_date(add_months(sysdate,-12)), 'MONTH') from dual;

Rename Tablespace

Until 10g, this is not really possible, although you can create a staging tablespace etc.

However in 10g, use the following simple command and it works a treat.

ALTER TABLESPACE "old tablespace name " RENAME TO "new tablespace name";

oracle managed files

Refer to the following link for further information.

http://download.oracle.com/docs/cd/B14117_01/server.101/b10739/omf.htm

To add temp files, do the following:

alter system set db_create_file_dest='...' scope = both;
create temporary tablespace temp_01 tempfile size 1000m;

Renaming ORACLE datafiles

1.Take the tablespace where the datafile resides offline.
i.e.: alter tablespace ... offline;

2. Rename the datafile (at the OS level)
i.e.: mv old_file new_file

3. Rename the datafile at the database level
i.e.: alter database rename file 'old_file' to 'new_file';

4. Bring the tablesace online.
i.e.: alter tablepsace ... online

Finding the time of the last transaction

Use the following to find when the last transaction occured for a particular session.

to_char(sysdate - (last_call_et) / 86400, 'dd-mon-yyyy HH24:MI:SS')

last_call_et is found in v$session. 86, 400 refers to the number of seconds in a given day.

Therefore if the last_call_etl gives a time of 1000, this equates to 0.0116 of a day, then by including sysdate in a function gives you the date of the last activity by the session.

A value of '0', of course means that this session is working away.

ORA-1031

This error is caused for numerous permission reasons.
Below is an explanation of some of these problems and are they can be resolved.

1. Selecting a view when underlying tables are owned by an alternative schema.

The problem is the underlying tables havn't been given the correct privs to the user that owns the view. The grants for these underlying tables need to be given to owner of the view with the grant option.

ie: GRANT SELECT ON "underlying table" TO "view owner" WITH GRANT OPTION