GUI Pain

You guessed it, I not a big fan of GUI’s. Now before you run off on a tangent, I will agree that they have their place and can be very valuable at times. At the same time I think that if you can’t function at the command-line on the console then you got problems. It is important to understand what the GUI is doing in the background.

Let me give you a non-oracle real world example. I have personally experienced younger generation cashiers at connivance stores who were not able to make change because the register was down. They were dependent on the tool and needed it to do the work for them.

An outage situation with a down production system that is costing you dollars by the minute and SLA’s are occurring at an alarming rate… well, that is just not the time to be GUI bound.

GUI’s – use them, benefit from them; just understand how-to with out them.

Anyway, on to the subject at hand:

Earlier today I was trying to install Oracle 11gR1 (11.1.0.6… which I will patch to .7 and of course bring all of the missing CPU’s patches up -to-date) on a Linux server. My GUI pain started when I realized that I could not do this the way I use to do it (via setting the DISPLAY variable, etc.) because we had switched to using FirePass for access to our network.

So here is what I did to get this to work:

• I downloaded and installed a free version of x-mins on my laptop from (www.hotfiles.com).
• I started the ‘X Server Root’ed Full Screen’ option.
• I started putty and under the ‘connection’ tab I clicked on the ‘ssh’ tab and then I clicked on the X11 tab and selected the ‘X11 forwarding’ option and save it for the sever I wanted to do the install on.
• Then I logged onto the server (in this case, linux) and become the oracle user. (Note: for this to work the oracle user cannot be set to ‘/bin/false’)
• Then I entered the following command: ssh –X oracle@localhost
• This then prompted me for the oracle user password. Input the password and then I was back at a prompt as the oracle user.
• I cd’ed to the location of the runinstaller executable and executed it (./runInstaller).
• When I did this I got the following error message:
[oracle@ database]$ ./runInstaller
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB. Actual 3785 MB Passed
Checking swap space: must be greater than 150 MB. Actual 8189 MB Passed
Checking monitor: must be configured to display at least 256 colors
>>> Could not execute auto check for display colors using command /usr/bin/xdpyinfo. Check if the DISPLAY variable is set. Failed <<<<

Some requirement checks failed. You must fulfill these requirements before

continuing with the installation, at which time they will be rechecked.

Continue? (y/n) [n] n

User Selected: No

Exiting Oracle Universal Installer, log for this session can be found at /u01/app/oracle/oraInventory/logs/installActions2009-11-20_09-39-29AM.log

• I just selected the ‘y’ (yes) option and the installer GUI came up in the fullscreen X window and worked like a charm.

When you run a GUI tool on the remote server, it should automatically be relinked to your local desktop. If this is not the case, then verify that the forward X11 is not set to "no" in /etc/ssh/ssh_config on the remote server. This can be accomplished as follows:

become root
root@$ grep ForwardX11 /etc/ssh/ssh_config
# ForwardX11 no
ForwardX11Trusted yes

Well, there you have it. Have a great day!

Thinking Smaller: The Shrink Command.

The question comes up often and is a good one to be asked; how can you reduce the wasted space in an Oracle standard table? There are different approaches that can be used. You could for example use an export and import or datapump process. However, there are easier and more efficient ways to accomplish a reorganization of tables, thereby reclaiming wasted space.

Some would use a CTAS to get the job done. Granted a CTAS is typically a fast solution because of the parallel feature; however, you lose all your grants, ects. And if it was a partitioned table you would lose your partitions using a “Create Table AS” statement as well.

I like using the “alter table” shrink command. The “shrink” command came along in Oracle release 10. You can’t use if for UNDO or Temporary segments. Neither will it work on Clustered tables. Nor tables with LONG datatypes. Tables with Materialized Views will also have problems.

