In Oracle Apex tabular reports the default width of a report is the greater of either the column headings or the data.

This can have undesirable effects, particularly if the data for a given column in the report is allowed to be very wide. Some of the undesirable effects include creating a row so wide you cannot see the right most portion of the row or having two report regions (or more) on the same page with different total widths.

Sometimes it's important to force the report region to a specific number of pixels. This can be accomplished a number of ways.

One of the easiest is to add a width value to the report attributes substitution string (see image below).

In this case we are setting the absolute width of the report region to 500 pixels.

Here's a simple PL SQL function and accompanying table definition that can be used for writing to a DEBUG table. This is very useful when you're debugging packages (perhaps called from APEX?) and you cannot write to the console easily.

PROCEDURE DEBUG_WRITE(P_MSG IN VARCHAR2, P_PROC IN VARCHAR2)
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   BEGIN
        INSERT INTO ES2_DEBUG_LOG (MSG_TIME, PROC_NAME, MSG)
        VALUES(SYSTIMESTAMP, P_PROC, P_MSG);
        COMMIT;        
   -- EXCEPTION      
   END;
END;

The table to hold the data looks like this

CREATE TABLE SAMPLE.ES2_DEBUG_LOG
(
  MSG_TIME   TIMESTAMP(6),
  MSG        VARCHAR2(1000 BYTE),
  PROC_NAME  VARCHAR2(40 BYTE)
)
TABLESPACE SAMPLE_DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

An example of writing to the log looks like this:

  DEBUG_WRITE('1','create es2');
    SELECT es2_experiment_id.NEXTVAL
    INTO l_experiment_id
    FROM dual;
    
    DEBUG_WRITE('2','create es2');
    
    p_experiment_id := l_experiment_id;
    
    SELECT status_id INTO l_status_id
    FROM es2_status_table
    WHERE status_desc = 'New';
    
    DEBUG_WRITE('3','create es2');

Here's the output from the log after one run

SQL Devleoper 3 is here. Today I downloaded the early adopter version and am intent on connecting to a local SQL Server database to extract some support information.

Documentation on this process is contained in the online manuals located here

http://download.oracle.com/docs/cd/E18464_01/doc.30/e17472/dialogs.htm#s...

Here's a Quick How To Connect SQL Devleoper 3 to SQL Server or MySQL

Note that you can use "Check For Updates" to locate the available drivers.

Here's the steps I followed.

Click Help->Check For Updates

Then check the boxes to enable Oracle Extensions and Third Party SQL Devleoper Extensions

In the list scroll down to

JDTS JDBC Driver 11.1.58.17

check the box

If you need MySQL support that driver is right below and is named

MySQL JDBC Driver 11.1.58.17

Click Next

Accept the GNU Lesser License Agreement

Click Next

and the drivers will download

Restart SQL Developer

Now Click Tools->Preferences->Database->Third Party Drivers

In the Browser box you will see a couple of folders, one should be 'tmp'

Browse into Temp and open the update folder

The drivers you downloaded are there

Now if you get a message that says

java.sql.SQLException: I/O Error: SSO Failed: Native SSPI library not loaded.

You are missing a DLL.

Goto this address at SourceForge and download the missing DLL

http://sourceforge.net/projects/jtds/files/jtds/1.2.5/jtds-1.2.5-dist.zi...

Browse into the zip file and find a file named

ntlmauth.dll

Put that anywhere in your path and restart SQL Developer.

You will be all set and should be able to login using NT Authentication

1/24/2011

Here's a handy SQL script to pull host name, instance name and IP Address ( of the primary interface).

SELECT A.HOST_NAME,A.INSTANCE_NAME,UTL_INADDR.GET_HOST_ADDRESS V$INSTANCE A;

Returns this

HOST_NAME            INSTANCE_NAME     GET_HOST_ADDRESS
-------------------------------------------------------
myhost.mydomain.com  foobar            10.10.10.88

I was recently asked by a colleague "How big should I set my log buffer?"

The answer is, you shouldn't set it at all in Oracle 10g and above, it will be set automatically. See this link to Oracle documentation for more information on log buffers. Oracle Log Buffer Docs

Here's some additional considerations:

Redo log switches are a factor of the size of the redo logs and number of groups AND the log_biuffer. The redo log buffer gets flushed when you commit, it's 1/3 full or every 3 seconds. Any space you allocate to the redo log buffer above and beyond what gets used (based on the above constraints) is wasted log buffer. Therefore, set the redo log buffer to an appropriate (but not excessive) size and then adjust the size of the redo log memober files and the number of log groups to attain a healthy cycle on the redo logs.

