Oracle 11g Rebuild indexes on partitions using ALTER

To rebuild indexes on partitions for a user in Oracle 11g run -

Select 'ALTER INDEX '|| index_name ||' rebuild partition ' || PARTITION_NAME ||';' from USER_IND_PARTITIONS;

Then run the output from that command

This is a fix for SQL Error: ORA-08102: index key not found, obj#

Querying Oracle for Fine Grained Auditing settings

An earlier post about Fine Grained Auditing shows how to enable FGA in an Oracle 11gR2 database - http://notastrophe.blogspot.com/2013/01/oracle-11gr2-fine-grained-auditing-fga.html . But how do you tell what FGA policies are applied. Typically to query audit settings you query DBA_STMT_AUDIT_OPTS, DBA_PRIV_AUDIT_OPTS and DBA_OBJ_AUDIT_OPTS, and these are shown in OEM's audit page and can be queried from there, but FGA requires a different query -

SELECT PNAME, PCOL,  FROM SYS.FGA$;

Gives the policy name and affected columns.

SELECT DBUID, CLIENTID, POLICYNAME FROM SYS.FGA_LOG$;

Gives audit trail of user, client id and audited policy. Can also get query details and object schema and name

Query against the regular audit log can be done against DBA_AUDIT_TRAIL and SYS.AUD$

Setting the value of a sequence in pl/sql in Oracle

In Oracle there is no direct way to set the value of a sequence once the sequence has been created. To set the sequence value to a specific value it is necessary to either drop and recreate the sequence, not forgetting all of the options specified in the create statement, or to increment the sequence by some positive or negative value to set the current value to the desired value.

To find out the current value of a sequence you can use the currval function on the sequence, but this will only return successfully if the sequence has already been initialized with a call to nextval. Another way to find the value for the sequence is to query the database as follows:

select sequence_name, last_number from user_sequences;

Below is sample pl/sql to set a sequence to the desired value, it defines two variables to store the current sequence and the desired sequence then alters the increment to the difference of those, increments the sequence then resets the sequence increment to the previous value. -


  procedure set_seq is
    l_max_row NUMBER(38,0) := 0;
    l_cur_seq NUMBER(38,0) := 0;
  begin

  --- Now change the sequence
   select max(id_col) into l_max_row from my_tab;
   select my_seq.nextval into l_cur_seq from dual;
   execute immediate 'alter sequence my_seq increment by ' || (l_max_row-l_cur_seq) || ' nocache';
   select my_seq.nextval into l_cur_seq from dual;
   execute immediate 'alter sequence my_seq increment by 1 nocache';
   select my_seq.nextval into l_cur_seq from dual;

Oracle 11G Shrinking Temp Tablespace

There are a few methods to shrink the temporary tablespace in Oracle 11G. In some cases you can directly resize or shrink the tablespace. If that is not possible then create a new temporary tablespace, reassign the default temp space and drop the old tablespace.


SELECT tablespace_name, file_name, bytes
FROM dba_temp_files WHERE tablespace_name like 'TEMP%';


alter database tempfile '/the/full/path/to/temp01.dbf' resize 256M;
alter database tempfile '/the/full/path/to/temp01.dbf' resize 256M

-- Can give -
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

-- If that fails, on 11G do -
alter tablespace TEMP shrink space keep 256M;

-- If that fails do

CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/datapath/temp2_01.dbf' SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

 ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

CREATE TEMPORARY TABLESPACE temp
2  TEMPFILE '/datapath/temp01.dbf' SIZE 256M REUSE
3  AUTOEXTEND ON NEXT 128M MAXSIZE unlimited
4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

From http://stackoverflow.com/questions/1824572/how-to-shrink-temp-tablespace-in-oracle

Adding more disk to OVM Linux image

To add more disk space to a LINUX Oracle Virtual Machine image, create the disk via the OVM administration console under the specific server that needs access to the disk. Then log in to the server and perform the commands below. This will format the newly created device and make it available to the image and ensure it is mounted at boot time.


root@myserver ~]# fdisk -l /dev/xvdc

Disk /dev/xvdc: 104.8 GB, 104857600000 bytes
255 heads, 63 sectors/track, 12748 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/xvdc doesn't contain a valid partition table

========================
Create the partitions


root@myserver ~]# fdisk /dev/xvdc

The number of cylinders for this disk is set to 12748.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
   (e.g., DOS FDISK, OS/2 FDISK)

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-12748, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-12748, default 12748): 800

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 2
First cylinder (801-12748, default 801):
Using default value 801
Last cylinder or +size or +sizeM or +sizeK (801-12748, default 12748):
Using default value 12748

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@myserver ~]# /sbin/mkfs -t ext3 /dev/xvdc2
mke2fs 1.39 (29-May-2006)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
12009472 inodes, 23993077 blocks
1199653 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
733 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
        4096000, 7962624, 11239424, 20480000, 23887872

Writing inode tables: done                          
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 35 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.
[root@myserver ~]# /sbin/e2label /dev/xvdc2 u02
[root@myserver ~]# mkswap -L SWAP3 /dev/xvdc1
Setting up swapspace version 1, size = 6580187 kB
LABEL=SWAP3, no uuid
[root@myserver ~]# swapon -L SWAP3
[root@myserver ~]# fdisk -l

Disk /dev/xvda: 12.8 GB, 12884901888 bytes
255 heads, 63 sectors/track, 1566 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

    Device Boot      Start         End      Blocks   Id  System
/dev/xvda1   *           1          13      104391   83  Linux
/dev/xvda2              14        1305    10377990   83  Linux
/dev/xvda3            1306        1566     2096482+  82  Linux swap / Solaris

