Oracle Knowledge Table

Friday, August 23, 2013

SQL*Plus DEFINE - versatile usage to pass multiple values to an IN clause

SQL*Plus DEFINE specifies a user or predefined variable and assigns a CHAR value to it, or lists the
value and variable type of a single variable or all variables.

Whenever you run a stored query or script, SQL*Plus substitutes the value of variable
for each substitution variable referencing variable (in the form &variable or &&variable).
SQL*Plus will not prompt you for the value of variable in this session until you
UNDEFINE variable.

DEFINE is very versatile when it comes to handle multiple values.
Say you want to define a variable which contains multiple values that you plan to use with an IN operator.

Tested in Oracle Database 12c, but works also in 11g or 10g
SQL> select banner from v$version where rownum =1;

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

This is as easy as:

SQL> define own = "'SYS', -
> 'SYSTEM', -
> 'NULL'"

This is interpreted as it follows;

SQL> define own
DEFINE OWN = "'SYS', 'SYSTEM', 'NULL'" (CHAR)

And now we will use it in a select:

SQL> select count(*), owner
2 from dba_objects
3 where owner in (&own)
4 group by owner;
old 3: where owner in (&own)
new 3: where owner in ('SYS', 'SYSTEM', 'NULL')

COUNT(*) OWNER
---------- ----------------------------------------
635 SYSTEM
41873 SYS

Sunday, August 18, 2013

EM Express configuration for new pluggable database

We are in 12c; 12.1.0.1.0.
We will show how to create new pluggable database from seed and configure EM Express for it.

Oracle Enterprise Manager Database Express (EM Express) is a web-based database management tool that is built inside the Oracle Database.
From an architectural perspective, EM Express has no mid-tier or middleware components, ensuring that its overhead on the database server is negligible.

C:\Users\hberca>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Aug 18 15:36:09 2013

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_id

CON_ID
------------------------------
1
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

Oracle XML DB is Mandatory in 12c – You Cannot Uninstall it

SQL> column comp_name format a30
SQL> column status format a20
SQL> column version format a20
SQL> select comp_name,status,version
2 from dba_registry
3 where comp_name like '%XML%';

COMP_NAME STATUS VERSION
------------------------------ -------------------- -------------
Oracle XML Database VALID 12.1.0.1.0

The DBMS_XDB_CONFIG package provides an interface for configuring Oracle XML DB and its repository (settings for the http protocol server)
It updates the settings for xdbconfig.xsd.

PL/SQL package DBMS_XDB_CONFIG is the Oracle XML DB resource application program interface (API) for PL/SQL for DBAs to configure their system.
This API provides functions and procedures to access and manage Oracle XML DB Repository resources using PL/SQL.
It includes methods for managing resource security and Oracle XML DB configuration.

We are using the procedure USEDPORT - this obtains the port numbers used by other pluggable databases in the same consolidation database.
We can see we have only configured port 5500, which is the default port for cdb$root which has by default con_id = 1.

SQL> select dbms_xdb_config.usedport from dual;

USEDPORT
--------------------------------------------------------------------------------
<portlist>
<port>
<pdbid>1</pdbid>
<httpport2>5500</httpport2>
</por

We are now creating new pluggable database from seed. We will use the below command:

SQL> create pluggable database pdb123 admin user pdb123 identified by oracle file_name_convert = ('pdbseed', 'pdb123');

Pluggable database created.

After creation, a new pluggable database is in MOUNT mode. We will open it.

SQL> alter pluggable database pdb123 open read write;

Pluggable database altered.

We will now query the view v$pdbs which will show us all pluggable database in this container.
We see PDB123 has con_id=5.

SQL> select con_id, name, open_mode from v$pdbs;

CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY
3 PDB1 MOUNTED
4 PDB_SAMPLE MOUNTED
5 PDB123 READ WRITE
6 Z READ WRITE

We are switching container to pdb123 in order to set the https port for EM express:

SQL> alter session set container=pdb123;

Session altered.

SQL> show con_id con_name

CON_ID
------------------------------
5

CON_NAME
------------------------------
PDB123

