How to drop a table partition without invalidating global indexes

My interval range partitioned table looks as follows:

CREATE TABLE SEGMENT_SIZES(
  SEGMENT_SIZES_ID NUMBER,
  STIMESTAMP      DATE,
  OWNER           VARCHAR2(30 BYTE),
  SEGMENT_NAME    VARCHAR2(30 BYTE),
  PARTITION_NAME  VARCHAR2(30 BYTE),
  SEGMENT_TYPE    VARCHAR2(20 BYTE),
  BYTES           NUMBER,
  CONSTRAINT SEGMENT_SIZES_PK PRIMARY KEY (SEGMENT_SIZES_ID)
)
PARTITION BY RANGE (STIMESTAMP )
-- Use 11gR1 Interval Partitioning
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
   PARTITION P062013 VALUES LESS THAN (TO_DATE('01.07.2013','DD.MM.YYYY'))
)
TABLESPACE TOOLS
ENABLE ROW MOVEMENT
COMPRESS FOR ALL OPERATIONS;

It was populated with data, which created the needed partititions automatically.
The primary key defined on the table will of course create a global index spanning all partitions.
Consequently, during partition maintenance operations, you will end up with an UNUSUABLE primary key index if you drop a partition, as follows:

ALTER TABLE SEGMENT_SIZES DROP PARTITION SYS_P42;

However, if you you add the "UPDATE INDEXES" clause, oracle will update the global index; the index will remain USABLE:

ALTER TABLE SEGMENT_SIZES DROP PARTITION SYS_P42 UPDATE INDEXES;

Keep in mind that you cannot drop the highest range partition of an interval-partitioned table!

Source: Oracle Documentation

why am I getting ORA-14006: invalid partition name when attempting to drop a partition in TOAD?

In TOAD you are trying to drop a partition, as follows:

alter table segment_sizes drop partition SYS_P41;

But you get ORA-14006: invalid partition name as a result.

Solution: remove the ";" at the end of the statement, and try again.
Alternatively, execute the statement through SQL*plus.

Source: Derya Oktay's Oracle Weblog

How to use the sqlplus "autotrace" facility

SET AUTOT ON
Executes the SQL and returns the execution plan and statistics (shorthand for "set autotrace on")

SET AUTOT TRACE
Executes the SQL and returns the execution plan and statistics (shorthand for "set autotrace traceonly")

Note: Using ON or TRACEONLY with no explicit options defaults to EXPLAIN STATISTICS.

SET AUTOT TRACE EXP
Explains the SQL, omits statistics and does not execute the SQL(shorthand for "set autotrace traceonly explain")
The SQL statement is never executed for real, only explained.

SET AUTOT TRACE EXP STAT
Executes the SQL, displays the execution plan, displays statistics. Executes the SQL, but supresses the output (shorthand for "set autotrace traceonly explain statistics")

SET AUTOT OFF
Disables autotraceing (shorthand for "set autotrace off")

If you have trouble getting the autotrace feature to work, make sure you have created the PLUSTRACE role.

Source: Oracle 19c Documentation

sdtperfmeter - a graphical monitoring of cpu, paging and I/O on Solaris

Create a file called monitor.sh:

#!/usr/bin/bash

/usr/dt/bin/sdtperfmeter -t page &
/usr/dt/bin/sdtperfmeter -t cpu &
/usr/dt/bin/sdtperfmeter -t disk &

exit

Start your X server, set display to your client, then execute in background:

./monitor.sh &

Works on Solaris 10 (and several previous versions)

How to generate a script to rebuild unusable index partitions

set trimspool on
set lines     200
set pages     0
set heading   off
set verify    off
set feedback  off
set echo      off
spool rebuild_stmts.sql
--Rebuild the index partition to the same tablespace:
SELECT 'alter index ' || idx.table_owner || '.' ||  idx.index_name || ' rebuild partition ' || idxpart.partition_name || ';'
FROM   dba_indexes idx, dba_ind_partitions idxpart
WHERE  idx.table_owner = 'SCOTT'
AND    idx.index_name = idxpart.index_name
AND    idxpart.status <> 'USABLE'
ORDER BY idx.table_owner, idx.index_name
/
--For a specific INDEX, reallocating the index partitions to a new tablespace:
SELECT 'alter index ' || idx.table_owner || '.' ||  idx.index_name || ' rebuild partition ' || idxpart.partition_name || ' TABLESPACE MYTABLESPACE;'
FROM    dba_indexes idx, dba_ind_partitions idxpart
WHERE   idx.table_owner = 'SCOTT'
AND     idx.index_name = idxpart.index_name
AND     idx.index_name = 'IDX_01'
AND     idxpart.status <> 'USABLE'
ORDER BY idx.table_owner, idx.index_name
/
--Use sqlplus variables, also reallocating the index partitions to a new tablespace:
SELECT 'alter index ' || idx.table_owner || '.' ||  idx.index_name || ' rebuild partition ' || idxpart.partition_name || ' TABLESPACE MYTABLESPACE;'
FROM    dba_indexes idx, dba_ind_partitions idxpart
WHERE   idx.table_owner = 'SCOTT'
AND     idx.index_name = idxpart.index_name
AND     idx.index_name = '&&index_name'
AND     idxpart.status <> 'USABLE'
ORDER BY idx.table_owner, idx.index_name
/
-- Rebuild index subpartitions. Also add a parallel statement and the ONLINE keyword:
SELECT 'alter index ' || IDX.TABLE_OWNER || '.' ||  IDX.INDEX_NAME || ' rebuild subpartition ' || IDXSUBPART.SUBPARTITION_NAME || ' parallel 4 tablespace ' || IDXSUBPART.TABLESPACE_NAME ||' online;'
FROM    DBA_INDEXES IDX, DBA_IND_SUBPARTITIONS IDXSUBPART
WHERE   IDX.TABLE_OWNER IN ('SCOTT','JAMES')
AND     IDX.INDEX_NAME = IDXSUBPART.INDEX_NAME
AND     IDX.INDEX_NAME IN (SELECT UNIQUE INDEX_NAME
                                        FROM DBA_IND_SUBPARTITIONS
                                        WHERE STATUS = 'UNUSABLE')
