Oracle DBA Daily Experiences

One can check syntax for RMAN commands without running the RMAN.

Example: Checking syntax of commands on the command line.
$ rman checksyntax

Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> backup database;

The command has no syntax errors

RMAN> exit

Example: Checking syntax of commands on the command script.
$ cat backup.txt
connect target /
connect catalog rmancatalog/rmancatlog@catalog

run {
backup database;
}

$ rman checksyntax @backup.txt

Recovery Manager: Release 10.2.0.1.0 – Production on Sun Nov 30 09:31:51 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target *
2> connect catalog *
3>
4> run {
5> backup database;
6> }
7>
The cmdfile has no syntax errors

Recovery Manager complete.

– compile public synonym that are invalid
Select ‘alter public synonym ‘||object_name||’ compile;’
From dba_objects
Where status <> ‘VALID’
And owner = ‘PUBLIC’
And object_type = ‘SYNONYM’;

– drop public synonym that are invalid
Select ‘drop  public synonym ‘||object_name||’;’
From dba_objects
Where status <> ‘VALID’
And owner = ‘PUBLIC’
And object_type = ‘SYNONYM’;

Using the package DBMS_SCHEDULER one can enable/disable jobs.

To disable job: This disables the job from running
SQL> exec dbms_scheduler.disable(‘GATHER_STATS_JOB’);

PL/SQL procedure successfully completed.

– check job status
SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = ‘GATHER_STATS_JOB’;

JOB_NAME ENABL
————————- —–
GATHER_STATS_JOB FALSE

To enable job:
SQL> exec dbms_scheduler.enable(‘GATHER_STATS_JOB’);

PL/SQL procedure successfully completed.

– check job status
SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = ‘GATHER_STATS_JOB’;

JOB_NAME ENABL
————————- —–
GATHER_STATS_JOB TRUE

In 10g one can find the jobs that are currently running by querying the following view

SELECT job_name, session_id, running_instance, elapsed_time, cpu_used FROM dba_scheduler_running_jobs;

Also one can use the following view to find the history details of job that has run.

SELECT job_name, log_date, status, actual_start_date, run_duration, cpu_used FROM dba_scheduler_job_run_details;

To find the jobs that haven’t succeeded
SELECT job_name, log_date, status, actual_start_date, run_duration, cpu_used FROM dba_scheduler_job_run_details where status ‘SUCCEEDED’;

– One can use the following query to find the name of the trace filename

– To find the name of the current session
select instance_name || ‘_ora_’ || spid || ‘.trc’ filename
from v$process p, v$session s, v$instance
where p.addr = s.paddr
and s.sid = (select sid from v$mystat where rownum = 1);

FILENAME
——————————
orcl_ora_1360.trc

– To find the filename for another known SID
select instance_name || ‘_ora_’ || spid || ‘.trc’ filename
from v$process p, v$session s, v$instance
where p.addr = s.paddr
and s.sid = 170;

FILENAME
——————————
orcl_ora_2552.trc

Using one of the following you can find your session id (SID).

SQL> SELECT sid FROM v$mystat WHERE rownum = 1;

SID
———-
145

SQL> SELECT sid FROM V$SESSION WHERE audsid = userenv(‘sessionid’);

SID
———-
145

SQL> SELECT sid FROM V$SESSION WHERE audsid = SYS_CONTEXT(‘userenv’,'sessionid’);

SID
———-
145

– Set default tablespace for all users, so when users are created without specifying default tablespace it will be user’s default tablespace
SQL> ALTER DATABASE DEFAULT TABLESPACE abc;

Database altered.

– To find the default tablespace name for the database is
SQL> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = ‘DEFAULT_PERMANENT_TABLESPACE’;

PROPERTY_VALUE
——————
ABC

Once when default tablespace is set for the database it can’t be dropped until it’s changed to a different tablespace otherwise one will get ORA-12919 when trying to drop the tablespace
SQL> drop tablespace abc including contents and datafiles;
drop tablespace abc including contents and datafiles
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace

So changing the default tablespace for the database will change default tablespace for users who have that tablespace as default tablespace. The following demonstrates what happens when default tablespace is changed for the database.

– set default tablespace to be users
SQL> ALTER DATABASE DEFAULT TABLESPACE users;

Database altered.

– create user with tablespace users as default
SQL> create user test1 identified by test1 default tablespace users;

User created.

– change default tablespace for the database
SQL> ALTER DATABASE DEFAULT TABLESPACE abc;

Database altered.

– it changes default tablespace for user TEST1
SQL> select default_tablespace from dba_users where username = ‘TEST1′;

DEFAULT_TABLESPACE
——————————
ABC

By default if default tablespace is not specified the default tablespace is set to SYSTEM, so you can specify the default tablespace when creating the database (For example: CREATE DATABASE … DEFAULT TABLESPACE abc…)