User Tools

Site Tools


dba:oracle:basic_oracle_sql_querys

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision
Previous revision
dba:oracle:basic_oracle_sql_querys [2022/02/11 11:12] – ↷ Page moved from oracle:basic_oracle_sql_querys to dba:oracle:basic_oracle_sql_querys dodgerdba:oracle:basic_oracle_sql_querys [2023/07/03 08:37] (current) dodger
Line 1: Line 1:
-<WRAP center round important 60%> +====== [DOCOracle basic queries ======
-This document is being splited into pieces!!!!! \\ +
-see [[oracle:oracle_sql_querys]] +
-</WRAP>+
  
 +====== [SECTION] PRE-basics ======
 +===== NOT owned by oracle =====
 +This is really useful to remove "noise" on queries:
 +<code sql>
 +and NOT owner in (
 +       'SYSTEM','CTXSYS','DBSNMP','EXFSYS','LBACSYS','MDSYS','MGMT_VIEW',
 +       'OLAPSYS','ORDDATA','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN',
 +       'SI_INFORMTN_SCHEMA','SYS','SYSMAN','WK_TEST','WKSYS','WKPROXY',
 +       'WMSYS','XDB','APEX_PUBLIC_USER','DIP','FLOWS_020100','FLOWS_030000',
 +       'FLOWS_040100','FLOWS_010600','FLOWS_FILES','MDDATA','ORACLE_OCM',
 +       'SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL','PERFSTAT',
 +       'SQLTXPLAIN','DMSYS','TSMSYS','WKSYS','APEX_040000','APEX_040200',
 +       'DVSYS','OJVMSYS','GSMADMIN_INTERNAL','APPQOSSYS','DVSYS','DVF',
 +       'AUDSYS','APEX_030200','MGMT_VIEW','ODM','ODM_MTR','TRACESRV','MTMSYS',
 +       'OWBSYS_AUDIT','WEBSYS','WK_PROXY','OSE$HTTP$ADMIN',
 +       'AURORA$JIS$UTILITY$','AURORA$ORB$UNAUTHENTICATED',
 +       'DBMS_PRIVILEGE_CAPTURE','CSMIG','MGDSYS','SDE','DBSFWUSER'
 +    )
 +</code>
  
-====== [DOC] Oracle basic queries ====== 
  
  
-======SECCIÓN : STATUS======+====== [SECTION] STATUS======
 ===== Hora de la bbdd ===== ===== Hora de la bbdd =====
 <code sql>SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL;</code> <code sql>SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL;</code>
Line 147: Line 162:
 </code> </code>
 El event 10046 está sacado de Doc ID 1198753.1 (para rman), se puede usar cualquier otro El event 10046 está sacado de Doc ID 1198753.1 (para rman), se puede usar cualquier otro
-======SECCIÓN : Configuración======+====== [SECTION] Config ======
 =====Nombre de la bd===== =====Nombre de la bd=====
 <code sql>show parameter db_name ;</code> <code sql>show parameter db_name ;</code>
Line 203: Line 218:
  
 ==== Localización de los REDO ==== ==== Localización de los REDO ====
 +
 <code sql> <code sql>
 +col member format a100
 select * from v$logfile ; select * from v$logfile ;
 </code> </code>
Line 221: Line 238:
 =====Parametros===== =====Parametros=====
 Por regla general: Por regla general:
-  * <code sql>show parameter <param_name> in sql*plus</code>+  * <code sql>show parameter <param_name></code> in sql*plus
   * <code sql>select value from v$parameter where name = lower('param_name') </code>   * <code sql>select value from v$parameter where name = lower('param_name') </code>
   * <code sql>select value from gv$parameter where name = lower('param_name') </code>   * <code sql>select value from gv$parameter where name = lower('param_name') </code>
Line 231: Line 248:
 </code> </code>
  
-======SECCIÓN : Storage======+ 
 +===== Standby redo logs ===== 
 +This query  will show ''ORL'' for //ONLINE redo logs// and ''SRL'' for //STANDBY redo logs// 
 + 
 +<code sql> 
 +COL member format 150 
 +select lf.group#,l_type.log_type as type, lf.member 
 +from v$logfile lf 
 +join ( 
 +   select group#,'ORL' as log_type from v$log 
 +   union 
 +   select group#,'SRL' as log_type from v$standby_log) l_type 
 +on lf.group#=l_type.group# 
 +order by lf.group# 
 +
 +</code> 
 + 
 + 
 + 
 +====== [SECTION]  Storage======
  
 =====Listar tablespaces===== =====Listar tablespaces=====
Line 241: Line 277:
 select * from v$dbfile order by 1; select * from v$dbfile order by 1;
 </code> </code>
-=====Ver datafiles (simple)=====+ 
 +===== List datafiles (simple)=====
 <code sql> <code sql>
 col FILE_NAME FORMAT A80; col FILE_NAME FORMAT A80;
Line 259: Line 296:
 </code> </code>
  
-=====Crear (datafile)===== +===== Tablespace creation ===== 
-====Sin ASM====+==== Without ASM====
 <code sql>CREATE SMALLFILE  <code sql>CREATE SMALLFILE 
     TABLESPACE "NOMBRE_DEL_TABLESPACE_DAT"     TABLESPACE "NOMBRE_DEL_TABLESPACE_DAT"
Line 268: Line 305:
     ;     ;
 </code> </code>
-====Con ASM====+==== With ASM====
 <code sql> <code sql>
 CREATE SMALLFILE  CREATE SMALLFILE 
Line 278: Line 315:
 </code> </code>
  
-=====Tablespace DDL=====+===== View Tablespace DDL=====
 <code sql> <code sql>
 select dbms_metadata.get_ddl('TABLESPACE','<TABLESPACE_NAME>') from dual ; select dbms_metadata.get_ddl('TABLESPACE','<TABLESPACE_NAME>') from dual ;
 </code> </code>
  
-=====Añadir espacio===== +===== Add space to Tablespace =====
-"Añadir espacio" al tablespace, hay que tener creado un datafile anteriormente, como en el ejemplo anterior:+
 <code sql>alter tablespace NOMBRE_DEL_TABLESPACE  <code sql>alter tablespace NOMBRE_DEL_TABLESPACE 
       add datafile /ruta/completa/al/datafile.dbf       add datafile /ruta/completa/al/datafile.dbf
Line 290: Line 326:
 </code> </code>
  
-=====Espacio===== + 
-====En ASM====+===== Space Information ===== 
 + 
 +=== ASM Diskgroup information====
 <code sql>select name, total_mb, free_mb from v$asm_diskgroup_stat ;</code> <code sql>select name, total_mb, free_mb from v$asm_diskgroup_stat ;</code>
 o o
Line 302: Line 340:
  
  
-====Tablespace====+====Tablespace information====
 Mi query, tiene en cuenta si el tablespace tiene autoextend o es de tamaño fijo para computar el espacio REAL: Mi query, tiene en cuenta si el tablespace tiene autoextend o es de tamaño fijo para computar el espacio REAL:
 <code sql> <code sql>
Line 375: Line 413:
  
  
-==== Espacio ocupado por tablas, indexes... ====+==== Space used by tables, indexes... ====
  
-Formateo de columnas:+Column format
 <code SQL> <code SQL>
 COL OWNER FORMAT A10; COL OWNER FORMAT A10;
Line 385: Line 423:
 COL SUM_BYTES FORMAT 999999999999999999.9999 ; COL SUM_BYTES FORMAT 999999999999999999.9999 ;
 </code> </code>
-  * Tablas:+  * Tables:
 <code SQL> <code SQL>
 SELECT  SEGMENT_NAME, SELECT  SEGMENT_NAME,
Line 410: Line 448:
 </code> </code>
  
-Ordenado por tamaño: +Sort by size
- +  * Tables:
-  * Tablas:+
 <code sql> <code sql>
 SELECT SEGMENT_NAME, SUM_BYTES, NVL(SUM_BYTES/1024/1024,0.0) TOTAL_MB FROM SELECT SEGMENT_NAME, SUM_BYTES, NVL(SUM_BYTES/1024/1024,0.0) TOTAL_MB FROM
Line 426: Line 463:
 ; ;
 </code> </code>
-  * Indices:+  * Indixes:
 <code sql> <code sql>
 SELECT SEGMENT_NAME, SUM_BYTES, NVL(SUM_BYTES/1024/1024,0.0) TOTAL_MB FROM SELECT SEGMENT_NAME, SUM_BYTES, NVL(SUM_BYTES/1024/1024,0.0) TOTAL_MB FROM
Line 441: Line 478:
 </code> </code>
  
-==== Espacio REAL ocupado por una tabla ==== +==== REAL space taken by a table ==== 
-Basado en:+Based on:
 How To Find The Size Of A Number Of Rows Of A Table (Doc ID 1370050.1): How To Find The Size Of A Number Of Rows Of A Table (Doc ID 1370050.1):
  
Line 470: Line 507:
  
  
-==== Espacio/Tamaño de una tabla con LOB ====+==== Space used by a table with LOB columns ====
 <code sql> <code sql>
 SELECT segment_name, segment_type, bytes / 1024 / 1024 size_in_MB SELECT segment_name, segment_type, bytes / 1024 / 1024 size_in_MB
Line 489: Line 526:
 </code> </code>
  
-===== TEMP tablespace Management ===== +==== Space DELTA of a table ==== 
-==== Espacio en el temporary tablespace ==== +Based on AWR tables: 
-<code SQL+<code sql
-COL TABLESPACE_SIZE FOR 999,999,999,999 + 
-COL ALLOCATED_SPACE FOR 999,999,999,999 +SELECT 
-COL FREE_SPACE FOR 999,999,999,999 +    h.BEGIN_INTERVAL_TIME  || ';' || o.OBJECT_NAME || ';' || s.SPACE_USED_DELTA 
-  +FROM 
-SELECT * +    DBA_OBJECTS o, 
-FROM   dba_temp_free_space +    DBA_HIST_SNAPSHOT h, 
-/ +    ( 
-</code> +        SELECT 
-Resumido en MB: +            SNAP_ID
-<code SQL> +            TS#
-SELECT  +            OBJ#
-   A.tablespace_name tablespace,  +            SPACE_USED_DELTA 
-   D.mb_total+        FROM 
-   SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used+            DBA_HIST_SEG_STAT 
-   D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free +        where SPACE_USED_DELTA >0 
-FROM  +    ) s
-   v$sort_segment A, +    v$tablespace t 
-( +WHERE 
-SELECT  +    s.OBJ# = o.OBJECT_ID 
-   B.name,  +    AND   s.TS# = t.TS
-   C.block_size,  +    and   o.owner = 'TABLE_OWNER' 
-   SUM (C.bytes) / 1024 / 1024 mb_total +    and   o.object_type = 'TABLE' 
-FROM  +    and s.SNAP_ID h.SNAP_ID 
-   v$tablespace B,  +ORDER BY 
-   v$tempfile C +    1 desc 
-WHERE  +; 
-   B.ts#= C.ts+
-GROUP BY  +
-   B.name,  +
-   C.block_size +
-) D +
-WHERE  +
-   A.tablespace_name D.name +
-GROUP by  +
-   A.tablespace_name,  +
-   D.mb_total +
-/+
 </code> </code>
 +
 +===== TEMP tablespace Management =====
  
 ==== TEMP tablespace datafiles ==== ==== TEMP tablespace datafiles ====
Line 543: Line 572:
     dba_temp_files;     dba_temp_files;
 </code> </code>
-==== Crear un nuevo TEMP ====+==== New TEMP ====
 <code sql> <code sql>
 CREATE TEMPORARY TABLESPACE TEMP tempfile '/u02/oradata/DBTEST/datafile/temp_01.dbf' SIZE 1G AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED ; CREATE TEMPORARY TABLESPACE TEMP tempfile '/u02/oradata/DBTEST/datafile/temp_01.dbf' SIZE 1G AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED ;
 </code> </code>
  
-==== Añadir espacio al temp ====+==== Add space to TEMP ====
 <code sql> <code sql>
 ALTER TABLESPACE TEMP ADD TEMPFILE  '+DG_RECO_01' SIZE 2G autoextend on next 512m maxsize unlimited; ALTER TABLESPACE TEMP ADD TEMPFILE  '+DG_RECO_01' SIZE 2G autoextend on next 512m maxsize unlimited;
 </code> </code>
  
-==== Cambiar de TEMP tablespace ====+==== Switch TEMP tablespace ====
 <code sql> <code sql>
 ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP ; ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP ;
Line 595: Line 624:
 AND c.hash_value = a.sql_hash_value AND c.hash_value = a.sql_hash_value
 ORDER BY b.tablespace, b.blocks; ORDER BY b.tablespace, b.blocks;
 +</code>
 +
 +==== Space used by TEMP====
 +<code SQL>
 +COL TABLESPACE_SIZE FOR 999,999,999,999
 +COL ALLOCATED_SPACE FOR 999,999,999,999
 +COL FREE_SPACE FOR 999,999,999,999
 + 
 +SELECT *
 +FROM   dba_temp_free_space
 +/
 +</code>
 +Resumido en MB:
 +<code SQL>
 +SELECT 
 +   A.tablespace_name tablespace, 
 +   D.mb_total,
 +   SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
 +   D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
 +FROM 
 +   v$sort_segment A,
 +(
 +SELECT 
 +   B.name, 
 +   C.block_size, 
 +   SUM (C.bytes) / 1024 / 1024 mb_total
 +FROM 
 +   v$tablespace B, 
 +   v$tempfile C
 +WHERE 
 +   B.ts#= C.ts#
 +GROUP BY 
 +   B.name, 
 +   C.block_size
 +) D
 +WHERE 
 +   A.tablespace_name = D.name
 +GROUP by 
 +   A.tablespace_name, 
 +   D.mb_total
 +/
 </code> </code>
  
Line 600: Line 670:
  
 =====Managing ASM===== =====Managing ASM=====
-====Conexión==== +==== Connection ==== 
-Exportar las variables habituales de oracle:+Export vars:
 <code sql> <code sql>
 export ORACLE_BASE=/opt/ora11g export ORACLE_BASE=/opt/ora11g
Line 612: Line 682:
 O usar el "*.env" por supuesto O usar el "*.env" por supuesto
  
-También podemos conectar al cliente del asm:+Asm cli/wrapper:
 <code sql>asmcmd -p</code> <code sql>asmcmd -p</code>
  
-====Vistas interesantes====+====Main views====
 | <code sql>V$ASM_ALIAS</code> |Displays a row for each alias present in every disk group mounted by the ASM instance.| | <code sql>V$ASM_ALIAS</code> |Displays a row for each alias present in every disk group mounted by the ASM instance.|
 | <code sql>V$ASM_CLIENT</code> |Displays a row for each database instance using a disk group managed by the ASM instance. | | <code sql>V$ASM_CLIENT</code> |Displays a row for each database instance using a disk group managed by the ASM instance. |
Line 626: Line 696:
  
  
-====Listado de discos====+==== Disk list ====
 <code sql> <code sql>
 COL PATH FORMAT A100 COL PATH FORMAT A100
Line 643: Line 713:
 ; ;
 </code> </code>
-====Listado de Diskgroups====+==== Diskgroup list====
 <code sql>COLUMN DISK_NAME FORMAT A25; <code sql>COLUMN DISK_NAME FORMAT A25;
 COLUMN DISKGROUP FORMAT A20; COLUMN DISKGROUP FORMAT A20;
Line 659: Line 729:
 FROM V$ASM_DISK D, V$ASM_DISKGROUP G FROM V$ASM_DISK D, V$ASM_DISKGROUP G
 WHERE D.GROUP_NUMBER=G.GROUP_NUMBER ; WHERE D.GROUP_NUMBER=G.GROUP_NUMBER ;
-CLEAR COLUMNS;+
 </code> </code>
  
  
-====Espacio en los Diskgroups====+==== Diskgroups space====
 <code sql> <code sql>
 COLUMN DISKGROUP FORMAT A20; COLUMN DISKGROUP FORMAT A20;
Line 676: Line 746:
 ; ;
 </code> </code>
-====Información de Diskgroups====+==== Diskgroups Info====
 <code sql> <code sql>
 COL NAME FORMAT A60 COL NAME FORMAT A60
Line 690: Line 760:
 </code> </code>
  
-====Creación de Diskgroup====+==== Diskgroup Creation ====
 Para crear el diskgroup, debemos hacer un : Para crear el diskgroup, debemos hacer un :
 <code sql> <code sql>
Line 706: Line 776:
  'ORCL:PATH_DISK2';  'ORCL:PATH_DISK2';
 </code> </code>
-====Borrar diskgroup====+==== Diskgroup deletion====
 <code sql> <code sql>
 DROP DISKGROUP 'DISKGROUP_NAME' DROP DISKGROUP 'DISKGROUP_NAME'
Line 724: Line 794:
 Flash recovery area Flash recovery area
  
-====Tamaño destinado a la FRA====+==== FRA reserved size ====
 <code sql> <code sql>
 show parameter db_recovery; show parameter db_recovery;
 </code> </code>
  
-==== Espacio Ocupado ====+==== FRA space usage====
 <code sql> select * from v$flash_recovery_area_usage; <code sql> select * from v$flash_recovery_area_usage;
 </code> </code>
Line 742: Line 812:
 </code> </code>
 ===== UNDO ===== ===== UNDO =====
-==== Ver información ====+==== Undo Info ====
 <code sql> <code sql>
 COL NAME FORMAT A20; COL NAME FORMAT A20;
Line 751: Line 821:
 </code> </code>
  
-==== Espacio usado en el UNDO ====+==== UNDO Space usage ====
 Very simple: Very simple:
 <code sql> <code sql>
Line 782: Line 852:
  
  
-==== Sesiones consumiendo UNDO ====+==== UNDO space usage by sessions  ====
 <code sql> <code sql>
 col ROLL_NAME format a30 col ROLL_NAME format a30
Line 833: Line 903:
 </code> </code>
  
-==== Espacio de UNDO necesario==== +==== UNDO space estimation ====
-En el momento de ejecutar la query:+
  
 <code sql> <code sql>
Line 865: Line 934:
  
  
-==== Añadir espacio al UNDO ====+==== Add space to UNDO ====
 <code sql> <code sql>
 ALTER TABLESPACE undotbs_01 ALTER TABLESPACE undotbs_01
Line 872: Line 941:
 </code> </code>
  
-==== Crear un nuevo espacio de UNDO ====+==== New UNDO ====
 <code sql> <code sql>
 CREATE UNDO TABLESPACE undotbs_02 CREATE UNDO TABLESPACE undotbs_02
Line 878: Line 947:
 </code> </code>
  
-==== Cambiar de UNDO ====+==== UNDO switch ====
 <code sql> <code sql>
 ALTER SYSTEM SET undo_tablespace = TESTUNDO SCOPE=BOTH; ALTER SYSTEM SET undo_tablespace = TESTUNDO SCOPE=BOTH;
Line 911: Line 980:
 </code> </code>
  
-==== Umbral de alerta del UNDO ====+==== UNDO alert threshold ====
 Cambiar ''UNDOTBS1'' por el nombre del tablespace de UNDO: Cambiar ''UNDOTBS1'' por el nombre del tablespace de UNDO:
 <code sql> <code sql>
Line 964: Line 1033:
  
  
-=====dNFS=====+===== dNFS =====
  
-==== Listar servidores dNFS ====+==== List dNFS servers ====
 <code sql> <code sql>
 COL RDMAENABLE FORMAT A10 COL RDMAENABLE FORMAT A10
Line 974: Line 1043:
 </code> </code>
  
-==== Listar canales dNFS ====+==== List dNFS channels ====
 <code sql> <code sql>
 col path format a40 col path format a40
Line 983: Line 1052:
  
  
-==== Listar ficheros dNFS ====+==== List dNFS Files ====
 <code sql> <code sql>
 COL FILENAME FORMAT A200 COL FILENAME FORMAT A200
Line 991: Line 1060:
  
  
-======SECCIÓN : Usuarios======+====== [SECTION]  Users ======
 =====Listar===== =====Listar=====
   * Todos:   * Todos:
-<code sql>SELECT USERNAME, PASSWORD, PROFILE from dba_users ;</code>+<code sql>SELECT USERNAME, PASSWORD, PROFILE, ACCOUNT_STATUS from dba_users order by 1 ;</code>
   * Activos:   * Activos:
 <code sql>SELECT USERNAME, PASSWORD, PROFILE, ACCOUNT_STATUS from dba_users WHERE ACCOUNT_STATUS LIKE 'OPEN';</code> <code sql>SELECT USERNAME, PASSWORD, PROFILE, ACCOUNT_STATUS from dba_users WHERE ACCOUNT_STATUS LIKE 'OPEN';</code>
Line 1003: Line 1072:
 col resource_name format a50 ; col resource_name format a50 ;
 col limit format a30 col limit format a30
-SELECT * FROM DBA_PROFILES order by profile, resource_name ;+ 
 +SELECT * 
 +FROM DBA_PROFILES  
 +order by profile, resource_name ;
 </code> </code>
  
Line 1038: Line 1110:
 WHERE GRANTEE='USERNAME'; WHERE GRANTEE='USERNAME';
 </code> </code>
-====Sobre Objetos====+====Sobre Objects====
  
 Mega query para obtener lo que sea: Mega query para obtener lo que sea:
Line 1110: Line 1182:
 </code> </code>
  
-Aparte, los grants sobre objetos se detallan en las vistas:+Aparte, los grants sobre objects se detallan en las vistas:
   * table_privileges   * table_privileges
   * dba_role_privs   * dba_role_privs
Line 1238: Line 1310:
 -- Andy Barry/A -- Andy Barry/A
 -- 20/02/06 -- 20/02/06
--- modified by jholgado+-- modified by dodger
 -- 17/02/2010 -- 17/02/2010
  
Line 1327: Line 1399:
 ALTER USER USERNAME PROFILE LIMIT ; ALTER USER USERNAME PROFILE LIMIT ;
 </code> </code>
-======SECCIÓN Objetos======+====== [SECTION]  Objects====== 
 + 
 +===== UDTUser defined types ===== 
 + 
 +==== List UDTs ==== 
 +<code sql> 
 +col type_name format a40 
 +col type_oid format a40 
 +col SUPERTYPE_NAME format a40 
 +col SUPERTYPE_OWNER format a40 
 +select owner,  
 +  type_name,  
 +  type_oid,  
 +  SUPERTYPE_OWNER,  
 +  SUPERTYPE_NAME  
 +from dba_types 
 +
 +</code> 
 + 
 +==== List UDTs usage in tables ==== 
 +<code sql> 
 +col type_name format a40 
 +col type_oid format a40 
 + 
 +select owner, 
 +  table_name, 
 +  column_name, 
 +  data_type, 
 +  virtual_column 
 +from dba_tab_cols 
 +where data_type in ( 
 +  select 
 +    type_name 
 +  from dba_types 
 +  where NOT owner in ( 
 +         'SYSTEM','CTXSYS','DBSNMP','EXFSYS','LBACSYS','MDSYS','MGMT_VIEW', 
 +         'OLAPSYS','ORDDATA','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN', 
 +         'SI_INFORMTN_SCHEMA','SYS','SYSMAN','WK_TEST','WKSYS','WKPROXY', 
 +         'WMSYS','XDB','APEX_PUBLIC_USER','DIP','FLOWS_020100','FLOWS_030000', 
 +         'FLOWS_040100','FLOWS_010600','FLOWS_FILES','MDDATA','ORACLE_OCM', 
 +         'SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL','PERFSTAT', 
 +         'SQLTXPLAIN','DMSYS','TSMSYS','WKSYS','APEX_040000','APEX_040200', 
 +         'DVSYS','OJVMSYS','GSMADMIN_INTERNAL','APPQOSSYS','DVSYS','DVF', 
 +         'AUDSYS','APEX_030200','MGMT_VIEW','ODM','ODM_MTR','TRACESRV','MTMSYS', 
 +         'OWBSYS_AUDIT','WEBSYS','WK_PROXY','OSE$HTTP$ADMIN', 
 +         'AURORA$JIS$UTILITY$','AURORA$ORB$UNAUTHENTICATED', 
 +         'DBMS_PRIVILEGE_CAPTURE','CSMIG','MGDSYS','SDE','DBSFWUSER' 
 +      ) 
 +  group by type_name 
 +  ) 
 +
 +</code>
  
 ===== Compilar ===== ===== Compilar =====
  
-==== Compilar un objeto ====+Compile an object:
 <code sql>ALTER <OBJECT_TYPE> [schema.]package_name COMPILE ; <code sql>ALTER <OBJECT_TYPE> [schema.]package_name COMPILE ;
 </code> </code>
Line 1340: Line 1463:
 </code> </code>
  
-==== Compilar todos los objetos invalidos ==== +Compile ALL objects (invalid): 
- +See:  
-See: [[oracle:recompile_it_all|Recompile all invalid objects inside database]] +[[dba:oracle:scripts:recompile_it_all|[SCRIPT] Recompile all database objects]]
  
 ===== DBMS_METADATA ===== ===== DBMS_METADATA =====
Line 1374: Line 1496:
 </code> </code>
  
-Lista de nombre de objetos soportados por [[http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_metada.htm#BGBIEDIA|DBMS_METADATA]].+Lista de nombre de objects soportados por [[http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_metada.htm#BGBIEDIA|DBMS_METADATA]].
  
 ==== Obtener metadatos de dependencias ==== ==== Obtener metadatos de dependencias ====
Line 1422: Line 1544:
  
  
-=====Tipos de objetos===== +=====Tipos de objects===== 
-No existe ninguna tabla que determine el nombre de los objetos, se pueden obtener (más o menos así):+No existe ninguna tabla que determine el nombre de los objects, se pueden obtener (más o menos así):
 <code sql> <code sql>
 SELECT OBJECT_TYPE FROM SYS.ALL_OBJECTS GROUP BY OBJECT_TYPE ; SELECT OBJECT_TYPE FROM SYS.ALL_OBJECTS GROUP BY OBJECT_TYPE ;
 </code> </code>
  
-=====Objetos por tipo=====+=====Objects por tipo=====
 Cualquier owner: Cualquier owner:
 <code sql> <code sql>
Line 1454: Line 1576:
 Se pueden listar: Se pueden listar:
 | NOMBRE DE LA TABLA | Descripción | | NOMBRE DE LA TABLA | Descripción |
-| dba_tables | objetos accesibles siendo un dba (todo) |+| dba_tables | objects accesibles siendo un dba (todo) |
 | user_tables | tablas accesibles por el usuario que estamos usando | | user_tables | tablas accesibles por el usuario que estamos usando |
 | all_tables | todo | | all_tables | todo |
Line 1504: Line 1626:
 WHERE OWNER = '<USERNAME>' WHERE OWNER = '<USERNAME>'
 ORDER BY OWNER,TABLE_NAME ; ORDER BY OWNER,TABLE_NAME ;
 +</code>
 +
 +
 +===== Virtual columns =====
 +<code sql>
 +COL OWNER FORMAT A30;
 +COL TABLE_NAME FORMAT A30;
 +COL COLUMN_NAME FORMAT A30;
 +COL virtual_column FORMAT A5;
 +
 +select owner,
 +  table_name,
 +  column_name,
 +  virtual_column
 +from dba_tab_cols
 +where VIRTUAL_COLUMN='YES'
 +and OWNER in ( 'USERNAME1', 'USER2' )
 +order by owner, table_name, column_name
 +/
 +</code>
 +
 +Not owned by system users:
 +<code sql>
 +COL OWNER FORMAT A30;
 +COL TABLE_NAME FORMAT A30;
 +COL COLUMN_NAME FORMAT A30;
 +COL virtual_column FORMAT A5;
 +
 +select owner,
 +  table_name,
 +  column_name,
 +  virtual_column
 +from dba_tab_cols
 +where VIRTUAL_COLUMN='YES'
 +and NOT owner in (
 +       'SYSTEM','CTXSYS','DBSNMP','EXFSYS','LBACSYS','MDSYS','MGMT_VIEW',
 +       'OLAPSYS','ORDDATA','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN',
 +       'SI_INFORMTN_SCHEMA','SYS','SYSMAN','WK_TEST','WKSYS','WKPROXY',
 +       'WMSYS','XDB','APEX_PUBLIC_USER','DIP','FLOWS_020100','FLOWS_030000',
 +       'FLOWS_040100','FLOWS_010600','FLOWS_FILES','MDDATA','ORACLE_OCM',
 +       'SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL','PERFSTAT',
 +       'SQLTXPLAIN','DMSYS','TSMSYS','WKSYS','APEX_040000','APEX_040200',
 +       'DVSYS','OJVMSYS','GSMADMIN_INTERNAL','APPQOSSYS','DVSYS','DVF',
 +       'AUDSYS','APEX_030200','MGMT_VIEW','ODM','ODM_MTR','TRACESRV','MTMSYS',
 +       'OWBSYS_AUDIT','WEBSYS','WK_PROXY','OSE$HTTP$ADMIN',
 +       'AURORA$JIS$UTILITY$','AURORA$ORB$UNAUTHENTICATED',
 +       'DBMS_PRIVILEGE_CAPTURE','CSMIG','MGDSYS','SDE','DBSFWUSER'
 +    )
 +order by owner, table_name, column_name
 +/
 </code> </code>
  
Line 1572: Line 1744:
 / /
 </code> </code>
-=====Dependencias entre objetos=====+=====Dependencias entre objects=====
  
  
Line 1611: Line 1783:
 </code> </code>
  
-Después ejecutar para el objetos que queramos, el procedure:+Después ejecutar para el objects que queramos, el procedure:
 <code sql> <code sql>
 EXEC deptree_fill('object_type', 'object_owner', 'object_name'); EXEC deptree_fill('object_type', 'object_owner', 'object_name');
Line 1759: Line 1931:
 </code> </code>
  
 +==== List VIEW dependencies ====
  
 +<code sql>
 +COL SCHEMA_NAME FORMAT A40 
 +COL REFERENCED_SCHEMA_NAME FORMAT A40 
 +COL REFERENCED_NAME FORMAT A40 
 +col REFERENCED_TYPE  format a30 
 +
 +select owner as schema_name,
 +       name as view_name,
 +       referenced_owner as referenced_schema_name,
 +       referenced_name,
 +       referenced_type
 +from sys.dba_dependencies
 +where type = 'VIEW'
 +-- AND OWNER='OWNER'
 +-- and name LIKE '%VIEW_NAME%'
 +order by owner, name, referenced_name, referenced_owner, referenced_type
 +/
 +</code>
  
 ==== More info ==== ==== More info ====
Line 1767: Line 1958:
  
  
-======SECCIÓN : Sesiones======+====== [SECTION]  Sesiones======
 =====Listar numero de sesiones===== =====Listar numero de sesiones=====
 <code sql>select rpad(c.name||':',11)|| <code sql>select rpad(c.name||':',11)||
Line 2177: Line 2368:
 </code> </code>
 ==== lock de DDLS ==== ==== lock de DDLS ====
-Mirar objetos que se están accediendo:+Mirar objects que se están accediendo:
 <code sql> <code sql>
 col object format a50 ; col object format a50 ;
Line 2259: Line 2450:
 / /
 </code> </code>
-======SECCIÓN : QUERYS======+====== [SECTION]  QUERYS======
  
 ====longest querys==== ====longest querys====
Line 2292: Line 2483:
  
 <code sql> <code sql>
-col target format a30 + 
-col  units format a15 +COL UNITS FORMAT A12 
-col message format a90+COL opname FORMAT A30 
 +COL target FORMAT A30 
 +COL message FORMAT A70 
 +COL SID_SERIAL FORMAT A15 
 select * from select * from
 ( (
Line 2308: Line 2503:
    from    from
         v$session_longops         v$session_longops
-  where start_time>(sysdate-1)  +  order by start_time desc
 ) )
-order by start_time asc +where rownum <=1;
-;+
 </code> </code>
  
Line 2335: Line 2529:
 ; ;
 </code> </code>
-======SECCIÓN : Execution PLANS======+====== [SECTION]  Execution PLANS======
 ===== Hidden options/formatting ===== ===== Hidden options/formatting =====
  
Line 2363: Line 2557:
 y y
 <code sql> <code sql>
-SET LINESIZE 130 +SET LINESIZE 280 
-SET PAGESIZE 0+SET PAGESIZE 999
 SELECT * FROM table(DBMS_XPLAN.DISPLAY); SELECT * FROM table(DBMS_XPLAN.DISPLAY);
 </code> </code>
Line 2422: Line 2616:
   * http://fordba.wordpress.com/tag/dbms_spm-alter_sql_plan_baseline/   * http://fordba.wordpress.com/tag/dbms_spm-alter_sql_plan_baseline/
   * http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_spm.htm   * http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_spm.htm
-======SECCIÓN : Performance======+====== [SECTION]  Performance======
 ===== Hard parse ===== ===== Hard parse =====
   * How to Identify Hard Parse Failures (Doc ID 1353015.1)   * How to Identify Hard Parse Failures (Doc ID 1353015.1)
-  * http://wiki.ciberterminal.net/doku.php?id=killing_hardparse+  * [[dba:oracle:docs:killing_hardparse|[DOC] Kill hardparse]]
 ===== Top 100 querys ===== ===== Top 100 querys =====
 Esta query se basa en las estadísticas guardadas en el repositorio awr. Esta query se basa en las estadísticas guardadas en el repositorio awr.
Line 2570: Line 2764:
 ===== AWR ===== ===== AWR =====
  
-* [[awr|Automated Workload Repository (awr)]]+* [[.docs:awr|Automated Workload Repository (awr)]]
  
  
-===== Paralelismo de objetos =====+===== Paralelismo de objects =====
  
  
Line 2607: Line 2801:
 </code> </code>
  
-======SECCIÓN : Otros======+====== [SECTION]  Otros======
  
 ===== Añadir un redo group ===== ===== Añadir un redo group =====
Line 2790: Line 2984:
  
 ===== PL/SQL ===== ===== PL/SQL =====
 +
 +==== Executing queries with bind variables ====
 +
 +<code sql>
 +declare
 +   c1 NUMBER;
 +   c2 NUMBER;
 +   c3 NUMBER;
 +--    ressult testing_binds%ROWTYPE;
 +--    inttype NUMBER := 4001;
 +   theSQL    VARCHAR2(2000);
 +BEGIN
 +    theSQL := '
 +    SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel OPT_ESTIMATE(@"innerQuery", TABLE, "T_ITS", SCALE_ROWS=21196.12092) */
 +    c1,
 +    c2,
 +    c3
 +FROM
 +    (
 +        SELECT /*+ qb_name("innerQuery") INDEX( "T_ITS" "IT_TBUFFER_SEARCH_UX3_R") */
 +            COUNT(*) AS c1,
 +            4294967295 AS c2,
 +            SUM(
 +                CASE
 +                    WHEN("T_ITS"."INTTYPE" =:b1) THEN 1
 +                    ELSE 0
 +                END
 +            ) AS c3
 +        FROM
 +            ciberterminal."T_IT_TBUFFER_SEARCH" "T_ITS"
 +        WHERE
 +            ( "T_ITS"."INTTYPE" =:b1 )
 +    ) innerquery' ;
 +    execute immediate theSQL into c1, c2, c3 using 4001, 4001;
 +end;
 +/
 +</code>
 +
 +
 +
 ==== very simple loop ==== ==== very simple loop ====
 <code sql> <code sql>
Line 2805: Line 3039:
 / /
 </code> </code>
- 
  
 ==== logging output in pl ==== ==== logging output in pl ====
Line 2899: Line 3132:
        ENABLED        ENABLED
 FROM dba_SCHEDULER_JOBS FROM dba_SCHEDULER_JOBS
-WHERE ENABLED=TRUE+WHERE ENABLED='TRUE'
 ; ;
 </code> </code>
Line 2944: Line 3177:
  
  
-======SECCIÓN : Documentos adicionales======+====== [SECTION]  Documentos adicionales======
  
 =====Auditoria===== =====Auditoria=====
-  * [[audit_mini-howto|Auditing Mini-HOWTO]]+  * [[.howtos:audit_mini-howto|Auditing Mini-HOWTO]]
  
 =====DataGuard===== =====DataGuard=====
  
-  * [[oracle:dataguard_mini-howto#status_querys|Documento]]+  * [[.docs:dataguard_mini-howto#status_querys|Documento]]
  
 =====RMAN===== =====RMAN=====
-  * [[oracle:rman_basic_querys|RMAN basic querys]]+  * [[.docs:rman_basic_querys|RMAN basic querys]]
  
 =====RAC Status===== =====RAC Status=====
-  * [[oracle:cli_tools|CommandLine tools para el control de Oracle]]+  * [[.docs:cli_tools|CommandLine tools para el control de Oracle]]
  
  
dba/oracle/basic_oracle_sql_querys.1644577949.txt.gz · Last modified: 2022/02/11 11:12 by dodger