Delivering the WOW! in Customer Service

All too often I find that many businesses just don't focus enough on customer service. If there's one thing that matters (maybe more than all others) then it's customer service. Glory Borgeson, author of "Catapult Your Business to New Heights: Sure-Fire Strategies to Increase Profit" has a great article on customer service and delivering the WOW! to your customer...that feeling the customer gets when they've been amazed, not just your product or service but by the entire experience. It's easy to just do the status quo and get the job done, but delivering WOW! in the work you do can take you or your business to new heights.

Read more on ChangeThis:
http://changethis.com/60.06.DeliveringWow
share on: facebook

A Review of: Lean Software Development - An Agile Toolkit

Lean Software Development describes some of the ways you can take the lean principles of the manufacturing world and apply them to the field software development. In this book Mary and Tom Poppendieck describe how various manufacturers, like Toyota, used lean processes to eliminate waste from their value stream. Even though the lean practices of manufacturing don't always make sense for software development, the Poppendieck's do a great job describing how those processes can still be used with a little bit of modification.

The book focuses on seven core practices including:

Eliminate Waste
Removing anything from your process that does not add direct value to the product or the customer. Writing documentation you know that no one will ever read...waste. Coding extra features in the application that is not immediately needed...waste. Switching tasks, waiting on other teams, constantly creating / working on defects....waste, waste, waste. Get creative...find ways to eliminate the overhead from your processes.

Amplify Learning
Increase feedback by using iterations. Work with the customer often to make sure you're on the right track. Write tests as you development the software (Test Driven Development) to quickly find defects as you create them. Capture useful metrics about the project and make sure the project is headed in the right direction. Shorten the feedback loop.

Decide as Late as Possible
Don't drill down into the detail of the application too quickly. That detail is likely to change as the project progresses, causing rework. Build capacity into your process that allows for change late in the game.

Deliver as Fast as Possible
Again, keep your development and feedback loops short. Design, Implement, Feedback, Improve, Rinse and Repeat. The shorter these cycles are, the less likely it is that the application will get off track.

Empower the Team
Give the team the power to change the process, make it more efficient, to get the job done. Give the team access to the customers. Let the team make their own commitments.

Build Integrity In
The application should have a balance of functionality, usability, reliability, and economy. Think Google...simple, functional, smooth.

See the Whole
Always remember to see the big picture. You should have collective ownership of the code...none of that "well so and so wrote that part of the application so have them fix it". The team is responsible for the code and defects. The application is not a sum of its parts, it's the product of its interactions with the outside world.

Resources:

• Lean Software Development - An Agile Toolkit on Amazon.com
• http://www.poppendieck.com/
• Interview: Mary and Tom Poppendieck on using Lean for Competitive Advantage

share on: facebook

Using Conditional Breakpoints in Eclipse

Some times when developing in Eclipse, you only want the debugger to kick in when a certain condition has been met. For example, you're processing thousands of records in a loop but only want the debugger to pause for a certain record. That's exactly what you can do by setting your Breakpoint Properties.

First, double click on the left hand side of your code editor to set a breakpoint like you normally would. Then, right click the breakpoint and choose "Breakpoint Properties..."

Now, you can tell the breakpoint when to pause the debugger. Click the "Enable Condition" checkbox and then in the textarea define your condition. You can enter some pseudo-java code here and even use cntrl-space for autocomplete. Now click 'OK' and voila! You now have a conditional breakpoint defined. You should see that your breakpoint has a question mark on it (indicating that it is a conditional breakpoint). Your breakpoint will now fire only when that condition is met.
share on: facebook

Working with XML data in an Oracle Database

Introduction
Is your application using XML to store data? With Oracle 9i and above you can store your XML in the database and query it to pull out the pieces of data that you need. You need to make sure that you've installed the XDK and XML capabilities of your Oracle Database before being able to use this XML functionality.

Creating a table with an XMLType datatype
The XMLType datatype (which is really the SYS.XMLType datatype) is basically a CLOB data type but with a some extra functions that allow the data to be made available to SQL. First create a new table like so:

$ sqlplus scott/tiger

SQL> CREATE TABLE mytable
2  (
3   myid  number(10),
4   myxml XMLType
5  );

Table created.

Then insert some XML data into the table.

SQL> insert into mytable values
2  (
3     1,
4     XMLType('<users>
5               <person>
6                <name>Bruce Scott</name>
7                <email>Bruce@SomeWebsite.com</email>
8               </person>
9              </users>'
10           )
11 );
1 row created.

And now we can use SQL to SELECT the data out:

SQL> select extractValue(myxml, '/USERS/PERSON/EMAIL') as XmlEMail
2  from   mytable
3  where  existsNode(myxml, '/USERS/PERSON[NAME="Bruce Scott"]') = 1;

XMLEMAIL
-----------------------------------------------------------------------

Bruce@SomeWebsite.com

It's as easy as that! That's all you need to do to store and retrieve XML from an Oracle Database.

If you have trouble, please post your comments on this web page.
share on: facebook

Searching for Files in UNIX and Linux

Introduction
This article will demonstrate how to use the 'find' and 'locate' commands in UNIX and Linux to search for files.

Searching for files with 'find' and 'locate'
To search for files in UNIX and Linux you can use the 'find' command. The 'find' command will do a real-time search of the filesystem looking for files that you have specified. On Linux and some other systems you can use the 'locate' command to search for files based on the filename. Usually a scheduled job runs an 'updatedb' command which puts every file on the filesystem into a 'database' or 'index' file. Then when you try to search for a file using 'locate' it searches through its indexed copy of file names instead of scanning the entire filesystem. This means that the 'locate' command is very fast while the 'find' command is slower. However the index database that the locate command uses is usually run just once a day so its index can become outdated quickly. The main drawbacks from using the 'find' command is that it's slower since it's done in real-time and can be very disk intensive.

Doing a Basic Search

[troygeek@localhost troygeek]$ find . -name "*.log" -print 2>/dev/null
./logs/error.log
./logs/access.log

In the above example, you'll see I call the 'find' command and pass it a few parameters. First, the . (dot) means do the search in my current directory and the files below. The -name option means I want to do a name pattern search. The "*.log" means look for all the filenames that end in *.log. This piece is what you are searching for. You can change it to look for myfile.txt, *someword*, or any other search string you can think of. The -print option means to print out the path of the files that are found. The 2>/dev/null means take any of the errors you get and throw them away into /dev/null (think of /dev/null as a trash bin).

Searching for all the .log files on all of the filesystems
To search for all of the .log files in the entire computer, you need to change the . (dot) to a / (forward slash) to tell it to look in everything under the root directory. The result would look like this:

