SCRIPT BLOQUEOS INSTANCIA DE BASE DE DATOS

select sid, ctime from v$lock

where block > 0;

select sid, serial#, username

from v$session where sid in (select blocking_session from v$session);

SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name  
FROM V$Locked_Object A, All_Objects B
WHERE A.Object_ID = B.Object_ID

Select

 s.sid SID,

 s.serial# Serial#,

 l.type type,

 ' ' object_name,

 lmode held,

 request request

from

 v$lock l,

 v$session s,

 v$process p

where

 s.sid = l.sid and

 s.username <> ' ' and

 s.paddr = p.addr and

 l.type <> 'TM' and

 (l.type <> 'TX' or l.type = 'TX' and l.lmode <> 6)

union

select

 s.sid SID,

 s.serial# Serial#,

 l.type type,

 object_name object_name,

 lmode held,

 request request

from

 v$lock l,

 v$session s,

 v$process p,

 sys.dba_objects o

where

 s.sid = l.sid and

 o.object_id = l.id1 and

 l.type = 'TM' and

 s.username <> ' ' and

 s.paddr = p.addr

union

select

 s.sid SID,

 s.serial# Serial#,

 l.type type,

 '(Rollback='||rtrim(r.name)||')' object_name,

 lmode held,

 request request

from

 v$lock l,

 v$session s,

 v$process p,

 v$rollname r

where

 s.sid = l.sid and

 l.type = 'TX' and

 l.lmode = 6 and

 trunc(l.id1/65536) = r.usn and

 s.username <> ' ' and

 s.paddr = p.addr

order by 5, 6

/

TRIGGER EVENTOS

List of Database Events

System Events

System events are related to entire instances or schemas, not individual tables or rows. Triggers created on startup and shutdown events must be associated with the database instance. Triggers created on error and suspend events can be associated with either the database instance or a particular schema.

Table 10-2 contains a list of system manager events.

Table 10-2   System Manager Events  

Event	When Fired?	Conditions	Restrictions	Transaction	Attribute Functions
STARTUP	When the database is opened.	None allowed	No database operations allowed in the trigger. Return status ignored.	Starts a separate transaction and commits it after firing the triggers.	ora_sysevent ora_login_user ora_instance_num ora_database_name
SHUTDOWN	Just before the server starts the shutdown of an instance. This lets the cartridge shutdown completely. For abnormal instance shutdown, this event may not be fired.	None allowed	No database operations allowed in the trigger. Return status ignored.	Starts a separate transaction and commits it after firing the triggers.	ora_sysevent ora_login_user ora_instance_num ora_database_name
SERVERERROR	When the error eno occurs. If no condition is given, then this event fires when any error occurs. Does not apply to ORA-1034, ORA-1403, ORA-1422, ORA-1423, and ORA-4030 conditions, because they are not true errors or are too serious to continue processing.	ERRNO = eno	Depends on the error. Return status ignored.	Starts a separate transaction and commits it after firing the triggers.	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_server_error ora_is_ servererror space_error_info

Client Events

Client events are the events related to user logon/logoff, DML, and DDL operations. For example:

CREATE OR REPLACE TRIGGER On_Logon  
  AFTER LOGON  
  ON The_user.Schema  
BEGIN  
  Do_Something;  
END;  

The LOGON and LOGOFF events allow simple conditions on UID( ) and USER( ). All other events allow simple conditions on the type and name of the object, as well as functions like UID( ) and USER( ).

The LOGON event starts a separate transaction and commits it after firing the triggers. All other events fire the triggers in the existing user transaction.

The LOGON and LOGOFF events can operate on any objects. For all other events, the corresponding trigger cannot perform any DDL operations, such as DROP and ALTER, on the object that caused the event to be generated.

The DDL allowed inside these triggers is altering, creating, or dropping a table, creating a trigger, and compile operations.

If an event trigger becomes the target of a DDL operation (such as CREATE TRIGGER), it cannot be fired later during the same transaction

Table 10-3 contains a list of client events.

Table 10-3   Client Events  

Event	When Fired?	Attribute Functions
BEFORE ALTER AFTER ALTER	When a catalog object is altered.	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_type ora_dict_obj_name ora_dict_obj_owner ora_des_encrypted_password (for ALTER USER events) ora_is_alter_column, ora_is_ drop_column (for ALTER TABLE events)
BEFORE DROP AFTER DROP	When a catalog object is dropped.	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_type ora_dict_obj_name ora_dict_obj_owner
BEFORE ANALYZE AFTER ANALYZE	When an analyze statement is issued	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner
BEFORE ASSOCIATE STATISTICS AFTER ASSOCIATE STATISTICS	When an associate statistics statement is issued	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner ora_dict_obj_name_list ora_dict_obj_owner_list
BEFORE AUDIT AFTER AUDIT BEFORE NOAUDIT AFTER NOAUDIT	When an audit or noaudit statement is issued	ora_sysevent ora_login_user ora_instance_num ora_database_name
BEFORE COMMENT AFTER COMMENT	When an object is commented	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner
BEFORE CREATE AFTER CREATE	When a catalog object is created.	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_type ora_dict_obj_name ora_dict_obj_owner ora_is_creating_nested_table (for CREATE TABLE events)
BEFORE DDL AFTER DDL	When most SQL DDL statements are issued. Not fired for ALTER DATABASE, CREATE CONTROLFILE, CREATE DATABASE, and DDL issued through the PL/SQL procedure interface, such as creating an advanced queue.	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner
BEFORE DISASSOCIATE STATISTICS AFTER DISASSOCIATE STATISTICS	When a disassociate statistics statement is issued	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner ora_dict_obj_name_list ora_dict_obj_owner_list
BEFORE GRANT AFTER GRANT	When a grant statement is issued	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner ora_grantee ora_with_grant_option ora_privileges
BEFORE LOGOFF	At the start of a user logoff	ora_sysevent ora_login_user ora_instance_num ora_database_name
AFTER LOGON	After a successful logon of a user.	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_client_ip_address
BEFORE RENAME AFTER RENAME	When a rename statement is issued.	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_owner ora_dict_obj_type
BEFORE REVOKE AFTER REVOKE	When a revoke statement is issued	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner ora_revokee ora_privileges
AFTER SUSPEND	After a SQL statement is suspended because of an out-of-space condition. The trigger should correct the condition so the statement can be resumed.	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_server_error ora_is_servererror space_error_info
BEFORE TRUNCATE AFTER TRUNCATE	When an object is truncated	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner

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%'