How to execute a stored dbms_scheduler job

In sqlplus, execute the following:

execute dbms_scheduler.run_job('CHANGE_PERMS',TRUE);

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;