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

Oracle rebuild index and status

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):

Source   
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:

Continue reading

Oracle database statistics, gathering, speed and activity redo log

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:

  • truncates
  • 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:

Continue reading

MySQL jobs, scheduling tasks

This time we will see how to perform a scheduled task in MySQL (relatively speaking a job Oracle).

First we have to make sure we have the scheduler started with this we see:

Source   
mysql> SHOW processlist;
+----+------+-----------------+------------+---------+------+-------+------------------+
| Id | User | Host            | db         | Command | Time | State | Info             |
+----+------+-----------------+------------+---------+------+-------+------------------+
|  6 | root | localhost:49987 | assets_pru | Sleep   |  299 |       | NULL             |
|  8 | root | localhost       | assets_pru | Query   |    0 | NULL  | SHOW processlist |
+----+------+-----------------+------------+---------+------+-------+------------------+
2 rows IN SET (0.00 sec)

It is not started, for this we have to change a parameter of the mysqld section in my.cnf:

Continue reading

Oracle quiesce database, only DBA sessions

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:

Continue reading

Compact blob Oracle data, compacting tables with BLOB objects

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:

Source   
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;

Continue reading

Oracle fake index

Recently following the document ID 456468.1, I made a verification of the integrity of the catalog. Anyway, they have appeared a couple of errors indicating a problem with an object identifier:

1703982

This object exist in dba_objects, the query:

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-01591 lock held by in-doubt distributed transaction

It is possible that an application displays in its log an ORA-01591 error, which according to official documentation is due to a 2-phase trasacción that can not be completed.

Such transactions are used, for example, when a commit using tables obtained from a dblink becomes.It verifies that you can make a remote commits, another local and finally performs in the 2 locations atomically.

Well when connecting with a dblink is lost during a transaction, the transaction is in an uncertain state and if another session attempts to modify the objects involved see the ORA-01591 error.

Continue reading

Oracle password expired, Oracle 11g

Since the release of Oracle Database 11g, it has reinforced security by default. This should in principle be positive, but there are some situations where some behaviors may be unpleasant.

The most obvious example can be found in the modification of the profile (PROFILE) DEFAULT, which is applied to any user created by default.

Continue reading