REBUILD INDICES

select  'ALTER INDEX HR.' || index_name || ' REBUILD;' from dba_indexes

where owner='HR'

and table_name='EMPLOYEES'

SELECT owner,table_name,num_rows from dba_tables where owner='HR'

ORACLE CONSUMO CPU X USUARIO

SELECT

   s.username,

   t.sid,

   s.serial#,

   SUM(VALUE/100) as "cpu usage (seconds)"

FROM

   v$session s,

   v$sesstat t,

   v$statname n

WHERE

   t.STATISTIC# = n.STATISTIC#

AND

   NAME like '%CPU used by this session%'

AND

   t.SID = s.SID

AND

   s.status='ACTIVE'

AND

   s.username is not null

GROUP BY username,t.sid,s.serial#

/

PRIVILEGIOS WITH ADMIN OPTION - WITH GRANT OPTION

Privileges that are granted WITH ADMIN OPTION can be passed to other users. Hence, many companies prohibit this option, and others check to ensure that all user ID's are proper.  Here is an example of the usage of the with admin option keyword.

GRANT
   CREATE INDEX
TO
   Robert
WITH ADMIN OPTION;

 

The metadata security audit information for users having the WITH ADMIN OPTION is located in the DBA_SYS_PRIVS view. Read more here.

 

The "with grant" privilege
 

The WITH GRANT option allows you to give the user you are assigning the privilege to grant this privilege to other users.  Only the schema that owns the object can grant privileges to that object unless the WITH GRANT option is included in the command.  Here is an example of the use of the with grant option:

GRANT SELECT ON
   emp
TO
   scott
WITH GRANT OPTION;

CREATE USER ROLE DBA

SQL> grant sysdba to dbo;

SQL> revoke sysdba from dbo;

SQL>SELECT grantee,granted_role from DBA_ROLE_PRIVS where granted_role='DBA';

select * from v$pwfile_users;

SQL> select * from v$pwfile_users;

USERNAME        SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS        TRUE  TRUE  FALSE FALSE FALSE FALSE     0
SYSDG        FALSE FALSE FALSE FALSE TRUE  FALSE     0
SYSBACKUP        FALSE FALSE FALSE TRUE  FALSE FALSE     0
SYSKM        FALSE FALSE FALSE FALSE FALSE TRUE     0

set define on 

prompt 'Digite el nombre de usuario:' "&&USERNAME"

CREATE USER "&USERNAME" PROFILE "DBA" IDENTIFIED BY XXXXXX PASSWORD EXPIRE DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;

GRANT ADMINISTER ANY SQL TUNING SET TO "&USERNAME" ;

GRANT ADMINISTER DATABASE TRIGGER TO "&USERNAME" ;

GRANT DBA TO "&USERNAME" ;

GRANT SYSDBA TO "&USERNAME" ;

GRANT ADMINISTER SQL TUNING SET TO "&USERNAME" ;

GRANT ADVISOR TO "&USERNAME" ;

GRANT ALTER ANY INDEX TO "&USERNAME" ;

GRANT ALTER ANY INDEXTYPE TO "&USERNAME" ;

GRANT ALTER ANY MATERIALIZED VIEW TO "&USERNAME" ;

GRANT ALTER ANY PROCEDURE TO "&USERNAME" ;

GRANT ALTER ANY SEQUENCE TO "&USERNAME" ;

GRANT ALTER ANY TABLE TO "&USERNAME" ;

GRANT ALTER ANY TRIGGER TO "&USERNAME" ;

GRANT ALTER DATABASE TO "&USERNAME" ;

GRANT ALTER SESSION TO "&USERNAME" ;

GRANT ALTER SYSTEM TO "&USERNAME" ;

GRANT ALTER TABLESPACE TO "&USERNAME" ;

GRANT ALTER USER TO "&USERNAME" ;

GRANT ANALYZE ANY TO "&USERNAME" ;

GRANT ANALYZE ANY DICTIONARY TO "&USERNAME" ;

GRANT BACKUP ANY TABLE TO "&USERNAME" ;

GRANT BECOME USER TO "&USERNAME" ;

GRANT CREATE ANY DIRECTORY TO "&USERNAME" ;

GRANT CREATE DATABASE LINK TO "&USERNAME" ;

GRANT CREATE JOB TO "&USERNAME" ;

GRANT CREATE PROFILE TO "&USERNAME" ;

GRANT CREATE ROLE TO "&USERNAME" ;

GRANT CREATE PUBLIC DATABASE LINK TO "&USERNAME" ;

GRANT CREATE PUBLIC SYNONYM TO "&USERNAME" ;

GRANT CREATE SESSION TO "&USERNAME" ;

GRANT EXECUTE ANY PROCEDURE TO "&USERNAME" ;

GRANT EXPORT FULL DATABASE TO "&USERNAME" ;

GRANT FLASHBACK ANY TABLE TO "&USERNAME" ;

GRANT GRANT ANY OBJECT PRIVILEGE TO "&USERNAME" ;

GRANT GRANT ANY PRIVILEGE TO "&USERNAME" ;

GRANT GRANT ANY ROLE TO "&USERNAME" ;

GRANT IMPORT FULL DATABASE TO "&USERNAME" ;

GRANT MANAGE TABLESPACE TO "&USERNAME" ;

GRANT RESTRICTED SESSION TO "&USERNAME" ;

GRANT SELECT ANY DICTIONARY TO "&USERNAME" ;

GRANT SELECT ANY SEQUENCE TO "&USERNAME" ;

GRANT SELECT ANY TABLE TO "&USERNAME" ;

GRANT "DBA" TO "&USERNAME" ;

GRANT "EXECUTE_CATALOG_ROLE" TO "&USERNAME" ;

GRANT "EXP_FULL_DATABASE" TO "&USERNAME" ;

GRANT "GATHER_SYSTEM_STATISTICS" TO "&USERNAME" ;

GRANT "IMP_FULL_DATABASE" TO "&USERNAME" ;

GRANT "MGMT_USER" TO "&USERNAME" ;

GRANT "OEM_ADVISOR" TO "&USERNAME" ;

GRANT "OEM_MONITOR" TO "&USERNAME" ;

GRANT "SELECT_CATALOG_ROLE" TO "&USERNAME" ;

ORACLE RAC COMANDOS BAJAR SUBIR INSTANCIA BD

echo "INSTANCIA DE BASE DE DATOS ORCLRAC"

echo ""

echo ""

srvctl stop instance  -d orclrac -i orclrac1

echo " VALIDAR PROCESO DE BASE DE DATOS "

echo ""

echo ""

ps -fea | grep pmon

echo "INSTANCIA DE BASE DE DATOS ORCLRAC"

echo ""

echo ""

srvctl stop instance  -d orclrac -i orclrac1

srvctl stop instance  -d orclrac -i orclrac2

echo " VALIDAR PROCESO DE BASE DE DATOS "

echo ""

echo ""

ps -fea | grep pmon

echo " VALIDAR STATUS DE BASE DE DATOS "

echo ""

echo ""

srvctl status database -d orclrac

~

BAJAR EL CLUSTER ORACLE RAC

echo ""

echo " BAJAR ORACLE HAS "

echo ""

echo ""

/u01/app/oracle/product/11.2.0/grid_1/bin/crsctl stop cluster 

echo ""

echo "VALIDAR ESTADO PROCESOS DE CLUSTER "

/u01/app/oracle/product/11.2.0/grid_1/bin/crsctl check crs   

CREACIÓN ACL ORACLE ACCESS LIST

CREACIÓN ACL

 

begin
      dbms_network_acl_admin.create_acl (
                                            acl         => 'utl_http.xml',
                                            description => 'HTTP Access',
                                            principal   => 'ORCL',
                                            is_grant    => TRUE,
                                            privilege   => 'connect',
                                            start_date  => null,
                                            end_date    => null
                                         );
commit;
End;
/

begin
      dbms_network_acl_admin.assign_acl (
                                          acl        => 'utl_http.xml',
                                          host       => '10.1.X.X',
                                          lower_port => 1,
                                          upper_port => 10000
                                         );
      commit;
 
end;
/

Borrar historial de Linux

$ history -c

$ man history

ORACLE RESPONSE FILE

setup -record -destinationFile C:\response_files\install_oracle11g.rsp (on Windows)
./runInstaller -record -destinationFile /private/temp/install_oracle11g.rsp (on UNIX)

setup.exe -responseFile <filename> <optional_parameters> (on Windows)
./runInstaller -responseFile <filename> <optional_parameters> (on UNIX)

SESIONES ORACLE FILTRANDO USUARIOS

set line 1000
select
       substr(a.spid,1,9) pid,
       substr(b.sid,1,5) sid,
       substr(b.serial#,1,5) ser#,
       substr(b.machine,1,6) box,
       substr(b.username,1,10) username,
       b.server,
       substr(b.osuser,1,8) os_user,
       substr(b.program,1,30) program
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
order by spid

Solaris LDOMs

Subir servicios de ftp en cada servidor

svcadm enable –rst svc:/network/ftp:default

svcadm disable –st svc:/network/ftp:default

Subir memoria de una ldom o bajarle se realiza desde la fisica

ldm set-mem cantidad total asignar eje 16g nombre del ldom (hostname)

configurar core a ldom

ldm set-core número de cores eje 4 nombre del ldom (hostname)

STORAGE

VERIFICAR EN CADA CONTROLADORA SI HAY ERRORES

USBSTZS3015# fmadm faulty

Allocating VCPU

1. Setting the VCPUs to 6 :

# ldm set-vcpu 6 ldom01

2. To add additional 2 VCPUs :

# ldm add-vcpu 2 ldom01

3. To remove 2 VCPUs :

# ldm remove-vcpu 2 ldom01

Allocating Memory

1. Setting the Memory to 6 GB :

# ldm set-memory 6G ldom01

2. To add additional 2 GB memory :

# ldm add-memory 2G ldom01

3. To remove 2 GB memory :

# ldm remove-memory 2G ldom0

# cat /etc/vfstab

#device         device          mount           FS      fsck    mount   mount

#to mount       to fsck         point           type    pass    at boot options

#

/devices        -               /devices        devfs   -       no      -

/proc           -               /proc           proc    -       no      -

ctfs            -               /system/contract ctfs   -       no      -

objfs           -               /system/object  objfs   -       no      -

sharefs         -               /etc/dfs/sharetab       sharefs -       no      -

fd              -               /dev/fd         fd      -       no      -

swap            -               /tmp            tmpfs   -       yes     -

/dev/zvol/dsk/rpool/swap        -               -               swap    -       no      -

/dev/zvol/dsk/ZSwap/Volswap_01  -               -               swap    -       no      -

root@# zpool list

NAME      SIZE  ALLOC   FREE  CAP  DEDUP  HEALTH  ALTROOT

Appbck   9.94T  6.17T  3.77T  62%  1.00x  ONLINE  -

Appbck1  9.94T  5.62T  4.31T  56%  1.00x  ONLINE  -

Apps      149G  54.7G  94.3G  36%  1.00x  ONLINE  -

ZSwap     104G  96.0G  7.50G  92%  1.00x  ONLINE  -

rpool    99.5G  69.2G  30.3G  69%  1.00x  ONLINE  -

You have new mail in /var/mail/root

SET SERVEROUTPUT ON MOSTRAN MENSAJES PROCEDURES & FUNCIONES

SQL> set serveroutput on;

CREATE FUNCTION dept_salary (dnum NUMBER) RETURN NUMBER IS

   CURSOR emp_cursor IS

      SELECT sal, comm FROM emp WHERE deptno = dnum;

   total_wages    NUMBER(11, 2) := 0;

   counter        NUMBER(10) := 1;

BEGIN

   FOR emp_record IN emp_cursor LOOP

      emp_record.comm := NVL(emp_record.comm, 0);

      total_wages := total_wages + emp_record.sal

         + emp_record.comm;

      DBMS_OUTPUT.PUT_LINE('Loop number = ' || counter || 

         '; Wages = '|| TO_CHAR(total_wages));  /* Debug line */

      counter := counter + 1; /* Increment debug counter */

   END LOOP;

   /* Debug line */

   DBMS_OUTPUT.PUT_LINE('Total wages = ' ||

     TO_CHAR(total_wages)); 

   RETURN total_wages;

END dept_salary;

spool generar archivo plano

CREATE OR REPLACE PROCEDURE export_tables (p_schema IN VARCHAR2)

IS

  v_sql VARCHAR2(4000);

BEGIN

  FOR r_table IN (SELECT table_name FROM all_tables WHERE owner = p_schema) LOOP

    spool '/home/oracle/' || r_table.table_name || '.txt';

    v_sql := 'SELECT * FROM ' || p_schema || '.' || r_table.table_name;

    EXECUTE IMMEDIATE v_sql;

    spool off;

  END LOOP;

END;

/

set linesize 1000

set pagesize 0

set colsep '|'

set trimspool on

set feedback off

set heading off

DECLARE

  v_sql VARCHAR2(4000);

BEGIN

  FOR r_table IN (SELECT table_name FROM all_tables WHERE owner = 'HR') LOOP -- Reemplazar 'HR' con el nombre del esquema deseado

    spool '/home/oracle/' || r_table.table_name || '.txt';

    v_sql := 'SELECT * FROM ' || r_table.table_name;

    EXECUTE IMMEDIATE v_sql;

    spool off;

  END LOOP;

END;

/

set heading on

set feedback on

exit;

//////////////////////////////////////

set line 100

set heading off

set feedback off

spool /home/oracle/empleados.txt

select * from hr.employees

/

spool off

set heading on

set feedback on

exit

alter session set current_schema=HR;







set echo off

set verify off

set heading off

set feedback off

set pagesize 0

spool /home/oracle/create_table.sql

select 'CREATE TABLE ' || TABLE_NAME || '_TEST ' || 'AS SELECT * FROM ' || TABLE_NAME || ' WHERE 1=2;' 

from user_tables

/

spool off

set echo off

set verify off

set feedback off

set pagesize 0

spool /home/oracle/drop_table.sql

select 'DROP TABLE ' || TABLE_NAME || ';'

from user_tables

where table_name like '%_TEST'

/

spool off

set heading on

set feedback on

exit

VER INDICES DE UNA TABLA

select index_owner,table_owner,

index_name,

substr(column_name, 1, 30) column_name

from dba_ind_columns

where index_owner='SYSADM'

and table_name = upper('PSAPMSGPUBHDR')

order by table_owner, index_name;

select owner,table_name,index_name,tablespace_name from dba_indexes

where owner='SYSADM'

and table_name='PSAPMSGPUBHDR'

order by owner,index_name;

CHECK CONSTRAINT ALTER TABLE ADD CONSTRAINT

Using an ALTER TABLE statement

The syntax for creating a check constraint in an ALTER TABLE statement in Oracle is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE];