Disk /dev/xvdb: 18.8 GB, 18874368000 bytes
255 heads, 63 sectors/track, 2294 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

    Device Boot      Start         End      Blocks   Id  System
/dev/xvdb1               1         390     3132643+  82  Linux swap / Solaris
/dev/xvdb2             391        2294    15293880   83  Linux

Disk /dev/xvdc: 104.8 GB, 104857600000 bytes
255 heads, 63 sectors/track, 12748 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

    Device Boot      Start         End      Blocks   Id  System
/dev/xvdc1               1         800     6425968+  83  Linux
/dev/xvdc2             801       12748    95972310   83  Linux

[root@myserver ~]# more /proc/swaps
Filename                                Type            Size    Used    Priority
/dev/xvda3                              partition       2096472 4       -1
/dev/xvdb1                              partition       3132632 0       -2
/dev/xvdc1                              partition       6425960 0       -3
[root@myserver ~]# mkdir /u02

Edit /etc/fstab
LABEL=u02        /u02    ext3    defaults    1    2

Oracle Installation create a reference in /etc/oraInst.loc on Linux

The following error installing Oracle 11gR2 on Linux -
You do not have sufficient permissions to access the inventory '/u01/app/oraInventory'

is caused by an old install of 11gR2 pointing to a directory that no longer exists.

The inventory location is kept in /etc/oraInst.loc
altering oraInst.loc or deleting it fixes the problem

Oracle Find all External Table Paths

Use this query to find out the location on disk of all external tables in Oracle:

select a.owner||’.'||a.table_name||’ stored in directory ‘||b.directory_path “EXTERNAL_TABLES”
from dba_external_locations a, dba_directories b
where a.directory_owner=b.owner
and a.directory_name=b.directory_name;

Oracle Gather Table Stats

After a large load, update or delete of data in an Oracle database schema the query optimizer statistics can lead to sub-optimal performance. To gather statistics and improve performance run the following command:


begin
dbms_stats.gather_schema_stats(
     ownname          => 'SCHEMA',
     estimate_percent => dbms_stats.auto_sample_size,
     method_opt       => 'for all columns size repeat',
     degree           => 8
   );
  end;


How many times have I seen this make a huge performance difference after a massive data load!
Make sure this is set as a regular job to keep the statistics up to date

Oracle Set Session Date

To alter the date format of the current connection's session information on an Oracle database run the following command:

alter session set nls_date_format = 'MM/DD/YYYY HH24:MI:SS';

Restart Oracle Database when FRA is full

The flash recovery area on Oracle is set to a fixed maximum size and it is possible for it to become full. When this happens Oracle will stop accepting inserts, updates, queries etc and will stop accepting connections except for SYSDBA. It will also fail to start if it is shutdown. To restart the server follow the steps below, they involve running commands as SYSDBA and editing the init.ora file.

If Oracle fails to start and startup.log shows eg:

ORA-03113: end-of-file on communication channel
Process ID: 9174
Session ID: 191 Serial number: 3

Look in alert_SID.log.

If it shows:

Errors in file /u01/app/oracle/diag/rdbms/t1234/T1234trace/T1234D_ora_10245.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 46773248 bytes disk space from 8388608000 limit
ARCH: Error 19809 Creating archive log file to '/u01/app/oracle/flash_recovery_area/T1234/archivelog/2012_11_13/o1_mf_1_20_%u_.arc'

Then the flash recovery area is full.

To fix on a test instance (ie not production data) run

sqlplus / as SYSDBA

startup mount 

alter database noarchivelog;

Remove all unwanted files from the file system

Then go into rman 

rman TARGET sys/pwd@SID

crosscheck archivelog all;

delete expired archivelog all;
delete force obsolete;

Then sqlplus / as SYSDBA

alter database archivelog;

alter database open;

To set the recovery window - in RMAN
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;

To manage flash recovery area size -
alter system set db_recovery_file_dest_size=xG SCOPE=BOTH;

To Move the FRA create a directory (preferably on a different disk) and
vi <ORACLE_HOME>/dbs/init.ora
update the line:
db_recovery_file_dest='/u02/flash_recovery_area'

(You can also change db_recovery_file_dest in here. Back up the init.ora first)

Shrink Oracle DBF Files

SQL commands for finding block size and size of all tablespace files in an Oracle database. THe script produces the necessary commands to reduce the disk space used in the related dbf files.

select value from v$parameter where name = 'db_block_size'

COLUMN SHRINK_DATAFILES FORMAT A75 WORD_WRAPPED
COLUMN VALUE NEW_VAL BLKSIZE
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_block_size';
SELECT 'ALTER DATABASE DATAFILE ''' || FILE_NAME || ''' RESIZE ' || CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) ||
'M;' SHRINK_DATAFILES FROM DBA_DATA_FILES DBADF,
(SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS
WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+) AND CEIL(BLOCKS*&&BLKSIZE/1024/1024)- CEIL((NVL(HWM,1)* &&BLKSIZE)/1024/1024 ) > 0;

SHRINK_DATAFILES
---------------------------------------------------------------------------
ALTER DATABASE DATAFILE 'F:\ORACLE\ORADATA\MYDBF\RBS01.DBF' RESIZE 25M;

Unlock Locked SYSDBA

Commands for unlocking a locked SYSDBA account in Oracle.


SET ORACLE_SID=orcl
sqlplus / AS SYSDBA
ALTER USER SYSTEM ACCOUNT UNLOCK
or ALTER USER SYSTEM IDENTIFIED BY <NEW PASSWORD> ACCOUNT UNLOCK