How to select rows from a specific partition

The syntax to use if you want to select directly from a specific partition:

SELECT row1, row2 ... 
FROM       my_partitioned_table
PARTITION (my_partition_name)
WHERE ....;

From a subpartition:

SELECT row1, row2 ... 
FROM       my_partitioned_table
SUBPARTITION (my_subpartition_name)
WHERE ....;

With Oracle 11g interval partitioning, the syntax has changed so that you can reference a date range, rather than a partition name, since the auto generated names won't say anything meaningful about their content:

select row1, row2 ..
from my_partitioned_table
PARTITION FOR (to_date('25-FEB-2015','dd-mon-yyyy')); 

Remember that aliases must be specified after the entire FROM-clause, including the PARTITION-part, like this:

select ee.col1
from my_partitioned_table
partition (SYS_P2447) ee
where ee.col2 = 'string1'
and ee.col3 like '%string2%'
order by ee.col1,col2;

If you place the alias immediately after the table name, you'll get the error:

partition (SYS_P2447)
          *
ERROR at line 3:
ORA-00924: missing BY keyword

upon execution.

What do do when Oracle Universal Installer reports missing package pdksh-5.2.14 oracle

On RHEL 6, if you encounter situations where the OUI is complaining about the absence of a package pdksh-5.2.14 oracle, do the following:

1. Change directory to /database/stage/cvu/cv/admin 

2. Backup cvu_config: % cp cvu_config backup_cvu_config

3. Edit cvu_config and change the following line:

CV_ASSUME_DISTID=OEL4

to:

CV_ASSUME_DISTID=OEL6

4. Save the updated cvu_config file
5. Install the 11.2.0.3 or 11.2.0.4 software using /database/runInstaller

% cd /database
% ./runInstaller

When installing a 32-bit client like in my case, the OUI will warn that "all requirements have not been met".
This message can be ignored and you can proceed with the installation.
Source: MOS document ID 1454982.1

How to list the content of a .tar file

tar -tvf yourtarfile.tar

How to check whether a library is compiled to 32-bit or 64-bit code on AIX

Use the nm-utility:

"Displays information about symbols in object files, executable files, and object-file libraries."

In the following case, the Library file is 32-bit, since using the -X64 flag is throwing an exception:

nm -X64 /u01/oracle/product/ora11g_client32/mylib.so

0654-210 /u01/oracle/product/ora11g_client32/mylib.so is not valid in the current object file mode.
        Use the -X option to specify the desired object mode.

while using the -X32 flag will produce output similar to the following (abbreviated):

z00u070:ar3u>nm -X32 /u01/oracle/product/ora11g_client32/mylib.so

                     f           -
../../../../../../../src/bos/usr/ccs/lib/libm/m_tables.c f           -
zstcXAForget         U           -
zstcXAOpen           U           -
zstcXAPrepare        U           -
zstcXARecover        U           -
zstcXARollback       U           -
zstcXAStart          U           -

Note that you can use the OBJECT_MODE environment variable and instead execute

export OBJECT_MODE=64
nm /u01/oracle/product/ora11g_client32/mylib.so

How to check whether a library is compiled to 32-bit or 64-bit code on Linux

On Linux, use objdump:

objdump -f /u01/oracle/product/oracle_client32/mylibdir/mylibfile.so
/u01/oracle/product/oracle_client32/mylibdir/mylibfile.so:     file format elf32-i386
architecture: i386, flags 0x00000150:
HAS_SYMS, DYNAMIC, D_PAGED
start address 0x00000560

How to use dbms_metadata to generate DDL for profiles

An easy way to migrate your profiles from a source database to a target database during migration is to use the dbms_metadata package.

To generate one call for each profile:

SELECT UNIQUE 'SELECT DBMS_METADATA.GET_DDL(''PROFILE'',' || ''''|| PROFILE || ''') FROM DUAL;'
FROM DBA_PROFILES;

In my case, the result was a total of three profiles. Use the resulting rows in the script below:

SET HEADING OFF
SET TRIMSPOOL ON
SET FEEDBACK OFF
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR', TRUE);
-- your calls to dbms_metadata here
SELECT DBMS_METADATA.GET_DDL('PROFILE','PROF') from dual;
SELECT DBMS_METADATA.GET_DDL('PROFILE','DEFAULT') from dual;
SELECT DBMS_METADATA.GET_DDL('PROFILE','ONLINEUSR') from dual;

How to use the nmap tool for Oracle networking troubleshooting

The Network exploration tool and security / port skanner (nmap) came in handy as I was checking the prerequisites for a Golden Gate installation.

We had to open ports in a firewall between the two servers to allow the Golden Gate Manager processes on each side to communicate with one another.

Initially, the ports seemed to be closed even though the firewall administrator claimed it was open.

Oracle Golden Gate needs two-way communication over the designated manager port, which by default is 7809.

So I used nmap to prove that it was indeed closed.

When the nmap status is closed or filtered, the man pages explains their state as


"Closed ports have no application listening on them, though they could open up at any time. Ports are classified as unfiltered when they are responsive to nmap's probes, but nmap cannot determine whether they are open or closed.

Filtered ports means that a firewall, filter, or other network obstacle is blocking the port so that nmap cannot tell whether it is open or closed."


Port 1521 was opened, as requested from the firewall team:

[root@myserver2 ~]#  nmap -p 1521 myserver1
Starting Nmap 5.51 ( http://nmap.org ) at 2015-03-24 14:02 CET

Nmap scan report for myserver1 (159.216.45.70)
Host is up (0.0018s latency).
rDNS record for 159.216.45.70: myserver1.mydomain.no
PORT     STATE SERVICE
1521/tcp open  oracle

Port 7809 was closed, as seen by the output below:

[root@myserver2 ~]# nmap -p 7809 myserver1 

Starting Nmap 5.51 ( http://nmap.org ) at 2015-03-24 15:14 CET
Nmap scan report for myserver1 (159.216.45.70)
Host is up.
rDNS record for 159.216.45.70: myserver1.mydomain.no
PORT     STATE    SERVICE
7809/tcp filtered unknown

Nmap done: 1 IP address (1 host up) scanned in 2.08 seconds

Later, the port range 7809-7820 was opened, as can be seen below. Note that there is no activity on ports 7810-7820 so they are for the time being marked as closed:

root@myserver2 ~]# nmap  -p 7809-7820 myserver1

Starting Nmap 5.51 ( http://nmap.org ) at 2015-03-24 15:48 CET
Nmap scan report for myserver1(159.216.45.70)
Host is up (0.0024s latency).
rDNS record for 159.216.45.70: myserver1.mydomain.no

PORT     STATE  SERVICE
7809/tcp open   unknown
7810/tcp closed unknown
7811/tcp closed unknown
7812/tcp closed unknown
7813/tcp closed unknown
7814/tcp closed unknown
7815/tcp closed unknown
7816/tcp closed unknown
7817/tcp closed unknown
7818/tcp closed unknown
7819/tcp closed unknown
7820/tcp closed unknown