The temporary (sort) segment of a given temporary tablespace is created at the time of the first sort operation, which has to write to disk to free up sort space in memory. The first disk sort (after instance startup) creates a sort segment in the temporary tablespace. Multiple transactions which need a sort on disk can share the same sort segment, however, they cannot share the same extent. The sort segment expands by allocating new extents. The sort extents are not de-allocated while the … [Read more...] about Script/Queries to Monitor Temporary Tablespace Usage in Oracle Database
oracle
How to monitor Undo Tablespace Usage and the Free Space in Oracle Database
This post provides the various queries that can be used to monitor Undo space usage. The Undo Space once allocated won't be deallocated to the OS, by default. But the space can be reused by other transactions once the UNDO_RETENION (or TUNED_UNDORETENTION period) is met. 1. To check the current size of the Undo tablespace: select sum(a.bytes) as undo_size from v$datafile a, v$tablespace b, dba_tablespaces c where c.contents = 'UNDO' and c.status = 'ONLINE' and b.name = c.tablespace_name and … [Read more...] about How to monitor Undo Tablespace Usage and the Free Space in Oracle Database
Script To Get Tablespace Utilization In Oracle Database 12c
This is a script to get the tablespace utilization in Oracle Database 12c. You can use this script to get the tablespace utilization ALLOCATED(MB), USED(MB) and Used parentage for all containers tablespaces. sqlplus -s / as sysdba set pages 80 set lin 120 set echo off set feed off column PCT format 999.99 column tbspce format A30 col container for a30 select substr(f.tablespace_name,1,30) tbspce, round(f.tsbytes/(1024*1024),0) "ALLOCATED(MB)", … [Read more...] about Script To Get Tablespace Utilization In Oracle Database 12c
RMAN-06059 During RMAN Backup of archivelogs ( How to backup archivelogs moved to a different location)
The Problem Archives were moved from their original archivelog destination to a new location. Experienced the following errors when running RMAN backup of the archivelogs: RMAN-06059: expected archived log not found ORA-19625: error identifying file /zklpdata/archive/zkpd/arch_1_44543_466886099.arc ORA-27037: unable to obtain file status IBM AIX RISC System/6000 Error: 2: No such file or directory Additional information: 3 The Solution RMAN is attempting to backup this archivelog … [Read more...] about RMAN-06059 During RMAN Backup of archivelogs ( How to backup archivelogs moved to a different location)
Manage ASM Audit Files with syslog – configure lograte and auditing
The audit file destination directories for an ASM instance can grow to contain a very large number of files if they are not regularly maintained. Having a very large number of files can cause the file system to run out of free disk space or inodes, or can cause Oracle to run very slowly due to file system directory scaling limits, which can have the appearance that the ASM instance is hanging on startup. This post explains how to use the Linux syslog facility to manage ASM auditing records so … [Read more...] about Manage ASM Audit Files with syslog – configure lograte and auditing
How to Delete ASM Disk on Multipath Device in CentOS/RHEL
The post outlines steps to remove ASM disk on multipath device (Labeled as ASMDISK01 as an example in this post) from CentOS/RHEL server. 1. First make sure that ASM disk is no longer used by any disk group, otherwise, you might risk data integrity. The DBA can check this by using asmcmd or by logging into the ASM database. 2. Find out ASM disk mapping: # oracleasm listdisks ASMDISK01 ... # oracleasm querydisk -p ASMDISK01 Disk "ASMDISK01" is a valid ASM disk /dev/sdf: … [Read more...] about How to Delete ASM Disk on Multipath Device in CentOS/RHEL
How to set custom device names using udev in CentOS/RHEL 7
There could be a requirement from client to have custom device names such as /dev/asmdisk01 for an actual device with name /dev/sdb. Follow the steps outlined in this post to set custom device names using udev. In order to create customer name (/dev/asmdisk01p1) for /dev/sdb set udev rules as below: 1. Find UUID of device First, find the the UUID of the device with the scsi_id command as shown below. # /usr/lib/udev/scsi_id -g -u /dev/sdb 1IET_00020002 Save this UUID, as this ID … [Read more...] about How to set custom device names using udev in CentOS/RHEL 7
Oracle database 12c : How to Drop Partition(s)
Dropping a Partition Dropping a partition will discard the rows stored in that partition as a DDL statement. This cannot be rolled back. It executes quickly and uses few system resources (Undo and Redo). You must be the owner of the table or have the DROP ANY TABLE privilege to drop a partition. You cannot drop a partition of a hash-partitioned table. You cannot drop a partition from a reference-partitioned table. If a drop partition is performed on a parent table, this operation cascades to … [Read more...] about Oracle database 12c : How to Drop Partition(s)
How to Drop/Truncate Multiple Partitions in Oracle 12C
The following are the partitioning related enhancements introduced in Oracle 12c: Online partition operations Reference partitioning enhancements Multi-partition maintenance operations Adding multiple partitions Truncating multiple partitions Merging multiple partitions Splitting into multiple partitions Dropping multiple partitions Dropping a Partition Dropping a partition will discard the rows stored in that partition as a DDL statement. This cannot be rolled back. It … [Read more...] about How to Drop/Truncate Multiple Partitions in Oracle 12C
How to Split a Partition Into Multiple Partitions in Oracle 12c
The following are the partitioning related enhancements introduced in Oracle 12c: Online partition operations Reference partitioning enhancements Multi-partition maintenance operations Adding multiple partitions Truncating multiple partitions Merging multiple partitions Splitting into multiple partitions Dropping multiple partitions Splitting Partition into multiple partitions You can split a partition into multiple partitions. In previous releases, you were able to split … [Read more...] about How to Split a Partition Into Multiple Partitions in Oracle 12c