[troygeek@localhost troygeek]$ find / -name "*.log" -print 2>/dev/null
/var/log/mysqld.log
/var/log/boot.log
/var/log/prelink.log
/var/log/yum.log
/etc/logrotate.d/vsftpd.log
/usr/share/doc/tux-3.2.12/sample.log
...

Searching through files that contain a specific string
Here's how you can search through a specific directory and below for all the files that contain a certain string. In this example, I'll look in my current directory and below for all the files that contain the word 'bookmarks'. This will display the filename that contains the string and a portion of the file that contains the string you're searching for.

[troygeek@localhost troygeek]$ find . -print|xargs grep -i "bookmarks"
./www/index.html:Please update your bookmarks.

Searching for large files
To search for large files using the 'find' command, just use the -size option and tell it the size in bytes that you're looking for. For example, here's how to search for files that are larger than 10,000 bytes (about 10 KB):

[troygeek@localhost troygeek]$ find . -size +10000 -print
./troygeek_main_final.sql
./troygeek_main_final_noweblog.sql
./logs/access.log
./troygeekLocal2.sql
./mysql-connector-java-3.1.8a.tar.gz

Searching for files that have changed in the last day
To search for all the files that have changed in the last day (or any amount of days really) just use the -mtime option and pass it the amount of days like below:

[troygeek@localhost troygeek]$ find . -mtime -1 -print
./.bash_history
./logs/error.log
./logs/access.log
./webapps/TroyGeek
./webapps/TroyGeek/Theme/ts_header.jsp
./webapps/TroyGeek/Pictures
./webapps/TroyGeek/Pictures/images
./webapps/TroyGeek/Pictures/images/1002839-R2-017-7_jpg.jpg
./webapps/TroyGeek/Pictures/images/1002839-R2-033-15_jpg.jpg
./webapps/TroyGeek/Pictures/images/100_0417_JPG.jpg
./webapps/TroyGeek/Pictures/images/100_0434_JPG.jpg
./webapps/TroyGeek/Pictures/images/100_0444_JPG.jpg
./webapps/TroyGeek/Pictures/images/100_0509_JPG.jpg
./webapps/TroyGeek/Pictures/images/100_0510_JPG.jpg
./webapps/TroyGeek/Pictures/images/100_0511_JPG.jpg

Using 'locate' to search for files.
As mentioned above the 'locate' command is usually installed on Linux systems but may also be on other flavors of UNIX. Here's how to do a simple search for all the .log files.

[troygeek@localhost troygeek]$ locate *.log
/var/log/prelink.log
/var/log/yum.log
/etc/logrotate.d/vsftpd.log
/usr/share/doc/tux-3.2.12/sample.log
/usr/lib/rpm/rpm.log
/usr/local/mysql/src/mysql-4.1.12/innobase/config.log
/usr/local/mysql/src/mysql-4.1.12/config.log

What you don't notice from the text above is that those search results were returned instantly whereas the results from the 'find' command have to search every file one by one which can take a long time.

Doing a case-insensitive search with locate
You may want to ignore case sensitivity when you're doing a search. To do that just use the -i option when doing a search.

[troygeek@localhost troygeek]$ locate -i PASSWD
/etc/passwd
[troygeek@localhost troygeek]$ locate PASSWD
[troygeek@localhost troygeek]$

And that's the basics of searching for files in UNIX and Linux.

If you have any trouble, please post your comments on this web page.
share on: facebook

Statspack 101: Interpreting Your Statspack Report

Introduction
In my last article, I discussed how to install Oracle's Statspack Utility; a free tool for monitoring your Oracle database instance. That article also discussed how to generate a Statspack Report. This article will help you interpret a lot of the information you will find in a statspack report.

The Statspack Header
The beginning of the statspack report shows you some basic information about your instance including the database name, instance name, DB ID, version, host and the start and end times of the snapshots used in your report. Here is an example:

STATSPACK report for

DB Name         DB Id    Instance     Inst Num Release     Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
ORCL          2586436430 ORCL                1 9.2.0.4.0   NO      localhost

 Snap Id     Snap Time      Sessions Curs/Sess Comment
 ------- ------------------ -------- --------- -------------------
Begin Snap:    4873 13-Dec-05 05:00:05      110      37.4
End Snap:    4875 13-Dec-05 07:00:04      651     203.7
Elapsed:              119.98 (mins)

Cache Sizes
The next section, Cache Sizes, shows you some of your instance settings including: Buffer Cache (DB_CACHE_SIZE), Standard Block Size (DB_BLOCK_SIZE), Shared Pool Size (SHARED_POOL_SIZE), and Log Buffer (LOG_BUFFER). These are all instance parameters which you can modify in your spfile/pfile. :

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
         Buffer Cache:     3,008M      Std Block Size:         8K
     Shared Pool Size:     1,920M          Log Buffer:    10,240K

Load Profile
The "Load Profile" section shows you the load on your instance per second and per transaction. You can compare this section between two Statspack Reports to see how the load on your instance is increasing or decreasing over time.

Redo Size & Block Changes Increase: If you see an increase here then more DML statements are taking place (meaning your users are doing more INSERTs, UPDATEs, and DELETEs than before.

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                             ---------------       ---------------
            Redo size:            352,535.71              8,517.66
        Logical reads:            202,403.30              4,890.29
        Block changes:              2,713.47                 65.56
       Physical reads:                 44.22                  1.07
      Physical writes:                 27.46                  0.66
           User calls:                787.32                 19.02
               Parses:                301.40                  7.28
          Hard parses:                  0.05                  0.00
                Sorts:                317.78                  7.68
               Logons:                  0.10                  0.00
             Executes:              2,975.84                 71.90
         Transactions:                 41.39

% Blocks changed per Read:    1.34    Recursive Call %:    87.43
Rollback per transaction %:   27.56       Rows per Sort:     7.22

Instance Efficiency Percentages
The "Instance Efficiency Percentages" section is very useful. It gives you an overview of your instance health. Anytime you make instance parameter changes you should take a look to see if this affects your instance efficiency in any way. Here is a description of some of the fields (Note, as stated in the statspack report, your goal here is to have these percentages be as close to 100% as possible):

Buffer Nowait %: This is the percentage of time that the instance made a call to get a buffer (all buffer types are included here) and that buffer was made available immediately (meaning it didn't have to wait for the buffer...hence "Buffer Nowait").

Buffer Hit %: This means that when a request for a buffer took place, the buffer was available in memory and physical disk I/O did not need to take place.

Library Hit %: If your Library Hit percentage is low it could mean that your shared pool size is too small or that the bind variables are not being used (or at least being used properly).

Execute to Parse %: This is the formula used to get this percentage: round(100*(1-parsevalue/executevalue),2)

So, if you run some SQL and it has to be parsed every time you execute it (because no plan exists for this statement) then your percentage would be 0%. The more times that your SQL statement can reuse an existing plan the higher your Execute to Parse ratio is.

One way to increase your parse ratio is to use bind variables. This allows the same plan to be used for multiple SQL statements. The only thing that changes in the SQL is the parameters used in your statement's WHERE clause. For Java/JDBC Programmers that means using PreparedStatements as opposed to regular Statements.

Parse CPU to Parse Elapsd %: Generally, this is a measure of how available your CPU cycles were for SQL parsing. If this is low, you may see "latch free" as one of your top wait events.

Redo NoWait %: You guessed it...the instance didn't have to wait to use the redo log if this is 100%.

In-memory Sort %: This means the instance could do its sorts in memory as opposed to doing physical I/O...very good. You don't want to be doing your sorts on disk...especially in an OLTP system. Try increasing your SORT_AREA_SIZE or PGA_AGGREGATE_TARGET in your spfile/pfile to see if that helps if your in-memory sorting is not between 95% and 100%.

Soft Parse %: This is an important one...at least for OLTP systems. This means that your SQL is being reused. If this is low (not between 95% and 100%) then make sure that you're using bind variables in the application and that they're being used properly.

Latch Hit %: This should be pretty close to 100%; if it's not then check out what your top wait events are.

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      Buffer Nowait %:  100.00       Redo NoWait %:  100.00
      Buffer  Hit   %:   99.98    In-memory Sort %:  100.00
      Library Hit   %:  100.04        Soft Parse %:   99.98
   Execute to Parse %:   89.87         Latch Hit %:   94.99
Parse CPU to Parse Elapsd %:   75.19     % Non-Parse CPU:   99.46

Top 5 Timed Events (Called "Top 5 Wait Events" in 8i)
This section is crucial in determining what some of the performance drains in your database are. It will actually tell you the amount of time the instance spent waiting. Here are some common reasons for high wait events:

DB file scattered read: This can be seen fairly often. Usually, if this number is high, then it means there are a lot of full tablescans going on. This could be because you need indexes or the indexes you do have are not not being used.

DB file sequential read: This could indicate poor joining orders in your SQL or waiting for writes to 'temp' space. It could mean that a lot of index reads/scans are going on. Depending on the problem it may help to tune PGA_AGGREGATE_TARGET and/or DB_CACHE_SIZE.

CPU Time: This could be completely normal. However, if this is your largest wait event then it could mean that you have some CPU intensive SQL going on. You may want to examine some of the SQL further down in the Statspack report for SQL statements that have large CPU Time.

SQL*Net more data to client: This means the instance is sending a lot of data to the client. You can decrease this time by having the client bring back less data. Maybe the application doesn't need to bring back as much data as it is.

log file sync: A Log File Sync happens each time a commit takes place. If there are a lot of waits in this area then you may want to examine your application to see if you are committing too frequently (or at least more than you need to).

Logfile buffer space: This happens when the instance is writing to the log buffer faster than the log writer process can actually write it to the redo logs. You could try getting faster disks but you may want to first try increasing the size of your redo logs; that could make a big difference (and doesn't cost much).

Logfile switch: This could mean that your committed DML is waiting for a logfile switch to occur. Make sure your filesystem where your archive logs reside are not getting full. Also, the DBWR process may not be fast enough for your system so you could add more DBWR processes or make your redo logs larger so log switches are not needed as much.

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file sequential read                           187,787         906    88.60
SQL*Net more data to client                        49,707          57     5.55
CPU time                                                           54     5.33
log file parallel write                             1,011           2      .22
latch free                                          6,226           2      .16
    -------------------------------------------------------------

The SQL Sections (Buffer Gets, Disk Reads, Executions, and Parse Counts)
The following sections show you the Top SQL (or 'worst performing' SQL) grouped by four sections: Buffer Gets, Disk Reads, Executions, and Parse Counts. You'll want to review the top SQL statements in each of these sections to see if they can be tuned better. These sections are a great way to how many times the SQL is being executed, how much CPU time is being used to execute them, and the total time for the statement to execute.

SQL ordered by Parse Calls for DB: ORCL  Instance: ORCL  Snaps: 4873 -4875
-> End Parse Calls Threshold:      1000

                     % Total
Parse Calls  Executions   Parses  Hash Value
------------ ------------ -------- ----------
144,300      144,300     6.65 4199666855
Module: JDBC Thin Client
select parameter, value from nls_session_parameters

Note: If you take the hash value for the SQL statement, then you run the ORACLE_HOME/rdbms/admin/sprepsql.sql script, and enter the hash value when it prompts you it will pull up the Execution Plan for that SQL statement. Pretty Cool!

Instance Activity Stats
This section may provide some insight into some potential performance problems that were not as easily visible from previous sections in the report. This section is also useful when comparing statspack reports from the same timeframes on different days.

Tablespace and Data File I/O Statistics
These sections help give you some visibility into I/O rolled up to the tablespace level and I/O stats on your data files.

Tablespace IO Stats for DB: ORCL  Instance: ORCL  Snaps: 4873 -4875
->ordered by IOs (Reads + Writes) desc

Tablespace
------------------------------
           Av      Av     Av                    Av        Buffer Av Buf
   Reads Reads/s Rd(ms) Blks/Rd       Writes Writes/s      Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
UNDOTBS
     146       0    5.8     1.0      117,119       16     50,681    1.3
APP1
  19,395       3   10.5     1.0       32,613        5      1,886    2.8
INDEX1
  36,919       5    0.7     6.3          977        0        526    5.0
APP2
   6,969       1   11.7     1.0       13,559        2      2,513    2.5
SYSTEM
  15,056       2    0.8     1.8          360        0         13    3.8

I hope this article helps you increase your ability to interpret Statspack Reports as it is an extremely useful tool for the Oracle DBA.
share on: facebook

Statspack 101: Installing Oracle's Statspack Utility

Introduction
Oracle's Statspack is a free utility that allows you to monitor and report on
performance problems with your Oracle database. In this tutorial we will install
Statspack on an Oracle 9i database.
Creating a Statspack Tablespace
The first step in installing statspack is creating a tablespace for the statspack
objects and data to reside. For our example we will call the tablespace "PERFSTAT"
and make it 500mb in size.

SQL> CREATE TABLESPACE PERFSTAT

2 DATAFILE '/path/to/my/tablespaces/perfstat.dbf' SIZE 500M AUTOEXTEND OFF

3 EXTENT MANAGEMENT LOCAL AUTOALLOCATE

4 LOGGING

5 ONLINE

6 SEGMENT SPACE MANAGEMENT AUTO

7 /

Tablespace created.

Installing Statspack

Next, we're actually going to install Statspack in the database. First you want
to change to the ORACLE_HOME/rdbms/admin/ directory. This directory contains
all the files needed for installing statspack.
$> cd $ORACLE_HOME/rdbms/admin/
Now that we are in the ORACLE_HOME/rdbms/admin/ directory we are going to run
the "spcreate.sql" script from SQLPLUS. This script will create the
statspack user, the statspack tables, and the statspack packages.

$> sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Nov 28 18:49:51 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.4.0 - Production
SQL> @spcreate;

... Installing Required Packages
Package created.


Grant succeeded.


View created.


Package body created.


Package created.


Synonym created.


Grant succeeded.


View created.


Synonym created...


...Synonym created.
... Creating PERFSTAT user ...
Choose the PERFSTAT user's password.
Not specifying a password will result in the installation FAILING
Specify PERFSTAT password

Enter value for perfstat_password: perfstatpass

perfstatpass
PL/SQL procedure successfully completed.


User created....


....Grant succeeded.


Below are the list of online tablespaces in this database.

Decide which tablespace you wish to create the STATSPACK tables

and indexes. This will also be the PERFSTAT user's default tablespace.
Specifying the SYSTEM tablespace will result in the installation FAILING, as using SYSTEM for performance data is not supported.

TABLESPACE_NAME CONTENTS

------------------------------ ---------

DRSYS PERMANEN

PERFSTAT PERMANENT

TEMP TEMPORARY

TOOLS PERMANENT

UNDOTBS UNDO

USERS PERMANENT

XDB PERMANENT
7 rows selected.


Specify PERFSTAT user's default tablespace

Enter value for default_tablespace: perfstat

Using perfstat for the default tablespace
PL/SQL procedure successfully completed.


User altered.


User altered.


Choose the PERFSTAT user's temporary tablespace.
Specifying the SYSTEM tablespace will result in the installation

FAILING, as using SYSTEM for the temporary tablespace is not recommended.
Specify PERFSTAT user's temporary tablespace.

Enter value for temporary_tablespace: temp

Using temp for the temporary tablespace


PL/SQL procedure successfully completed.


User altered.....
Creating Package STATSPACK...
Package created.
No errors.

Creating Package Body STATSPACK...
Package body created.
No errors.
NOTE:

SPCPKG complete. Please check spcpkg.lis for any errors.

Review any messages and make sure you didn't receive any errors.


Execute a Snapshot and Generate a Statspack Report

Now that you have statspack installed, let's take two snapshots and then generate
a Statspack Report. Assuming you are still in the ORACLE_HOME/rdbms/admin/ directory
and logged in as perfstat, run this command twice like so:

SQL> execute statspack.snap
PL/SQL procedure successfully completed.
SQL> execute statspack.snap
PL/SQL procedure successfully completed.
We now have two snapshots to compare in order to create a Statspack Report.
Run the "spreport.sql" script in SQLPLUS to create a Statspack Report:
SQL> @spreport
Current Instance

~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance

----------- ------------ -------- ------------

4184632401 ORCL 1 ORCL

Instances in this Statspack schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host

----------- -------- ------------ ------------ ------------

4184632401 1 ORCL ORCL localhost
Using 4184632401 for database Id

Using 1 for instance number
Completed Snapshots
Snap Snap

Instance DB Name Id Snap Started Level Comment

------------ ------------ ----- ----------------- ----- ----------------------

ORCL ORCL 1 28 Nov 2005 20:00 5

2 28 Nov 2005 20:04 5

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 1

Begin Snapshot Id specified: 1
Enter value for end_snap: 2

End Snapshot Id specified: 2
Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is sp_1_2. To use this name,

press to continue, otherwise enter an alternative.

Enter value for report_name: myStatspackReport.txt
-- a bunch of the report flies by the screen here,

-- but don't worry we saved a copy to look at afterwards.
End of Report
SQL>

Here I saved my Statspack Report under the name "myStatspackReport.txt".
You can exit SQLPLUS and open the file in a text editor to view the report.


Scheduling the Statspack Report
Now that you have statspack up and running, lets schedule it to run at an automatic
interval. Again, assuming you are in the ORACLE_HOME/rdbms/admin/ directory
and logged in as perfstat, run the "spauto.sql" script:

SQL> @spauto
PL/SQL procedure successfully completed.


Job number for automated statistics collection for this instance

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Note that this job number is needed when modifying or removing

the job:
JOBNO

----------

1


Job queue process

~~~~~~~~~~~~~~~~~

Below is the current setting of the job_queue_processes init.ora

parameter - the value for this parameter must be greater

than 0 to use automatic statistics gathering:
NAME TYPE

------------------------------------ --------------------------------

VALUE

------------------------------

job_queue_processes integer

2


Next scheduled run

~~~~~~~~~~~~~~~~~~

The next scheduled run for this job is:
JOB NEXT_DATE NEXT_SEC

---------- --------------- ------------------------

1 28-NOV-05 21:00:00

The "spauto.sql" script, by default, schedules the statspack job
to take a snapshot every hour. If you want a statspack snapshot to be taken
more often than that then make a copy of the spauto.sql file and edit it. You
want to change the section that says:

variable jobno number;

variable instno number;

begin

select instance_number into :instno from v$instance;

dbms_job.submit(:jobno, 'statspack.snap;',

trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);

commit;

end;

/

If you want the statspack to run every 10 minutes then change it to say this:

variable jobno number;

variable instno number;

begin

select instance_number into :instno from v$instance;

dbms_job.submit(:jobno, 'statspack.snap;',

trunc(sysdate+1/144,'MI'), 'trunc(SYSDATE+1/144,''MI'')', TRUE, :instno);

commit;

end;

/

Issue your copy of the spauto.sql script and it will schedule a snapshot to
be taken every 10 minutes.


Check the job_queue_processes parameter
Make sure that you have the job_queue_processes parameter set higher than zero
or you will not be able to use the auto snapshot feature to take statspack snapshots
at a given interval.

SQL> set linesize 150;

SQL> show parameter job_queue_processes
NAME TYPE VALUE

------------------------------------ -------------------------------- -----

job_queue_processes integer 0

Here I don't have job_queue_processes set above zero so I issue a ALTER SYSTEM
command to increase it to 2:

SQL> ALTER SYSTEM SET job_queue_processes = 2 SCOPE=BOTH;
System altered.
SQL> SHOW PARAMETER job_queue_processes
NAME TYPE VALUE

------------------------------------ -------------------------------- -----

job_queue_processes integer 2

How to Uninstall Statspack
To uninstall Statspack from an Oracle database running Oracle 8.1.7 and above,
you just need to run the ORACLE_HOME/rdbms/admin/spdrop.sql script. This will
get rid of the user, tables, and procedures.
To uninstall Statspack from an Oracle database running Oracle 8.1.6, you just
need to run the ORACLE_HOME/rdbms/admin/spdrp.sql
In a future article I hope to cover how to interpret your StatsPack Reports.
share on: facebook

Importing and Exporting data in MySQL

Introduction
In my spare time I like to take photos...lots of photos. I put together a little PHP/MySQL app to host all the photos over a TroyGeek.com (http://www.troygeek.com/). Usually I do my PHP development on my Windows computer and then upload the code to my UNIX web host. I also run a copy of MySQL on my Windows computer and before I start my development I like to get a copy of what my 'production' database looks like and load it into my Mysql database on my Windows machine. This article will describe how to do this using the mysqldump and mysql command line utilities to import and export data to/from a MySQL database.

Setting Your Path
First what I like to do is to put the mysql and mysqldump programs in my command line path so I don't need to use the full path when calling the them. To do this on Windows XP, right click your 'My Computer' icon and go to properties. Under the Advanced tab you should see a button for 'Environment Variables'. Click that button and a new window should pop up showing two menu lists. The top menu is for 'user' environment variables, meaning only the user currently logged on to Windows can use these environment variables and the second menu list is for 'System' environment variables which are variables made available to anyone on the computer.

Under the System environment variables, look for a variable called 'Path'. Highlight that variable and click the 'Edit Variable' button. At the very of end the 'Variable Value' field add ;c:\mysql\bin assuming that c:\mysql is where you have MySQL installed. Note the semi-colon at the beginning...that seperates this statement from the one before it. Now you should be able to call the mysqladmin and mysql tools from a command prompt.

Getting the Data Out With Mysqldump
The main utility used to get data out of mysql is called mysqldump. It's a command line utility that works the same way on Windows, UNIX, Linux, etc. and is fairly easy to use. In the following example the mysqldump utility will take in your username, password, computer name, and dump the DDL and data from your database to a text file. Open up a command prompt (for Windows, go to Start > Run > and type in 'cmd'). Now type in the below statement.

mysqldump -u myUsername -p --host=localhost myDatabase > someTextFile.sql

If you hadn't added c:\mysql\bin to your path you would need to run this command:

c:\mysql\bin\mysqldump -u myUsername -p --host=localhost myDatabase > someTextFile.sql

...assuming c:\mysql is where you have installed MySQL.

In the example above the username to the database was myUsername, the computer that the MySQL database runs on goes after --host (in this case we used localhost though it could be your unix server name) , the database name is myDatabase and we sent all the data we get to a file called sometextFile.sql. If you were to open up the someTextFile.sql file you should see all the CREATE and INSERT statements needed to rebuild your database. You can find more options with the mysqldump command here in the MySQL Manual (http://dev.mysql.com/doc/mysql/en/mysqldump.html)

Putting the Data Back
Now that you've gotten the data out, how do you get it back in? You can actually use the mysql command line utility you would normally use to run queries with. Make sure that any tables you want to create do not already exist. To create the tables and load data back into mysql you would run this command:

mysql -u myUsername -p myDatabase <>

...where someTextFile.sql is the export you took in the previous example. Basically what this does is opens a connnection to mysql and then runs all the commands that are in the .sql file.

And that's about all you need to do to take exports/imports in MySQL!
As always, if you have trouble, please post your comments on this web page.
share on: facebook

How Oracle Searches for Configuration Files

Introduction
Do you have trouble finding which tnsnames.ora or listener.ora is the one on your
computer that Oracle is actually looking at? There's a few places that Oracle
looks for its configuration files (tnsnames.ora, sqlnet.ora, listener.ora, and
oratab).
Where does Oracle search for these files?
Oracle will search for these configuration files in the following locations
in the following order:

1. $TNS_ADMIN
2. /etc (or /var/opt/oracle on Solaris)
3. $ORACLE_HOME/network/admin

The TNS_ADMIN ($TNS_ADMIN in Unix/Linux and %TNS_ADMIN%
in Windows) environment variable allows you to point to any directory you wish
the files to be located at. By default the files get installed to
<ORACLE_HOME>/network/admin/.

share on: facebook

Extracting Oracle DDL from a SQL Command Line

Introduction
Need to extract DDL from a table in an Oracle database but don't have a tool to
do so? Don't fret, you can query Oracle's Metadata API to retrieve the DDL or
even XML on the given object. This works with oracle 9i and above.

Extracting the DDL from the 'DEPT' table in the 'SCOTT' schema
First, you need a schema/object to extract DDL from. For this example, I ran
the 'demo' schema creation script located here: <ORACLE_HOME>/rdbms/admin/utlsampl.sql.
This sets up a few example schemas including the SCOTT schema. I want to now
extract DDL for the SCOTT.DEPT table. You do this using the DBMS_METADATA.GET_DDL()
function. The DBMS_METADATA.GET_DDL() function takes in a few parameters: Object
Type, Name, and Schema. Here is how we extract the DDL for the SCOTT.DEPT table:

SQL> set pagesize 0
SQL> set long 90000
SQL> SELECT dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') FROM dual;

...where 'TABLE' is the type of object, 'DEPT' is the name of the object, and
'SCOTT' is the schema that the object resides in. The above statement would
then return this:

CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"

If you wanted to extract the DDL for an index in the SCOTT schema you would
then replace 'TABLE' with 'INDEX' and 'DEPT' with the name of the index.

Extracting DDL for an entire schema
Now that we know how to extract DDL for a individual object, we'll now look
at how to extract DDL for all the tables in the entire SCOTT schema. You can
do this by combining the dbms_metadata.get_ddl() function with one of the Oracle
System Views like ALL_TABLES. Here is an example of extracting all the DDL for
the tables in the SCOTT schema:

SQL> set pagesize 0
SQL> set long 90000
SQL> SELECT dbms_metadata.get_ddl('TABLE', table_name, 'SCOTT')
2 FROM ALL_TABLES WHERE OWNER = 'SCOTT';
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"

CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"

CREATE TABLE "SCOTT"."BONUS"
( "ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"SAL" NUMBER,
"COMM" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
CREATE TABLE "SCOTT"."SALGRADE"
( "GRADE" NUMBER,
"LOSAL" NUMBER,
"HISAL" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"

Now you have a the DDL for all the tables in the SCOTT schema.

Extracting XML Metadata
If you like, you can also extract XML formatted metadata from objects using
the DBMS_METADATA.GET_XML() function. Here's an example how:

SQL> SELECT dbms_metadata.get_xml('TABLE','DEPT','SCOTT') FROM dual;
<?xml version="1.0"?><ROWSET><ROW>
<TABLE_T>
<VERS_MAJOR>1</VERS_MAJOR>
<VERS_MINOR>0 </VERS_MINOR>
<OBJ_NUM>45084</OBJ_NUM>
<SCHEMA_OBJ>
<OBJ_NUM>45084</OBJ_NUM>
<DATAOBJ_NUM>45084</DATAOBJ_NUM>
<OWNER_NUM>49</OWNER_NUM>
<OWNER_NAME>SCOTT</OWNER_NAME>
<NAME>DEPT</NAME>
<NAMESPACE>1</NAMESPACE>
<TYPE_NUM>2</TYPE_NUM>
<TYPE_NAME>TABLE</TYPE_NAME>
<CTIME>2005-06-12:18:21:30</CTIME>
<MTIME>2005-06-12:18:21:30</MTIME>
<STIME>2005-06-12:18:21:30</STIME>
<STATUS>1</STATUS>
<FLAGS>0</FLAGS>
<SPARE1>6</SPARE1>
<SPARE2>1</SPARE2>
</SCHEMA_OBJ>
<STORAGE>
<FILE_NUM>4</FILE_NUM>
<BLOCK_NUM>11</BLOCK_NUM>
<TYPE_NUM>5</TYPE_NUM>
<TS_NUM>4</TS_NUM>
<BLOCKS>8</BLOCKS>
<EXTENTS>1</EXTENTS>
<INIEXTS>8</INIEXTS>
<MINEXTS>1</MINEXTS>
<MAXEXTS>2147483645</MAXEXTS>
<EXTSIZE>128</EXTSIZE>
<EXTPCT>0</EXTPCT>
<USER_NUM>49</USER_NUM>
<LISTS>1</LISTS>
<GROUPS>1</GROUPS>
<BITMAPRANGES>0</BITMAPRANGES>
<CACHEHINT>0</CACHEHINT>
<SCANHINT>0</SCANHINT>
<HWMINCR>45084</HWMINCR>
<FLAGS>131329</FLAGS>
</STORAGE>
<TS_NAME>USERS</TS_NAME>
<BLOCKSIZE>8192</BLOCKSIZE>
<DATAOBJ_NUM>45084</DATAOBJ_NUM>
<COLS>3</COLS>
<PCT_FREE>10</PCT_FREE>
<PCT_USED>40</PCT_USED>
<INITRANS>1</INITRANS>
<MAXTRANS>255</MAXTRANS>
<FLAGS>1073741825</FLAGS>
<AUDIT_VAL>--------------------------------------</AUDIT_VAL>
<INTCOLS>3</INTCOLS>
<KERNELCOLS>3</KERNELCOLS>
<PROPERTY>536870912</PROPERTY>
<XMLSCHEMACOLS>N</XMLSCHEMACOLS>
<TRIGFLAG>0</TRIGFLAG>
<SPARE1>736</SPARE1>
<SPARE6>12-JUN-05</SPARE6>
<COL_LIST>
<COL_LIST_ITEM>
<OBJ_NUM>45084</OBJ_NUM>
<COL_NUM>1</COL_NUM>
<INTCOL_NUM>1</INTCOL_NUM>
<SEGCOL_NUM>1</SEGCOL_NUM>
<PROPERTY>0</PROPERTY>
<NAME>DEPTNO</NAME>
<TYPE_NUM>2</TYPE_NUM>
<LENGTH>22</LENGTH>
<PRECISION_NUM>2</PRECISION_NUM>
<SCALE>0</SCALE>
<NOT_NULL>1</NOT_NULL>
<CHARSETID>0</CHARSETID>
<CHARSETFORM>0</CHARSETFORM>
<SPARE1>0</SPARE1>
<SPARE2>0</SPARE2>
<SPARE3>0</SPARE3>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<OBJ_NUM>45084</OBJ_NUM>
<COL_NUM>2</COL_NUM>
<INTCOL_NUM>2</INTCOL_NUM>
<SEGCOL_NUM>2</SEGCOL_NUM>
<PROPERTY>0</PROPERTY>
<NAME>DNAME</NAME>
<TYPE_NUM>1</TYPE_NUM>
<LENGTH>14</LENGTH>
<NOT_NULL>0</NOT_NULL>
<CHARSETID>178</CHARSETID>
<CHARSETFORM>1</CHARSETFORM>
<SPARE1>0</SPARE1>
<SPARE2>0</SPARE2>
<SPARE3>14</SPARE3>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<OBJ_NUM>45084</OBJ_NUM>
<COL_NUM>3</COL_NUM>
<INTCOL_NUM>3</INTCOL_NUM>
<SEGCOL_NUM>3</SEGCOL_NUM>
<PROPERTY>0</PROPERTY>
<NAME>LOC</NAME>
<TYPE_NUM>1</TYPE_NUM>
<LENGTH>13</LENGTH>
<NOT_NULL>0</NOT_NULL>
<CHARSETID>178</CHARSETID>
<CHARSETFORM>1</CHARSETFORM>
<SPARE1>0</SPARE1>
<SPARE2>0</SPARE2>
<SPARE3>13</SPARE3>
</COL_LIST_ITEM>
</COL_LIST>
<CON0_LIST/>
<CON1_LIST>
<CON1_LIST_ITEM>
<OWNER_NUM>49</OWNER_NUM>
<NAME>PK_DEPT</NAME>
<CON_NUM>4180</CON_NUM>
<OBJ_NUM>45084</OBJ_NUM>
<PROPERTY>536870912</PROPERTY>
<NUMCOLS>1</NUMCOLS>
<CONTYPE>2</CONTYPE>
<ENABLED>45085</ENABLED>
<INTCOLS>1</INTCOLS>
<MTIME>12-JUN-05</MTIME>
<FLAGS>4</FLAGS>
<OID_OR_SETID>0</OID_OR_SETID>
<COL_LIST>
<COL_LIST_ITEM>
<CON_NUM>4180</CON_NUM>
<OBJ_NUM>45084</OBJ_NUM>
<INTCOL_NUM>1</INTCOL_NUM>
<POS_NUM>1</POS_NUM>
<SPARE1>0</SPARE1>
<OID_OR_SETID>0</OID_OR_SETID>
<COL>
<OBJ_NUM>45084</OBJ_NUM>
<COL_NUM>1</COL_NUM>
<INTCOL_NUM>1</INTCOL_NUM>
<SEGCOL_NUM>1</SEGCOL_NUM>
<PROPERTY>0</PROPERTY>
<NAME>DEPTNO</NAME>
<TYPE_NUM>2</TYPE_NUM>
</COL>
</COL_LIST_ITEM>
</COL_LIST>
<IND>
<VERS_MAJOR>1</VERS_MAJOR>
<VERS_MINOR>2 </VERS_MINOR>
<OBJ_NUM>45085</OBJ_NUM>
<SCHEMA_OBJ>
<OBJ_NUM>45085</OBJ_NUM>
<DATAOBJ_NUM>45085</DATAOBJ_NUM>
<OWNER_NUM>49</OWNER_NUM>
<OWNER_NAME>SCOTT</OWNER_NAME>
<NAME>PK_DEPT</NAME>
<NAMESPACE>4</NAMESPACE>
<TYPE_NUM>1</TYPE_NUM>
<TYPE_NAME>INDEX</TYPE_NAME>
<CTIME>2005-06-12:18:21:30</CTIME>
<MTIME>2005-06-12:18:21:30</MTIME>
<STIME>2005-06-12:18:21:30</STIME>
<STATUS>1</STATUS>
<FLAGS>0</FLAGS>
<SPARE1>0</SPARE1>
<SPARE2>65535</SPARE2>
</SCHEMA_OBJ>
<COL_LIST>
<COL_LIST_ITEM>
<OBJ_NUM>45085</OBJ_NUM>
<BO_NUM>45084</BO_NUM>
<INTCOL_NUM>1</INTCOL_NUM>
<COL>
<OBJ_NUM>45084</OBJ_NUM>
<COL_NUM>1</COL_NUM>
<INTCOL_NUM>1</INTCOL_NUM>
<SEGCOL_NUM>1</SEGCOL_NUM>
<PROPERTY>0</PROPERTY>
<NAME>DEPTNO</NAME>
<TYPE_NUM>2</TYPE_NUM>
</COL>
<POS_NUM>1</POS_NUM>
<SEGCOL_NUM>0</SEGCOL_NUM>
<SEGCOLLEN>0</SEGCOLLEN>
<OFFSET>0</OFFSET>
<FLAGS>0</FLAGS>
<SPARE2>0</SPARE2>
</COL_LIST_ITEM>
</COL_LIST>
<TS_NAME>USERS</TS_NAME>
<BLOCKSIZE>8192</BLOCKSIZE>
<STORAGE>
<FILE_NUM>4</FILE_NUM>
<BLOCK_NUM>19</BLOCK_NUM>
<TYPE_NUM>6</TYPE_NUM>
<TS_NUM>4</TS_NUM>
<BLOCKS>8</BLOCKS>
<EXTENTS>1</EXTENTS>
<INIEXTS>8</INIEXTS>
<MINEXTS>1</MINEXTS>
<MAXEXTS>2147483645</MAXEXTS>
<EXTSIZE>128</EXTSIZE>
<EXTPCT>0</EXTPCT>
<USER_NUM>49</USER_NUM>
<LISTS>1</LISTS>
<GROUPS>1</GROUPS>
<BITMAPRANGES>0</BITMAPRANGES>
<CACHEHINT>0</CACHEHINT>
<SCANHINT>0</SCANHINT>
<HWMINCR>45085</HWMINCR>
<FLAGS>131329</FLAGS>
</STORAGE>
<DATAOBJ_NUM>45085</DATAOBJ_NUM>
<BASE_OBJ_NUM>45084</BASE_OBJ_NUM>
<BASE_OBJ>
<OBJ_NUM>45084</OBJ_NUM>
<DATAOBJ_NUM>45084</DATAOBJ_NUM>
<OWNER_NUM>49</OWNER_NUM>
<OWNER_NAME>SCOTT</OWNER_NAME>
<NAME>DEPT</NAME>
<NAMESPACE>1</NAMESPACE>
<TYPE_NUM>2</TYPE_NUM>
<TYPE_NAME>TABLE</TYPE_NAME>
<CTIME>2005-06-12:18:21:30</CTIME>
<MTIME>2005-06-12:18:21:30</MTIME>
<STIME>2005-06-12:18:21:30</STIME>
<STATUS>1</STATUS>
<FLAGS>0</FLAGS>
<SPARE1>6</SPARE1>
<SPARE2>1</SPARE2>
</BASE_OBJ>
<INDMETHOD_NUM>0</INDMETHOD_NUM>
<COLS>1</COLS>
<PCT_FREE>10</PCT_FREE>
<INITRANS>2</INITRANS>
<MAXTRANS>255</MAXTRANS>
<TYPE_NUM>1</TYPE_NUM>
<FLAGS>0</FLAGS>
<PROPERTY>4097</PROPERTY>
<INTCOLS>1</INTCOLS>
<NUMCOLSDEP>1</NUMCOLSDEP>
<SPARE6>12-JUN-05</SPARE6>
<FOR_PKOID>0</FOR_PKOID>
</IND>
</CON1_LIST_ITEM>
</CON1_LIST>
<CON2_LIST/>
</TABLE_T>
</ROW></ROWSET>

Note: You may have noticed that I'm using SQLPLUS to run this SQL and so it's
important to set the PAGESIZE and LONG variables, otherwise the DDL returned
may be truncated.
And that's all you need to do to extract object DDL from a SQL command prompt.
If you have trouble, please post your comments on this web page.
share on: facebook

Editing Your Oracle spfile

Introduction
This article will describe how to modify your Oracle spfile configuration file in Oracle 9i. It assumes that you are currently running your database using your spfile and not your pfile.

Creating a Pfile from your Spfile
First, if you want, copy your file in /dbs/init.ora (where is your oracle instance name) which will be overwritten by this process. Then log into your oracle database as sysdba. Usually from a privileged operating system account you can do this like this:

[oracle@mynode oracle]$ sqlplus /nolog


SQL> connect / as sysdba;
Connected.

Once connected, it's time to create a pfile, which you can edit with a text editor, from your current spfile. You can do this like this:

SQL> CREATE pfile FROM spfile;

File created.

SQL> quit;

Modifying Your Pfile
This created a pfile called init.ora in /dbs/, which in my case was called initORCL.ora. Now you can edit your init.ora file with a plain text editor like VI or Notepad. Once you're done editing the pfile, it's recommended that you start the database with the pfile to make sure it works and that the syntax is correct before making a spfile from it:

[oracle@mynode oracle]$ sqlplus /nolog

SQL> connect / as sysdba;
Connected.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup pfile=initORCL.ora;
ORACLE instance started.

Total System Global Area  404194808 bytes
Fixed Size                   742904 bytes
Variable Size             201326592 bytes
Database Buffers          201326592 bytes
Redo Buffers                 798720 bytes
Database mounted.
Database opened.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Creating Your New Spfile
If all went well, then your database started up with the pfile. Next, we want to create the spfile from the pfile. Leaving your sqlplus session open you can create the new spfile, shutdown the database, and then bring it back up without the pfile parameter so that it uses the spfile like so:

SQL> connect / as sysdba;
Connected.


SQL> CREATE spfile FROM pfile;

File created.



SQL> startup;
ORACLE instance started.

Total System Global Area  404194808 bytes
Fixed Size                   742904 bytes
Variable Size             201326592 bytes
Database Buffers          201326592 bytes
Redo Buffers                 798720 bytes
Database mounted.
Database opened.

SQL>

Your oracle instance should now be running with your new spfile. Check out your database to make sure that the changes you made took effect.

As always, if you have trouble, please post your comments on this web page.
share on: facebook

Dropping Your Default Oracle Temporary Tablespace

Introduction
Dropping your default oracle temporary tablespace can be tricky. You cannot simply issue a drop tablespace command because oracle does not allow it. This article will cover the steps needed to drop and recreate your default temporary tablespace.

How To Drop Your Default Temporary Tablespace
If you've tried dropping your default temporary tablespace by just issuing a DROP TABLESPACE command you've probably noticed that Oracle won't let you.

SQL> DROP TABLESPACE temp;
DROP TABLESPACE temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

Because this is the default temporary tablespace Oracle won't let you drop it. Here are the steps needed to drop your default temporary tablespace.

1) Create a New Temporary Tablespace
First, you will need to create a new temporary tablespace...in this example I've called the tablespace "TEMPX". Note that I purposefully made the tablespace small in size (10mb). This is because we will drop this tablespace at the end of this process.

SQL> CREATE TEMPORARY TABLESPACE TEMPX
  2 TEMPFILE '/data/tempx.dbf' SIZE 10M AUTOEXTEND OFF
  3 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K
  4 SEGMENT SPACE MANAGEMENT MANUAL
/

Tablespace created.

2) Make Your New Temporary Tablespace the Default Temporary Tablespace

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tempx;

Database altered.

3) Drop Your Original Temporary Tablespace

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

4) Recreate Your 'TEMP' Tablespace

SQL> CREATE TEMPORARY TABLESPACE TEMP
  2 TEMPFILE '/data/temp.dbf' SIZE 1000M AUTOEXTEND OFF
  3 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K
  4 SEGMENT SPACE MANAGEMENT MANUAL
/

5) Make Your 'TEMP' Tablespace the Default Temporary Tablespace Again

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.

6) Drop Your 'TEMPX' Tablespace Now That It Is Not Needed

SQL> DROP TABLESPACE tempx INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

And that is all that is needed to recreate your default temporary tablespace. It would be nice if Oracle supplied a command to do this for us, but if you want to go really crazy I'm sure you could script up your own procedure to perform the same functionality.
share on: facebook

Connecting to LDAP/Active Directory using JNDI (Java)

LDAP (Lightweight Directory Access Protocol) has become a standard in most companies for storing hierarchical data. Some of the more common uses are storing employee and customer data. It is very similar to a database (in fact most LDAP implementations tend to have an underlying database in which the data is stored) with the main difference being that the data is in a hierarchy (think folders within folders). Java provides an API for communicating with directories like LDAP called JNDI (Java Naming and Directory Interface). Here is a basic example on how to search for a few common attributes using JNDI:

import java.util.Hashtable;

import javax.naming.Context;

import javax.naming.NamingEnumeration;

import javax.naming.NamingException;

import javax.naming.directory.Attributes;

import javax.naming.directory.DirContext;

import javax.naming.directory.SearchControls;

import javax.naming.directory.SearchResult;

import javax.naming.ldap.InitialLdapContext;



public class DirectorySearch {



public static void main(String[] args) {



 // Define object to hold environmental properties

 Hashtable env = new Hashtable();



 // My username, password, and URL for LDAP

 String username = "uid=myusername,ou=Users,o=MyCompany";

 String password = "myPassword";

 String url = "ldap://localhost:389";



 // Define connection properties.

 env.put(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.ldap.LdapCtxFactory");

 env.put(Context.SECURITY_AUTHENTICATION, "simple");

 env.put(Context.SECURITY_PRINCIPAL, username);

 env.put(Context.SECURITY_CREDENTIALS, password);

 env.put(Context.PROVIDER_URL, url);



 try {



  // Establish the directory context

  DirContext ctx = new InitialLdapContext(env, null);



  // Create a SearchControls object to do some filtering.

  // Define what attributes I want to get back from LDAP

  SearchControls searchCtls = new SearchControls();

  String returnedAtts[] = { "sn", "givenName", "mail" };

  searchCtls.setReturningAttributes(returnedAtts);



  // Specify the scope of my search (one level down,

  // recursive subtree, etc.)

  searchCtls.setSearchScope(SearchControls.SUBTREE_SCOPE);



  // My ldap search filter...what am I looking for?

  String searchFilter = "(uid=someUserID)";



  // Where to start my search

  String searchBase = "ou=Users,o=MyCompany";



  // Actually perform the search telling JNDI where to start

  // the search, what to search for, what how to filter.

  NamingEnumeration results = ctx.search(searchBase, searchFilter, searchCtls);



  // Loop through the search results

  while (results.hasMoreElements()) {



   SearchResult searchResult = (SearchResult) results.next();



   System.out.println("FOUND OBJECT : " + searchResult.getName());



   // Get a collection of the attributes that were returned

   // from the SearchResult.

   Attributes attrs = searchResult.getAttributes();



   if (attrs != null) {



    System.out.println("--> LASTNAME :  " + attrs.get("sn").get());

    System.out.println("--> FIRSTNAME:  " + attrs.get("givenName").get());

    System.out.println("--> EMAIL    :  " + attrs.get("mail").get());



   }

  }

  ctx.close();

 } catch (NamingException e) {

  e.printStackTrace();

 }
}
}

share on: facebook