Creating or replacing a synonym

Oracle/PLSQL: Synonyms

---

A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.

Creating or replacing a synonym

The syntax for creating a synonym is:

create [or replace] [public] synonym [schema .] synonym_name
for [schema .] object_name [@ dblink];

The or replace phrase allows you to recreate the synonym (if it already exists) without having to issue a DROP synonym command.

The public phrase means that the synonym is a public synonym and is accessible to all users. Remember though that the user must first have the appropriate privileges to the object to use the synonym.

The schema phrase is the appropriate schema. If this phrase is omitted, Oracle assumes that you are referring to your own schema.

The object_name phrase is the name of the object for which you are creating the synonym. It can be one of the following:

table	package
view	materialized view
sequence	java class schema object
stored procedure	user-defined object
function	synonym

For example:

create public synonym suppliers
for app.suppliers;

This first example demonstrates how to create a synonym called suppliers. Now, users of other schemas can reference the table called suppliers without having to prefix the table name with the schema named app. For example:

select * from suppliers;

If this synonym already existed and you wanted to redefine it, you could always use the or replace phrase as follows:

create or replace public synonym suppliers
for app.suppliers;

Dropping a synonym

It is also possible to drop a synonym. The syntax for dropping a synonym is:

drop [public] synonym [schema .] synonym_name [force];

The public phrase allows you to drop a public synonym. If you have specified public, then you don't specify a schema.

The force phrase will force Oracle to drop the synonym even if it has dependencies. It is probably not a good idea to use the force phrase as it can cause invalidation of Oracle objects.

For example:

drop public synonym suppliers;

This drop statement would drop the synonym called suppliers that we defined earlier.

Re: Pregunta de Oracle (email!)

El día 14 de mayo de 2008 11:25, Federico <fede@mail.com> escribió:

Hola Martín, cómo estás? Me surgió una duda sobre Oracle y se me ocurrió molestarte a vos...de todos modos no es mucho.

Tengo una tabla de unos 9 millones de registros (que no está particionada en table spaces), y cuando quiero borrar una cantidad importante de registros el SQL Plus se queda colgado, supongo que en algún momento da un timeout y luego se pone a hacer el rollback del que me contabas el otro día. Te parece que hacer un delete con un rownum pueda funcionar? o puedo llegar a romper algo?

mil gracias,

un abrazo,

Fede

El día 14 de mayo de 2008 23:16, Martin <martin@mail.com> escribió:

Romper no... a lo sumo va a seguir sin responder.

Pueden ocurrir muchas cosas... Se me ocurren otras alternativas pero no te sabria decir cual es la que te conviene mas...

¿Cuantos registros de los 9 millones tenes que borrar? ¿Son muchos mas que los que dejas? ¿Los borras con algun criterio o estas borrando los primeros N millones (la alternativa donde te serviria el ROWNUM)?

Al ser millones de reg la cosa se pone mas complicada...

Puede que esten mal dimensionados los parametros de la base, que los redo o el undo sean chicos para una transaccion tan grande (Oracle se asegura grabando en los redo y undo logs que de caerse la base pueda mantener la atomicidad del DELETE)...

Si la tabla estuviera particionada podrias truncar una particion (una particion no un tablespace, una tabla siempre esta contenida dentro de un unico tablespace). Aunque tendrias que tener cuidado que no tenga indices globales que se invaliden, si los hubiera tenes que hacer el truncate de la particion con la opcion rebuild online...

Ahora la pregunta es tienen pensado particionarla? ¿Pueden particionarla y despues hacer el truncate de la particion? (para particionarla hay que recrear el objeto).

Si no fueran muchos registros te conviene hacer un pequeño PL-SQL que haga commits cada cierta cantidad de registros (ojo porque le estas quitando atomicidad a la operacion y deja de hacerse o deshacerse por completo, solo x bloque PL que definas)...

Podes usar una tabla temporal en modo nologging asi no te caga llenando redo y undo y mandar los registros que queres conservar a la temporal y dropear la original para despues renombrar la temporal a la original... vas a tener que hacer otras cosas en medio como deshabilitar constraints si los hubiera... fijarte que el "manoseo" no haga que te exploten los tablespaces por el espacio que va a ocupar la tabla temporal (si no es un gran porcentaje el que borras pensa que seria como duplicar la tabla)...

