Sometimes it is very useful to get the DDL of an object of particular database.
We can recreate such a user from one environment to another without even knowing the password.
DDL statements can be obtained by calling the function:
object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT 'DDL')
With each version or release of Oracle Database, new features and bug fixes added in the optimizer, this is good initially but any code modification may include new bugs.
When upgrading from one version of Oracle to a higher is possible that some queries run really bad, this can be solved with a workaround like (if we have migrated from 11gR2 10.2.0.4 for example):
ALTER session SET optimizer_features_enabled='10.2.0.4';
or to make it permanent:
In tables where changes constantly, as a maintenance over the DB, you have to rebuild the indexes B-TREE periodically, for example (the syntax is more complex):
ALTER INDEX SCOTT.PK_EMP REBUILD;
A B-TREE ideal has branches all perfectly balanced, a B-TREE degraded can offer linear time rather than logarithmic search because of strong imbalance.
To determine if an index should be rebuilt:
Each new version of the optimizer is increasingly dependent on the statistics, the quality of which may be sufficient for an earlier version but not the current.
As a rule be analyzed a table (in casacada, ie including indexes) significant modifications are made upon it. For example:
- Insert into .. select …
- Any insert, update and deletion bulk (bulk means that affects more than 20% of all records)
The statistics can be obtained in many ways, some faster than others (partial estimates, complete, etc …) and more or less redologs generation.
For example if we use the DBMS_STATS package (https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS059) specifically the sub-program GATHER_TABLE_STATS (https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS68582), the syntax is very similar to the sub-programs:
There is one thing I particularly like, but it is possible that we find people who say that if the database performance is greatly improved with raw redo in front of a filesystem (ext4 for example).
To find out for sure we can work with RAW mode redologs very easily. Let’s take an example made in Red Hat 4, with a test BDD with 3 redologs of 51 MB each:
[oracle@clu01 DBU]$ ls -l
-rw-r----- 1 oracle oinstall 9748480 Jun 25 20:38 control01.ctl
-rw-r----- 1 oracle oinstall 9748480 Jun 25 20:38 control02.ctl
-rw-r----- 1 oracle oinstall 7061504 Jun 12 11:36 control03.ctl
-rw-r----- 1 oracle oinstall 52429312 Jun 25 11:06 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jun 25 10:39 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Jun 25 10:39 redo03.log
-rw-r----- 1 oracle oinstall 545267712 Jun 25 20:38 sysaux01.dbf
-rw-r----- 1 oracle oinstall 744497152 Jun 25 20:38 system01.dbf
-rw-r----- 1 oracle oinstall 20979712 Jun 23 21:22 temp01.dbf
-rw-r----- 1 oracle oinstall 36708352 Jun 25 20:38 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 Jun 25 20:38 users01.dbf
In 10g and 11g versions of Oracle Database, it has been simplified configuration memory structures for both the SGA and the PGA significantly.
Oracle version >=10g
From 10g, 2 new memory management parameters that enormously simplify this task are introduced.
SGA_TARGET, simply set a value and resizes demand values (if they are = 0):
- Buffer cache (DB_CACHE_SIZE)
- Shared pool (SHARED_POOL_SIZE)
- Large pool (LARGE_POOL_SIZE)
- Java pool (JAVA_POOL_SIZE)
- Streams pool (STREAMS_POOL_SIZE)
Sometimes you need to perform operations on a BD when opened, for example moving datafiles or put tablespaces in read only.
These transactions generally can be performed with the BD in production, but the problem arises when we have a high activity and we have to make many changes. In these cases it is best to restrict access to users, allowing access only DBA (sys or system if we have not created any other).
We can put the database in quiesce mode (still or inactive), only SYS and SYSTEM can create new sessions, the rest remain until they complete the transaction.
To view the status of the database:
BLOBs tables with different tablespaces in the remainder of the table, have a somewhat different treatment.
We will prepare a test environment:
We create two tablespaces with:
CREATE SMALLFILE TABLESPACE "TS_PFIRMA_DATOS" LOGGING DATAFILE '/u01/app/oracle/oradata/PRUEBA/TS_PFIRMA_DATOS.dbf' SIZE 10M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "TS_PFIRMA_BLOB" LOGGING DATAFILE '/u01/app/oracle/oradata/PRUEBA/TS_PFIRMA_BLOB.dbf' SIZE 10M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
Analyzing the log listener, by default “listener.log” in may see messages such as:
23-FEB-2012 08:17:56 * service_update * BD01 * 0
23-FEB-2012 08:19:08 * service_update * BD02 * 0
23-FEB-2012 08:27:56 * service_update * BD01 * 0
23-FEB-2012 08:29:08 * service_update * BD02 * 0
23-FEB-2012 08:37:56 * service_update * BD01 * 0
23-FEB-2012 08:39:08 * service_update * BD02 * 0
23-FEB-2012 08:47:56 * service_update * BD01 * 0
23-FEB-2012 08:49:08 * service_update * BD02 * 0
23-FEB-2012 08:57:59 * service_update * BD01 * 0
23-FEB-2012 08:58:08 * service_update * BD02 * 0
23-FEB-2012 09:03:59 * service_update * BD01 * 0
23-FEB-2012 09:05:08 * service_update * BD02 * 0
23-FEB-2012 09:12:59 * service_update * BD01 * 0
23-FEB-2012 09:14:23 * service_update * BD02 * 0
Surely more than once have deleted records from a table in large quantities, perhaps millions. Once this is done it expected would be a reduction of the space used in the tablespaces, but it does not. Extensions but remain free to use the given table, can not use another table.
After the removal of millions of records, you must compact the table to return to tablespace free space once done we can even reduce the size of the datafiles without meeting the limitation imposed by the HIGH WATER MARK (maximum size reached by the objects within a datafile).
Suppose the table: