Humans will often need some time to react to the output from your scripts. To briefly "pause" the output from a pl/sql script, add the following directive:
dbms_lock.sleep(no_of_seconds);
Minimalistic Oracle contains a collection of practical examples from my encounters with Oracle technologies. When relevant, I also write about other technologies, like Linux or PostgreSQL. Many of the posts starts with "how to" since they derive directly from my own personal experience. My goal is to provide simple examples, so that they can be easily adapted to other situations.
Thursday, October 31, 2013
How to enable tracing for a session
To enable trace for a session:
execute dbms_monitor.session_trace_disable(127,29);
Alternatively, use the dbms_system package:
execute dbms_system.set_sql_trace_in_session(17,45171,true);
When finished, disable tracing:
dbms_system.stop_trace_in_session(17,45171);
To enable the writing of a trace file for your current session:
execute dbms_session.set_sql_trace(true);
Note that the user needs the ALTER SESSION privilege to perform tracing:
GRANT ALTER SESSION TO scott;
Note 293661.1 published on MOS is another great source to use the dbms_monitor package efficiently.
execute dbms_monitor.session_trace_enable(session_id => 127, serial_num => 29, waits => TRUE, binds => FALSE);Disable as follows:
execute dbms_monitor.session_trace_disable(127,29);
Alternatively, use the dbms_system package:
execute dbms_system.set_sql_trace_in_session(17,45171,true);
When finished, disable tracing:
dbms_system.stop_trace_in_session(17,45171);
To enable the writing of a trace file for your current session:
execute dbms_session.set_sql_trace(true);
Note that the user needs the ALTER SESSION privilege to perform tracing:
GRANT ALTER SESSION TO scott;
Note 293661.1 published on MOS is another great source to use the dbms_monitor package efficiently.
Syntax for using the dbms_stats.gather_table_stats and gather_schema_stats procedures
To use the default options:
Estimate_percent means percentage of rows to estamite. NULL means compute statistics, and gives the most accurate results, but may be completely innappropriate with large tables. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to let Oracle figure out the appropriate number autmatically. In my opinion, this is sufficient for most situations.
Granularity means Granularity of statistics to collect (only relevant if the table is partitioned).
'ALL' - gathers all (subpartition, partition, and global) statistics
'AUTO' - determines the granularity based on the partitioning type. (DEFAULT)
'DEFAULT' - This option is obsolete. You should use the 'GLOBAL AND PARTITION' for this functionality.
'GLOBAL' - gathers global statistics
'GLOBAL AND PARTITION' - gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object
'PARTITION' - gathers partition-level statistics
'SUBPARTITION' - gathers subpartition-level statistics
cascade means whether or not statistics for the indexes on this table should be gathered.
Using this option is equivalent to running the GATHER_INDEX_STATS Procedure on each of the table's indexes.
Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine whether index statistics to be collected or not (DEFAULT).
degree is the degree of parallelism. The default is NULL, and instructs Oracle to use the DOP (Degree of Parallelism) specified by the DEGREE-clause when the table or index was created. By using the constant DBMS_STATS.DEFAULT_DEGREE you tell Oracle to calculate the DOP for you, using the formula (PARALLEL_THREADS_PER_CPU * CPU_COUNT). Oracle may still choose to use serial execution, if the size of the Object doesn't warrant a parallel execution. See this post for more information about the DEGREE setting.
method_opt means "collect histograms"
Can be:
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]...]
size_clause: SIZE {integer | REPEAT | AUTO | SKEWONLY}
integer : Number of histogram buckets. Must be in the range [1,254].
'REPEAT' - Collects histograms only on the columns that already have histograms.
'AUTO' - Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
'SKEWONLY' - Oracle determines the columns to collect histograms based on the data distribution of the columns.
The default is FOR ALL COLUMNS SIZE AUTO. Note that you can change what's default using the SET_PARAM Procedure.
option GATHER AUTO is the default. GATHER forces a complete re-gathering of all tables
What is a histogram?
Column statistics may be stored as histograms. These histograms provide accurate estimates of the distribution of column data. Histograms provide improved selectivity estimates in the presence of data skew, resulting in optimal execution plans with nonuniform data distributions.
About privileges:
To call this procedure you must either a) be owner of the table or b) have been granted the ANALYZE ANY privilege.
See also this post about how to gather statistics on a specific partition only
Source: Oracle documentation
BEGIN SYS.DBMS_STATS.GATHER_TABLE_STATS ( OwnName => 'SCOTT' ,TabName => 'EMP' ,Estimate_Percent => DBMS_STATS.AUTO_SAMPLE_SIZE ,method_opt => 'FOR ALL COLUMNS SIZE AUTO' ,Degree => NULL ,Cascade => DBMS_STATS.AUTO_CASCADE ,granularity => 'AUTO' ,No_Invalidate => DBMS_STATS.AUTO_INVALIDATE); END; / BEGIN DBMS_STATS.GATHER_SCHEMA_STATS ( ownname => 'SCOTT', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', degree => DBMS_STATS.DEFAULT_DEGREE, granularity => 'ALL', cascade => DBMS_STATS.AUTO_CASCADE, options => 'GATHER', no_invalidate => DBMS_STATS.AUTO_INVALIDATE); END; /
Estimate_percent means percentage of rows to estamite. NULL means compute statistics, and gives the most accurate results, but may be completely innappropriate with large tables. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to let Oracle figure out the appropriate number autmatically. In my opinion, this is sufficient for most situations.
Granularity means Granularity of statistics to collect (only relevant if the table is partitioned).
'ALL' - gathers all (subpartition, partition, and global) statistics
'AUTO' - determines the granularity based on the partitioning type. (DEFAULT)
'DEFAULT' - This option is obsolete. You should use the 'GLOBAL AND PARTITION' for this functionality.
'GLOBAL' - gathers global statistics
'GLOBAL AND PARTITION' - gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object
'PARTITION' - gathers partition-level statistics
'SUBPARTITION' - gathers subpartition-level statistics
cascade means whether or not statistics for the indexes on this table should be gathered.
Using this option is equivalent to running the GATHER_INDEX_STATS Procedure on each of the table's indexes.
Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine whether index statistics to be collected or not (DEFAULT).
degree is the degree of parallelism. The default is NULL, and instructs Oracle to use the DOP (Degree of Parallelism) specified by the DEGREE-clause when the table or index was created. By using the constant DBMS_STATS.DEFAULT_DEGREE you tell Oracle to calculate the DOP for you, using the formula (PARALLEL_THREADS_PER_CPU * CPU_COUNT). Oracle may still choose to use serial execution, if the size of the Object doesn't warrant a parallel execution. See this post for more information about the DEGREE setting.
method_opt means "collect histograms"
Can be:
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]...]
size_clause: SIZE {integer | REPEAT | AUTO | SKEWONLY}
integer : Number of histogram buckets. Must be in the range [1,254].
'REPEAT' - Collects histograms only on the columns that already have histograms.
'AUTO' - Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
'SKEWONLY' - Oracle determines the columns to collect histograms based on the data distribution of the columns.
The default is FOR ALL COLUMNS SIZE AUTO. Note that you can change what's default using the SET_PARAM Procedure.
option GATHER AUTO is the default. GATHER forces a complete re-gathering of all tables
What is a histogram?
Column statistics may be stored as histograms. These histograms provide accurate estimates of the distribution of column data. Histograms provide improved selectivity estimates in the presence of data skew, resulting in optimal execution plans with nonuniform data distributions.
About privileges:
To call this procedure you must either a) be owner of the table or b) have been granted the ANALYZE ANY privilege.
See also this post about how to gather statistics on a specific partition only
Source: Oracle documentation
Wednesday, October 30, 2013
How to create a DDL trigger in a schema
CREATE TABLE AUDIT_DDL ( d date, OSUSER varchar2(255), CURRENT_USER varchar2(255), HOST varchar2(255), TERMINAL varchar2(255), owner varchar2(30), type varchar2(30), name varchar2(30), sysevent varchar2(30));
connect uid/pwd
CREATE OR REPLACE TRIGGER audit_ddl_trg after ddl on schema BEGIN IF(ora_sysevent='TRUNCATE') THEN null; -- If we do not care about truncate ELSE INSERT INTO audit_ddl(d, osuser,current_user,host,terminal,owner,type,name,sysevent) values( sysdate, sys_context('USERENV','OS_USER') , sys_context('USERENV','CURRENT_USER') , sys_context('USERENV','HOST') , sys_context('USERENV','TERMINAL') , ora_dict_obj_owner, ora_dict_obj_type, ora_dict_obj_name, ora_sysevent ); END IF; END; /
Source: Don Burleson http://www.dba-oracle.com/t_ddl_triggers.htm
How to print the predefined error messages in the error number in pl/sql
dbms_output.put_line(sqlerrm(sqlcode));
Tuesday, October 29, 2013
How to set the current schema in a session
alter session set current_schema=SCOTT;
The CURRENT_SCHEMA parameter changes the current schema of the session to the specified schema.
Subsequent unqualified references to schema objects during the session will resolve to objects in the specified schema.
The setting persists for the duration of the session or until you issue another ALTER SESSION SET CURRENT_SCHEMA statement.
This setting offers a convenient way to perform operations on objects in a schema other than that of the current user
without having to qualify the objects with the schema name.
This setting changes the current schema, but it does not change the session user or the current user,
nor does it give the session user any additional system or object privileges for the session.
Source: Oracle Documentation
The CURRENT_SCHEMA parameter changes the current schema of the session to the specified schema.
Subsequent unqualified references to schema objects during the session will resolve to objects in the specified schema.
The setting persists for the duration of the session or until you issue another ALTER SESSION SET CURRENT_SCHEMA statement.
This setting offers a convenient way to perform operations on objects in a schema other than that of the current user
without having to qualify the objects with the schema name.
This setting changes the current schema, but it does not change the session user or the current user,
nor does it give the session user any additional system or object privileges for the session.
Source: Oracle Documentation
Monday, October 28, 2013
How to exclude certain file systems when search for files belonging to tablespaces
Statement can be useful for example when reorganizing databases etc.
SELECT file_name FROM dba_data_files
WHERE file_name NOT LIKE '/data/oracle/u01%'
INTERSECT
SELECT file_name FROM dba_data_files
WHERE file_name NOT LIKE '/data/oracle/u02%';
SELECT file_name FROM dba_data_files
WHERE file_name NOT LIKE '/data/oracle/u01%'
INTERSECT
SELECT file_name FROM dba_data_files
WHERE file_name NOT LIKE '/data/oracle/u02%';
Subscribe to:
Posts (Atom)