See AWK also, which is often a companion to SED.

HANDY ONE-LINERS FOR SED (Unix stream editor) Apr. 26, 2004 compiled by Eric Pement - pemente[at]northpark[dot]edu version 5.4

Latest version of this file is usually at: http://sed.sourceforge.net/sed1line.txt

TYPICAL USE: Sed takes one or more editing commands and applies all of them, in sequence, to each line of input. After all the commands have been applied to the first input line, that line is output and a second input line is taken for processing, and the cycle repeats. The preceding examples assume that input comes from the standard input device (i.e, the console, normally this will be piped input). One or more filenames can be appended to the command line if the input does not come from stdin. Output is sent to stdout (the screen). Thus:

 cat filename | sed '10q'        # uses piped input
 sed '10q' filename              # same effect, avoids a useless "cat"
 sed '10q' filename > newfile    # redirects output to disk

Pages: 1 · 2 · 3

See SED also, which is often a companion to AWK.

HANDY ONE-LINERS FOR AWK 22 July 2003 compiled by Eric Pement version 0.22

Latest version of this file is usually at: http://www.student.northpark.edu/pemente/awk/awk1line.txt

Most of my experience comes from version of GNU awk (gawk) compiled for Win32. Note in particular that DJGPP compilations permit the awk script to follow Unix quoting syntax '/like/ {"this"}'.

However, the user must know that single quotes under DOS/Windows do not protect the redirection arrows ( ) nor do they protect pipes (|). Both are special symbols for the DOS/CMD command shell and their special meaning is ignored only if they are placed within "double quotes."

Likewise, DOS/Win users must remember that the percent sign (%) is used to mark DOS/Win environment variables, so it must be doubled (%%) to yield a single percent sign visible to awk.

If I am sure that a script will NOT need to be quoted in Unix, DOS, or CMD, then I normally omit the quote marks. If an example is peculiar to GNU awk, the command 'gawk' will be used.

Please notify me if you find errors or new commands to add to this list (total length under 65 characters). I usually try to put the shortest script first.

USAGE:

    Unix:  awk '/pattern/ {print "$1"}'    # standard Unix shells
 DOS/Win:  awk '/pattern/ {print "$1"}'    # okay for DJGPP compiled
           awk "/pattern/ {print \"$1\"}"  # required for Mingw32

Pages: 1 · 2

Re-build all user indexes on-line

Notes: Run this script from sqlplus as a DBA user.

Pages: 1 · 2

You know the all_tables view. It gives you all the tables you can access. But who doesn't ask "which are the tables user foo can access ?" and "why can he access this table".

The following view gives you for all users (column username), the owner and the name of the accessible tables (columns owner and table_name) and the origin of the access privilege (column why) with:

if the user is the owner of the table Object Priv.
if the access is via an object privilege System Priv.
if the access is via a system privilege
if the privilege or the role is granted to public
if the privilege or the role is granted to the user.

Remark: You must have the select privilege on the underlying tables.
For example, SELECT ANY TABLE in Oracle7 or 8, and SELECT ANY DICTIONARY in Oracle8i+.

Pages: 1 · 2

As a DBA, it was often asked how the database grows and when we'll full up the disc space.
Consider when you create a database, to automatically add a job to follow the space occupation.

It's in two parts:

1- a table, to record the statistics
2- a job, run every day at minight.

Pages: 1 · 2

Often you get a message (for instance, corruption) or query dynamic table (for instance, hot block) giving you a file and a block. This tip will show you how to retrieve the object belonging to these informations. If you get the file id (say &file_id) and the block id (say &block_id), your retrieve the object with:

Set linesize 120 
Set heading off 
Set feedback off 

Select 'Block &block_id of file '||f.file_name||' : 
   '||decode(e.owner, NULL, e.segment_name, 
                      e.owner||'.'||e.segment_name)|| 
         decode(e.partition_name, NULL, '', '.'||e.partition_name)|| 
         ' ('||e.segment_type||')' obj 
from dba_extents e, dba_data_files f 
where f.file_id = &file_id 
  and e.file_id = &file_id 
  and &block_id between e.block_id and e.block_id+e.blocks-1 
/ 
If you have the file name (say &file_name) and the block id:
Select 'Block &block_id of file &file_name : 
   '||decode(e.owner, NULL, e.segment_name, 
                      e.owner||'.'||e.segment_name)|| 
         decode(e.partition_name, NULL, '', '.'||e.partition_name)|| 
         ' ('||e.segment_type||')' obj 