We will use SETHTTPSPORT procedure, this sets the HTTPS port to a new value, in our case 5502.

SQL> exec DBMS_XDB_CONFIG.SETHTTPSPORT(5502);

PL/SQL procedure successfully completed.

We double check the port has been set using function GETHTTPSPORT:

SQL> select dbms_xdb_config.gethttpsport from dual;

GETHTTPSPORT
------------
5502

If we attempt to view the usedports in the container from the pluggable database, an error will be raised:

SQL> select dbms_xdb_config.usedport from dual;
ERROR:
ORA-31120: usedPort cannot be invoked in a Pluggable Database
ORA-06512: at "XDB.DBMS_XDB_CONFIG", line 32

We will switch to CDB$ROOT.

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> set long 10000
SQL> select dbms_xdb_config.usedport from dual;

USEDPORT
---------------------------------------------------------
<portlist>
<port>
<pdbid>1</pdbid>
<httpport2>5500</httpport2>
</port>
<port>
<pdbid>5</pdbid>
<httpport2>5502</httpport2>
</port>
</portlist>

We now see that pdbid 5, which is our newly create pluggable database pdb123 is allocated port 5502.

The USEDPORT procedure is also useful to find the used ports in case we are trying to allocate same port to another pluggable database. We will hit the error ORA-44718: Port conflict in XDB Configuration file.

SQL> alter session set container=z;

Session altered.

SQL> exec DBMS_XDB_CONFIG.SETHTTPSPORT(5502);
BEGIN DBMS_XDB_CONFIG.SETHTTPSPORT(5502); END;

*
ERROR at line 1:
ORA-44718: Port conflict in XDB Configuration file
ORA-06512: at "XDB.DBMS_XDB", line 528
ORA-06512: at "XDB.DBMS_XDB_CONFIG", line 375
ORA-06512: at "XDB.DBMS_XDB_CONFIG", line 226
ORA-06512: at line 1

Reference:
Oracle® Database PL/SQL Packages and Types Reference
12c Release 1 (12.1)
E17602-14

Oracle® Database 2 Day DBA
12c Release 1 (12.1)
E17643-12

Wednesday, August 7, 2013

Create PDB with Sample schemas in DB12c

In Oracle Database 12c, Sample Schemas are delivered in an xml format as a template and at this point only dbca can use it.

This facilitates plugging and un-plugging into a CDB.

Invoke dbca, choose Manage Pluggable Databases.

Click Next to continue

Then select Create a Pluggable Database

Choose among the container database you have installed.

Now choose the option to Create Pluggable Database using PDB File Set.

Browse on Pluggable Database Metadata File and choose sampleschema.xml

Define the name for your Pluggable Database

Review the summary and click Install.

Installation is in progress

SQL> select banner from v$version;

BANNER

--------------------------------------------------------------------------------

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

PL/SQL Release 12.1.0.1.0 - Production

CORE 12.1.0.1.0 Production

TNS for 64-bit Windows: Version 12.1.0.1.0 - Production

NLSRTL Version 12.1.0.1.0 – Production

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> select pdb_id, pdb_name, status from cdb_pdbs;

PDB_ID PDB_NAME STATUS

---------- ------------------------- -------------

3 PDB1 NORMAL

2 PDB$SEED NORMAL

4 PDB_SAMPLE NORMAL

SQL> alter session set container=pdb_sample;

Session altered.

SQL> select * from (

2 select username, account_status from dba_users order by created desc)

3 where rownum <=7;

USERNAME ACCOUNT_STATUS

------------------------- --------------------------------

SCOTT EXPIRED & LOCKED

BI EXPIRED & LOCKED

SH EXPIRED & LOCKED

IX EXPIRED & LOCKED

PM EXPIRED & LOCKED

OE EXPIRED & LOCKED

HR EXPIRED & LOCKED

7 rows selected.

Thursday, July 18, 2013

DB12c: 171 parameters modifiable at PDB level

There are 171 parameters modifiable at PDB level.
In the list below I filtered one the log_archive_* ones as this would make a too long list.

SQL> select banner from v$version where rownum < 2;

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