To determine what healthy is, size the redo logs so that in addition to the current log, only one other group is ACTIVE. If your groups are too few, or the mmbers to small, you will end up with a situation where all your log files are current or active and you may get a

'private strand flush incomplete'

or

'Checkpoint not complete'

SELECT * FROM V$LOG;

and you will see output similar (perhaps to this):

niku> set lin 2000
niku> /

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME

         1          1        431 1073741824          1 NO  INACTIVE            6040091582 03-DEC-10
         2          1        432 1073741824          1 NO  INACTIVE            6041112547 04-DEC-10
         3          1        433 1073741824          1 NO  INACTIVE            6041954837 05-DEC-10
         4          1        434 1073741824          1 NO  CURRENT             6042824300 05-DEC-10

This posting describes a way to solve a problem of how to open an ssh session and login automatically (without using a private key). This is not a good idea, but can be useful and demonstrates the usefulness of expect scripting.

Note that I would recommend against doing this in most cases because the password is clearly documented in the script. Having said that, this is the situation at many Fortune 500 companies and Global 1000 companies.

My direct experience is that these companies have thousands of scripts in production that have passwords directly embedded into them or passwords set to the account ID.

Given that we cannot fix everything all the time, and need to work fast when we are tired, this script can be useful. Also, there are other applications of expect scripting that are benign. Hopefully this example will lead the reader to see other ways to use expect scripting.

Script to perform an automated login and sudo to oracle account

#!/usr/bin/expect 
# this script opens a ssh session and then logs is using a password containing a $
# then sudo over to the oracle account 

spawn ssh $argv 
expect "password:" 
send "Jan\$6rath\r" 

#sudo to oracle
send "sudo su - oracle\r" 
expect "password:" 
send "Jan\$6rath\r"
expect "$" 

#cd to your home location -- different depending on your OFA
send "cd /ora/admin\r"
expect "$" 
send "ls -l\r"

# return console to user
interact

Moving a datafile from mount point /disk2 to /disk1 on an Oracle 10g database that is up and running

/* take the tablespace offline */
ALTER TABLESPACE TP6_TS_TABLES OFFLINE NORMAL;

# make sure you have the right data files, prevent unhappy accidents
ls -lh /disk1/oradata/datafiles/emspri.dbf 
ls -lh /disk2/oradata/datafiles/emspri.dbf 

# copy the file in the OS
cp /disk2/oradata/datafiles/emspri.dbf /disk1/oradata/datafiles/emspri.dbf 

-- let Oracle know the file is moved
ALTER DATABASE RENAME FILE '/disk2/oradata/datafiles/emspri.dbf' to '/disk1/oradata/datafiles/emspri.dbf';

-- put the tablespace back online
ALTER TABLESPACE TP6_TS_TABLES ONLINE;

After you move the file

clean up tasks -- delete the old data file to free up disk space
backup the control files

DRAFT - contains typos

A project I'm working on required myself and another DBA to assemble an accurate list of all the Oracle databases in the company. The company has many Oracle databases in a variety of locations. The versions vary from 8i to 11g on a variety of platforms. Some are RAC clusters, some are standalone. The inventory needs to include RAC and standalone but exclude Weblogic and OCJ4.

The company uses Oracle Enterprise Manager OEM and Oracle Management Server OMS. As a consequence we can see all the information we need is in OEM. So we started down the path of querying the data out of the SYSMAN schema, in particular the MGMT$ views.

At first I thought the MGMT$ views would be widely known and fairly well documented, much like the V$ views. Well, so far, I haven't found much of anything that is helpful about SYSMAN and MGMT$. Perhaps (most likely) its buried somewhere but being short on time and patience we decided to dig in and figure it out for ourselves.

What follows are some comments, SQL queries and guesses about what's in these views. In the end, we found what we wanted. Along they way, we opened a MetaLink TAR, hoping for a nice tech note and documentation from Oracle Support. This we did not get. We got some advice, but not all of it was on target.

Here's the goal. We want to fill in a spreadsheet with these columns (for all production databases):

OS
OS Version
# Active CPU's
Server IP Address
Server Name
Database Listener TCP Ports
Instance Name
Database Version

This trigger will monitor DDL, specifically DROP statements and log the schema (user) performing the drop into a table called DDL_LOG. A test script is presented below. Before you use this in production make sure you think through the location of the log table, and size it appropriately. If the trigger fails to fire there can be complications, so make sure you test this and understand it before you deploy it outside of a test instance.