Cambiar puertos oracle 8080

C:\WINDOWS\system32>sqlplus system@xe

Aqui pide el password:

SQL> Exec DBMS_XDB.SETHTTPPORT(999); 

Group concat en oracle 11g como el de MYSQL

Group concat en oracle 11g como el de MYSQL

SELECT 
    col1,
    LISTAGG(col2, ', ') WITHIN GROUP (ORDER BY col2) "names"
FROM table_x
GROUP BY col1

Consulta lenta en oracle por DBLINK

Hace unos días tenía una consulta q funcionaba perfectamente bien y se conectaba a otra base de datos, sin embargo por motivos de migración se movió de servidor la otra base de datos, y me dejaron un dblink y entonces, se me empezaron a disparar los tiempos de mis consultas, le agregue el siguiente hint a la consulta SELECT /*+DRIVING_SITE(tablaAlias)*/ * y con distinct SELECT /*+DRIVING_SITE(tablaAlias)*/ * DISTINCT , solucionado que es lo que hace? 

Si esta consulta se ejecuta sin el hint, las filas de tablaAlias serán enviados a la ubicación local y la unión serán ejecutados allí. Con el hint, las filas de las tablas Locales se enviarán al sitio remoto y la consulta se ejecutará allí, devolviendo el resultado al sitio local. 

Extender tablespace en oracle

alter database datafile 'ruta/archivo.dbf' autoextend on next 1000M maxsize 20000M;

Como ver los table spaces en Oracle

SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024 TABLESPACE_KB, SUM(fs.bytes)/1024 KBYTES_FREE, MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024
ORDER BY dd.tablespace_name, dd.file_name;

Eliminar claves foraneas

SELECT 'ALTER TABLE '|| TABLE_NAME || ' DROP CONSTRAINT ' || CONSTRAINT_NAME 
|| ' ;'
FROM dba_constraints where constraint_type = 'R' and status = 'ENABLED'
and owner='nombreEsquema';

Eliminar tablas en cascada en oracle

select 'drop table '
||table_name||' cascade constraints;' 
from user_tables;

Obtener la versión de Oracle

select * from v$version

Eliminar todas las tablas de una base de datos Oracle

Creditos y gracias a szaman en http://stackoverflow.com/questions/1690404/how-to-drop-all-user-tables.

BEGIN
   FOR cur_rec IN (SELECT object_name, object_type
                     FROM user_objects
                    WHERE object_type IN
                             ('TABLE',
                              'VIEW',
                              'PACKAGE',
                              'PROCEDURE',
                              'FUNCTION',
                              'SEQUENCE'
                             ))
   LOOP
      BEGIN
         IF cur_rec.object_type = 'TABLE'
         THEN
            EXECUTE IMMEDIATE    'DROP '
                              || cur_rec.object_type
                              || ' "'
                              || cur_rec.object_name
                              || '" CASCADE CONSTRAINTS';
         ELSE
            EXECUTE IMMEDIATE    'DROP '
                              || cur_rec.object_type
                              || ' "'
                              || cur_rec.object_name
                              || '"';
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (   'FAILED: DROP '
                                  || cur_rec.object_type
                                  || ' "'
                                  || cur_rec.object_name
                                  || '"'
                                 );
      END;
   END LOOP;
END;
/

Cambiar puertos de oracle xpress edition en windows

 Muchas veces cuando desarrollas en java el xpress edition ocupa el puerto 8080 el cual ocupa jboss o glassfish, entonces si utilizas varios servidores es mas facil cambiar el puerto de oracle que el de los servidores.

Todo esto es por línea de comandos en Windows.

C:\WINDOWS\system32>sqlplus system@xe

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Express Edition Release

SQL> -- ver qaue estatus tienen
SQL> select dbms_xdb.gethttpport as "HTTP-Port"
            , dbms_xdb.getftpport as "FTP-Port" from dual;

HTTP-Port   FTP-Port
---------- ----------
     8080          0

SQL> -- estable el puerto http y ftp