No se si escuchaste de una instruccion BULK INSERT (o BULK COLLECT) que son para cargas masivas de datos, tenes una similar para borrar masivamente (que jamás la usé pero imagino que te puede ser de suma utilidad) que se llama...

chan ... chan... jajajaja. BULK DELETE como a esta altura ya te imaginaras por analogia con el anterior.

Aca va un ejemplo que encontre en la WEB:

DECLARE
TYPE num_nt   IS TABLE OF VARCHAR2(20);
 TYPE descr_nt IS TABLE OF VARCHAR2(20);
 
   nt_x_nums      num_nt := num_nt();
   nt_a_descrs    descr_nt := descr_nt();
 
   v_cnt_sqlrow    NUMBER := 0;
SELECT id BULK COLLECT
     INTO  nt_x_nums
      FROM  a;
   
   v_cnt_sqlrow :=SQL%ROWCOUNT;
   -- DBMS_OUTPUT.PUT_LINE(v_cnt_sqlrow);
 
   FORALL i IN nt_x_nums.FIRST..nt_x_nums.LAST
       DELETE FROM t
       WHERE  id= nt_x_nums(i)
      
       RETURNING id BULK COLLECT INTO nt_a_descrs

Espero que te haya servido!

Cualquier cosa preguntame!

Slds,

Martin

PD: Aca va el link de la pagina de donde saqué el ejemplo.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5033906925164.

Tuning & Performance Tips

Algunas paginas utiles con tips para mejorar performance en Oracle:


http://download-west.oracle.com/docs/cd/B10500_01/appdev.920/a96624/12_tune.htm
http://www.devshed.com/c/a/Oracle/Tuning-PLSQL-Code/
http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10807/12_tune.htm

CREATE DATABASE LINK / DROP DATABASE LINK

CREATE DATABASE LINK command


PURPOSE: To create a database link. A database link is an object in the local database that allows you to access objects on a remote database or to mount a secondary database in read-only mode. The remote database can be either an Oracle or a non-Oracle database.

SYNTAX:

CREATE [PUBLIC] DATABASE LINK dblink [CONNECT TO user IDENTIFIED BY password] [USING 'connect_string']

where:

• PUBLIC creates a public database link available to all users. If you omit this option, the database link is private and is available only to you. dblink is the complete or partial name of the database link. For guidelines for naming database links, see the section Referring to Objects in Remote Databases.
• CONNECT TO user IDENTIFIED BY password is the username and password used to connect to the remote database. If you omit this clause, the database link uses the username and password of each user who uses the database link.
• USING specifies either: * the database specification of a remote database * the specification of a secondary database for a read-only mount. For information on specifying remote databases, see the SQL*Net User's Guide for your specific SQL*Net protocol. Read-only mounts are only available in Trusted Oracle and can only be specified for public database links.
  

PREREQUISITES: To create a private database link, you must have CREATE DATABASE LINK system privilege. To create a public database link, you must have CREATE PUBLIC DATABASE LINK system privilege. Also, you must have CREATE SESSION privilege on a remote database. SQL*Net must be installed on both the local and remote databases.

Drop a Database Link.

Syntax:

   DROP [PUBLIC] DATABASE LINK link_name

Restrictions: You cannot drop a database link in another user's schema and you must specify PUBLIC to drop a PUBLIC database link

Sources:
http://www.ss64.com/ora/link_d.html
http://www-eleves-isia.cma.fr/documentation/OracleDoc/CREATE-DATABASE-LINK.html

Refrescar un Snapshot MANUALMENTE

The 'REFRESH' stored procedure of the DBMS_SNAPSHOT package can be used
to manually refresh a snapshot. The syntax is:

EXECUTE dbms_snapshot.refresh (' [ schema.] name ' [, 'refresh_option'])

For example, to do a FAST refresh of the EMP snapshot:

EXECUTE dbms_snapshot.refresh ('emp', ' f ' ) ;

Refresh Options: 'F' or 'f' = FAST refresh
'C' or 'c' = COMPLETE refresh
'?' = DEFAULT refresh option

Startup

Starting your Oracle Database

