SAP BASIS,HANA AND ORACLE DBA BLOGS

                                         Monitoring Tablespace Usage in Oracle I often see questions in Oracle newsgroups and forums about monitoring space in tablespaces, and many of those questions are regarding Enterprise Manager 12c and the alerts it generates reporting the percentage of used space. Of course how you monitor free space depends on whether or not the datafiles are autoextensible. Let's look at several ways to monitor free and used space, starting with methods used in older releases. In versions prior to Oracle 10 DBA_TABLESPACES, DBA_DATA_FILES and DBA_FREE_SPACE were used to generate such reports. One script, from MOS, uses these three views to compute the used space percentage: SELECT SUM(s.used_gb)/SUM(f.total_gb)*100 FROM dba_tablespaces t, ( SELECT tablespace_name, SUM(NVL(bytes,0))/(1024*1024*1024) total_gb FROM dba_data_files ...

     Automatic Memory Management (AMM) in Oracle Oracle has made great strides in simplifying memory management over the last few versions of the database. Oracle 9i automated PGA management by introducing  PGA_AGGREGATE_TARGET  parameter. Oracle 10g continued this trend by automating SGA management using the  SGA_TARGET  parameter. Oracle 11g takes this one step further by allowing you to allocate one chunk of memory, which Oracle uses to dynamically manage both the SGA and PGA. Automatic memory management is configured using two new initialization parameters: MEMORY_TARGET : The amount of shared memory available for Oracle to use when dynamically controlling the SGA and PGA. This parameter is dynamic, so the total amount of memory available to Oracle can be increased or decreased, provided it does not exceed the  MEMORY_MAX_TARGET  limit. The default value is "0". MEMORY_MAX_TARGET : This defines the maximum size the  M...

     Locally vs. Dictionary Managed Tablespaces When Oracle allocates space to a segment (like a table or index), a group of contiguous free blocks, called an extent, is added to the segment. Metadata regarding extent allocation and unallocated extents are either stored in the data dictionary, or in the tablespace itself. Tablespaces that record extent allocation in the dictionary, are called dictionary managed tablespaces, and tablespaces that record extent allocation in the tablespace header, are called locally managed tablespaces. SQL> select tablespace_name, extent_management, allocation_type from dba_tablespaces; TABLESPACE_NAME EXTENT_MAN ALLOCATIO ------------------------------ ---------- --------- SYSTEM DICTIONARY USER SYS_UNDOTS LOCAL SYSTEM TEMP LOCAL UNIFORM Dictionary Managed Tablespaces (DMT): Oracle use the data dictionary (tables in the SYS ...