Oracle get ddl of objects

Sometimes it is very useful to get the DDL of an object of particular database.base_datos_objetos

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:

Source   
DBMS_METADATA.GET_DDL (
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')
RETURN CLOB;

Continue reading

Memory management for Oracle 10g and >= Oracle 11g

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)

Continue reading

ORA-13605 sql access advisor, when execute SQL Access Advisor en Oracle 10.2.0.5

Once again he repeats the story, a PathSet installed with the idea of solving problems and new ones appear (this time without document).

This time it was to install the PatchSet 10.2.0.5 on the version 10.2.0.1. The fact is that now when trying to run the SQL Access Advisor, the task is not created but an error in the alert.

 

Continue reading

ORA-07445 [kkecdn()+9776] After apply Pacth Set 10.2.0.5

Recently, to solve a production problem identified as a bug (solventable with Path Set 10.2.0.4) has been necessary to update BD Oracle version 10.2.0.1 to 10.2.0.5 (that matter we are going to last Patch Set).

After installing the Path Set 10.2.0.5 for Linux 64 bits, which went smoothly and update catalogs databases, everything seemed to go perfectly.

At the moment the COMPATIBLE parameter remained with the original version 10.2.0.1.

Problems occurred about a day later when a report stopped working, the alert displays:

Continue reading

Oracle ora-00600 librarycachenotemptyonclose, the famed in Oracle 10g

This error is caused by a bug recognized by Oracle that can be easily found in Metalink [ID 4483084.8], the error indicates that you have tried to stop the database without the Library Cache was empty.

The proposed solution is officially migrate to 11.1.0.6 and further indicates that it does not produce any kind of corruption in the datafiles, suggesting that we can get used to seeing on the alert without worrying.

What they do not say is that this error may generate a typical shutdown immediate, may fail and be the BD in nomount state, this has happened to me in HP-UX systems. If it happens during a cold weekend back, the database will not start because it is, but of course in nomunt state.

Continue reading