from dba_extents e, dba_data_files f 
where f.file_name = '&file_name' 
  and e.file_id = f.file_id 
  and &block_id between e.block_id and e.block_id+e.blocks-1 
/ 

This query will give you how to find all the objects that can't extend and the reason why they can't extend: max extent reached, no free extend in the tablespace to fit the next extent, quota exceeded:

set echo off 
set feedback off 
set linesize 120 
column owner format a15 heading "Owner" 
column segment_name format a30 heading "Objet" 
column tablespace_name format a13 heading "Tablespace" 
column extents format 999G999 heading "Extents" 
column maxext format a10 heading "Max Extents" 
column nextext format 9G999G999 heading "Next (K)" 
column maxfree format 999G999G999 heading "Max Free (K)" 
column quota format a10 heading " Quota (K)" 
def nolimit = ' Unlimited' 
prompt 
select s.owner, s.segment_name, s.tablespace_name, 
       s.next_extent/1024 nextext, nvl(f.max_free,0)/1024 maxfree, 
       s.extents, 
       decode (s.max_extents, 2147483645, '&nolimit', 
               tcms_char(s.max_extents, '9G999G999')) maxext, 
       decode (p.privilege, NULL, 
                            decode(nvl(q.max_bytes,decode(s.owner,'SYS',-1,0)), 
                                   -1, '&nolimit', 
                                   tcms_char(nvl(q.max_bytes,0)/1024,'9G999G999')), 
               '&nolimit') quota 
from dba_ts_quotas q, dba_sys_privs p, 
     ( select t.tablespace_name, nvl(max(f.bytes),0) max_free 
       from dba_free_space f, dba_tablespaces t 
       where f.tablespace_name (+) = t.tablespace_name 
       group by t.tablespace_name) f, 
     dba_segments s 
where q.username (+) = s.owner 
  and p.grantee (+) = s.owner 
  and p.privilege (+) = 'UNLIMITED TABLESPACE' 
  and f.tablespace_name = s.tablespace_name 
  and nvl(f.max_free,0) 

When we add a new user we often have to grant him some privileges that are the same of another previous user.

The following script generates a script to copy all the privileges from one user to another.
It needs 3 parameters :

1/ From user 
2/ To user 
3/ Service name for the connection to database. 

The latter parameter is necessary to grant the object privileges as these are granted by the owner of the objects.

Pages: 1 · 2

With Oracle 8i/9i, you can implement security?once, in the server?and give users, customers, and suppliers access to production systems at the same time.
Achieving the right balance between providing appropriate access and enforcing security is no easy task. Giving each user access to all that he or she needs?but only that?can involve complex development and administration scenarios, particularly with a large number of applications and different types of users requiring different levels of access?administrator or user, manager or staff, marketing or manufacturing, for example.

User-access issues such as these are difficult enough to manage within the confines of a single organization; the situation becomes exponentially more challenging when you want to open your production databases to customers, trading partners, and suppliers.

Today's 24x7 Internet-driven world is calling for applications and new business models that challenge traditional security capabilities.

Click here for more information.

Whenever you read or write data in Oracle, you do so by issuing an SQL Statement. One of Oracle's task when it receives such a statement is to build a statement execution plan. An execution plan defines how Oracle finds or writes the data. For example, an important decision that Oracle has to take is if it uses indexes or not. And if there are more indexes, which of these is used. All this is contained in an execution plan.

If one wants to explore such an execution plan, Oracle provides the SQL Statement EXPLAIN PLAN to determine this.

The general syntax of EXPLAIN PLAN is:

explain plan for your-precious-sql-statement;

If you do an EXPLAIN PLAN, Oracle will analyze the statment and fill a special table with the Execution plan for that statement. You can indicate which table has to be filled with the following SQL command:

explain plan into table_name for your-precious-sql-statement;

If you omit the INTO TABLE_NAME clause, Oracle fills a table named PLAN_TABLE by default.

The Plan Table

The plan table is the table that Oracle fills when you have it explain an execution plan for an SQL Statement. You must make sure such a plan table exists. Oracle ships with the script UTLXPLAN.SQL which creates this table, named PLAN_TABLE (which is the default name used by EXPLAIN PLAN). If you like, however, you can choose any other name for the plan table, as long as you have been granted insert on it and it has all the fields as here.

Pages: 1 · 2 · 3 · 4 · 5