SQL> select name, ispdb_modifiable
2 from v$parameter
3 where ispdb_modifiable='TRUE' and name not like 'log_archive%

NAME ISPDB
---------------------------------------- -----
sessions TRUE
timed_statistics TRUE
timed_os_statistics TRUE
resource_limit TRUE
nls_language TRUE
nls_territory TRUE
nls_sort TRUE
nls_date_language TRUE
nls_date_format TRUE
nls_currency TRUE
nls_numeric_characters TRUE
nls_iso_currency TRUE
nls_calendar TRUE
nls_time_format TRUE
nls_timestamp_format TRUE
nls_time_tz_format TRUE
nls_timestamp_tz_format TRUE
nls_dual_currency TRUE
nls_comp TRUE
nls_length_semantics TRUE
nls_nchar_conv_excp TRUE
resource_manager_plan TRUE
db_file_multiblock_read_count TRUE
db_create_file_dest TRUE
db_create_online_log_dest_1 TRUE
db_create_online_log_dest_2 TRUE
db_create_online_log_dest_3 TRUE
db_create_online_log_dest_4 TRUE
db_create_online_log_dest_5 TRUE
db_unrecoverable_scn_tracking TRUE
temp_undo_enabled TRUE
resumable_timeout TRUE
heat_map TRUE
recyclebin TRUE
db_index_compression_inheritance TRUE
db_securefile TRUE
create_stored_outlines TRUE
O7_DICTIONARY_ACCESSIBILITY TRUE
global_names TRUE
listener_networks TRUE
remote_dependencies_mode TRUE
smtp_out_server TRUE
plsql_v2_compatibility TRUE
plsql_warnings TRUE
plsql_code_type TRUE
plsql_debug TRUE
plsql_optimize_level TRUE
plsql_ccflags TRUE
plscope_settings TRUE
java_jit_enabled TRUE
cursor_sharing TRUE
result_cache_mode TRUE
parallel_instance_group TRUE
result_cache_remote_expiration TRUE
object_cache_optimal_size TRUE
object_cache_max_size_percent TRUE
commit_write TRUE
commit_wait TRUE
commit_logging TRUE
optimizer_features_enable TRUE
fixed_date TRUE
sort_area_size TRUE
sort_area_retained_size TRUE
cell_offload_processing TRUE
cell_offload_decryption TRUE
cell_offload_parameters TRUE
cell_offload_compaction TRUE
cell_offload_plan_display TRUE
open_cursors TRUE
sql_trace TRUE
optimizer_mode TRUE
star_transformation_enabled TRUE
parallel_degree_policy TRUE
parallel_io_cap_enabled TRUE
optimizer_index_cost_adj TRUE
optimizer_index_caching TRUE
query_rewrite_enabled TRUE
query_rewrite_integrity TRUE
workarea_size_policy TRUE
optimizer_dynamic_sampling TRUE
statistics_level TRUE
cursor_bind_capture_destination TRUE
skip_unusable_indexes TRUE
optimizer_secure_view_merging TRUE
ddl_lock_timeout TRUE
deferred_segment_creation TRUE
optimizer_use_pending_statistics TRUE
optimizer_capture_sql_plan_baselines TRUE
optimizer_use_sql_plan_baselines TRUE
parallel_min_time_threshold TRUE
parallel_degree_limit TRUE
parallel_force_local TRUE
optimizer_use_invisible_indexes TRUE
dst_upgrade_insert_conv TRUE
max_string_size TRUE
optimizer_adaptive_reporting_only TRUE
parallel_fault_tolerance_enabled TRUE
parallel_degree_level TRUE
optimizer_adaptive_features TRUE
enable_ddl_logging TRUE
xml_db_events TRUE
olap_page_pool_size TRUE
asm_diskstring TRUE
sqltune_category TRUE
spatial_vector_acceleration TRUE
max_dump_file_size TRUE
cell_offloadgroup_name TRUE
pdb_file_name_convert TRUE

Thursday, July 11, 2013

Oracle Database 12c Installation on Windows

Tuesday, July 9, 2013

Identify hot blocks

SQL> select banner from v$version;

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> create table horia.new_date as select * from all_objects;
SQL>

