Default Temporary Tablespace

When I was teaching about temporary tablespace, I forgot about how to know where is the default temporary tablespace that is used in current Oracle database? After surfed arround the digital world, I have found some articles that give me a new knowledge. Here are the practice :

prompt> sqlplus /nolog
SQL> conn / as sysdba
SQL> col property_value format a20
SQL> select property_name, property_value from database_properties;

From above query, we can get all of database properties include default_temp_tablespace. If you want to change the default temporary tablespace, you can take a little steps below (my assumption is your database use OMF):

SQL> conn / as sysdba
SQL> create temporary tablespace temp1 datafile 20M;
SQL> alter database default temporary tablespace temp1;
SQL> select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE';

If you want to know about the temporary status and space information, you can query with this:
select * from v$tempfile;

Another FGA

When I tried to find more new thing about FGA (Fine Grained Audit), I found a simple practice to define a simple auditing based on a condition for a table. Here are the steps:

prompt> sqlplus /nolog
SQL> conn / as sysdba
SQL> execute dbms_fga.add_policy('HR', 'EMPLOYEES', 'EMPDEPT_POLICY', 'department_id > 90');
SQL> analyze table hr.employees compute statistics;
SQL> conn hr/hr
SQL> select first_name from employees where department_id = 20;
SQL> select first_name from employees where department_id = 100;
SQL> select distinct deparment_id from employees;
SQL> conn / as sysdba
SQL> select timestamp#, obj$name, policyname, scn, lsqltext from fga_log$;
SQL> execute dbms_fga.drop_policy('HR', 'EMPLOYEES', 'EMPDEPT_POLICY');