One of the most common jobs of the database administrator is to startup or shutdown the Oracle database. Typically we hope that database shutdowns will be infrequent for a number of reasons:

* Inconvenience to the user community.

* Anytime you cycle the database, there is a risk that it will not restart.

* It flushes the Oracle memory areas, such as the database buffer cache.

Performance on a restarted database will generally be slow until the database memory areas are "warmed" up.

Why would you shutdown your database? Some reasons include database maintenance:

* Applying a patch or an upgrade.

* Allow for certain types of application maintenance.

* Performing a cold (offline) backup of your database. (We recommend hot backups that allow you to avoid shutting down your database)

* An existing bug in your Oracle software requires you to restart the database on a regular basis.

When the time comes to "bounce" the database (using the shutdown and startup commands), you will use SQL*Plus to issue these commands. Let's look at each of these commands in more detail.

The Oracle Startup Command

You start the Oracle database with the startup command. You must first be logged into an account that has sysdba or sysoper privileges such as the SYS account (we discussed connecting as SYSDBA earlier in this book). Here then is an example of a DBA connecting to his database and starting the instance:

C:\Documents and Settings\Robert>set oracle_sid=booktst

C:\Documents and Settings\Robert>sqlplus "sys as sysdba"

SQL*Plus: Release 10.1.0.2.0 - Production on Mon Feb 21 12:35:48

Enter password: xxxx

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 251658240 bytes

Fixed Size 788368 bytes

Variable Size 145750128 bytes

Database Buffers 104857600 bytes

Redo Buffers 262144 bytes

Database mounted.

Database opened.

In this example from a Windows XP server, we set the ORACLE_SID to the name of the database and we log into SQL*Plus using the "sys as sysdba" login. This gives us the privileges we need to be able to startup the database. Finally, after we enter our password, we issue the startup command to startup the database. Oracle displays its progress as it opens the database, and then returns us to the SQL*Plus prompt once the startup has been completed.

When Oracle is trying to open your database, it goes through three distinct stages, and each of these is listed in the startup output listed previously. These stages are:

* Startup (nomount)

* Mount

* Open

Let's look at these stages in a bit more detail.

The Startup (nomount) Stage

When you issue the startup command, the first thing the database will do is enter the nomount stage. During the nomount stage, Oracle first opens and reads the initialization parameter file (init.ora) to see how the database is configured. For example, the sizes of all of the memory areas in Oracle are defined within the parameter file.

After the parameter file is accessed, the memory areas associated with the database instance are allocated. Also, during the nomount stage, the Oracle background processes are started. Together, we call these processes and the associated allocated memory the Oracle instance. Once the instance has started successfully, the database is considered to be in the nomount stage. If you issue the startup command, then Oracle will automatically move onto the next stage of the startup, the mount stage.

Starting the Oracle Instance (Nomount Stage)

There are some types of Oracle recovery operations that require the database to be in nomount stage. When this is the case, you need to issue a special startup command: startup nomount, as seen in this example:

SQL> startup nomount

The Mount Stage

When the startup command enters the mount stage, it opens and reads the control file. The control file is a binary file that tracks important database information, such as the location of the database datafiles.

In the mount stage, Oracle determines the location of the datafiles, but does not yet open them. Once the datafile locations have been identified, the database is ready to be opened.

Mounting the Database

Some forms of recovery require that the database be opened in mount stage. To put the database in mount stage, use the startup mount command as seen here:

SQL> startup mount

If you have already started the database instance with the startup nomount command, you might change it from the nomount to mount startup stage using the alter database command:

SQL> alter database mount;

The Open Oracle startup Stage

The last startup step for an Oracle database is the open stage. When Oracle opens the database, it accesses all of the datafiles associated with the database. Once it has accessed the database datafiles, Oracle makes sure that all of the database datafiles are consistent.

Opening the Oracle Database

To open the database, you can just use the startup command as seen in this example

SQL> startup

If the database is mounted, you can open it with the alter database open command as seen in this example:

SQL> alter database open;

Opening the Database in Restricted Mode

