How to rebuild an index subpartition

To limit a rebuild to a specific index subpartition only, use the following syntax:

ALTER INDEX SCOTT.SALES_IDX3
  REBUILD SUBPARTITION SALES_IDX3_SP_2017_06 ONLINE
  TABLESPACE DATA2;

Notice the use of the ONLINE keyward, which will allow DML against the table (and thus update the index).

To generate rebuild-statements, one index at a time, I have used the script below.

It takes as arguments
1. The index owner
2. The index name
3. The new tablespace where you want to place your subpartitions
4. The old tablespace from which you want to move out

set termout off
select 'alter session set nls_language=''american'';' from dual;
set termout on
accept index_owner prompt 'Index owner: '
accept index_name prompt 'Index name: '
accept new_tabspc prompt 'New tablespace: '
accept old_tabspc prompt 'Old tablespace: '

set lines 300
set heading off
set feedback off
set verify off
set echo off
set pages 0
set trimspool on
spool exec_move_subpart_&&index_owner..&&index_name..sql
select 'alter session set nls_language=''american'';' from dual;

select 'alter session force parallel ddl;' from dual;
select 'set timing on' from dual;
select 'set lines 200' from dual;
select 'set trimspool on' from dual;
select 'spool exec_rebuild_subpart_&&index_owner..&&index_name..log' from dual;
select 'exec DBMS_APPLICATION_INFO.SET_MODULE(module_name=>''Move'', action_name=>''Rebuild_supbart_&&index_name'');' from dual;
select 'exec DBMS_APPLICATION_INFO.SET_CLIENT_INFO (client_info=>''Rebuild_supbart_t_&&index_name'');' from dual;
select 'exec DBMS_SESSION.SET_IDENTIFIER (client_id=>''Rebuild_supbart_&&index_name''); ' from dual;

select 'prompt moving the index subpartitions from &&old_tabspc to &&new_tabspc,' from dual;
select 'prompt and setting default attributes for the involved partitions. ' from dual;
SELECT 'ALTER INDEX ' ||  IDXSUBPART.INDEX_OWNER || '.' || IDXSUBPART.INDEX_NAME || ' rebuild subpartition ' || IDXSUBPART.SUBPARTITION_NAME || ' tablespace &&new_tabspc ONLINE;'
FROM   DBA_IND_SUBPARTITIONS IDXSUBPART JOIN DBA_SEGMENTS S
ON     (IDXSUBPART.SUBPARTITION_NAME = S.PARTITION_NAME )
WHERE  IDXSUBPART.INDEX_NAME='&&index_name'
AND    IDXSUBPART.TABLESPACE_NAME='&&old_tabspc'
AND    S.SEGMENT_TYPE='INDEX SUBPARTITION'
union
select distinct 'alter index ' ||   IDXSUBPART.INDEX_OWNER || '.' || IDXSUBPART.INDEX_NAME || ' modify default attributes for partition ' || idxsubpart.partition_name || ' tablespace &&new_tabspc;'
FROM   DBA_IND_SUBPARTITIONS IDXSUBPART JOIN DBA_SEGMENTS S
ON     (IDXSUBPART.SUBPARTITION_NAME = S.PARTITION_NAME )
WHERE  IDXSUBPART.INDEX_NAME='&&index_name'
AND    IDXSUBPART.TABLESPACE_NAME='&&old_tabspc'
AND    S.SEGMENT_TYPE='INDEX SUBPARTITION';

select 'exit' from dual;
exit

Run it as a user with access to the data dictionary:

sqlplus / as sysdba @gen_move_idx_subpart.sql

It will generate a new script called exec_move_subpart_<username>.<index_name>.sql which will actually perform the rebuild:

sqlplus / as sysdba @exec_move_subpart_<username>.<index_name>.sql

I also added som calls to dbms_application_info so that the session can be easily found in v$session

How to solve "INS-32035: The chosen installation conflicts with software already installed in the given oracle home" when installing into an old directory

Workaround for the error

INS-32035: The chosen installation conflicts with software already installed in the given oracle home

from the Oracle Universal Installer:

1. detatch the home from the ORACLE_HOME you install from:

cd $current_oracle_home/oui/bin
./runInstaller -detachHome ORACLE_HOME=/u01/oracle/product/11204 invPtrLoc=/u01/oracle/product/11201/oraInst.loc

2. edit the file /u01/oraInventory/ContentsXML/inventory.xml

In my case from:

<HOME_LIST>
<HOME NAME="OraDb11g_home1" LOC="/u01/oracle/product/db/11201" TYPE="O" IDX="1"/>
<HOME NAME="OraDb11g_home2" LOC="/u01/oracle/product/db/11204" TYPE="O" IDX="2"/>
</HOME_LIST>

to

<HOME_LIST>
<HOME NAME="OraDB11g_home1" LOC="/u01/oracle/product/db/11201" TYPE="O" IDX="1"/>
</HOME_LIST> 

3. Delete the files in /u01/oracle/product/db/11204 physically from disk, before you try again.

That's it.
Universal Installer should now recognize the selected directory, and not give you any complaints when reinstalling into the same directory.

Automatic startup and shutdown script for Oracle databases on the Linux Platform

On most installations of the Oracle database, it is desirable to add a script for automatic startup and shutdown.
This is how to do it.
The instructions holds true for both 11g and 12c.

1. Edit the /etc/oratab file, so that the entry for your database has a Y at the end:

testdb01:/u01/oracle/product/db/112:Y

2. cd /etc/init.d

3. create a file called dbora, and add the following lines to it. Note that you should enter the name of your ORACLE_HOME path and the oracle software installation owner as values for ORA_HOME and ORA_OWNER, respectively:

#! /bin/sh 
# description: Oracle auto start-stop script.
#
# Set ORACLE_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORACLE_HOME.

ORA_HOME=/u01/oracle/product/db/112
ORA_OWNER=oracle

case "$1" in
'start')
    # Start the Oracle databases:
    # The following command assumes that the oracle login
    # will not prompt the user for any values
    # Remove "&" if you don't want startup as a background process.
    su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME" &
    touch /var/lock/subsys/dbora
    ;;

'stop')
    # Stop the Oracle databases:
    # The following command assumes that the oracle login
    # will not prompt the user for any values
    su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME" &
    rm -f /var/lock/subsys/dbora
    ;;
esac

4. Set permissions:

# chgrp dba dbora
# chmod 750 dbora

5. Create symlinks to the different run level script directories:

ln -s /etc/init.d/dbora /etc/rc.d/rc0.d/K01dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/S99dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/S99dbora

Verify the symlinks. They should all point back to /etc/init.d/dbora

ls -la /etc/rc.d/rc0.d/K01dbora
ls -la /etc/rc.d/rc3.d/S99dbora
ls -la /etc/rc.d/rc5.d/S99dbora

What about the listener?
According to Oracle, as long as it has the default name of "LISTENER", you don't need to add it to your startup/shutdown script. If you use a different name, you must add the following line to the /etc/init.d/dbora script:

$ORACLE_HOME/bin/lsnrctl {start|stop} listener_name

The original instructions from Oracle can be found here

Possible solution to unix error "X11 connection rejected because of wrong authentication"

I was trying to start an X window Application on my server as user Oracle, but received the error

oracle@myserver:[testdb01]# xclock
X11 connection rejected because of wrong authentication.
Error: Can't open display: localhost:10.0

The DISPLAY variable was set to localhost:10.0.

The solution was to logon as root and execute

root@myserver~]# xauth list $DISPLAY

The result from this was

myserver.mydomain.com/unix:10  MIT-MAGIC-COOKIE-1  83f9f76d03106db6fc0880edfb005607

Then change user back to oracle and add the cookie to oracle's authorization file:

[root@myserver ~]# su - oracle
oracle@myserver:[testdb01]# xauth add :10 MIT-MAGIC-COOKIE-1 83f9f76d03106db6fc0880edfb005607

If you get the error:

xauth:  file /home/oracle/.Xauthority does not exist

create the file first:

touch /home/oracle/.Xauthority

Then retry the "xauth add" command above.
Verify with:

oracle@myserver:[testdb01]# xauth list $DISPLAY
myserver.mydomain.com/unix:10  MIT-MAGIC-COOKIE-1  83f9f76d03106db6fc0880edfb005607
oracle@myserver:[testdb01]# echo $DISPLAY
localhost:10.0

I could now execute X Applications:

oracle@myserver:[testdb01]# xclock

Possible solution to ORA-01450: maximum key length (3800) exceeded when rebuilding an index

As a part of disabling TDE in a test database, I was moving indexes out of the TDE encrypted tablespace to another, similarly created tablespace, but one without encryption.

When trying to execute

ALTER INDEX SCOTT.MYTABLE_U01 REBUILD ONLINE TABLESPACE DATA32K_NOTDE

one of the tables returned an error during the online rebuild:

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3800) exceeded

This error is normally worked around by one of the following actions:

1. Rebuild the database with a larger blocksize
2. Add a new tablespace with a larger blocksize (my preferred solution)
3. Make the index smaller, meaning drop and recreate the index and omit one or more of the previously indexed columns


This is documented in Doc ID 747107.1 "ORA-01450 Error on Create Index" at Oracle Support.

For me though, it worked by simply changing

REBUILD ONLINE 

to

REBUILD

and the index rebuild executed without problems.
The Oracle Documentation for 12cR1 has a few restrictions regarding the use of online rebuilds, but none of them seems relevant to the error I observed.

For more information about the ALTER INDEX statement, see the official documentation from Oracle

How to check if Database Vault is enabled or disabled

To check whether or not the Database Vault option is enabled in your database, run the following query as a privileged user:

SELECT parameter, value
FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';

The true/false value of the parameter will indicate whether or not it is enabled.

Source: Oracle Documentation>

Create a tablespace using input variables

Here is a script to help set up tablespaces based on the path used for the system tablespace:

SET LINES 200
-- NEW_VALUE in sqlplus specifies a variable to hold a column value
COL tabspace_path FORMAT A50 NEW_VALUE path

SELECT SUBSTR(FILE_NAME, 1, INSTR(FILE_NAME, '/', -1) -1) tabspace_path
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'SYSTEM';

SELECT '&path' variable_value
FROM DUAL;

CREATE BIGFILE TABLESPACE test_tbspc DATAFILE '&path/&tablespace_name..dbf' SIZE 32M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

exit

Run the script:

oracle@myserver:[testdb01]# sqlplus / as sysdba @test.sql

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options


TABSPACE_PATH
--------------------------------------------------
/u02/oradata/testdb01
old   1: SELECT '&path' variable_value
new   1: SELECT '/u02/oradata/testdb01' variable_value

VARIABLE_VALUE
---------------------
/u02/oradata/testdb01

Enter value for tablespace_name: mytablespace
old   1: CREATE BIGFILE TABLESPACE mytablespace DATAFILE '&path/&tablespace_name..dbf' SIZE 32M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED
new   1: CREATE BIGFILE TABLESPACE mytablespace DATAFILE '/u02/oradata/testdb01/mytablespace.dbf' SIZE 32M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED

Tablespace created.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options