In TOAD simply write your query, then hit Ctrl-E to display your explain plan. If you see a permissions window popup regarding access to the PLAN_TABLE, then coordinate with DBA's to obtain the appropriate grants. Generally speaking low cost means better performance. Fewer outer joins and full table scans, generally means better performance. Remember also, that under your TOAD options, your Plan Table should be defined as PLAN_TABLE and the explain identifier is your workstation id.

In SQLPLUS you will need to run a statement like the following :

explain plan for your-precious-sql-statement;

Example ::

explain plan for
select *
from some.table
  where rec_id = 3227;

Followed by the following to generate a formatted explain plan. This will work at the unix level as well as windows :

select * from table(dbms_xplan.display);

Note: Originally sourced from :

http://www.adp-gmbh.ch/

Occassionally you may run into a situation which requires you to capture some test information while your process is running. You could always create a standardized log table just for your process and log items to it. You could even build one specific to your process. However, often it would be nice if there was already something there that you could easily use any time.

What I might suggest is the creation of a temporary log table. This table would have a key identifier on it to separate your items from other individuals/processes logging to the table. Also it would have some date, number, character, blob, clob fields to pass data into.

In your stored proc, you would have code to insert data into the table as your process runs. Then you could simply review the table when things are completed. I would recommend cleaning up your data from the temporary table when you are done analyzing the data.

Pages: 1 · 2

Well our Applicant Tracking project is off and running. It has been quite the tangled path too. I didn't really have any knowledge of how to process Hires into the OA environment, let alone applicants. My trusty skills at google and metalink did not help too much either.

For obvious reasons I can't divulge my entire packaged solution, however given the trouble I had finding anything, I will divulge some secrets.

Pages: 1 · 2

Batch Element Entry has significant advantages over PAYMIX, but it should also be noted that PAYMIX is being phased out in 11.5.4 of the application. BEE can be utilized manually, but can be batch loaded as well. In 11.0.3 the application did not provide the api's required for loading, so we emulated the PAYMIX api's on our own. These were later replaced by 11.5.4 api's.

Some things to note:

- BEE loader files are comma delimited format (csv). These can be easily generated from an Excel spreadsheet. (see below for layout requirements)
- Batch line entries that fail will need to be resubmitted under a new batch name. They cannot be appended to an existing batch.
- If no batch lines are created, or the batch header creation fails completely, it may require one of the following :
- Deletion of the Batch Header via the application
- Deletion of the entries that apply to the Batch in question within the TEMP_PAY_BATCH_LINES table (this table stores the csv loader file for processing)
- Output of job will be displayed in the concurrent manager, indicating failure points if they occur.
- Upon successful completion of the BEE loader, the data in TEMP_PAY_BATCH_LINES will be truncated since it is temporary only.

Sample code here

Pages: 1 · 2 · 3 · 4 · 5

We ran into a situation where we needed to email encrypted zip files to a client generated from reports. Using the Oracle Applications as a platform for entering the parms, I created a simple approach to fulfilling this requirement. It could be expanded to support PGP, Mime attachments, etc. However, it works as is. To see the setup of job and shell script, click the Read More link below.

Pages: 1 · 2

Noticed that on 10g there is functionality for CLOB to BLOB and BLOB to CLOB converson. Problem is that we are a few months away still from being on 10g. So whats a 9iR2 guy supposed to do... write your own.

Here are two handy functions you can use.. not sure what the max size conversions is but I would assume whatever LOB size max is.. so 4 GB for now ? and in 10g+ ... Terrabytes.

Pages: 1 · 2

Always forgetting about these useful functions... and even when we do, how to use them to our advantage....(see comments at bottom for example)

Ranking Function

Rank:-assigns A Unique Number For Each Row Starting With 1,except For Rows That Have Duplicate Values,in Which Case The Same Ranking Is Assigned And A Gap Appears In The Sequence For Each Duplicate Ranking.

Row_number:-returns A Unique Number For Each Row Starting With 1.for Rows That Have Duplicate Values,numbers Are Arbitarily Assigned.

Dense_rank:-assigns A Unique Number For Each Row Starting With 1,except For Rows That Have Duplicate Values ,in Which Case Same Ranking Is Assigned.

Pages: 1 · 2 · 3

Thought I would migrate away from my CMS setup of previous to a blob type environment. A bit easier to run in and post something without all the frills. So this makes for my first entry of many to come. Initially, it will be just catch up until I get everything posted. So stay tuned and enjoy.