To make this work you have to first issue an “alter table” statement allowing rows to be moved in the table you want to “shrink”. This is because rows normally when created within the base are assigned a permanent rowid that will be unique and will never change during the life of the row. However, as I stated, “normally”. This alter statement will override that normalcy. It also will require a row level lock to accomplish this. Once this is done, then the “alter table shrink” statement can be issued. The “shrink” statement moves the High Watermark. As you would expect, Oracle will require a table lock for this operation. You can also add a “cascade” to the “alter” statement if you want to include dependent objects.

Here is an example of this process:

TABLE_OPTOUT_TMP (table name) 4,309,647,360 (size in bytes)

15:25:55 SQL> alter table OWNER.TABLE_OPTOUT_TMP enable row movement;

Table altered.

Elapsed: 00:00:00.03

15:26:35 SQL> alter table OWNER.TABLE_OPTOUT_TMP shrink space;

Table altered.

Elapsed: 00:00:06.55

WIRELESS_DNC_OPTOUT_TMP 20,971,520

As you can see, we went from 4.3 gbs to 21 mbs in 7 seconds.

Nice… now you give it a try.

How about a list of tables to do this great little exercise on. Glad you asked. You can do a simple query against dba_advisor_findings as follows:

Select distinct message from dba_advisor_findings where message like '%shrink%';

This should return something that looks similar to this:

“Enable row movement of the table OWNER.TABLE_NAME and perform shrink, estimated savings is 21404475 bytes.”

Enjoy.

To Exist or Not to Exist

My how time seems to get away. I have wanted to add to my blog for some time.

The other day I was reviewing the load on the primary RDBMS (Sun E25K: 16 Quad Core Boards; 256gbs Ram; 45tbs useable Disk (RAID5); 15tbs 10R2 Database) and saw a CTAS that had been churning for over 4.5 hours. Here is what the CTAS looked like:

create table newtab

as select *

from tab1 c1

where exists (select 'x' from tab2 c2 where c1.field1 = c2.field2);

Table tab1 has 38,835,141 records

Table tab2 has 6,412,588 records

I killed off that create statement and rewrote it as follows:

create table sam_newtab

nologging

parallel(degree 6)

as

select *

from owner.tab1

where field1 in (select field1 from owner.tab2);

This new CTAS took 3 minutes and 5 seconds to complete; much better if I say so myself.

The reason this ran so much faster was because an “IN” subquery is only executed once, while an “EXISTS” subquery is executed once per row of the parent query. If there was an index involved on the subquery table, then and “EXISTS” would be able to take advantage of it. However, there were no indexes on these two tables and therefore negated any advantages of using an “EXISTS”.

ORACLE: Unpredictable Query Results and Core Dumps.

I ran in to this issue on our 10.0.2.1 instance. On SunOS 10.

I was getting these error messages:
ORA-00600: internal error code, arguments: [KGHLKREM1], [0x3C1120018], [], [],
[], [], [], []
Wed Jul 5 10:43:35 2006
Errors in file /u01/app/oracle/admin/gedb/udump/gedb_ora_12927.trc:
ORA-07445: exception encountered: core dump [kgscDump()+232] [SIGSEGV] [Address
not mapped to object] [0x000000010] [] []
ORA-00600: internal error code, arguments: [KGHLKREM1], [0x3C1120018], [], [],
[], [], [], []