The DISABLE keyword is optional. If you create a check constraint using the DISABLE keyword, the constraint will be created, but the condition will not be enforced.

Example

ALTER TABLE suppliers
ADD CONSTRAINT check_supplier_name
  CHECK (supplier_name IN ('IBM', 'Microsoft', 'NVIDIA'));

CREATE PUBLIC SYNONYM

SQL> show user;

USER is "SYS"

SQL> create public synonym paises for hr.prueba;

Synonym created.

SQL> select * from paises;

SCRIPT AWK PARA MONITOREAR PROCESOS

SCRIPT AWK PARA MONITOREAR PROCESOS

while true; do df -h; sleep 5; ls -ltr | wc -l; sleep 5; done

linux grep BUSQUEDAS DENTRO DE LOS ARCHIVOS

SCRIPT AWK PARA MONITOREAR PROCESOS

while true; do df -h; sleep 5; ls -ltr | wc -l; sleep 5; done

BUSCAR DENTRO DE UN ARCHIVO

grep -i ".nextval" *.sql > secuencias.txt

BUSCAR UN ARCHIVO DENTRO DEL SISTEMA OPERATIVO

find . -name utlrp.sql -print

UTLRP.SQL RECOMPILAR OBJECT INVALIDOS

SQL> select owner,object_name from dba_objects where status='INVALID';

