Brinsmead Data Services

Insert on a table reports: ORA-01536.

The following query will report dependent objects which may be causing quotas to be exceeded.

select /*+ rule */ distinct owner, object_type from dba_objects where OBJECT_NAME
in (select distinct NAME from dba_dependencies where REFERENCED_NAME
in (select table_name from dba_tables where tablespace_name = 'ZZTSPACE'))
order by owner, object_type;

Append date and time to a filename in DOS

Sometimes its necessary to append a date and time to a file after processing it in a script. The %date% and %time% variables hold the date and time respectively and can be manipulated. e.g.

ren myfile.txt myfile.txt.%date:~10,4%%date:~7,2%%date:~4,2%%time:~0,2%%time:~3,2%%time:~6,2%

Create standby database script

#!/bin/sh

# ora_create_standby.sh
# Script to create a standby database
# Howard Brinsmead
#
# Assumptions:
# Oracle is installed on the destination server
# TNS entry for the standby database
# TNS entry for the myprod database on the destination server
# Listener configured on the destination server
# Mount points exists on the destination server
#    /data
#    /redoa
#    /redob
#
# SSH configured to login without a password
#    ssh-keygen -t dsa
#    cat .ssh/id_dsa.pub | ssh  "umask 077; test -d .ssh || mkdir .ssh ; cat >> .ssh/authorized_keys"

DEST=$1
if [ 'x'$DEST == 'x' ]
then
        DEFAULT=tst-ora-00
        printf "Enter new standby server: [$DEFAULT] "; read DEST
        DEST=${DEST:-$DEFAULT}
fi

ORACLE_SID=myprod
ORACLE_HOME=/opt/oracle/OraHome1
export ORACLE_SID ORACLE_HOME DEST
touch /tmp/ora_create_standby.tmp

#1. Do the backup plus archive logs required for the restore

echo ..Backing up the database, control file and archive logs
rman target / </tmp/start.sql; ${ORACLE_HOME}/bin/sqlplus "/ as sysdba" @/tmp/start.sql'
ssh ${DEST} 'ORACLE_SID=myprod; ORACLE_HOME=/opt/oracle/OraHome1; export ORACLE_SID ORACLE_HOME; ${ORACLE_HOME}/bin/lsnrctl stop; ${ORACLE_HOME}/bin/lsnrctl start'

#4. Create the standby

echo .. Creating the new standby database
rman target / auxiliary sys/mypassword@standby <
					

Do you know the website number?

http://1249717604/

Useful SQL Plus formats

set echo off — suppress showing sql in result set
set feedback off — eliminate row count message
set linesize 100 — make line long enough to hold data
set pagesize 0 — suppress headings and page breaks
set sqlprompt ” — eliminate SQL*Plus prompt from output
set trimspool on — eliminate trailing blanks
set termout off — suppress output of sql commands

Oracle TNS less connections

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 – Production on Sat Sep 15 23:04:00 2007

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

SQL> connect  scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=testsrv)(Port=1521))(CONNECT_DATA=(SID=TEST)))
Connected.
SQL>

If you think about above connection, it is more like specifying complete address in the connect string as you have in the tnsnames.ora file.

For 10g, you can use following method as well. This method does not work for 8i or 9i databases.

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 – Production on Sat Sep 15 23:04:00 2007

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

SQL> scott/tiger@testsrv:1521/TEST
Connected.
SQL>

Adding an Oracle user – quick summary

SQL> create user bob identified by password default tablespace users temporary tablespace temp

User created.

SQL> grant select any table to bob;

Grant succeeded.

SQL> grant create table to bob;

Grant succeeded.

SQL> grant create session to bob;

Grant succeeded.

SQL> grant create view to bob;

Grant succeeded.

SQL> grant create snapshot to bob;

Grant succeeded.

SQL> grant unlimited tablespace to bob;

Grant succeeded.

SQL> grant create sequence to bob;

Grant succeeded.

SQL> grant create trigger to bob;

Grant succeeded.

Oracle tracing summary

Create plan table
@?/rdbms/admin/utlxplan.sql

Start Trace
1. Enable trace at instance level

Put the following line in init.ora. It will enable trace for all sessions and the background
processes

sql_trace = TRUE

to disable trace:

sql_trace = FALSE

- or -

to enable tracing without restarting database run the following command in sqlplus

SQLPLUS> ALTER SYSTEM SET trace_enabled = TRUE;

to stop trace run:

SQLPLUS> ALTER SYSTEM SET trace_enabled = FALSE;

2. Enable trace at session level

to start trace:

ALTER SESSION SET sql_trace = TRUE;

to stop trace:

ALTER SESSION SET sql_trace = FALSE;

- or -

EXECUTE dbms_session.set_sql_trace (TRUE);
EXECUTE dbms_session.set_sql_trace (FALSE);

- or -

EXECUTE dbms_support.start_trace;
EXECUTE dbms_support.stop_trace;

3. Enable trace in another session

Find out SID and SERIAL# from v$session. For example:

SELECT * FROM v$session WHERE osuser = OSUSER;

to start trace:

EXECUTE dbms_support.start_trace_in_session (SID, SERIAL#);

to stop trace:

EXECUTE dbms_support.stop_trace_in_session (SID, SERIAL#);

- or -

EXECUTE dbms_system.set_sql_trace_in_session (SID, SERIAL#, TRUE);
EXECUTE dbms_system.set_sql_trace_in_session (SID, SERIAL#, FALSE);

- or -

alter session set events ‘10046 trace name context forever, level 8′;      (1,4,8 or 12)
alter session set events ‘10046 trace name context off’ ;

-or systemwide –
alter system set events ‘10046 trace name context forever, level 8′;      (1,4,8 or 12)
alter system set events ‘10046 trace name context off’ ;

Standby database creation summary

On Primary

rman target /
configure channel device type disk format ‘/data/backup/ora_df%t_s%s_s%p’;
backup database include current controlfile for standby;
sql “alter system archive log current”;
backup filesperset 10 archivelog all delete input;
exit;

scp -Cp /data/backup/ora* <standby>:/data/backup

On Standby

(Fix init.ora if necessary)
sqlplus “/ as sysdba”
startup nomount
!lsnrctl start
exit;

On Primary

rman target / auxiliary sys/password@standby
duplicate target database for standby dorecover nofilenamecheck;
exit;

ssh without login

1) Generate private/public key into .ssh

ssh-keygen -t dsa

2) copy public key to destn

cat .ssh/id_dsa.pub | ssh <destn> “umask 077; test -d .ssh || mkdir .ssh ; cat >> .ssh/authorized_keys”