You can also start the database in restricted mode. Restricted mode will only allow users with special privileges (we will discuss user privileges in a later chapter) to access the database (typically DBA's), even though the database is technically open. We use the startup restrict command to open the database in restricted mode as seen in this example.

SQL> startup restrict

You can take the database in and out of restricted mode with the alter database command as seen in this example:

-- Put the database in restricted session mode.

SQL> alter database enable restricted session;

-- Take the database out of restricted session mode.

SQL> alter database disable restricted session;

Note: Any users connected to the Oracle instance when going into restricted mode will remain connected; they must be manually disconnected from the database by exiting gracefully or by the DBA with the "alter system kill session" command.

Problems during Oracle Startup

The typical DBA life is like that of an airline pilot, "Long moments of boredom followed by small moments of sheer terror", and one place for sheer terror is an error during a database startup.

¿Soy un DBA? - El catálogo del sistema en ORACLE

¿Cómo saber si estoy dentro del ROL de DBAs?

Hace un par de días tuve que verificar si mi nuevo usuario tenía permisos de DBA sobre algunas de las bases de datos. Ahora ¿Cómo podría hacer esta verificación?

La respuesta es fácil, necesito consultar las tablas con prefijo 'DBA_'. Si tengo granteado el acceso a las mismas entonces tengo acceso al catalogo del sistema y por ende los derechos de un DBA. Estas no son las únicas tablas que componen el catalogo, pero para el ejemplo y para descubrir si somos o no DBAs son más que suficientes.

El Catálogo de Oracle

Oracle cuenta con una serie de tabla y vistas que conforman una estructura denominada catálogo. La principal función del catálogo de Oracle es almacenar toda la información de la estructura lógica y física de la base de datos, desde los objetos existentes, la situación de los datafiles, la configuración de los usuarios, etc.

El catálogo sigue un estándar de nomenclatura para que su memorización sea más fácil:

Prefijo/Descripción

DBA_ Objetos con información de administrador. Sólo accesibles por con permisos DBA.

USER_ Objetos con información propia del usuario al que estamos conectado. Accesible desde todos los usuarios. Proporcionan menos información que los objetos DBA_

ALL_ Objetos con información de todos los objetos en base de datos.

V_$ ó V$ Vistas dinámicas sobre datos de rendimiento

Existe un pseudo-usuario llamado PUBLIC el cual tiene acceso a todas las tablas del catálogo público. Si se quiere que todos los usuarios tengan algún tipo de acceso a un objeto, debe darse ese privilegio a PUBLIC y todo el mundo dispondrá de los permisos correspondientes.

El catálogo público son aquellas tablas (USER_ y ALL_) que son accesibles por todos los usuarios. Normalmente dan información sobre los objetos creados en la base de datos.

El catálogo de sistema (DBA_ y V_$) es accesible sólo desde usuarios DBA y contiene tanto información de objetos en base de datos, como información específica de la base de datos en sí (versión, parámetros, procesos ejecutándose…)

Ciertos datos del catálogo de Oracle están continuamente actualizados, como por ejemplo las columnas de una tabla o las vistas dinámicas (V$). De hecho, en las vistas dinámicas, sus datos no se almacenan en disco, sino que son tablas sobre datos contenidos en la memoria del servidor, por lo que almacenan datos actualizados en tiempo real. Algunas de las principales son:

• V$DB_OBJECT_CACHE: contiene información sobre los objetos que están en el caché del SGA.
• V$FILESTAT: contiene el total de lecturas y escrituras físicas sobre un data file de la base de datos.
• V$ROLLSTAT: contienen información acerca de los segmentos de rollback.

Sin embargo hay otros datos que no pueden actualizarse en tiempo real porque penalizarían mucho el rendimiento general de la base de datos, como por ejemplo el número de registros de una tabla, el tamaño de los objetos, etc. Para actualizar el catálogo de este tipo de datos es necesario ejecutar una sentencia especial que se encarga de volcar la información recopilada al catálogo:

ANALYZE [TABLEINDEX] nombre [COMPUTEESTIMATEDELETE] STATISTICS;

La cláusula COMPUTE hace un cálculo exacto de la estadísticas (tarda más en realizarse en ANALYZE), la cláusula ESTIMATE hace una estimación partiendo del anterior valor calculado y de un posible factor de variación y la cláusula DELETE borra las anteriores estadísticas.