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
Friday, August 23, 2013
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
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.
Subscribe to:
Posts (Atom)