no rows selected

SQL> 

> 1

[oracle@localhost admin]$ pwd

/u01/app/oracle/product/12.1.0/db_1/rdbms/admin

[oracle@localhost admin]$ cd

[oracle@localhost ~]$ cd $ORACLE_HOME/rdbms/admin

[oracle@localhost admin]$ ls -ltr utlrp.sql

-rw-r--r--. 1 oracle oinstall 3221 Aug 15  2003 utlrp.sql

[oracle@localhost admin]$ 

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jun 2 19:28:24 2020

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> start utlrp.sql

UNDO RETENTION GUARANTEE ESTADISTICAS CONFIGURACION

SQL>show parameter undo_retention;

select to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,
to_char(end_time, 'DD-MON-RR HH24:MI') end_time, tuned_undoretention
from v$undostat order by end_time;

BEGIN_TIME      END_TIME        TUNED_UNDORETENTION
--------------- --------------- -------------------
04-FEB-05 00:01 04-FEB-05 00:11               12100
      ...                                          
07-FEB-05 23:21 07-FEB-05 23:31               86700
07-FEB-05 23:31 07-FEB-05 23:41               86700
07-FEB-05 23:41 07-FEB-05 23:51               86700
07-FEB-05 23:51 07-FEB-05 23:52               86700

576 rows selected.

SQL>alter tablespace undotbs1 retention guarantee

SQL>alter tablespace undotbs1 retention noguarantee

-- Guarantee the minimum threshold is maintained.
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

SELECT tablespace_name, retention FROM dba_tablespaces;

TABLESPACE_NAME                RETENTION
------------------------------ -----------
SYSTEM                         NOT APPLY
UNDOTBS1                       GUARANTEE
SYSAUX                         NOT APPLY
TEMP                           NOT APPLY
USERS                          NOT APPLY

5 rows selected.

-- Switch back to the default mode.
ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;
SQL> select tablespace_name,retention from dba_tablespaces;

TABLESPACE_NAME 	       RETENTION
------------------------------ -----------
SYSTEM			       NOT APPLY
SYSAUX			       NOT APPLY
UNDOTBS1		       NOGUARANTEE
TEMP			       NOT APPLY
USERS			       NOT APPLY
EXAMPLE 		       NOT APPLY
DATOS			       NOT APPLY

7 rows selected.

SQL> alter tablespace undotbs1 retention guarantee;

Tablespace altered.

SQL> select tablespace_name,retention from dba_tablespaces;

TABLESPACE_NAME 	       RETENTION
------------------------------ -----------
SYSTEM			       NOT APPLY
SYSAUX			       NOT APPLY
UNDOTBS1		       GUARANTEE
TEMP			       NOT APPLY
USERS			       NOT APPLY
EXAMPLE 		       NOT APPLY
DATOS			       NOT APPLY

7 rows selected.
SQL> ALTER SYSTEM SET TEMP_UNDO_ENABLED=TRUE;

V$TEMPUNDOSTAT;

SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,
TO_CHAR(END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME,
UNDOTSN, UNDOBLKCNT, MAXCONCURRENCY
FROM V$TEMPUNDOSTAT;

CONSULTAR MEMORIA ORACLE DATABASE

sga_target

sga_max_size

select component,current_size/1024/1024 from v$sga_dynamic_components

memory_target

memory_max_target

select component,current_size/1024/1024 from v$memory_dynamic_components

VER PARAMETROS DE MEMORIA INSTANCIA DE BASE DE DATOS

select name, value, isdefault from v$parameter where name like '%size%'