SQL> begin

 2    dbms_xdb.sethttpport('9999');

 3    dbms_xdb.setftpport('2100');

 4  end;

 5  /



PL/SQL procedure successfully completed.



SQL> select dbms_xdb.gethttpport as "HTTP-Port"

            , dbms_xdb.getftpport as "FTP-Port" from dual;



HTTP-Port   FTP-Port

---------- ----------

       80       2100

SQL> -- Deshabilitar el accesos

SQL> begin

 2    dbms_xdb.sethttpport('0');

 3    dbms_xdb.setftpport('0');

 4  end;

 5  /

Otros objetos en un esquema Oracle

Sirve para obtener el ddl de tablas, vistas y procedimientos.

select DBMS_METADATA.GET_DDL('PROCEDURE' ,OBJECT_NAME)  from SYS.ALL_OBJECTS where upper(OBJECT_TYPE) = upper('PROCEDURE')
AND OWNER='nombre_esquema'
order by OWNER, OBJECT_NAME

select DBMS_METADATA.GET_DDL('TABLE' ,OBJECT_NAME)  from SYS.ALL_OBJECTS where upper(OBJECT_TYPE) = upper('TABLE')
AND OWNER='nombre_esquema'
 order by OWNER, OBJECT_NAME 
 
select DBMS_METADATA.GET_DDL('VIEW' ,OBJECT_NAME)  from SYS.ALL_OBJECTS where upper(OBJECT_TYPE) = upper('VIEW')
AND OWNER='nombre_esquema'
 order by OWNER, OBJECT_NAME

Indices en un esquema

Obtener el ddl de los índices de un esquema.

select DBMS_METADATA.GET_DDL('INDEX' ,OBJECT_NAME)  from SYS.ALL_OBJECTS where upper(OBJECT_TYPE) = upper('INDEX')
AND OWNER='nombre_esquema'
order by OWNER, OBJECT_NAME

Equivalente a sp_helptext en Oracle

Pues en oracle sería así:
 
SELECT TEXT
  FROM ALL_SOURCE
 WHERE NAME = 'nombre_delStore'

Obtener informacion de una tabla en Oracle

Para obtener información de una tabla en oracle se puede ejecutar la siguiente sentencia.

select at.table_name,at.column_id,at.column_name,at.data_type, at.nullable,at.default_length,
(
SELECT 'True'
FROM all_constraints cons, all_cons_columns cols
WHERE cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner and cons.owner='nombre_esquema'
and COLS.TABLE_NAME=at.table_name and COLS.column_name=at.column_name
)Primary_Key
from sys.all_tab_columns at
where owner='nombre_esquema' 

Formatear fecha Oralce

Bueno pues si queremos dar formato a una fecha por ejemplo para realizar una busqueda es muy sencillo

Select  to_char(fecha,'dd/mm/yyyy')  From tabla where to_char(fecha,'dd/mm/yyyy')='27/12/2011'

Util para cuando una base de datos no esta configurada de acuerdo al idioma regional o local

Otro uso de TRUNC Date

Otro uso de trunc es en las fechas es para acortar los formatos de fecha ejemplo

Select * from Tabla Where TRUNC(Fecha)='12/01/2010'

Redondeo hacia arriba y hacia abajo en Oracle

En el post anterior vimos que la diferencia de meses, pero regresa en decimales los decimales representan los días bueno puesn existen dos tipos de redondeos en Oracle hacia arriba y hacia abajo, Round redondea el digito de la derecha 0-4 hacia abajo, y si es 5 a 9 lo redondea hacia arriba por ejemplo 29.5 con round regresa 3 bueno pues truncate es como un redondeo hacia abajo es decir 29.5 con truncate seria 29, bueno menos palabras y más código.

Select  round(8.5) From dual

Select  trunc(8.5) From dual

Diferencia de meses en Oracle

Pues la función months_between realiza todo el trabajo, ejemplo.

Select  months_between(sysdate, to_date ('2010/03/14', 'yyyy/mm/dd') ) From tabla
sysdate es la fecha de termino actual en el sistema