This was causing the same query (using PQ's) when execute over and over to return different results.

The core dumps were constanly filling up the /var directory.

In Oracle the heapdump shows that the problem resides in a chunk called qesblFilter_seg which is used for "bloom filter"
functionality. These issues are being caused by Bug 4927717 and Bug 5076183.
.
The "bloom filter" functionality is a new feature in 10 R2 for PQ related operations.

I have set the following parameter on hulk in the init.ora file as well as “altering the system” so that it is now in effect for the instance.
The following parameter turns off the “bloom filter" functionality.

_bloom_filter_enabled=FALSE

There might be a small performance impact on PQ operations, however, it should be minor, as turning this off causes it to simply revert back to the 10 R1 behavior. There are several other bloom filter related bugs on 10.2.0.1 as well. Turning this off will also prevent those issues from occurring as well.

There is no patch for these bugs at this time.

Note: I had to add double quotes around the param. since it is a hidden param. to get it to work with the alter system statement:

1* alter system set "_bloom_filter_enabled"=FALSE
10:59:05 SQL> /

System altered.
Elapsed: 00:00:00.02

Including Datafiles

Here is a nice little feature that works in Oracle 10g.

drop tablespace including contents and datafiles.

Oh I love that; all is cleaned up in one easy statement.

Exporting Made A Little Easier

Even though exporting a several tables in a series may not necessarily be a daunting task, it sure can be a tedious one. Therefore the creation of the following script.

A buddy of mine and myself combined two different pieces of our own code to come up with this nice little export script.

The on-demand export script allows you to create a flat file called ondemand.lst and list within it the tables you want to export in the following manner:

owner.tablename
owner.tablename
etc.

The script will loop through this lst file until all entries have been exported.

As an added value, the table being exported will be gzip’ed in memory before being written to disk.

Here is the code for the ondemand_exp.sh script:
Note: Remember to change the

#!/bin/ksh
. /home/sfulke/.profile

curdt=`date +"%Y-%m-%d_%H:%M"`
clear

################################################################
# MODULE: ondemand_exp.sh (ksh)
# DESCRIPTION: Allows user to export tables by listing them in file . Output is
# compressed in memory via gzip.
# PURPOSE: To increase user options when exporting Oracle tables.
# Example: ./ondemand_exp.sh
#Disclaimer: Use is at your sole risk. All info is provided "as is", without any warranty, # whether express or implied, of its accuracy, completeness.
# CHANGE HISTORY:
# Date Who Change Description
# ------------- --------------------- ----------------------------
# 08-NOV-2004 Tony Bryant (abryan) Initial Creation
# 08-NOV-2004 Samual C. Fulkerson Export code supplied
################################################################

exp_log='’
exp_fil=''
exp_cod=''

filename=$exp_cod/ondemand.lst
read_file_line=`wc -l < $filename`
i=1
while [ "$i" -le "$read_file_line" ]
do
table_name=`cat $filename | head -$i | tail -1`

# remove perviously created named pipe
rm $exp_cod/.ondemand.exp.pipe
# create a named pipe
mknod $exp_cod/.ondemand.exp.pipe p
# read the pipe - output to zip file in the background
gzip < $exp_cod/.ondemand.exp.pipe > $exp_fil/exp_$table_name.$curdt.dmp.gz &
# feed the pipe
command="exp userid= file=$exp_cod/.ondemand.exp.pipe log=$exp_log/exp_$table_name._$curdt.log grants=Y index
es=Y rows=Y constraints=Y direct=Y compress=Y tables=$table_name"
$command
wait
i=`expr $i + 1`
done

exit

You can add a mailx statement to email you upon creation it you like. For exporting a large table list you might even wan to add an error checking piece for grep’ing through the log files and emailing you the results to simplify the task somewhat.

Hiding Passwords from Unix

When you execute a sqlplus session from a unix command line as such (sqlplus /) you can see the user name and password by doing a simple ps –ef from the command line. This of course is a serious security violation. One way to avoid this is to allow sqlplus to prompt you for the password.

If you are executing a script (sqlplus / @scriptname.sql) you will be able to do a ps –ef and see it there as well. To avoid this you can imbed a connect string (connect /) into the first line of the sql script you are executing(sqlplus @scritpname.sql).

Another way to do this would be using an OS authenticated user account to execute the sql script (sqlplus / @scriptname.sql).

However, since we use SAS and since SAS does not play well with Oracle’s OS Authentication and requires a username/password you could try it this way:

echo password |sqlplus username @scriptname.sql

Thanks

FlashBacks (UnDroping A Table)

It is a beautiful day outside, the sun is shining and a cool breeze is gently blowing… and then the phone rings. “Opps, I dropped a table! I dropped the big, I need it now to complete the production process table!”

This table was 611 gbs in size. The process that creates it takes many hours (36) to complete as there are many steps and joins that make it up.

So as not to keep you in suspense, I used the flashback feature of Oracle 10R2 to recover it from the “recycle bin”. It took less than 1 second to accomplish the recovery.

Note: This is Oracle 10gR2 (10.2.0.1) on SunOS 10.
This base is not running in archive log mode.

Here is the syntax from an example:

Note: Don't use this next line unless you really want to empty your recycle bin!
11:48:07 SQL> purge recyclebin;
Recyclebin purged.

11:48:21 SQL> show recyclebin
11:48:29 SQL>

11:48:49 SQL> create table test1
11:48:59 2 as
11:49:00 3 select * from dba_tables;
Table created.

11:49:07 SQL> drop table test1;
Table dropped.

11:49:23 SQL> show recyclebin
ORIGNAME_PLUS_SHOW_RECYC OBJECTNAME_PLUS_SHOW_RECYC
-------------------------------- ------------------------------
OBJTYPE_PLUS_SHOW_RECYC DROPTIME_PLUS_SHOW_
------------------------- -------------------
TEST1 BIN$FX3qmRLpXRfgRAADug+/iA==$0
TABLE 2006-06-05:11:49:23

11:49:31 SQL> flashback table test1 to before drop;
Flashback complete.

11:51:47 SQL> show recyclebin;

11:52:34 SQL> select count(1) from test1;
COUNT(1)
--------------------
1,970

It is as simple as that.

Now if you do not want to have a table go to the recycle bin when you drop a table… then:

11:54:10 SQL> drop table test1 purge;

Table dropped.

Elapsed: 00:00:00.82
11:54:30 SQL> show recyclebin;

To purge a user recycle bin:
11:54:32 SQL> purge recyclebin;
Whatever user you are logged in as at the time of this statement execution will have their recycle bin objects purged.

If you want to purge all users recycle bins:
11:55:01:17 SQL> purge dba_recyclebin; (for 9i and 10R1)

11:55:12:22 SQL> connect sys/ as sysdba
Connected
11:55:12:83 SQL> purge dba_recyclebin; (for 10gR2)

Thanks

Patching from 10.2.0.1 to 10.2.0.2

Today’s task is to patch from .1 to .2 on our SunOS(10) E25K, 160 gbs of ram, 20 dual core CPU’s and 18 terabytes of useable RAID 5 disks.

I would highly recommend that you do a metalink (http://metalink.oracle.com) search for: <10.2.0.2 Patch Set - Known Issues> before you get to far into this. Consider, how this search id’s Note 359415.1. This note contains important additional information for the SunOS I am dealing with. It lets me know that there is an additional OUI (Oracle Universal Installer) patch that must be installed post 10.2.0.2 and pre any other patchs (i.e. 1offs, .3, etc.). This additional patch is to correct bug 5117016. This bug causes the OUI to install the libserver10.a library into $ORACLE_HOME/rdbms/lib
instead of into $ORACLE_HOME/lib. This in-turn can cause subsequent problems when applying patches on top of 10.2.0.2. Therefore this is a Mandatory Patch and must be applied immediately after completing the installation of 10.2.0.2

I downloaded the patch set from metalink (p4547817_10202_SOLARIS64.zip) aprox. 788 mbs in size and unzip’ed it using Sun’s unzip feature.

I pointed my web browser to the README.html file that came with the patch set.

Validated that the cold backup (oracle was down during the backup) completed successfully.

I made sure that the shared_pool_size and java_pool_size were at least 150M or larger in the init.ora file.

I then shutdown the base: sqlplus “/ as sysdba” from the oracle Unix user and then by issuing a . I also shutdown the lsnr. via command.

Next I made of copy of the ../Disk1/response/patchset.rsp file in the ../Disk1/ directory to install the patch set noninteractively. And of course I edited the variables that required changing in the patchset.rsp file.
 A couple of “got-u’s” to watch for:
 One: ensure that the ORACLE_HOME and ORACLE_SID values are set in your environment.
 In the patchset.rsp template ensure you set the oracle home to your current oracle home value. For example: ORACLE_HOME="/u01/app/oracle/product/10.2.0"
 Ensure that you set the oracle home name value as follows: ORACLE_HOME_NAME="OraDb10g_home1".

Then from the ../Disk1/ directory I executed the following on the command line:
./runInstaller -silent –responseFile ../Disk1/patchset.rsp

Then executed the $ORACLE_HOME/roo.sh.

Then since this is a single-instance database I logged into sqlplus as sysdba and did a STARTUP MOUNT.

Then I entered the following SQL*PLUS commands:
1. SQL> STARTUP UPGRADE
2. SQL> SPOOL patch.log
3. SQL> start $ORACLE_HOME/rdbms/admin/catupgrd.sql
4. SQL> SPOOL OFF
5. SQL> SHUTDOWN IMMEDIATE
6. SQL> STARTUP
7. SQL> start $ORACLE_HOME/rdbms/admin/utlrp.sql

The catupgrd.sql script reexectues your catalog and catproc sql scripts. The utlrp.sql script recompiles all PL/SQL invalid packages now instead of when the packages are accessed for the first time.

Now I can restart the lsnr and turn the user loose again; only now on a 10.2.0.2 instance!

GUI, Not Really (Scripts)

I am not a strong proponent for GUI’s. I am not opposed to them exactly; I just think you should know how to operate at the command-line level; then you can use a GUI if you so choose. With that typed; here are some of the scripts I use regularly instead of GUI’s:

Remember the old TOP tool in OEM? Basically this script produces similar output for the most part given you session information.

"top.sql" 93 lines, 2369 characters
-----------------------------------------------------------------
-- Author: Sam Fulkerson --
-- Date : 5/16/1999 --
-- Purpose: Display Oracle session information. --
-- Example: sqlplus / @top.sql --
-- Disclaimer: Use is at your sole risk. All info is provided --
-- "as -is", without any warranty, whether express --
-- or implied, of its accuracy, completeness. --
-----------------------------------------------------------------

create table toptab
as
select sid,sum(value) as memory
from v$sesstat
group by sid
/
set pagesize 132 linesize 2048 trimspool on recsep off space 2 wrap off
column username format a10
column sid format 999999
column serial# format 9999999
column osuser format a10
column memory format 999999999999
column command format a12
column status format a8
column machine format a15
column program format a25
spool top.log
select username
,v$session.sid
,serial#
,osuser
,memory
,status
,decode(command ,1, 'Create Table'
,2, 'Insert'
,3, 'Select'
,4, 'Create Cluster'
,5, 'Alter Cluster'
,6, 'Update'
,7, 'Delete'
,8, 'Drop'
,9, 'Create Index'
,10, 'Drop Index'
,11, 'Alter Index'
,12, 'Drop Table'
,13, '---'
,14, '---'
,15, 'Alter Table'
,16, '---'
,17, 'Grant'
,18, 'Revoke'
,19, 'Create Synonym'
,20, 'Drop Synonym'
,21, 'Create View'
,22, 'Drop View'
,23, '---'
,24, '---'
,25, '---'
,26, 'Lock Table'
,27, 'No Operation'
,28, 'Rename'
,29, 'Comment'
,30, 'Audit'
,31, 'Noaudit'
,32, 'Create External Database'
,33, 'Drop External Database'
,34, 'Create Database'
,35, 'Alter Database'
,36, 'Create Rollback Segment'
,37, 'Alter Rollback Segment'
,38, 'Drop Rollback Segment'
,39, 'Create Tablespace'
,40, 'Ater Tablespace'
,41, 'Drop Tablespace'
,42, 'Alter Session'
,43, 'Alter User'
,44, 'Commit'
,45, 'Rollback'
,46, 'Savepoint'
, 'Unknown') as command
,program
,machine
from v$session, toptab
where v$session.sid = toptab.sid
and v$session.sid is not null
order by status, memory desc, username, osuser
/
spool off
set pagesize 0 feedback off
select ' '
from dual
/
select ' '
from dual
/
select ' NOTE: Alter System Kill Session''SID,Serial#'';' from dual
/
drop table toptab;
set pagesize 23 linesize 80 trimspool off space 2 feedback on wrap on
clear columns


This little script allows you to quickly display all of the objects in tablespace and their size:

"segment.sql" 20 lines, 867 characters
-----------------------------------------------------------------
-- Author: Sam Fulkerson --
-- Date : 2/10/2006 --
-- Purpose: Display all segments for a given tablespace. --
-- Parameter: Tablespace Name (Upper, Lower, Mixed Case OK --
-- Example: sqlplus / @segment.sql tablespace_namne --
-- Disclaimer: Use is at your sole risk. All info is provided --
-- "as -is", without any warranty, whether express --
-- or implied, of its accuracy, completeness. --
-----------------------------------------------------------------

set numf 999,999,999,999,999
column segment_name format a32

select segment_name,sum(bytes)
from dba_segments
where tablespace_name=upper('&tspacename')
group by segment_name
order by sum(bytes) asc
/

This script will allow you to do a string search of the sql area.

"tsql1.sql" 72 lines, 2795 characters
-----------------------------------------------------------------
-- Author: Sam Fulkerson --
-- Date : 1/12/2006 --
-- Purpose: Display information about sql currently in the --
-- sql area. --
-- Parameter: String (i.e. insert) Param. is not case senstive --
-- Example: sqlplus / @tsql1.sql string --
-- Disclaimer: Use is at your sole risk. All info is provided --
-- "as -is", without any warranty, whether express --
-- or implied, of its accuracy, completeness. --
-----------------------------------------------------------------


set linesize 132 pagesize 132
column username format a8
column osuser format a6
column command format a6
column sid format 9999
column serial# format 99999
select a.username,a.osuser,a.sid,a.SERIAL#,substr(sql_text,1,85)
,decode(command ,1, 'CrTab'
,2, 'Ins'
,3, 'Sel'
,4, 'CrClus'
,5, 'AltClus'
,6, 'Up'
,7, 'Del'
,8, 'Drop'
,9, 'CrInd'
,10, 'DrInd'
,11, 'AltInd'
,12, 'DrTab'
,13, '---'
,14, '---'
,15, 'AltTab'
,16, '---'
,17, 'Grant'
,18, 'Revoke'
,19, 'CrSyn'
,20, 'DrSyn'
,21, 'CrVi'
,22, 'DrVi'
,23, '---'
,24, '---'
,25, '---'
,26, 'LkTab'
,27, 'NoOper'
,28, 'Rena'
,29, 'Com'
,30, 'Aud'
,31, 'Noaud'
,32, 'CrExtDb'
,33, 'DrExtDb'
,34, 'CrDb'
,35, 'AltDb'
,36, 'CrRolbSeg'
,37, 'AltRolbSeg'
,38, 'DrRolbSeg'
,39, 'CrTsp'
,40, 'AltTsp'
,41, 'DrTsp'
,42, 'AltSes'
,43, 'AltUsr'
,44, 'Commit'
,45, 'Rolbk'
,46, 'Savpo'
, 'Unknown') as command
from v$session a,v$sqlarea b
where upper(b.sql_text) like upper('%&searchstring.%')
and b.address = a.sql_address
order by a.username,a.osuser,b.sql_text,a.sid,a.serial#
/


This script will display detail information about all the tablespaces:

"tsspace.sql" 35 lines, 1522 characters
-----------------------------------------------------------------
-- Author: Sam Fulkerson --
-- Date : 10/06/2004 --
-- Purpose: Display usage information about tablespaces. --
-- Example: sqlplus / @tsspace.sql --
-- Disclaimer: Use is at your sole risk. All info is provided --
-- "as -is", without any warranty, whether express --
-- or implied, of its accuracy, completeness. --
-----------------------------------------------------------------


clear columns
clear breaks
set linesize 132 trimspool on pagesize 66

column total_bytes format 999,999,999,999,999
column bytes_free format 999,999,999,999,999
column bytes_used format 999,999,999,999,999
column largest_free format 999,999,999,999,999
column bytes format 999,999,999,999,999
column tablespace_name format a28

spool tsspace.log

select a.TABLESPACE_NAME,a.BYTES total_bytes,a.BYTES - b.BYTES bytes_used,b.BYTES bytes_free
,b.largest largest_free,round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from (select TABLESPACE_NAME,sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME) a, (select TABLESPACE_NAME,sum(BYTES) BYTES,max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME) b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
order by ((a.BYTES-b.BYTES)/a.BYTES) desc;

spool off


This script will figure out and display how large a table can be in MB’s for a given tablespace based on the extent size feed into the script:

"./nexte.sql" 12 lines, 678 characters
-----------------------------------------------------------------
-- Author: Sam Fulkerson --
-- Date : 4/28/2005 --
-- Purpose: Used to figure out the largest table that can be --
-- created in a tablespace based on an extent size --
-- Parameters: A: Extent_Size (in MB's) B: Tablespace_Name --
-- Example: sqlplus / @nexte.sql 1 tablespace_name --
-----------------------------------------------------------------

select ltrim(to_char(sum(floor((bytes/1048576)/&&amp;amp;1))*&&1,'999,999,999,999')) as TotMBs
from dba_free_space
where tablespace_name = upper


Here is a nice little script for displaying information about the number of commits, logons, etc.

"tinfo.sql" 11 lines, 520 characters
-----------------------------------------------------------------
-- Author: Sam Fulkerson --
-- Date : 9/15/2004 --
-- Purpose: To display info. such as no. of transactions, --
-- commits, aborts, logons current, cumulative, etc. --
-- Example: sqlplus / @tinfo.sql --
-----------------------------------------------------------------

select value,name
from v$sysstat
where statistic# <7;>
"notdparm.sql" 26 lines, 856 characters
-----------------------------------------------------------------
-- Author: Sam Fulkerson --
-- Date : 9/15/2004 --
-- Purpose: To display non-default init. parm's. --
-- Example: sqlplus / @notdparm.sql --
-----------------------------------------------------------------

spool notdpar.lst
set pagesize 999

ttitle center 'Not Default Init. Parameters' skip 1 -
center '----------------------------' skip 2;
col num format 9999 head 'Parameters'
col name format a45 head 'Name' wrap
col type format 9999 head 'Type'
col value format a45 wrap head 'Value'
col isdefault format a9 head 'Default?'

select name,value
from v$parameter
where isdefault = 'FALSE'
order by name;

ttitle off
spool off

Here is a great little script for producing the ddl required to create an object. The 3 variables must be passed in, in upper case for this to work.

"./getddl.sql" 15 lines, 677 characters
-----------------------------------------------------------------
-- Author: Sam Fulkerson --
-- Date : 8/17/2005 --
-- Purpose: Used to create the ddl for object. --
-- Parameters: A: Object Type (i.e. Table) B: Object Name --
-- C: Object owner (variables must be in upper case --
-- Example: sqlplus / @getddl.sql table table_name owner_name --
-----------------------------------------------------------------

set heading off echo off pagesize 999 long 90000

spool getddl_&&amp;amp;amp;amp;amp;amp;amp;3.&&2.sql
select dbms_metadata.get_ddl('&&amp;amp;amp;amp;amp;amp;amp;1','&&2','&&3')
from dual;
spool off

This script will provide info. on the temporary tablespaces:


"././tmp1.sql" 12 lines, 631 characters------------------------------------------------------------------- Author: Sam Fulkerson ---- Date : 4/26/2006 ---- Purpose: To provide info. on the temporary tablespace. ---- Example: sqlplus / @tmp1.sql -------------------------------------------------------------------
set numf 999,999,999,999,999 linesize 132select tablespace_name, SUM(bytes_used) as Used, SUM(bytes_free) as Free, sum(bytes_used + bytes_free) as Totalfrom v$temp_space_headergroup by tablespace_nameorder by tablespace_name;


This script will display info. about who is using temp:


"././tmp2.sql" 23 lines, 963 characters------------------------------------------------------------------- Author: Sam Fulkerson ---- Date : 4/26/2006 ---- Purpose: To provide info. on who is using temporary ---- tablespace. ---- Parameters: Username-- Example: sqlplus / @tmp2.sql -------------------------------------------------------------------
column tablespace format a8
select a.username, b."USER", b.tablespace, b.contents, b.extents, b.blocksfrom sys.v_$session a, sys.v_$sort_usage bwhere a.saddr = b.session_addr;
select a.osuser, a.process, a.username, a.serial#, sum(b.blocks)*c.value/1024 sort_sizefrom sys.v_$session a, sys.v_$sort_usage b, sys.v_$parameter cwhere a.saddr = b.session_addrand c.name = 'db_block_size'and a.osuser like '&&1'group by a.osuser, a.process, a.username, a.serial#, c.value;

CRON (crontab info.)

From a Unix prompt type the following:

crontab –l (to list out the contents of the crontab.)
crontab –e (to edit the contents of the crontab.)
crontab –v (to verify the crontab.)
crontab –r (to remove the contents of the crontab.) Careful, as an ‘e’ and ‘r’ are next to each other on the keyboard.



Here is a header I put in all my crontab’s:

# Parameter 1: Minute <0-59>
# Parameter 2: Hour <0-23>
# Parameter 3: Day of the Month <1-31>
# Parameter 4: Month <1-12>
# Parameter 5: Day of the Week <0-6 sunday="0">
# Parameter 6: Command
# Place a comma between multiple values
# Place a hyphen to indicate a range
# Place an asterisk to indicate all possible values.
#############################################

Some AIX Stuff

To find out what maintenance level the OS is at type:
oslevel –r

FIND (Unix Command)

The “find” command is a very powerful and useful tool. Here are just a few examples to wet your taste buds with:

The below command will give you a list of all files over a gig in size. Just change the '/u01/' to look at what ever mount point is appropriate.

find /u01/ -size +1048576000c -print

The output can be redirected from standard output to a file by appending the following to the end of the command:

>/directory/path/file_name.txt

If you want to remove all files in a directory and its sub-directories, move to the starting directory and execute the following command:

cd /u01/directory_name/
find . -name '*.txt -mtime +32 -exec rm -f {} ";"

This will remove all *.txt files 32 days or older. A ‘oops’ to watch out for, is that some OS’s such as AIX tend to not always update the timestamp on a file when it is moved into another directory such as one being used for archiving. For example; if you just moved a file into a directory you have identified for archival purposes and the timestamp is say, 64 days old on the file in question and it is not re-stamped during the “mv” command and you run this “find” example, that file will be removed. This would be a bad thing if your backup routine had not executed on that file yet. Just a little something to keep in mind.

Creating a primary key with a parallel degree.

Step 1: (Create a primary key, disabled.)
alter table table_name
add (constraint pk_key_name
primary key (field_name))
disable primary key;

Step 2: (Create an unique index with the same name and columns as the primary key.)
create unique index pk_key_name on table_name(field_name)
tablespace tablespace_name
storage (initial ?M next ?M pctincrease 0)
parallel (degree ?)
nologging;

Step 3: (Now enable the primary key.)
alter table table_name enable primary key;

What do you want to do?

Let’s bring this question into more focus. I am of the opinion that simpler is better. “The more you over tic the plumbing, the easier it is to stop it up.” So from a high level:

If you want Replication, you want Streams.
If you want a Stand-By Database, you want Data Guard.
If you want Fail-Over, you want RAC.

Test Post

This is a test 'post', had this been an actual post, it would have contained useful information.