Run 3 sessions in parallel in order to touch the same block several times and introduce latch contention.

declare
n number;
begin
for i in 1..1000000 loop
select object_id
into n
from new_date
where 1=1 --object_type='TABLE'
and object_id =116 and rownum <2;
end loop;
end;

Identify a high or rapid increasing wait count on the CACHE BUFFERS CHAINS latch.

SQL> select CHILD# "cCHILD"
2 , ADDR "sADDR"
3 , GETS "sGETS"
4 , MISSES "sMISSES"
5 , SLEEPS "sSLEEPS"
6 from v$latch_children
7 where name = 'cache buffers chains'
8 and sleeps !=0
9 order by 5, 1, 2, 3;

cCHILD sADDR sGETS sMISSES sSLEEPS
---------- ---------------- ---------- ---------- ----------
3421 000007FF309E0200 25923210 974760 1
889 000007FF30FF4028 51849242 2044527 3

Run the above query a few times to establish the id(ADDR) that has the most
consistent amount of sleeps. Once the id(ADDR) with the highest sleep count is found
then this latch address can be used to get more details about the blocks
currently in the buffer cache protected by this latch.
The query below should be run just after determining the ADDR with
the highest sleep count.

SQL> column segment_name format a35
SQL> select /*+ RULE */
2 e.owner ||'.'|| e.segment_name segment_name,
3 e.extent_id extent#,
4 x.dbablk - e.block_id + 1 block#,
5 x.tch,
6 l.child#
7 from
8 sys.v$latch_children l,
9 sys.x$bh x,
10 sys.dba_extents e
11 where
12 x.hladdr = '&ADDR' and
13 e.file_id = x.file# and
14 x.hladdr = l.addr and
15 x.dbablk between e.block_id and e.block_id + e.blocks -1
16 and x.tch>10
17 order by x.tch desc ;
Enter value for addr: 000007FF30FF4028
old 12: x.hladdr = '&ADDR' and
new 12: x.hladdr = '000007FF30FF4028' and

SEGMENT_NAME EXTENT# BLOCK# TCH CHILD#
----------------------------------- ---------- ---------- ---------- ----------
HORIA.NEW_DATE 0 3 156 889

Depending on the TCH column (The number of times the block is hit by a SQL
statement), you can identify a hot block. The higher the value of the TCH column,
the more frequent the block is accessed by SQL statements.

Monday, July 8, 2013

How to check if you have empty or stale statistics?

I have created and one can use this simple script.

set serveroutput on
declare
obj_empty dbms_stats.objecttab;
obj_stale dbms_stats.objecttab;
begin

dbms_stats.gather_database_stats(OPTIONS=>'LIST EMPTY',OBJLIST=>obj_empty);
for i in 1 .. obj_empty.count
loop
dbms_output.put_line('Empty statistics--->' ||' Owner: '||obj_empty(i).ownname||'--'||'Object name: '||obj_empty(i).objName||'--'||'Object type: '||obj_empty(i).objType);
end loop;

dbms_stats.gather_database_stats(OPTIONS=>'LIST STALE',OBJLIST=>obj_stale);
for j in 1 .. obj_stale.count
loop
dbms_output.put_line('Stale statistics--->' ||' Owner: '||obj_stale(j).ownname||'--'||'Object name: '||obj_stale(j).objName||'--'||'Object type: '||obj_stale(j).objType);
end loop;
end;
/

Sample output below:

Empty statistics---> Owner: HORIA--Object name: CONTRACTS_SEC--Object type: TABLE
Empty statistics---> Owner: SH--Object name: SYS_IOT_TOP_76625--Object type: INDEX
Empty statistics---> Owner: SH--Object name: SYS_IOT_TOP_76627--Object type: INDEX
Stale statistics---> Owner: SYS--Object name: HISTGRM$--Object type: TABLE
Stale statistics---> Owner: SYS--Object name: HIST_HEAD$--Object type: TABLE
Stale statistics---> Owner: SYS--Object name: IND$--Object type: TABLE
Stale statistics---> Owner: SYS--Object name: MON_MODS$--Object type: TABLE