AND INDEX_OWNER NOT IN ('SYS','SYSTEM')
AND IDXSUBPART.STATUS <> 'USABLE'
ORDER BY IDX.OWNER, IDX.INDEX_NAME
/

Upon completion, the file "rebuild_stmts.sql" should contain your "ALTER INDEX REBUILD PARTITION..." directives.

How to display session information, followed by a kill-statement

Often, my customers ask me to kill a runaway session for them, typically this will be in the form "Can you please kill session with session id 170 for us?. It's urgent!"

Before I do so, I'd like to be one houndred percent certain that I kill the right session.
To help me in such situations, I execute the script below. It will take one parameter, the sessions SID, and echo back some basic information about what the session is doing. Finally it prints a "kill session" command, which I can choose to execute if it was indeed the right session.

set verify           off
set linesize 200
col "os process"     format a30
col "os process id"  format a10
col "osuser"         format a15
col "schemaname"     format a20
col "client program" format a20
col "client name"    format a20
col "session type"   format a20
col status           format a10
col "session type"   format a15
cle scr
prompt ===================================================
prompt This script will print basic information about a
prompt session.
prompt
accept SID prompt 'Pls enter SID of session: '
prompt ===================================================
prompt  Information about database session &&SID
prompt ===================================================
column host_name new_value v_hostname;

set termout off
select host_name
from   v$instance;
set termout on

select
        p.program "os process",
        p.spid "os process id",
        p.username "osuser",
        s.sid,s.serial# "serial num",
        lower(s.schemaname) "schemaname",
        lower(s.osuser) "client name",
        s.program "client program",
        lower(s.status) "status",
        lower(s.type) "session type"
from v$process p, v$session s
where p.program not like 'oracle@v_hostname (%'
and p.addr = s.paddr
and p.addr = (select paddr from v$session where sid=&&SID)
order by s.username asc
/
set heading off
prompt ===================================================
prompt  Kill statement for session &&SID:
prompt ===================================================
select 'alter system kill session ''' || s.sid || ',' || s.serial# || ''';'
from   v$process p, v$session s
where  p.program not like 'oracle@v_hostname (%'
and    p.addr = s.paddr
and    p.addr = (select paddr from v$session where sid=&&SID)
order by s.username asc
/

exit

How to use SYS_CONTEXT to display user information

Oracle provides a built-in namespace called USERENV, which describes the current session.

The function SYS_CONTEXT can be used to return the value of parameter associated with the context namespace. You can use this function in both SQL and PL/SQL statements.

You can use the SYS_CONTEXT to retreive userinformation from the namespace USERENV, some examples below:

SELECT SYS_CONTEXT ('userenv','OS_USER') "OS user", 
       SYS_CONTEXT('userenv','CURRENT_SCHEMA') "Current schema",
       SYS_CONTEXT('userenv','IDENTIFICATION_TYPE') "Identification type",
       SYS_CONTEXT('userenv','IP_ADDRESS') "IP",
       SYS_CONTEXT('userenv','HOST') "Host name",
       SYS_CONTEXT('userenv','SID') "SID",
       SYS_CONTEXT('userenv','SERVICE_NAME') "Service"
FROM DUAL;

Executed from a remote session, logged into the database as user SCOTT, the output from the query above will be:

OS user	Current schema	Identification type	IP	Host name	SID	Service
SCOTT	SCOTT	LOCAL	192.168.1.2	MYDOMAIN\MYCLIENT	170	myservice.mydomain.com

To find the serial# of your session, you will need to query the v$session dynamic performance view. you need to have SELECT privileges on the V$SESSION view to use this query, so first, a grant is needed.

SQL> show user
USER is "SYS"

GRANT SELECT ON V_$SESSION TO SCOTT;

Now, as user SCOTT, you can execute the query

SELECT SID, SERIAL#
FROM V$SESSION
WHERE SID=SYS_CONTEXT('USERENV','SID');


Source: Oracle Documentation The 12.2 SYS_CONTEXT documentation can be found here