Habilitar Consola Enterprise Manager
$ emctl start dbconsole
$ emctl stop dbconsole
$ emctl status dbconsole
Habilitar ISQLPLUS
$ isqlplusctl start
$ isqlplusctl stop
http://maquina:1158/em
http://maquina:5560/isqlplus
http://maquina:5560/isqlplus/dba
Archivos configuración Oracle
listener.ora
sqlnet.ora
tnsnames.ora
Oracle Wall Manager
$ own
Database Configurant Assistant
$dbca
Database upgrade Assitant
$dbua
GENERADOR DE REPORTES
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpti.sql
@$ORACLE_HOME/rdbms/admin/ashrpt.sql
@$ORACLE_HOME/rdbms/admin/addmrpt.sql
CREATE BASELINE
SET SERVEROUTPUT ON
DECLARE
l_return NUMBER;
BEGIN
-- Using procedures.
DBMS_WORKLOAD_REPOSITORY.create_baseline(
start_snap_id => 2490,
end_snap_id => 2491,
baseline_name => 'test1_bl',
expiration => 60);
DBMS_WORKLOAD_REPOSITORY.create_baseline(
start_time => TO_DATE('09-JUL-2008 17:00', 'DD-MON-YYYY HH24:MI'),
end_time => TO_DATE('09-JUL-2008 18:00', 'DD-MON-YYYY HH24:MI'),
baseline_name => 'test2_bl',
expiration => NULL);
-- Using functions.
l_return := DBMS_WORKLOAD_REPOSITORY.create_baseline(
start_snap_id => 2492,
end_snap_id => 2493,
baseline_name => 'test3_bl',
expiration => 30);
DBMS_OUTPUT.put_line('Return: ' || l_return);
l_return := DBMS_WORKLOAD_REPOSITORY.create_baseline(
start_time => TO_DATE('09-JUL-2008 19:00', 'DD-MON-YYYY HH24:MI'),
end_time => TO_DATE('09-JUL-2008 20:00', 'DD-MON-YYYY HH24:MI'),
baseline_name => 'test4_bl',
expiration => NULL);
DBMS_OUTPUT.put_line('Return: ' || l_return);
END;
/
Return: 8
Return: 9
PL/SQL procedure successfully completed.
SQL>
COLUMN baseline_name FORMAT A15
SELECT baseline_id, baseline_name, START_SNAP_ID,
TO_CHAR(start_snap_time, 'DD-MON-YYYY HH24:MI') AS start_snap_time,
END_SNAP_ID,
TO_CHAR(end_snap_time, 'DD-MON-YYYY HH24:MI') AS end_snap_time
FROM dba_hist_baseline
WHERE baseline_type = 'STATIC'
ORDER BY baseline_id;
BASELINE_ID BASELINE_NAME START_SNAP_ID START_SNAP_TIME END_SNAP_ID END_SNAP_TIME
----------- --------------- ------------- ----------------- ----------- -----------------
6 test1_bl 2490 09-JUL-2008 17:00 2491 09-JUL-2008 18:00
7 test2_bl 2490 09-JUL-2008 17:00 2491 09-JUL-2008 18:00
8 test3_bl 2492 09-JUL-2008 19:00 2493 09-JUL-2008 20:00
9 test4_bl 2492 09-JUL-2008 19:00 2493 09-JUL-2008 20:00
4 rows selected.
SQL>
SELECT *
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.select_baseline_details(6));
SELECT *
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.select_baseline_metric('SYSTEM_MOVING_WINDOW'));
BEGIN
DBMS_WORKLOAD_REPOSITORY.rename_baseline(
old_baseline_name => 'test4_bl',
new_baseline_name => 'test5_bl');
END;
/
BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_baseline(baseline_name => 'test1_bl');
DBMS_WORKLOAD_REPOSITORY.drop_baseline(baseline_name => 'test2_bl');
DBMS_WORKLOAD_REPOSITORY.drop_baseline(baseline_name => 'test3_bl');
DBMS_WORKLOAD_REPOSITORY.drop_baseline(baseline_name => 'test5_bl');
END;
/
The Moving Window Baseline
Oracle 11g introduces the concept of a moving window baseline, which is used to calculate metrics for the adaptive thresholds. The window is a view of the AWR data within the retention period. The default size of the window matches the default AWR retention period of 8 days, but it can be set as a subset of this value. Before you can increase the size of the window you must first increase the size of the AWR retention period.
The current AWR retention period can be displayed by querying the RETENTION column of the DBA_HIST_WR_CONTROL view.
SELECT retention FROM dba_hist_wr_control;
RETENTION
---------------------------------------------------------------------------
+00008 00:00:00.0
1 row selected.
SQL>The retention period is altered using the MODIFY_SNAPSHOT_SETTINGS procedure, which accepts a RETENTION parameter in minutes.
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 43200); -- Minutes (= 30 Days).
END;
/
SELECT retention FROM dba_hist_wr_control;
RETENTION
---------------------------------------------------------------------------
+00030 00:00:00.0
1 row selected.
SQL>The current moving window size is displayed by querying the DBA_HIST_BASELINE view.
SELECT moving_window_size
FROM dba_hist_baseline
WHERE baseline_type = 'MOVING_WINDOW';
MOVING_WINDOW_SIZE
------------------
8
1 row selected.
SQL>The size of the moving window baseline is altered using the MODIFY_BASELINE_WINDOW_SIZE procedure, which accepts a WINDOW_SIZE parameter in days.
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size(
window_size => 20);
END;
/
SELECT moving_window_size
FROM dba_hist_baseline
WHERE baseline_type = 'MOVING_WINDOW';
MOVING_WINDOW_SIZE
------------------
20
1 row selected.
SQL>Oracle recommend of window size greater than or equal to 30 days when using adaptive thresholds.
To adjust the retention periods in Enterprise Manager, click on the "Edit" button in the "Automatic Workload Repository" screen (Server > Automatic Workload Repository).
Baseline Templates
Baseline templates allow you to define baselines you would like to capture in the future. Overloads of the CREATE_BASELINE_TEMPLATE procedure define the capture of individual baselines, or repeating baselines. Creating a single baseline template is similar to creating a time-based baseline, except the time is in the future.
BEGIN
DBMS_WORKLOAD_REPOSITORY.create_baseline_template(
start_time => TO_DATE('01-DEC-2008 00:00', 'DD-MON-YYYY HH24:MI'),
end_time => TO_DATE('01-DEC-2008 05:00', 'DD-MON-YYYY HH24:MI'),
baseline_name => '01_dec_008_00_05_bl',
template_name => '01_dec_008_00_05_tp',
expiration => 100);
END;
/Templates for repeating baselines are a little different as they require some basic scheduling information. The START_TIME and END_TIME parameters define when the template is activated and deactivated. The DAY_OF_WEEK, HOUR_IN_DAY and DURATION parameters define the day (MONDAY - SUNDAY or ALL) the baselines are generated on and the start and end point of the baseline. Since the template will generate multiple baselines, the baseline name is derived from the BASELINE_NAME_PREFIX concatenated to the date. The following example creates a template that will run for the next six months, gathering a baseline every Monday between 00:00 and 05:00.
BEGIN
DBMS_WORKLOAD_REPOSITORY.create_baseline_template(
day_of_week => 'MONDAY',
hour_in_day => 0,
duration => 5,
start_time => SYSDATE,
end_time => ADD_MONTHS(SYSDATE, 6),
baseline_name_prefix => 'monday_morning_bl_',
template_name => 'monday_morning_tp',
expiration => NULL);
END;
/Information about baseline templates is displayed using the DBA_HIST_BASELINE_TEMPLATE view.
SELECT template_name,
template_type,
baseline_name_prefix,
start_time,
end_time,
day_of_week,
hour_in_day,
duration,
expiration
FROM dba_hist_baseline_template;
TEMPLATE_NAME TEMPLATE_ BASELINE_NAME_PREFIX START_TIME
------------------------------ --------- ------------------------------ --------------------
END_TIME DAY_OF_WE HOUR_IN_DAY DURATION EXPIRATION
-------------------- --------- ----------- ---------- ----------
01_dec_008_00_05_tp SINGLE 01_dec_008_00_05_bl 01-DEC-2008 00:00:00
01-DEC-2008 05:00:00 100
monday_morning_tp REPEATING monday_morning_bl_ 11-JUL-2008 14:43:36
11-JAN-2009 14:43:36 MONDAY 0 5
2 rows selected.
SQL>Notice the BASELINE_NAME_PREFIX column holds either the prefix or full baseline name depending on the type of baseline being captured.
Baseline templates are dropped using the DROP_BASELINE_TEMPLATE procedure.
BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_baseline_template (template_name => '01_dec_008_00_05_tp');
DBMS_WORKLOAD_REPOSITORY.drop_baseline_template (template_name => 'monday_morning_tp');
END;
/
Consola Enterprise Manager
$oemapp paramete
Ej oemapp dbastudio
TABLAS VISTAS DE LA BASE DE DATOS
USER
ALL
DBA
SQL>select * from database_properties
SQL>select * from global_name
SQL>select * from v$database
SQL>select * from product_component_version
$ sqlplus '/as sysdba'
ARRANQUE AUTOMATICO DE LA BASE DE DATOS
BASE DE DATOS MONTAR
Solo Administradores
$ starup restrict
$ starup nomount o Alter database mount
$ startup mount Alter database open read only
$ startup force
$ startup open
$ startup pfile=init.ora
COMANDOS SQLPLUS
SQL> DEFINE _EDITOR=vi
BAJAR BASE DE DATOS
$ shutdown abort
$ shutdown Transactional
$ shutdown immediate
$ shutdown normal
Privilegios para entrar a la base de datos en modo restrict
grant restrict session to dbo;
SQL> alter system enable restricted session;
System altered.
SQL> alter system disable restricted session;
System altered.
SQL> ALTER USER dbo ACCOUNT LOCK;
SQL> ALTER USER dbo ACCOUNT UNLOCK;
Ver status Listener
$lsnrctl status
LSNRCTL>statust
LSNRCTL>start
Listener el port defaul 1521
Mostar todas las vistas
v$fixed_table
PING AL SERVICIO
tnsping ORCL
PARAMETROS SGA
SQL> show parameter sga_max_size
SQL> show parameter sga_target
CAMBIAR PARAMETROS MEMORIA INSTANCIA DE LA BASE DE DATOS
SQL> alter system set sga_target= value scope= {spfilememoryboth}
SQL> alter system set sga_max_size=1500M scope=spfile;
SQL> alter system set sga_target=1500M scope=spfile;
SQL> alter system set sga_target 992M scope=both;
SQL> alter system set shared_pool_size=0;
SQL> alter system set large_pool_size=0;
SQL> alter system set java_pool_size=0;
SQL> alter system set db_cache_size=0;
SQL> alter system set streams_pool_size=0;
VER SESIONES
SQL> select username,sid,serial# from v$session
SQL> alter system kill session '7,15';
ACTIVAR MODE ARCHIVE LOG
SQL> sqlplus '/as sysdba'
SQL> archive log list;
Modo log de la base de datos Modo de Archivado
Archivado automatico Deshabilitado
Destino del archivo USE_DB_RECOVERY_FILE_DEST
Secuencia de log en linea mas antigua 8
Siguiente secuencia de log para archivar 10
Secuencia de log actual 10
SQL> select log_mode from v$database;
SQL> select * from v$logfile;
SQL> select * from v$controlfile;
SQL> show parameter log_archive_start;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_start boolean FALSE
SQL> show parameter db_recovery;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 2G
COLOCAR INSTANCIA EN MODE ARCHIVELOG
SQL> show parameter log_archive_format;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.dbf
SQL> shutdown immediate
SQL> startup mount
SQL> alter system set log_archive_dest_n=' ' scope = spfile;
SQL> alter database archivelog;
SQL> show parameter log_archive_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_2 string
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
ACTIVAR FLASHBACK INSTANCIA
SQL> alter database flashback on;
SQL> alter database archivelog;
SQL> alter system set log_archive_start = true scope=spfile;
SQL> archive log list;
Modo log de la base de datos Modo de Archivado
Archivado automatico Activado
Destino del archivo USE_DB_RECOVERY_FILE_DEST
Secuencia de log en linea mas antigua 8
Siguiente secuencia de log para archivar 10
Secuencia de log actual 10
SQL> alter system switch logfile;
SQL> alter system archive log stop;
SQL> alter system checkpoint;
SQL> alter system archive log current;
SQL> select log_mode from v$database;
SQL> show parameter log_archive_max;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes integer 2
SQL> select sum(value) from v$sga;
SUM(VALUE)
----------
104857600
VISTAS SGA MEMORIA
V$SGA, V$SGAINFO, V$SGASTAT, V$SGA_DYNAMIC_COMPONENT, V$SGA_DYNAMIC_FREE_MEMORY, V$SGA_RESIZE_OPS, V$SGA_CURRENT_RESIZE_OPS, V$SGA_TARGET_ADVICE
VISTAS ARCHIVED
V$ARCHIVED_LOG, V$ARCHIVED_DEST, V$ARCHIVE_PROCESSES,
V$BACKUP_REDOLOG, V$LOG, V$LOG_HISTORY
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10 G ;
SQL> show parameter db_flashback;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET= 2000;
Sistema modificado.
SQL> show parameter db_block_check;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_checking string FALSE
db_block_checksum string TRUE PARAMETRO QUE VERIFICA LOS BLOQUES EN LOS RED LOGS
CREACION DE CONTROLFILES
Creating a Controlfile: Example
statement, database demo was created with the WE8DEC character set. The example
uses the word path where you would normally insert the path on your system to the
appropriate Oracle Database directories.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "demo" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 32
MAXINSTANCES 1
MAXLOGHISTORY 449
LOGFILE
GROUP 1 '/path/oracle/dbs/t_log1.f' SIZE 500K,
GROUP 2 '/path/oracle/dbs/t_log2.f' SIZE 500K
# STANDBY LOGFILE
DATAFILE
'/path/oracle/dbs/t_db1.f',
'/path/oracle/dbs/dbu19i.dbf',
'/path/oracle/dbs/tbs_11.f',
'/path/oracle/dbs/smundo.dbf',
'/path/oracle/dbs/demo.dbf'
CHARACTER SET WE8DEC
;
CREACION DE DATABASE
The following statement creates a database and fully
Creating a Database: Example
specifies each argument:
CREATE DATABASE sample
CONTROLFILE REUSE
LOGFILE
GROUP 1 ('diskx:log1.log', 'disky:log1.log') SIZE 50K,
GROUP 2 ('diskx:log2.log', 'disky:log2.log') SIZE 50K
MAXLOGFILES 5
MAXLOGHISTORY 100
MAXDATAFILES 10
MAXINSTANCES 2
ARCHIVELOG
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
DATAFILE
'disk1:df1.dbf' AUTOEXTEND ON,
'disk2:df2.dbf' AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE temp_ts
UNDO TABLESPACE undo_ts
SET TIME_ZONE = '+02:00';
CREACION DATABASE LINK
CREATE PUBLIC DATABASE LINK remote
USING 'remote';
This database link allows user hr on the local database to update a table on the
remote database (assuming hr has appropriate privileges):
UPDATE employees@remote
SET salary=salary*1.1
WHERE last_name = 'Baer';
CREACION DIMENSION
This statement was used to create the
Creating a Dimension: Examples
customers_dim dimension in the sample schema sh:
CREATE DIMENSION customers_dim
LEVEL customer IS (customers.cust_id)
LEVEL city IS (customers.cust_city)
LEVEL state IS (customers.cust_state_province)
LEVEL country IS (countries.country_id)
LEVEL subregion IS (countries.country_subregion)
LEVEL region IS (countries.country_region)
HIERARCHY geog_rollup (
customer CHILD OF
city CHILD OF
state CHILD OF
country CHILD OF
subregion CHILD OF
region
JOIN KEY (customers.country_id) REFERENCES country
)
ATTRIBUTE customer DETERMINES
(cust_first_name, cust_last_name, cust_gender,
cust_marital_status, cust_year_of_birth,
cust_income_level, cust_credit_limit)
ATTRIBUTE country DETERMINES (countries.country_name) ;
CREACION DIRECTORIO
CREATE DIRECTORY admin AS 'oracle/admin';
CREACION DE INDICES FUNCIONES
CREATE INDEX ord_customer_ix_demo
ON orders (order_mode)
NOSORT
NOLOGGING;
CREATE INDEX income_ix
ON employees(salary + (salary*commission_pct));
SELECT first_name' 'last_name "Name"
FROM employees
WHERE (salary*commission_pct) + salary > 15000;
CREATE INDEX idx_personnel ON CLUSTER personnel;
CREATE INDEX cost_ix ON sales (amount_sold)
GLOBAL PARTITION BY RANGE (amount_sold)
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2500),
PARTITION p3 VALUES LESS THAN (MAXVALUE));
CREATE INDEX prod_idx ON hash_products(product_id) LOCAL
STORE IN (tbs_01, tbs_02);
CREATE INDEX sales_ix ON composite_sales(time_id, prod_id)
STORAGE (INITIAL 1M MAXEXTENTS UNLIMITED)
LOCAL
(PARTITION q1_1998,
PARTITION q2_1998,
PARTITION q3_1998,
PARTITION q4_1998,
PARTITION q1_1999,
PARTITION q2_1999,
PARTITION q3_1999,
PARTITION q4_1999,
PARTITION q1_2000,
PARTITION q2_2000
(SUBPARTITION pq2001, SUBPARTITION pq2002,
SUBPARTITION pq2003, SUBPARTITION pq2004,
SUBPARTITION pq2005, SUBPARTITION pq2006,
SUBPARTITION pq2007, SUBPARTITION pq2008),
PARTITION q3_2000
(SUBPARTITION c1 TABLESPACE tbs_02,
SUBPARTITION c2 TABLESPACE tbs_02,
SUBPARTITION c3 TABLESPACE tbs_02,
SUBPARTITION c4 TABLESPACE tbs_02,
SUBPARTITION c5 TABLESPACE tbs_02),
PARTITION q4_2000
(SUBPARTITION pq4001 TABLESPACE tbs_03,
SUBPARTITION pq4002 TABLESPACE tbs_03,
SUBPARTITION pq4003 TABLESPACE tbs_03,
SUBPARTITION pq4004 TABLESPACE tbs_03)
);
CREATE BITMAP INDEX product_bm_ix
ON hash_products(list_price)
TABLESPACE tbs_1
LOCAL(PARTITION ix_p1 TABLESPACE tbs_02,
PARTITION ix_p2,
PARTITION ix_p3 TABLESPACE tbs_03,
PARTITION ix_p4,
PARTITION ix_p5 TABLESPACE tbs_04 );
CREATE UNIQUE INDEX nested_tab_ix
ON textdocs_nestedtab(NESTED_TABLE_ID, document_typ);
CREACION LIBRERIA
CREATE LIBRARY ext_lib AS '/OR/lib/ext_lib.so';
/
CREACION VISTAS MATERIALIZADAS
CREATE MATERIALIZED VIEW sales_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT t.calendar_year, p.prod_id,
SUM(s.amount_sold) AS sum_sales
FROM times t, products p, sales s
WHERE t.time_id = s.time_id AND p.prod_id = s.prod_id
GROUP BY t.calendar_year, p.prod_id;
Creating Materialized Join Views: Example The following statement creates and
populates the materialized aggregate view sales_by_month_by_state using tables
in the sample sh schema. The materialized view will be populated with data as soon
as the statement executes successfully. By default, subsequent refreshes will be
accomplished by reexecuting the defining query of the materialized view:
CREATE MATERIALIZED VIEW sales_by_month_by_state
TABLESPACE example
PARALLEL 4
BUILD IMMEDIATE
REFRESH COMPLETE
ENABLE QUERY REWRITE
AS SELECT t.calendar_month_desc, c.cust_state_province,
SUM(s.amount_sold) AS sum_sales
FROM times t, sales s, customers c
WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id
GROUP BY t.calendar_month_desc, c.cust_state_province;
CREATE PROFILE
CREATE PROFILE new_profile
LIMIT PASSWORD_REUSE_MAX 10
PASSWORD_REUSE_TIME 30;
CREATE PROFILE app_user LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL 3000
CONNECT_TIME 45
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL 1000
PRIVATE_SGA 15K
COMPOSITE_LIMIT 5000000;
CREACION DE TRIGGER SHEMA
CREATE OR REPLACE TRIGGER drop_trigger
BEFORE DROP ON DBO.SCHEMA
BEGIN
RAISE_APPLICATION_ERROR (
num => -20000,
msg => 'Cannot drop object');
END;
/
CREATE OR REPLACE TRIGGER drop_trigger_prueba
BEFORE DELETE ON PRUEBA
BEGIN
RAISE_APPLICATION_ERROR (
num => -20000,
msg => 'Cannot delete object');
END;
/
MANEJO DE LOGFILES
SQL> ALTER DATABASE ADD LOGFILE
('/DISK3/log3a.rdo', '/DISK4/log3b.rdo') SIZE 100M
SQL> ALTER DATABASE ADD LOGFILE GROUP 4
('/DISK3/log1.log') SIZE 100M
SQL> ALTER DATABASE ADD LOGFILE MEMBER
'/DISK4/log1b.rdo' TO GROUP 1;
'/DISK4/log2b.rdo' TO GROUP 2;
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
SQL> ALTER DATABASE DROP LOGFILE MEMBER
'/DISK4/log2b.rdo';
SQL> ALTER DATABASE CLEAR LOGFILE '/DISK3/log2a.rdo';
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER DATABASE DATAFILE '/u01/example01.dbf' OFFLINE;
SQL> ALTER DATABASE RENAFILE '/u01/example01.dbf' TO '/u02/example01.dbf';
SQL> ALTER DATABASE DATAFILE '/oradatos/BDDWSGRD/DATOS04.dbf' AUTOEXTEND
ON
VISTAS
V$LOG, V$LOGFILE, V$LOG_HISTORY, V$DATAFILE, DBA_DATAFILES, DBA_TEMP_FILES, DBA_USERS, DBA_TS_QUOTAS, DBA_SEGMENTS, USERS_SEGMENTS, V$DBA_FREE_SPACE, USER_FREE_SPACE
CREACION DE TABLESPACES
SQL> CREATE SMALLFILE
TABLESPACE "DATOS"
LOGGING
DATAFILE '/u01/app/oracle/oradata/POWER/DATOS01.dbf' SIZE 5M,
'/u01/app/oracle/oradata/POWER/DATOS02.dbf' SIZE 5M EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
SQL> DROP TABLESPACE DATOS
SQL> ALTER TABLESPACE DATOS
DEFAUL STORAGE ( INITIAL 2M NEXT 2M MAXEXTENTS 999);
SQL> drop tablespace datos including contents and datafiles;
Tablespace borrado.
SQL> alter database default temporary tablespace temp2
SQL> ALTER TABLESPACE DATOS OFFLINE;
SQL> ALTER TABLESPACE DATOS ONLINE;
SQL> ALTER TABLESPACE DATOS READ ONLY;
SQL> ALTER TABLESPACE DATOS READ WRITE
SQL> ALTER TABLESPACE DATOS RENAME TO DATOS1;
SQL> ALTER DATABASE
DATAFILE '/u01/app/oracle/oradata/POWER/DATOS01.dbf' RESIZE
10M ;
TABLESPACE UNDO
SQL> ALTER TABLESPACE DATOS RETENTION NOGUARANTEE
SQL> ALTER TABLESPACE DATOS RETENTION GUARANTEE
SQL> ALTER TABLESPACE DATOS NOLOGGING;
SQL> ALTER TABLESPACE DATOS LOGGING;
SQL> ALTER TABLESPACE DATOS BEGIN BACKUP;
Tablespace modificado.
SQL> ALTER TABLESPACE DATOS END BACKUP;
Tablespace modificado.
CREACION TABLESPACE BIGFILE
SQL> CREATE BIGFILE TABLESPACE bigtbs
DATAFILE '/u02/oracle/data/bigtbs01.dbf' SIZE 50G
SQL> ALTER TABLESPACE DATOS RESIZE 80G;
SQL> CREATE SMALLFILE TEMPORARY TABLESPACE TEMPO TEMPFILE '/u01/app/oracle/oradata/POWER/TEMPO01.dbf' SIZE 20M, '/u01/app/oracle/oradata/POWER/TEMPO02.dbf' SIZE 20M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M
CREACION TABLESPACE UNDO
SQL> CREATE UNDO TABLESPACE UNDO2 datafile '/u01/app/oracle/oradata/POWER/undo2.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 1G
PACKAGE TABLESPACE
DBMS_SPACE_ADMIN
VISTAS TABLESPACES
DBA_FREE_SPACE
DBA_DATA_FILES
DBA_TABLESPACES
USER_TABLESPACES
V$TABLESPACE
DBA_EXTENTS
USER_EXTENTS
V$DATAFILE
V$DATAFILE_HEADER
SQL> alter system set db_16k_cache_size=16384;
Sistema modificado.
SQL> CREATE SMALLFILE
TABLESPACE "DATOS"
LOGGING
DATAFILE '/u01/app/oracle/oradata/POWER/DATOS01.dbf' SIZE 5M,
'/u01/app/oracle/oradata/POWER/DATOS02.dbf' SIZE 5M EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
BLOCKSIZE 16K
CREACION DE USUARIOS
SQL> CREATE USER DBO IDENTIFIED BY PRUEBA DEFAULT TABLESPACE USERS;
SQL> CREATE USER DBO IDENTIFIED BY PRUEBA
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
SQL> alter user dbo account lock;
SQL> alter user dbo account unlock;
SQL> drop user dbo;
CREACION DE PERFILES
SQL> CREATE PROFILE app_user LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL 3000
CONNECT_TIME 45
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL 1000
PRIVATE_SGA 15K
COMPOSITE_LIMIT 5000000;
SQL> DROP PROFILE APP_USER;
SQL> ALTER PROFILE app_user
LIMIT PASSWORD_REUSE_TIME DEFAULT
PASSWORD_REUSE_MAX UNLIMITED;
SQL> GRANT CREATE SESSION TO DBO;
SQL> GRANT RESTRICTED SESSION TO DBO;
SQL> GRANT CREATE SESSION TO DBO WITH ADMIN OPTION;
CREACION DE ROLES
SQL> CREATE ROLE SALES;
SQL> CREATE ROLE SALES IDENTIFIED BY SALES;
SQL> DROP ROLE SALES;
SQL> GRANT SALES TO DBO;
SQL> ALTER USER DBO DEFAULT ROLE SALES;
SQL> REVOKE SALES FROM DBO;
POLITICAS DE VIOLACIONES ORACLE 10G CUANDO SE INSTALA EL MOTOR
SQL> revoke execute on utl_file from public;
SQL> revoke execute on DBMS_RANDOM from public;
SQL> revoke execute on UTL_HTTP from public;
SQL> revoke execute on UTL_SMTP from public;
SQL> revoke execute on UTL_TCP from public;
SQL> ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS 5;
VISTAS ROLES
DBA_ROLES
DBA_SYS_PRIVS
ROLE_ROLE_PRIVS
DBA_TAB_PRIVS
DBA_COLS_PRIVS
ALL_COLS_PRIVS
USER_COLS_PRIVS
SQL> GRANT CONNECT, RESOURCE TO DBO;
SQL> ALTER USER DBO QUOTA UNLIMITED ON USERS;
USING ORAPWD
orapwd file=
tnsping ORCL
CREACION DE TABLAS
CREATE TABLE employees_demo
( employee_id NUMBER(6)
, first_name VARCHAR2(20)
, last_name VARCHAR2(25)
CONSTRAINT emp_last_name_nn_demo NOT NULL
, email VARCHAR2(25)
CONSTRAINT emp_email_nn_demo NOT NULL
, phone_number VARCHAR2(20)
, hire_date DATE DEFAULT SYSDATE
CONSTRAINT emp_hire_date_nn_demo NOT NULL
, job_id VARCHAR2(10)
CONSTRAINT emp_job_nn_demo NOT NULL
, salary NUMBER(8,2)
CONSTRAINT emp_salary_nn_demo NOT NULL
, commission_pct NUMBER(2,2)
, manager_id NUMBER(6)
, department_id NUMBER(4)
, dn VARCHAR2(300)
, CONSTRAINT emp_salary_min_demo
CHECK (salary > 0)
, CONSTRAINT emp_email_uk_demo
UNIQUE (email)
) ;
CREATE TABLE employees_demo
( employee_id NUMBER(6)
first_name VARCHAR2(20)
, last_name VARCHAR2(25)
CONSTRAINT emp_last_name_nn_demo NOT NULL
, email VARCHAR2(25)
CONSTRAINT emp_email_nn_demo NOT NULL
, phone_number VARCHAR2(20)
, hire_date DATE DEFAULT SYSDATE
CONSTRAINT emp_hire_date_nn_demo NOT NULL
, job_id VARCHAR2(10)
CONSTRAINT emp_job_nn_demo NOT NULL
, salary NUMBER(8,2)
CONSTRAINT emp_salary_nn_demo NOT NULL
, commission_pct NUMBER(2,2)
, manager_id NUMBER(6)
, department_id NUMBER(4)
, dn VARCHAR2(300)
, CONSTRAINT emp_salary_min_demo
CHECK (salary > 0)
, CONSTRAINT emp_email_uk_demo
UNIQUE (email)
)
TABLESPACE USERS
STORAGE (INITIAL 6144
NEXT 6144
MINEXTENTS 1
MAXEXTENTS 5 );
SQL> CREATE DIRECTORY external AS '/u01/app/oracle/external';
Directorio creado.
SQL> ALTER TABLE EMPLOYEE
MOVE TABLESPACE DATOS;
SQL> TRUCATE TABLE EMPLOYEE;
SQL> DROP TABLE EMPLOYER;
SQL> DROP TABLE EMPLOYEE CASCADE CONSTRAINTS;
SQL> DROP TABLE EMPLOYEE PURGE;
SQL> SELECT * FROM RECYCLEBIN;
SQL> SELECT * FROM USER_RECYCLEBIN;
SQL> ALTER TABLE EMPLOYEE DROP COLUMN A;
Tabla modificada.
EJEMPLO TABLA UNUSED
SQL> ALTER TABLE EMPLOYEE SET UNUSED COLUMN A;
Tabla modificada.
SQL> insert into employee values ('PRIMERO','SEGUNDO');
insert into employee values ('PRIMERO','SEGUNDO')
*
ERROR en linea 1:
ORA-00913: demasiados valores
SQL> insert into employee values ('PRIMERO');
1 fila creada.
SQL> commit;
Confirmacion terminada.
SQL> DESC EMPLOYEE;
Nombre ?Nulo? Tipo
----------------------------------------- -------- ----------------------------
B VARCHAR2(30)
SQL> ALTER TABLE EMPLOYEE DROP UNUSED COLUMNS;
Tabla modificada.
SQL> ALTER TABLE EMPLOYEE DROP UNUSED COLUMNS CHECKPOINT 250;
SQL> ALTER TABLE EMPLOYEE DROP CONSTRAIN EMP_ID;
SQL> ALTER TABLE EMPLOYEE MODIFY B ENCRYPT;
SQL> ALTER TABLE EMPLOYEE MODIFY B DECRYPT;
SQL> analyze table DES_TIPO_LINEA compute statistics;
SQL> analyze table DES_TIPO_LINEA VALIDATE STRUCTURE;
SQL> ALTER TABLE EMPLOYEE ENABLE ROW MOVEMENT;
SQL> ALTER TABLE EMPLOYEE SHRINK SPACE;
Tabla modificada.
SQL> ALTER TABLE EMPLOYEE SHRINK SPACE COMPACT;
Tabla modificada.
ADICIONAR COLUMNA A TABLA
SQL> ALTER TABLE EMPLOYEE ADD (BONUS NUMBER(7,2));
Tabla modificada.
SQL> CREATE TABLE PRUEBA AS (SELECT * FROM PER_PERSONA);
Tabla creada.
LABORATORIO DE FLASHBACK DATABASE
SQL> create table prueba as (select * from per_sexo);
Tabla creada.
SQL> select current_scn,flashback_on from v$database;
CURRENT_SCN FLASHBACK_ON
----------- ------------------
853235 YES
SQL> desc prueba;
Nombre ?Nulo? Tipo
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
CODIGO_SEXO NOT NULL NUMBER(10)
DESCRIPCION NOT NULL VARCHAR2(40)
ABREVIATURA NOT NULL VARCHAR2(20)
USUARIO_CREACION NOT NULL VARCHAR2(40)
FECHA_CREACION NOT NULL DATE
USUARIO_MODIFICA NOT NULL VARCHAR2(40)
FECHA_MODIFICA NOT NULL DATE
USUARIO_RED NOT NULL VARCHAR2(40)
MAQUINA NOT NULL VARCHAR2(40)
ESTADO NOT NULL NUMBER(10)
SQL> update prueba set estado = 50
2 ;
2 filas actualizadas.
SQL> commit;
Confirmacion terminada.
SQL> select current_scn,flashback_on from v$database;
CURRENT_SCN FLASHBACK_ON
----------- ------------------
853288 YES
SQL> select current_scn,flashback_on from v$database;
CURRENT_SCN FLASHBACK_ON
----------- ------------------
853291 YES
SQL> connect sys/sys as sysdba
Conectado.
SQL> flashback database to scn 853235;
flashback database to scn 853235
*
ERROR en linea 1:
ORA-38757: La base de datos debe estar montada y no estar abierta para FLASHBACK.
SQL> shutdown immediate;
Base de datos cerrada.
Base de datos desmontada.
Instancia ORACLE cerrada.
SQL>
SQL> startup mount;
Instancia ORACLE iniciada.
Total System Global Area 104857600 bytes
Fixed Size 1217932 bytes
Variable Size 71305844 bytes
Database Buffers 25165824 bytes
Redo Buffers 7168000 bytes
Base de datos montada.
SQL> flashback database to scn 853235;
Flashback terminado.
SQL> alter database open resetlogs;
SQL> connect dbo/password
Conectado.
SQL> select estado from prueba;
ESTADO
----------
1
1
SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;
OLDEST_FLASHBACK_SCN OLDEST_F
-------------------- --------
764664 02/01/08
General Index Examples
The following statement shows how the sample index
Creating an Index: Example
ord_customer_ix on the customer_id column of the sample table oe.orders
was created:
CREATE INDEX ord_customer_ix
ON orders (customer_id);
To create the ord_customer_ix_demo index
Compressing an Index: Example
with the COMPRESS clause, you might issue the following statement:
CREATE INDEX ord_customer_ix_demo
ON orders (customer_id, sales_rep_id)
COMPRESS 1;
The index will compress repeated occurrences of customer_id column values.
If the sample table orders had
Creating an Index in NOLOGGING Mode: Example
been created using a fast parallel load (so all rows were already sorted), you could
issue the following statement to quickly create an index.
/* Unless you first sort the table oe.orders, this example fails
because you cannot specify NOSORT unless the base table is
already sorted.
*/
CREATE INDEX ord_customer_ix_demo
ON orders (order_mode)
NOSORT
NOLOGGING;
To create an index for the personnel cluster,
Creating a Cluster Index: Example
which was created in "Creating a Cluster: Example" on page 14-7, issue the following
statement:
CREATE INDEX idx_personnel ON CLUSTER personnel;
Creating an Index on an XMLType Table: Example The following example creates an
index on the area element of the xwarehouses table (created in "XMLType Table
Examples" on page 16-55):
CREATE INDEX area_index ON xwarehouses e
(EXTRACTVALUE(VALUE(e),'/Warehouse/Area'));
Creating a Function-Based Index: Example The following statement creates a
function-based index on the employees table based on an uppercase evaluation of the
last_name column:
CREATE INDEX upper_ix ON employees (UPPER(last_name));
Partitioned Index Examples
The following statement
Creating a Range-Partitioned Global Index: Example
creates a global prefixed index cost_ix on the sample table sh.sales with three
partitions that divide the range of costs into three groups:
CREATE INDEX cost_ix ON sales (amount_sold)
GLOBAL PARTITION BY RANGE (amount_sold)
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2500),
PARTITION p3 VALUES LESS THAN (MAXVALUE));
Creating a Hash-Partitioned Global Index: Example The following statement creates
a hash-partitioned global index cust_last_name_ix on the sample table
sh.customers with four partitions:
CREATE INDEX cust_last_name_ix ON customers (cust_last_name)
GLOBAL PARTITION BY HASH (cust_last_name)
PARTITIONS 4;
The following statement
Creating an Index on a Hash-Partitioned Table: Example
creates a local index on the product_id column of the hash_products partitioned
table (which was created in "Hash Partitioning Example" on page 16-57). The STORE
IN clause immediately following LOCAL indicates that hash_products is hash
partitioned. Oracle Database will distribute the hash partitions between the tbs1 and
tbs2 tablespaces:
CREATE INDEX prod_idx ON hash_products(product_id) LOCAL
STORE IN (tbs_01, tbs_02);
Creating an Index on a Composite-Partitioned Table: Example The following
statement creates a local index on the composite_sales table, which was created in
"Composite-Partitioned Table Examples" on page 16-58. The STORAGE clause specifies
default storage attributes for the index. However, this default is overridden for the five
subpartitions of partitions q3_2000 and q4_2000, because separate TABLESPACE
storage is specified.
The creator of the index must have quota on the tablespaces specified. See CREATE
TABLESPACE on page 16-61 for examples that create tablespaces tbs_1 and tbs_2.
CREATE INDEX sales_ix ON composite_sales(time_id, prod_id)
STORAGE (INITIAL 1M MAXEXTENTS UNLIMITED)
LOCAL
(PARTITION q1_1998,
PARTITION q2_1998,
PARTITION q3_1998,
PARTITION q4_1998,
PARTITION q1_1999,
PARTITION q2_1999,
ARTITION q3_1999,
PARTITION q4_1999,
PARTITION q1_2000,
PARTITION q2_2000
(SUBPARTITION pq2001, SUBPARTITION pq2002,
SUBPARTITION pq2003, SUBPARTITION pq2004,
SUBPARTITION pq2005, SUBPARTITION pq2006,
SUBPARTITION pq2007, SUBPARTITION pq2008),
PARTITION q3_2000
(SUBPARTITION c1 TABLESPACE tbs_02,
SUBPARTITION c2 TABLESPACE tbs_02,
SUBPARTITION c3 TABLESPACE tbs_02,
SUBPARTITION c4 TABLESPACE tbs_02,
SUBPARTITION c5 TABLESPACE tbs_02),
PARTITION q4_2000
(SUBPARTITION pq4001 TABLESPACE tbs_03,
SUBPARTITION pq4002 TABLESPACE tbs_03,
SUBPARTITION pq4003 TABLESPACE tbs_03,
SUBPARTITION pq4004 TABLESPACE tbs_03)
);
Bitmap Index Example
The following creates a bitmap join index on the table oe.hash_products, which
was created in "Hash Partitioning Example" on page 16-57:
CREATE BITMAP INDEX product_bm_ix
ON hash_products(list_price)
TABLESPACE tbs_1
LOCAL(PARTITION ix_p1 TABLESPACE tbs_02,
PARTITION ix_p2,
PARTITION ix_p3 TABLESPACE tbs_03,
PARTITION ix_p4,
PARTITION ix_p5 TABLESPACE tbs_04 );
ndexes on Nested Tables: Example
The sample table pm.print_media contains a nested table column ad_textdocs_
ntab, which is stored in storage table textdocs_nestedtab. The following example
creates a unique index on storage table textdocs_nestedtab:
CREATE UNIQUE INDEX nested_tab_ix
ON textdocs_nestedtab(NESTED_TABLE_ID, document_typ);
CREACION DE TABLAS
CREACION DE TABLA POR RANGE
CREATE TABLE range_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
(PARTITION SALES_Q1_1998 VALUES LESS THAN (TO_DATE('01-APR-1998','DD-MON-YYYY')),
PARTITION SALES_Q2_1998 VALUES LESS THAN (TO_DATE('01-JUL-1998','DD-MON-YYYY')),
PARTITION SALES_Q3_1998 VALUES LESS THAN (TO_DATE('01-OCT-1998','DD-MON-YYYY')),
PARTITION SALES_Q4_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')),
PARTITION SALES_Q1_1999 VALUES LESS THAN (TO_DATE('01-APR-1999','DD-MON-YYYY')),
PARTITION SALES_Q2_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
PARTITION SALES_Q3_1999 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
PARTITION SALES_Q4_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
PARTITION SALES_Q1_2000 VALUES LESS THAN (TO_DATE('01-APR-2000','DD-MON-YYYY')),
PARTITION SALES_Q2_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000','DD-MON-YYYY')),
PARTITION SALES_Q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000','DD-MON-YYYY')),
PARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE))
;
CREACION DE TABLA POR LIST
CREATE TABLE list_customers
( customer_id NUMBER(6)
, cust_first_name VARCHAR2(20)
, cust_last_name VARCHAR2(20)
, cust_address CUST_ADDRESS_TYP
, nls_territory VARCHAR2(30)
, cust_email VARCHAR2(30))
PARTITION BY LIST (nls_territory) (
PARTITION asia VALUES ('CHINA', 'THAILAND'),
PARTITION europe VALUES ('GERMANY', 'ITALY', 'SWITZERLAND'),
PARTITION west VALUES ('AMERICA'),
PARTITION east VALUES ('INDIA'),
PARTITION rest VALUES (DEFAULT));
CREACION DE TABLA POR HASH
CREATE TABLE hash_products
( product_id NUMBER(6)
, product_name VARCHAR2(50)
, product_description VARCHAR2(2000)
, category_id NUMBER(2)
, weight_class NUMBER(1)
, warranty_period INTERVAL YEAR TO MONTH
, supplier_id NUMBER(6)
, product_status VARCHAR2(20)
, list_price NUMBER(8,2)
, min_price NUMBER(8,2)
, catalog_url VARCHAR2(50)
, CONSTRAINT product_status_lov_demo
CHECK (product_status in ('orderable'
,'planned'
,'under development'
,'obsolete')
) )
PARTITION BY HASH (product_id)
PARTITIONS 5
STORE IN (tbs_01, tbs_02, tbs_03, tbs_04)
CREACION DE TABLA COMPOSITE PARTITIONED
CREATE TABLE composite_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
SUBPARTITION BY HASH (channel_id)
(PARTITION SALES_Q1_1998 VALUES LESS THAN (TO_DATE('01-APR-1998','DD-MON-YYYY')),
PARTITION SALES_Q2_1998 VALUES LESS THAN (TO_DATE('01-JUL-1998','DD-MON-YYYY')),
PARTITION SALES_Q3_1998 VALUES LESS THAN (TO_DATE('01-OCT-1998','DD-MON-YYYY')),
PARTITION SALES_Q4_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')),
PARTITION SALES_Q1_1999 VALUES LESS THAN (TO_DATE('01-APR-1999','DD-MON-YYYY')),
PARTITION SALES_Q2_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
PARTITION SALES_Q3_1999 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
PARTITION SALES_Q4_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
PARTITION SALES_Q1_2000 VALUES LESS THAN (TO_DATE('01-APR-2000','DD-MON-YYYY')),
PARTITION SALES_Q2_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000','DD-MON-YYYY'))
SUBPARTITIONS 8,
PARTITION SALES_Q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000','DD-MON-YYYY'))
(SUBPARTITION ch_c,
SUBPARTITION ch_i,
SUBPARTITION ch_p,
SUBPARTITION ch_s,
SUBPARTITION ch_t),
PARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE)
SUBPARTITIONS 4)
;
CREACION DE TABLA RANGE – SUBPARTITION
CREATE TABLE customers_part (
customer_id NUMBER(6),
cust_first_name VARCHAR2(20),
cust_last_name VARCHAR2(20),
nls_territory VARCHAR2(30),
credit_limit NUMBER(9,2))
PARTITION BY RANGE (credit_limit)
SUBPARTITION BY LIST (nls_territory)
SUBPARTITION TEMPLATE
(SUBPARTITION east VALUES
('CHINA', 'JAPAN', 'INDIA', 'THAILAND'),
SUBPARTITION west VALUES
('AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND'),
SUBPARTITION other VALUES (DEFAULT))
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2500),
PARTITION p3 VALUES LESS THAN (MAXVALUE))
ALTER TABLE
ALTER TABLE employees ADD CONSTRAINT check_comp
CHECK (salary + (commission_pct*salary) <= 5000) DISABLE; ALTER TABLE employees ENABLE ALL TRIGGERS; ALTER TABLE employees DEALLOCATE UNUSED; ALTER TABLE customers RENAME COLUMN credit_limit TO credit_amount; ALTER TABLE list_customers SPLIT PARTITION rest VALUES ('MEXICO', 'COLOMBIA') INTO (PARTITION south, PARTITION rest); ALTER TABLE list_customers MERGE PARTITIONS asia, rest INTO PARTITION rest; ALTER TABLE list_customers SPLIT PARTITION rest VALUES ('CHINA', 'THAILAND') INTO (PARTITION asia, partition rest); ALTER TABLE print_media_part DROP PARTITION p3; ALTER TABLE print_media_part MOVE PARTITION p2b TABLESPACE omf_ts1; ALTER TABLE sales RENAME PARTITION sales_q4_2003 TO sales_currentq; ; ALTER TABLE print_media_demo TRUNCATE PARTITION p1 DROP STORAGE; ALTER TABLE countries ADD (duty_pct NUMBER(2,2) CHECK (duty_pct <> alter table per_sexo storage(buffer_pool keep);
SQL> alter table per_sexo storage(buffer_pool default);
CREACION DE SCHEMA
CREATE SCHEMA AUTHORIZATION oe
CREATE TABLE new_product
(color VARCHAR2(10) PRIMARY KEY, quantity NUMBER)
CREATE VIEW new_product_view
AS SELECT color, quantity FROM new_product WHERE color = 'RED'
GRANT select ON new_product_view TO hr;
CREATE SCHEMA AUTHORIZATION scott
CREATE TABLE dept (
deptno NUMBER(3,0) PRIMARY KEY,
dname VARCHAR2(15),
loc VARCHAR2(25))
CREATE TABLE emp (
empno NUMBER(5,0) PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
job VARCHAR2(10),
mgr NUMBER(5,0),
hiredate DATE DEFAULT (sysdate),
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(3,0) NOT NULL
CONSTRAINT dept_fkey REFERENCES dept)
CREATE VIEW sales_staff AS
SELECT empno, ename, sal, comm
FROM emp
WHERE deptno = 30
WITH CHECK OPTION CONSTRAINT sales_staff_cnst
GRANT SELECT ON sales_staff TO human_resources
SQL> ANALYZE TABLE PER_PERSONA VALIDATE STRUCTURE;
SQL> ANALYZE TABLE PER_PERSONA VALIDATE STRUCTURE CASCADE;
SQL> TRUNCATE TABLE EMP;
SQL> ALTER TABLE PER_SEXO ENABLE ALL TRIGGERS;
SQL> ALTER TABLE PER_SEXO DISABLE ALL TRIGGERS;
SQL> ALTER TABLE DEPT
RENAME CONSTRAINT DNAME_UK TO DNAME_UNIKEY;
SQL> ALTER VIEW EMP_DEPT COMPILE;
SQL> ALTER PACKAGE ACCT_MGMT COMPILE BODY;
SQL> ALTER PACKAGE ACCT_MGMT COMPILE BODY;
The following statement opens the database in
READ ONLY / READ WRITE: Example
read-only mode:
SQL> ALTER DATABASE OPEN READ ONLY;
SQL> ALTER DATABASE OPEN READ WRITE RESETLOGS;
SQL> ALTER DATABASE
RECOVER TABLESPACE tbs_03
PARALLEL;
SQL> ALTER DATABASE
ADD LOGFILE GROUP 3
('diska:log3.log' ,
'diskb:log3.log') SIZE 50K;
SQL> ALTER DATABASE
ADD LOGFILE THREAD 5 GROUP 4
('diska:log4.log',
'diskb:log4:log');
SQL> ALTER DATABASE
ADD LOGFILE MEMBER 'diskc:log3.log'
TO GROUP 3;
SQL> ALTER DATABASE
DROP LOGFILE MEMBER 'diskb:log3.log';
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
SQL> ALTER DATABASE
RENAME FILE 'diskc:log3.log' TO 'diskb:log3.log';
Setting the Default Type of Tablespaces: Example
SQL> ALTER DATABASE
SET DEFAULT BIGFILE TABLESPACE;
SQL> ALTER DATABASE
DEFAULT TEMPORARY TABLESPACE tbs_05;
SQL> ALTER DATABASE
CREATE DATAFILE 'tbs_f03.dbf'
AS 'tbs_f04.dbf';
SQL> ALTER DATABASE TEMPFILE 'temp02.dbf' OFFLINE;
SQL> ALTER DATABASE RENAME FILE 'temp02.dbf' TO 'temp03.dbf';
SQL> ALTER DATABASE
RENAME GLOBAL_NAME TO demo.world.oracle.com;
Enabling and Disabling Block Change Tracking: Examples The following statement
enables block change tracking and causes Oracle Database to create a block change
tracking file named tracking_file and overwrite the file if it already exists:
SQL> ALTER DATABASE
ENABLE BLOCK CHANGE TRACKING
USING FILE 'tracking_file' REUSE;
The following statement disables block change tracking and deletes the existing block
change tracking file:
SQL> ALTER DATABASE
DISABLE BLOCK CHANGE TRACKING;
SQL> ALTER DATABASE
DATAFILE 'diskb:tbs_f5.dat' RESIZE 10 M;
SQL> ALTER DATABASE
CLEAR LOGFILE 'diskc:log3.log';
SQL> ALTER DATABASE
RECOVER AUTOMATIC DATABASE;
SQL> ALTER DATABASE
RECOVER LOGFILE 'diskc:log3.log';
SQL> ALTER DATABASE
RECOVER STANDBY DATAFILE '/finance/stbs_21.f'
UNTIL CONTROLFILE;
The following statement performs time-based recovery of the database:
SQL> ALTER DATABASE
RECOVER AUTOMATIC UNTIL TIME '2001-10-27:14:00:00';
Enabling Parallel DML: Example
DML mode for the current session:
SQL> ALTER SESSION ENABLE PARALLEL DML;
SQL> ALTER SESSION
CLOSE DATABASE LINK local;
SQL> ALTER SESSION
SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';
Changing the Decimal Character and Group Separator: Example The following
statement dynamically changes the decimal character to comma (,) and the group
separator to period (.):
SQL> ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.' ;
Enabling SQL Trace: Example To enable the SQL trace facility for your session, issue
the following statement:
SQL> ALTER SESSION
SET SQL_TRACE = TRUE;
Enabling Query Rewrite: Example This statement enables query rewrite in the
current session for all materialized views that have not been explicitly disabled:
SQL> ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;
SQL> ALTER SESSION CURRENT_SCHEMA = ' ';
SQL> alter table per_persona enable row movement;
SQL> ALTER TABLE PER_PERSONA SHRINK SPACE;
SQL> ALTER TABLE DEPT MODIFY PARTITION DEPT_P1 SHRINK SPACE;
SQL> EXEC DBMS_UTILITY.analyze_schema('DBO','COMPUTE');
SQL> EXEC DBMS_STATS.gather_schema_stats('DBO');
VISTAS
DBA_OBJECTS
ALL_OBJECTS
USER_OBJECTS
DBA_CATALOG
ALL_CATALOG
USER_CATALOG
DBA_DEPENDENCIES
ALL_DEPENDENCIES
USER_DEPENDENCIES
DBA_EXTENTS
USER_EXTENTS
DBA_FREE_SPACE
USER_FREE_SPACE
PACKAGE DBMS_SPACE
VARIABLE total_blocks NUMBER
VARIABLE total_bytes NUMBER
VARIABLE unused_blocks NUMBER
VARIABLE unused_bytes NUMBER
VARIABLE lastextf NUMBER
VARIABLE last_extb NUMBER
VARIABLE lastusedblock NUMBER
exec DBMS_SPACE.UNUSED_SPACE('DBO', 'PER_SEXO', 'TABLE', :total_blocks, :total_bytes, :unused_blocks, :unused_bytes, :lastextf, :last_extb, :lastusedblock)
/
DBA_UPDATABLE_COLUMNS;
ALL_UPDATABLE_COLUMNS;
USER_UPDATABLE_COLUMNS;
SQL> CREATE SEQUENCE EMP_SEQUENCE INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE;
SQL> ALTER SEQUENCE EMP_SEQUENCE INCREMENT BY 10 MAXVALUE 100000 CYCLE;
SQL> INSERT INTO ORDERS (ORDERNO,CUSTNO) VALUES (ORDERS_SEQ.NETVAL,1032);
SQL> SELECT ORDER_SEQ.NEXTVAL FROM DUAL;
CREACION DE SINONIMOS
SQL> CREATE PUBLIC SYNONYM CLIENTE FOR DBO.PER_PERSONA;
SQL> DROP PUBLIC SYNONYM CLIENTE;
VISTAS
DBA_VIEWS
ALL_VIEWS
USER_VIEWS
DBA_SYNONYMS
ALL_SYNONYMS
USER_SYNONYMS
DBA_SEQUENCES
ALL_SEQUENCES
USER_SEQUENCES
DBA_UPDATABLE_COLUMNS
ALL_UPDATABLE_COLUMNS
USER_UPDATABLE_COLUMNS
CONFIGURACION DE PARAMETROS MEMORIA
SQL> alter system set sga_max_size = 8G scope = spfile;
System altered.
SQL> create pfile from spfile;
File created.
SQL> alter system set sga_target = 8G scope = spfile;
System altered.
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
PRACTICAS.
TRABAJOS.
1. Identificar los procesos de sistema operativo que corresponden a la base de
datos, ¿existe algún proceso coordinador de trabajos -cjqNNN- corriendo en
la instancia?.
/home/CURSO/curso01 (CURSO01)> ps -efgrep cj
curso01 25608 25524 0 13:11 pts/2 00:00:00 grep cj
2. Ver el contenido del paquete dbms_scheduler.
Revisar documentación en linea: “Oracle Database PL/SQL Packages and Types
Reference”.
3. Ver la descripción de las vistas dba_scheduler_jobs y
dba_scheduler_job_log. Buscar la descripción de cada uno de los campos en
la documentación en línea.
SQL> desc dba_scheduler_jobs
Nombre ?Nulo? Tipo
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
JOB_NAME NOT NULL VARCHAR2(30)
JOB_SUBNAME VARCHAR2(30)
JOB_CREATOR VARCHAR2(30)
CLIENT_ID VARCHAR2(64)
GLOBAL_UID VARCHAR2(32)
PROGRAM_OWNER VARCHAR2(4000)
PROGRAM_NAME VARCHAR2(4000)
JOB_TYPE VARCHAR2(16)
JOB_ACTION VARCHAR2(4000)
NUMBER_OF_ARGUMENTS NUMBER
SCHEDULE_OWNER VARCHAR2(4000)
SCHEDULE_NAME VARCHAR2(4000)
SCHEDULE_TYPE VARCHAR2(12)
START_DATE TIMESTAMP(6) WITH TIME ZONE
REPEAT_INTERVAL VARCHAR2(4000)
EVENT_QUEUE_OWNER VARCHAR2(30)
1
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
EVENT_QUEUE_NAME VARCHAR2(30)
EVENT_QUEUE_AGENT VARCHAR2(30)
EVENT_CONDITION VARCHAR2(4000)
EVENT_RULE VARCHAR2(65)
END_DATE TIMESTAMP(6) WITH TIME ZONE
JOB_CLASS VARCHAR2(30)
ENABLED VARCHAR2(5)
AUTO_DROP VARCHAR2(5)
RESTARTABLE VARCHAR2(5)
STATE VARCHAR2(15)
JOB_PRIORITY NUMBER
RUN_COUNT NUMBER
MAX_RUNS NUMBER
FAILURE_COUNT NUMBER
MAX_FAILURES NUMBER
RETRY_COUNT NUMBER
LAST_START_DATE TIMESTAMP(6) WITH TIME ZONE
LAST_RUN_DURATION INTERVAL DAY(9) TO SECOND(6)
NEXT_RUN_DATE TIMESTAMP(6) WITH TIME ZONE
SCHEDULE_LIMIT INTERVAL DAY(3) TO SECOND(0)
MAX_RUN_DURATION INTERVAL DAY(3) TO SECOND(0)
LOGGING_LEVEL VARCHAR2(4)
STOP_ON_WINDOW_CLOSE VARCHAR2(5)
INSTANCE_STICKINESS VARCHAR2(5)
RAISE_EVENTS VARCHAR2(4000)
SYSTEM VARCHAR2(5)
JOB_WEIGHT NUMBER
NLS_ENV VARCHAR2(4000)
SOURCE VARCHAR2(128)
DESTINATION VARCHAR2(128)
COMMENTS VARCHAR2(240)
FLAGS NUMBER
SQL> desc dba_scheduler_job_log
Nombre ?Nulo? Tipo
----------------------------------------- -------- ----------------------------
LOG_ID NOT NULL NUMBER
LOG_DATE TIMESTAMP(6) WITH TIME ZONE
OWNER VARCHAR2(30)
JOB_NAME VARCHAR2(65)
JOB_SUBNAME VARCHAR2(65)
JOB_CLASS VARCHAR2(30)
OPERATION VARCHAR2(30)
STATUS VARCHAR2(30)
USER_NAME VARCHAR2(30)
CLIENT_ID VARCHAR2(64)
2
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
GLOBAL_UID VARCHAR2(32)
ADDITIONAL_INFO CLOB
4. Como usuario SYSTEM crear un trabajo que llamará al procedimiento
descrito seguidamente, también propiedad del SYSTEM, que permite analizar
el esquema de un cierto numero de usuarios y generar las estadísticas
internas usadas por el analizador sintáctico de Oracle (previamente deben
crearse la tabla “usuarios_estadisticas” y el procedimiento
“analiza_usuarios”).
Se ejecutara en el momento de su creación y con periodicidad semanal.
create table usuarios_estadisticas (username varchar2(30) not null,
fecha date,
error varchar2(80))
tablespace users
storage (initial 16K next 16K maxextents 10);
CREATE OR REPLACE procedure analiza_usuarios as
cursor usuarios is
select username,rowid from system.usuarios_estadisticas;
werror varchar2(80);
wrowid urowid;
begin
FOR rec_usuarios IN usuarios LOOP
wrowid:=rec_usuarios.rowid;
begin
DBMS_UTILITY.ANALYZE_SCHEMA(rec_usuarios.username,'ESTIMATE',
NULL,3);
update system.usuarios_estadisticas
set fecha=sysdate, error=null
where rowid=rec_usuarios.rowid;
exception
when others then
werror:=rpad(sqlerrm,80);
update system.usuarios_estadisticas
set error=werror, fecha=sysdate
where rowid=wrowid;
end;
END LOOP;
end analiza_usuarios;
/
3
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
Crear un "script" llamado crea_trabajo1.sql, por ejemplo, con el
contenido siguiente:
/home/CURSO/curso01 (CURSO01)> vi crea_trabajo_1.sql
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'TRABAJO_ANALIZA',
job_type => 'STORED_PROCEDURE',
job_action => 'ANALIZA_USUARIOS',
start_date => SYSDATE,
repeat_interval => 'FREQ = WEEKLY');
END;
/
Ejecutarlo desde sqlplus:
SQL> @crea_trabajo1.sql
Procedimiento PL/SQL terminado correctamente.
5. Comprobar en las vistas la información sobre el trabajo
“TRABAJO_ANALIZA”. ¿Está el trabajo habilitado o no?, en caso de estar
deshabilitado ... habilitar el trabajo para su ejecución y comprobar de nuevo
sus características.
SQL> select owner, job_name, job_creator, client_id, job_type, start_date,
repeat_interval, end_date, auto_drop, enabled, restartable from
dba_scheduler_jobs where owner='SYSTEM' and job_name ='TRABAJO_ANALIZA';
OWNER JOB_NAME JOB_CREATOR
------------------------------ ------------------------------ -------------------------
CLIENT_ID JOB_TYPE
---------------------------------------------------------------------------
START_DATE
---------------------------------------------------------------------------
REPEAT_INTERVAL
---------------------------------------------------------------------------------------
END_DATE AUTO_ ENABL RESTA
----------------------------------------------------------------------------------------------
SYSTEM TRABAJO_ANALIZA SYSTEM
STORED_PROCEDURE
08/12/06 14:41:19,000000 +01:00
FREQ = WEEKLY
4
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
TRUE FALSE FALSE
Como se observa en la consulta, el trabajo está deshabilitado (columna
ENABLED igual a FALSE). También se observa que el trabajo será borrado
cuando se complete (AUTO_DROP igual a TRUE).
Se habilita el trabajo para su ejecución:
/home/CURSO/curso01 (CURSO01)> vi activa_trabajo1.sql
BEGIN
DBMS_SCHEDULER.ENABLE('TRABAJO_ANALIZA');
END;
/
~
"activa_trabajo1.sql" 4L, 55C escritos
Se lanza el procedimiento DBMS_SCHEDULER.ENABLE y se comprueba las
características del trabajo:
SQL> @activa_trabajo1.sql
Procedimiento PL/SQL terminado correctamente.
SQL> select owner, job_name, job_creator, client_id, job_type, start_date,
repeat_interval, end_date, auto_drop, enabled, restartable from
dba_scheduler_jobs where owner='SYSTEM' and job_name ='TRABAJO_ANALIZA';
OWNER JOB_NAME JOB_CREATOR
-------------------------------------------------------------------------------------
CLIENT_ID JOB_TYPE
-------------------------------------------------------------------------------------
START_DATE
---------------------------------------------------------------------------
REPEAT_INTERVAL
---------------------------------------------------------------------------------------------
END_DATE AUTO_ ENABL RESTA
---------------------------------------------------------------------------------------------
SYSTEM TRABAJO_ANALIZA SYSTEM
STORED_PROCEDURE
08/12/06 14:41:19,000000 +01:00
FREQ = WEEKLY
TRUE TRUE FALSE
5
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
6. ¿Cuál es el valor para el máximo número de ejecuciones y máximo número
de fallos para este trabajo?. ¿Y su número de fallos?.
SQL> select job_name, max_runs, max_failures, failure_count from
dba_scheduler_jobs where owner='SYSTEM' and job_name ='TRABAJO_ANALIZA';
JOB_NAME MAX_RUNS MAX_FAILURES FAILURE_COUNT
-----------------------------------------------------------------------------------------
TRABAJO_ANALIZA 0
7. Forzar la ejecución del trabajo “TRABAJO_ANALIZA”. Comprobar antes y
después de forzar la ejecución el valor para el número de ejecuciones,
última fecha de ejecución y duración de la última ejecución (“RUN_COUNT”,
“LAST_START_DATE” y “LAST_RUN_DURATION” respectivamente).
/home/CURSO/curso01 (CURSO01)> vi fuerza_trabajo1.sql
BEGIN
DBMS_SCHEDULER.RUN_JOB('TRABAJO_ANALIZA',FALSE);
END;
/
~
"fuerza_trabajo1.sql" 4L, 62C escritos
SQL> select run_count, to_char(last_start_date,'dd-mm-yyyy hh:mi') FECHA,
last_run_duration DURACION from dba_scheduler_jobs where owner='SYSTEM'
and job_name ='TRABAJO_ANALIZA';
RUN_COUNT FECHA DURACION
----------------------------------------------------------
0
SQL> @fuerza_trabajo1.sql
Procedimiento PL/SQL terminado correctamente.
SQL> select run_count, to_char(last_start_date,'dd-mm-yyyy hh:mi') FECHA,
last_run_duration DURACION from dba_scheduler_jobs where owner='SYSTEM' and
job_name ='TRABAJO_ANALIZA';
RUN_COUNT FECHA DURACION
-----------------------------------------------------------------------------------
1 08-12-2006 04:12 +000000000 00:00:00.168563
6
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
8. Como usuario “prueba01” crear un trabajo que llamara al procedimiento
descrito seguidamente, también propiedad de “prueba01”, que permite
averiguar cuáles de las tablas propiedad del usuario tienen ocupado más del
80% de las extensiones que le son permitidas (debe tener cuota sobre el
espacio de almacenamiento donde se creará la tabla y permiso para crear
procedimientos).
Insertará una fila en la tabla “tablas_revision” por cada una de las tablas
que cumplan la condición. Se ejecutará cada hora.
Previamente debe crearse la siguiente tabla:
CREATE TABLE tablas_revision
(nombre_tabla varchar2(30),
ocupacion number)
storage (initial 100k next 100k);
¿En que espacio de almacenamiento se ha creado la tabla?, ¿con que
parámetros de almacenamiento?.
CREATE OR REPLACE procedure chequear_tablas as
extensiones integer;
maximo_extensiones integer;
ocupacion integer;
porcentaje_extensiones integer := 80;
cursor c_tablas is select table_name from user_tables;
begin
FOR rec_tables IN c_tablas LOOP
select count(*) into extensiones
from user_extents
where segment_name=rec_tables.table_name;
select max_extents into maximo_extensiones
from user_tables
where table_name=rec_tables.table_name;
ocupacion:=(round(extensiones*100/maximo_extensiones));
IF (ocupacion > porcentaje_extensiones) THEN
insert into tablas_revision values (rec_tables.table_name,
ocupacion);
commit;
7
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
END IF;
END LOOP;
end chequear_tablas;
/
SQL> connect prueba01
Introduzca su clave:
Conectado.
SQL> CREATE TABLE tablas_revision
2 (nombre_tabla varchar2(30),
3 ocupacion number)
4 storage (initial 100k next 100k);
Tabla creada.
Para ver las caracteristicas de almacenamiento de la tabla consultar la vista
"user_tables".
Crear un "script" llamado chequear_tablas.sql, por ejemplo, que contenga las
sentencias de creacion del procedimiento:
SQL> @chequear_tablas.sql
Procedimiento creado.
Crear un "script" llamado crea_trabajo2.sql, por ejemplo, con el contenido
siguiente:
/home/CURSO/curso01 (CURSO01)> vi crea_trabajo2.sql
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'TRABAJO_CHEQUEAR_TABLAS',
job_type => 'STORED_PROCEDURE',
job_action => 'CHEQUEAR_TABLAS',
start_date => SYSDATE,
repeat_interval => 'FREQ = HOURLY; INTERVAL=1',
ENABLED => TRUE,
AUTO_DROP => FALSE,
8
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
COMMENTS => 'Trabajo para chequear tablas cada hora');
END;
/
~
"crea_trabajo2.sql" 12L, 311C escritos
SQL> @crea_trabajo2.sql
BEGIN
*
ERROR en linea 1:
ORA-27486: privilegios insuficientes
ORA-06512: en "SYS.DBMS_ISCHED", linea 99
ORA-06512: en "SYS.DBMS_SCHEDULER", linea 262
ORA-06512: en linea 2
¿Por qué se ha producido el error anterior al intentar crear el trabajo?: el
usuario “prueba01” carece de permisos para poder crear trabajos. Es
necesario otorgar el permiso “create job”.
SQL> connect system
Introduzca la contrase?a:
Conectado.
SQL> grant create job to prueba01;
Concesion terminada correctamente.
Se vuelve a realizar la conexión como “prueba01” y se ejecuta
“crea_trabajo2.sql”.
SQL> connect prueba01
Introduzca la contrase?a:
Conectado.
SQL> @crea_trabajo2.sql
Procedimiento PL/SQL terminado correctamente.
Se consultan las características del trabajo.
9
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
SQL> select job_name, job_creator, client_id, job_type, start_date,
repeat_interval, end_date, auto_drop, enabled, restartable from
user_scheduler_jobs
JOB_NAME JOB_CREATOR CLIENT_ID
JOB_TYPE
---------------------------------------------------------------------------------------
START_DATE
---------------------------------------------------------------------------
REPEAT_INTERVAL
---------------------------------------------------------------------------------------------
END_DATE AUTO_ ENABL RESTA
---------------------------------------------------------------------------------------------
TRABAJO_CHEQUEAR_TABLAS PRUEBA01
STORED_PROCEDURE
08/12/06 17:01:50,000000 +01:00
FREQ = HOURLY; INTERVAL=1
FALSE TRUE FALSE
9. Obtener, a nivel de sistema operativo, un listado de los procesos asociados a
la base de datos.
Forzar la ejecución del trabajo “TRABAJO_CHEQUEAR_TABLAS”, mientras
está ejecutándose volver a obtener, a nivel de sistema operativo, un listado
de los procesos asociados a la base de datos. ¿Qué procesos se observan en
ambos casos?.
Se crea un "script" llamado “fuerza_trabajo2.sql” con el contenido siguiente:
/home/CURSO/curso01 (CURSO01)> vi fuerza_trabajo2.sql
BEGIN
DBMS_SCHEDULER.RUN_JOB('TRABAJO_CHEQUEAR_TABLAS',FALSE);
END;
/
~
"fuerza_trabajo2.sql" 4L, 69C escritos
/home/CURSO/curso01 (CURSO01)>
Se obtiene el listado de los procesos asociados a la bd.
/home/CURSO/curso01 (CURSO01)> ps -efgrep CURSO01
oracle 18916 1 0 Dec03 ? 00:00:01 ora_pmon_CURSO01
...
10
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
oracle 27563 1 0 17:01 ? 00:00:00 ora_cjq0_CURSO01
curso01 27616 27594 0 17:17 pts/1 00:00:00 grep CURSO01
Como usuario “prueba01” se lanza el “script" “fuerza_trabajo2.sql”:
SQL> show user
USER es "PRUEBA01"
SQL> @fuerza_trabajo2.sql
Procedimiento PL/SQL terminado correctamente.
Se obtiene nuevamente el listado de los procesos asociados a la bd.
/home/CURSO/curso01 (CURSO01)> ps -efgrep CURSO01
oracle 18916 1 0 Dec03 ? 00:00:01 ora_pmon_CURSO01
...
oracle 27563 1 0 17:01 ? 00:00:00 ora_cjq0_CURSO01
oracle 27618 1 2 17:17 ? 00:00:00 ora_j000_CURSO01
curso01 27620 27594 0 17:17 pts/1 00:00:00 grep CURSO01
Si posteriormente se vuelve a ejecutar esta consulta, se observará que
ha desaparecido el proceso esclavo “ora_j000_CURSO01”.
10.Como usuario SYSTEM crear un trabajo que llame al procedimiento
“monitorizar_usuarios” descrito seguidamente, también propiedad del
SYSTEM, que permite monitorizar el numero de sesiones activas de usuario
en la instancia. Se ejecutará cada minuto.
CREATE OR REPLACE procedure monitorizar_usuarios as
sesiones number(4);
activas number(4);
cursor c_usuarios is
select usuario
from usuarios_monitorizados
where monitorizar = 'S';
begin
FOR rec_usuarios IN c_usuarios LOOP
select nvl(count(*),0) into sesiones
11
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
from v$session
where username=rec_usuarios.usuario
and status in ('ACTIVE','INACTIVE');
select nvl(count(*),0) into activas
from v$session
where username=rec_usuarios.usuario
and status = 'ACTIVE';
insert into usuarios_monitorizados_log
values (rec_usuarios.usuario,sysdate,sesiones,activas);
END LOOP;
commit;
end monitorizar_usuarios;
/
Previamente deben crearse las siguientes tablas:
CREATE TABLE usuarios_monitorizados_log
(usuario varchar2(30),
fecha_log date,
sesiones number(4),
activas number(4))
storage (initial 100k next 100k);
CREATE TABLE usuarios_monitorizados
(usuario varchar2(30),
monitorizar char(1),
descripcion varchar2(80))
storage (initial 100k next 100k);
SQL> CREATE TABLE usuarios_monitorizados_log
(usuario varchar2(30),
fecha_log date,
sesiones number(4),
activas number(4))
storage (initial 100k next 100k);
Tabla creada.
SQL> CREATE TABLE usuarios_monitorizados
(usuario varchar2(30),
monitorizar char(1),
descripcion varchar2(80))
12
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
storage (initial 100k next 100k);
Tabla creada.
Conectarse como usuario SYS y dar permisos sobre vista SESSION:
SQL> connect / as sysdba;
Conectado.
SQL> grant select on V_$SESSION to system;
Concesion terminada correctamente.
Crear un "script" llamado monitorizar_usuarios.sql, por ejemplo, que contenga
las sentencias de creacion del procedimiento:
SQL> @monitorizar_usuarios.sql
Procedimiento creado.
Crear un "script" llamado crea_trabajo3.sql, por ejemplo, con el contenido
siguiente:
/home/CURSO/curso01 (CURSO01)> vi crea_trabajo3.sql
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'TRABAJO_MONITORIZAR_USUARIOS',
job_type => 'STORED_PROCEDURE',
job_action => 'MONITORIZAR_USUARIOS',
start_date => SYSDATE,
repeat_interval => 'FREQ = MINUTELY; INTERVAL=1',
ENABLED => TRUE,
AUTO_DROP => FALSE,
COMMENTS => 'Trabajo para monitorizar usuarios');
END;
/
~
"crea_trabajo3.sql" 12L, 314C escritos
SQL> @crea_trabajo3.sql
Procedimiento PL/SQL terminado correctamente.
13
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
11. Monitorizar el número de conexiones del usuario “prueba01”.
Se crea una entrada en la tabla “usuarios_monitorizados” indicando que se
quiere monitorizar al usuario “prueba01”.
SQL> desc usuarios_monitorizados
Nombre ?Nulo? Tipo
---------------------------------------------------------------------
USUARIO VARCHAR2(30)
MONITORIZAR CHAR(1)
DESCRIPCION VARCHAR2(80)
SQL> insert into usuarios_monitorizados values ('PRUEBA01','S',null);
1 fila creada.
SQL> commit;
Confirmacion terminada.
Se abren distintas sesiones del usuario “prueba01”, cuyo número quedará
reflejado en “usuarios_monitorizados_log”.
SQL> select USUARIO, to_char(FECHA_LOG,'dd-mm-yyyy hh:mi:ss') FECHA,
SESIONES, ACTIVAS from usuarios_monitorizados_log order by 2;
USUARIO FECHA SESIONES ACTIVAS
------------------------------------------------------------------------------------------
PRUEBA01 09-12-2006 10:12:07 1 0
PRUEBA01 09-12-2006 10:12:07 2 0
PRUEBA01 09-12-2006 10:12:07 2 0
PRUEBA01 09-12-2006 11:12:07 2 0
12.Consultar todos los trabajos definidos para el usuario SYSTEM obteniendo el
nombre de trabajo, tipo de trabajo, fecha de la próxima ejecución, intervalo,
numero de fallos producidos y su estado.
SQL> select job_name, job_type, start_date, next_run_date, repeat_interval,
failure_count, enabled from dba_scheduler_jobs where owner='SYSTEM' order by
job_name;
JOB_NAME JOB_TYPE START_DATE
NEXT_RUN_DATE
14
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
REPEAT_INTERVAL
FAILURE_COUNT ENABL
-----------------------------------------------------------------------------------------------------
TRABAJO_ANALIZA STORED_PROCEDURE 08/12/06 14:41:19,000000
+01:00 15/12/06 14:41:19,000000 +01:00
FREQ = WEEKLY
0 TRUE
TRABAJO_MONITORIZAR_USUARIOS STORED_PROCEDURE 09/12/06
10:36:07,000000 +01:00 09/12/06 12:56:07,000000 +01:00
FREQ = MINUTELY; INTERVAL=1
0 TRUE
13.Consultar todos los trabajos definidos para el usuario PRUEBA01 obteniendo
el nombre de trabajo, tipo de trabajo, fecha de la próxima ejecución,
intervalo, numero de fallos producidos y su estado.
SQL> select job_name, job_type, start_date, next_run_date, repeat_interval,
failure_count, enabled from dba_scheduler_jobs where owner='PRUEBA01' order
by job_name;
JOB_NAME JOB_TYPE START_DATE
NEXT_RUN_DATE
REPEAT_INTERVAL
FAILURE_COUNT ENABL
-----------------------------------------------------------------------------------------------------
TRABAJO_CHEQUEAR_TABLAS STORED_PROCEDURE 08/12/06
17:01:50,000000 +01:00 09/12/06 13:01:50,000000 +01:00
FREQ = HOURLY; INTERVAL=1
0 TRUE
14.Conectarse como usuario “prueba01” e intentar eliminar de la cola de
trabajos el trabajo “TRABAJO_MONITORIZAR_USUARIOS” perteneciente al
usuario SYSTEM, ¿qué sucede?.
Se crea un “script” de nombre, por ejemplo, “borra_analiza_usuarios.sql”
con el siguiente contenido:
/home/CURSO/curso01 (CURSO01)> vi borra_analiza_usuarios.sql
BEGIN
DBMS_SCHEDULER.DROP_JOB('TRABAJO_MONITORIZAR_USUARIOS');
15
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
END;
/
~
"borra_analiza_usuarios.sql" 4L, 70C escritos
SQL> connect prueba01
Introduzca la contrase?a:
Conectado.
SQL> @borra_analiza_usuarios.sql
BEGIN
*
ERROR en linea 1:
ORA-27475: "PRUEBA01.TRABAJO_MONITORIZAR_USUARIOS" debe ser job
ORA-06512: en "SYS.DBMS_ISCHED", linea 178
ORA-06512: en "SYS.DBMS_SCHEDULER", linea 544
ORA-06512: en linea 2
No existe ningún trabajo perteneciente al usuario prueba01 llamado de esta
forma.
15.Deshabilitar el trabajo que realiza el chequeo de tablas,
“TRABAJO_CHEQUEAR_TABLAS” perteneciente al usuario PRUEBA01.
Consultar las características del trabajo.
SQL> show user
USER es "PRUEBA01"
SQL> begin
2 DBMS_SCHEDULER.DISABLE('TRABAJO_CHEQUEAR_TABLAS');
3 END;
4 /
Procedimiento PL/SQL terminado correctamente.
SQL> select job_name, job_type, enabled from user_scheduler_jobs where
job_name='TRABAJO_CHEQUEAR_TABLAS'
JOB_NAME JOB_TYPE ENABL
-------------------------------------------------------------------------------
TRABAJO_CHEQUEAR_TABLAS STORED_PROCEDURE FALSE
16
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
16.Conectarse como usuario SYSTEM y modificar el trabajo que llama al
procedimiento de monitorización de usuarios para que se realice cada dos
minutos. Consultar las nuevas características del trabajo.
/home/CURSO/curso01 (CURSO01)> vi modificar_atributos.sql
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE('TRABAJO_MONITORIZAR_USUARIOS','REPEAT_IN
TERVAL','FREQ = MINUTELY; INTERVAL = 2');
END;
/
~
"modificar_atributos.sql" [Nuevo] 4L, 125C escritos
SQL> show user
USER es "SYSTEM"
SQL> @modificar_atributos.sql
Procedimiento PL/SQL terminado correctamente.
SQL> select job_name, job_type, start_date, next_run_date, repeat_interval,
failure_count, enabled from dba_scheduler_jobs where
job_name='TRABAJO_MONITORIZAR_USUARIOS';
JOB_NAME JOB_TYPE START_DATE
NEXT_RUN_DATE
REPEAT_INTERVAL
FAILURE_COUNT ENABL
----------------------------------------------------------------------------------------------------
TRABAJO_MONITORIZAR_USUARIOS STORED_PROCEDURE 09/12/06
10:36:07,000000 +01:00 09/12/06 13:36:07,000000 +01:00
FREQ = MINUTELY; INTERVAL = 2
0 TRUE
17.Conectarse como usuario SYSTEM y eliminar el trabajo que realiza el
chequeo de ocupación de tablas (“TRABAJO_CHEQUEAR_TABLAS”).
SQL> SHOW USER
USER es "SYSTEM"
SQL> BEGIN
2 DBMS_SCHEDULER.DROP_JOB('TRABAJO_CHEQUEAR_TABLAS',TRUE);
3 END;
17
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
4 /
BEGIN
*
ERROR en linea 1:
ORA-27475: "SYSTEM.TRABAJO_CHEQUEAR_TABLAS" debe ser job
ORA-06512: en "SYS.DBMS_ISCHED", linea 178
ORA-06512: en "SYS.DBMS_SCHEDULER", linea 544
ORA-06512: en linea 2
No existe ningún trabajo perteneciente al usuario SYSTEM llamado de
esta forma y ni siquiera el administrador puede borrar trabajos de otro
usuario.
18.Conectarse como usuario PRUEBA01 y eliminar el trabajo que realiza el
chequeo de ocupación de tablas (“TRABAJO_CHEQUEAR_TABLAS”).
SQL> show user
USER es "PRUEBA01"
SQL> BEGIN
2 DBMS_SCHEDULER.DROP_JOB('TRABAJO_CHEQUEAR_TABLAS',TRUE);
3 END;
4 /
Procedimiento PL/SQL terminado correctamente.
SQL> select job_name, job_type, start_date, next_run_date, repeat_interval,
failure_count, enabled from user_scheduler_jobs where
job_name='TRABAJO_CHEQUEAR_TABLAS';
ninguna fila seleccionada
18
alter tablespace temp add tempfile '/dev/md/db_olap/rdsk/d104' size 5119M,
'/dev/md/db_olap/rdsk/d105' size 5119M,
'/dev/md/db_olap/rdsk/d106' size 5119M
/
alter tablespace undotbs1 add datafile '/dev/md/db_olap/rdsk/d108' size 5119M
/
alter tablespace undotbs2 add datafile '/dev/md/db_olap/rdsk/d110' size 5119M
/
CREATE TABLESPACES RAW DEVICES EXAMPLE
create tablespace datos datafile '/dev/md/db_olap/rdsk/d201' size 10239M,
'/dev/md/db_olap/rdsk/d202' size 10239M,
'/dev/md/db_olap/rdsk/d203' size 10239M,
'/dev/md/db_olap/rdsk/d204' size 10239M,
'/dev/md/db_olap/rdsk/d205' size 10239M,
'/dev/md/db_olap/rdsk/d206' size 10239M,
'/dev/md/db_olap/rdsk/d207' size 10239M,
'/dev/md/db_olap/rdsk/d208' size 10239M,
'/dev/md/db_olap/rdsk/d209' size 10239M,
'/dev/md/db_olap/rdsk/d210' size 10239M,
'/dev/md/db_olap/rdsk/d211' size 10239M
/
create tablespace indices datafile '/dev/md/db_olap/rdsk/d301' size 10239M,
'/dev/md/db_olap/rdsk/d302' size 10239M,
'/dev/md/db_olap/rdsk/d303' size 10239M,
'/dev/md/db_olap/rdsk/d304' size 10239M,
'/dev/md/db_olap/rdsk/d305' size 10239M,
'/dev/md/db_olap/rdsk/d306' size 10239M,
'/dev/md/db_olap/rdsk/d307' size 10239M,
'/dev/md/db_olap/rdsk/d308' size 10239M,
'/dev/md/db_olap/rdsk/d309' size 10239M,
'/dev/md/db_olap/rdsk/d310' size 10239M,
'/dev/md/db_olap/rdsk/d311' size 10239M
/
-- RECONSTRUYE_INDICES.SQL --
select 'alter index '||index_owner||'.'||index_name||
' rebuild partition '||partition_name||
' tablespace &tablespace_origen storage (initial &t_extension_inicial M);' SENTENCIA
from dba_ind_partitions
where upper(tablespace_name)='&tablespace_origen'
union all
select 'alter index '||owner||'.'||index_name||' rebuild;'
from dba_indexes
where status='UNUSABLE'
union all
select 'alter index '||index_owner||'.'||index_name||
' rebuild partition '||partition_name||';'
from dba_ind_partitions
where status='UNUSABLE';
GENERAR ESTADISTICAS TABLAS
set heading off;
set feedback off;
set echo off;
set pagesize 0;
set line 300
set termout off;
spool estaditicas_tablas.sql
select 'exec dbms_stats.gather_table_stats (ownname => ' || '''SPE''' || ', tabname => ' || ' ''' || tname || ''');' from tab;
spool off;
/
GENERAR ESTADISTICAS INDICES
set heading off;
set feedback off;
set echo off;
set pagesize 0;
set line 300;
set termout off;
spool estaditicas_indices.sql
select 'exec dbms_stats.gather_index_stats (ownname => ' || '''SPE''' || ', indname => ' || ' ''' || index_name || ''');' from dba_indexes where owner='SPE';
spool off;
/
Setting Alert Thresholds
For each tablespace, you can set just percent-full thresholds, just free-space-remaining thresholds, or both types of thresholds simultaneously. Setting either type of threshold to zero disables it.
The ideal setting for the warning threshold is one that issues an alert early enough for you to resolve the problem before it becomes critical. The critical threshold should be one that issues an alert still early enough so that you can take immediate action to avoid loss of service.
To set alert threshold values:
•For locally managed tablespaces, use Enterprise Manager (see Oracle Database 2 Day DBA for instructions) or the DBMS_SERVER_ALERT.SET_THRESHOLD package procedure (see Oracle Database PL/SQL Packages and Types Reference for usage details).
•For dictionary managed tablespaces, use Enterprise Manager. See Oracle Database 2 Day DBA for instructions.
Example—Locally Managed Tablespace
The following example sets the free-space-remaining thresholds in the USERS tablespace to 10 MB (warning) and 2 MB (critical), and disables the percent-full thresholds.
BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.TABLESPACE_BYT_FREE,
warning_operator => DBMS_SERVER_ALERT.OPERATOR_LE,
warning_value => '10240',
critical_operator => DBMS_SERVER_ALERT.OPERATOR_LE,
critical_value => '2048',
observation_period => 1,
consecutive_occurrences => 1,
instance_name => NULL,
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name => 'USERS');
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
warning_operator => DBMS_SERVER_ALERT.OPERATOR_GT,
warning_value => '0',
critical_operator => DBMS_SERVER_ALERT.OPERATOR_GT,
critical_value => '0',
observation_period => 1,
consecutive_occurrences => 1,
instance_name => NULL,
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name => 'USERS');
END;
/
Example: Creating a Repair TableThe following example creates a repair table for the users tablespace.
BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => 'REPAIR_TABLE',
TABLE_TYPE => dbms_repair.repair_table,
ACTION => dbms_repair.create_action,
TABLESPACE => 'USERS');
END;
/
For each repair or orphan key table, a view is also created that eliminates any rows that pertain to objects that no longer exist. The name of the view corresponds to the name of the repair or orphan key table and is prefixed by DBA_ (for exampl, DBA_REPAIR_TABLE or DBA_ORPHAN_KEY_TABLE).
The following query describes the repair table that was created for the users tablespace.
DESC REPAIR_TABLE
Name Null? Type
---------------------------- -------- --------------
OBJECT_ID NOT NULL NUMBER
TABLESPACE_ID NOT NULL NUMBER
RELATIVE_FILE_ID NOT NULL NUMBER
BLOCK_ID NOT NULL NUMBER
CORRUPT_TYPE NOT NULL NUMBER
SCHEMA_NAME NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
BASEOBJECT_NAME VARCHAR2(30)
PARTITION_NAME VARCHAR2(30)
CORRUPT_DESCRIPTION VARCHAR2(2000)
REPAIR_DESCRIPTION VARCHAR2(200)
MARKED_CORRUPT NOT NULL VARCHAR2(10)
CHECK_TIMESTAMP NOT NULL DATE
FIX_TIMESTAMP DATE
REFORMAT_TIMESTAMP DATE
Example: Creating an Orphan Key TableThis example illustrates the creation of an orphan key table for the users tablespace.
BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => 'ORPHAN_KEY_TABLE',
TABLE_TYPE => dbms_repair.orphan_table,
ACTION => dbms_repair.create_action,
TABLESPACE => 'USERS');
END;
/
The orphan key table is described in the following query:
DESC ORPHAN_KEY_TABLE
Name Null? Type
---------------------------- -------- -----------------
SCHEMA_NAME NOT NULL VARCHAR2(30)
INDEX_NAME NOT NULL VARCHAR2(30)
IPART_NAME VARCHAR2(30)
INDEX_ID NOT NULL NUMBER
TABLE_NAME NOT NULL VARCHAR2(30)
PART_NAME VARCHAR2(30)
TABLE_ID NOT NULL NUMBER
KEYROWID NOT NULL ROWID
KEY NOT NULL ROWID
DUMP_TIMESTAMP NOT NULL DATE
Example: Detecting Corruption
The CHECK_OBJECT procedure checks the specified object, and populates the repair table with information about corruptions and repair directives. You can optionally specify a range, partition name, or subpartition name when you want to check a portion of an object.
Validation consists of checking all blocks in the object that have not previously been marked corrupt. For each block, the transaction and data layer portions are checked for self consistency. During CHECK_OBJECT, if a block is encountered that has a corrupt buffer cache header, then that block is skipped.
The following is an example of executing the CHECK_OBJECT procedure for the scott.dept table.
SET SERVEROUTPUT ON
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => 'SCOTT',
OBJECT_NAME => 'DEPT',
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
CORRUPT_COUNT => num_corrupt);
DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;
/
Example: Fixing Corrupt Blocks
Use the FIX_CORRUPT_BLOCKS procedure to fix the corrupt blocks in specified objects based on information in the repair table that was generated by the CHECK_OBJECT procedure. Before changing a block, the block is checked to ensure that the block is still corrupt. Corrupt blocks are repaired by marking the block software corrupt. When a repair is performed, the associated row in the repair table is updated with a timestamp.
This example fixes the corrupt block in table scott.dept that was reported by the CHECK_OBJECT procedure.
SET SERVEROUTPUT ON
DECLARE num_fix INT;
BEGIN
num_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
SCHEMA_NAME => 'SCOTT',
OBJECT_NAME=> 'DEPT',
OBJECT_TYPE => dbms_repair.table_object,
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
FIX_COUNT=> num_fix);
DBMS_OUTPUT.PUT_LINE('num fix: ' || TO_CHAR(num_fix));
END;
/
SQL*Plus outputs the following line:
num fix: 1
The following query confirms that the repair was done.
SELECT OBJECT_NAME, BLOCK_ID, MARKED_CORRUPT
FROM REPAIR_TABLE;
OBJECT_NAME BLOCK_ID MARKED_COR
------------------------------ ---------- ----------
DEPT 3 TRUE
Example: Finding Index Entries Pointing to Corrupt Data Blocks
The DUMP_ORPHAN_KEYS procedure reports on index entries that point to rows in corrupt data blocks. For each index entry, a row is inserted into the specified orphan key table. The orphan key table must have been previously created.
This information can be useful for rebuilding lost rows in the table and for diagnostic purposes.
Note:
This should be run for every index associated with a table identified in the repair table.
In this example, pk_dept is an index on the scott.dept table. It is scanned to determine if there are any index entries pointing to rows in the corrupt data block.
SET SERVEROUTPUT ON
DECLARE num_orphans INT;
BEGIN
num_orphans := 0;
DBMS_REPAIR.DUMP_ORPHAN_KEYS (
SCHEMA_NAME => 'SCOTT',
OBJECT_NAME => 'PK_DEPT',
OBJECT_TYPE => dbms_repair.index_object,
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
ORPHAN_TABLE_NAME=> 'ORPHAN_KEY_TABLE',
KEY_COUNT => num_orphans);
DBMS_OUTPUT.PUT_LINE('orphan key count: ' || TO_CHAR(num_orphans));
END;
/
The following output indicates that there are three orphan keys:
orphan key count: 3
Index entries in the orphan key table implies that the index should be rebuilt. This guarantees that a table probe and an index probe return the same result set.
Example: Skipping Corrupt Blocks
The SKIP_CORRUPT_BLOCKS procedure enables or disables the skipping of corrupt blocks during index and table scans of the specified object. When the object is a table, skipping applies to the table and its indexes. When the object is a cluster, it applies to all of the tables in the cluster, and their respective indexes.
The following example enables the skipping of software corrupt blocks for the scott.dept table:
BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
SCHEMA_NAME => 'SCOTT',
OBJECT_NAME => 'DEPT',
OBJECT_TYPE => dbms_repair.table_object,
FLAGS => dbms_repair.skip_flag);
END;
/
Querying scott's tables using the DBA_TABLES view shows that SKIP_CORRUPT is enabled for table scott.dept.
SELECT OWNER, TABLE_NAME, SKIP_CORRUPT FROM DBA_TABLES
WHERE OWNER = 'SCOTT';
OWNER TABLE_NAME SKIP_COR
------------------------------ ------------------------------ --------
SCOTT ACCOUNT DISABLED
SCOTT BONUS DISABLED
SCOTT DEPT ENABLED
SCOTT DOCINDEX DISABLED
SCOTT EMP DISABLED
SCOTT RECEIPT DISABLED
SCOTT SALGRADE DISABLED
SCOTT SCOTT_EMP DISABLED
SCOTT SYS_IOT_OVER_12255 DISABLED
SCOTT WORK_AREA DISABLED
10 rows selected.
Listing All Tablespace Quotas
The following query lists all tablespace quotas specifically assigned to each user:
SELECT * FROM DBA_TS_QUOTAS;
TABLESPACE USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS
---------- --------- -------- ---------- ------- ----------
USERS JFEE 0 512000 0 250
USERS DCRANNEY 0 -1 0 -1
An index can be monitored to verify if it is used within the period between
alter index index_name monitoring usage;
The result is stored in v$object_usage. See also On verifying if an index is used.
SQL> desc v$object_usage;
Name Null? Type
----------------------------------------- -------- ----------------------------
INDEX_NAME NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
MONITORING VARCHAR2(3)
USED VARCHAR2(3)
START_MONITORING VARCHAR2(19)
END_MONITORING VARCHAR2(19)
SQL>
No hay comentarios:
Publicar un comentario