Feed aggregator

Forcing a query to timeout

Tom Kyte - Mon, 2016-07-11 23:06
I have several PL/SQL stored procedure reports that monitor various aspects of our database environment (ASM, tablespace, backups, alerts, ...). These all have the same framework, loop thru the entries in a table, using a db_link, query the remote sy...
Categories: DBA Blogs

Network-based IMPDP with local SCN?

Tom Kyte - Mon, 2016-07-11 23:06
Hi, I found a procedure for a network based impdp. The procedure sets the actual (flashback) scn of the target system (see below). That?s a mistake, isn?t it? What will impdp do with this invalid scn? Simply ignore it? Cheers, Markus ...
Categories: DBA Blogs

Creating a Service within IntelliJ IDEA to be used by the Service Registry for Pivotal Cloud Foundry

Pas Apicella - Mon, 2016-07-11 19:59
In this example I am going to show how to use IntelliJ IDEA 15 to create a service application from the IDE to be consumed by the Service Registry service in Pivotal Cloud Foundry (PCF). For more information on this service view the docs page below.


Service Registry for Pivotal Cloud Foundry® (PCF) provides your applications with an implementation of the Service Discovery pattern, one of the key tenets of a microservice-based architecture. Trying to hand-configure each client of a service or adopt some form of access convention can be difficult and prove to be brittle in production. Instead, your applications can use the Service Registry to dynamically discover and call registered services

1. Start IntelliJ IDEA and either "Create a New project" or add a "New Module" to an existing project.

2. Ensure you select "Spring Initializer" as shown below

3. Click Next

4. Describe your project or module, I normally use Maven and generate a JAR file

5. Click Next

6. At the minimum here we only need to select "Service Registry (PCF)" as shown below for the dependency. Of course you would select other options dependncies depending on what the service needed such as REST, JPA, H2 or MySQL etc

7. Click Next

8. Name your new model or project

9. Click Finish

10. Click Finish

11. Your service application must include the @EnableDiscoveryClient annotation on a configuration class. To do that we simply add the annotation to our main class as follows

Java Code
package pas.au.pivotal.service.hr;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.cloud.client.discovery.EnableDiscoveryClient;

import javax.annotation.PostConstruct;

public class EmployeeServiceApplication
private EmployeeRepository employeeRepository;

public static void main(String[] args) {
SpringApplication.run(EmployeeServiceApplication.class, args);

public void init()
employeeRepository.save(new Employee("pas"));
employeeRepository.save(new Employee("lucia"));
employeeRepository.save(new Employee("siena"));
employeeRepository.save(new Employee("lucas"));

12. Set the spring.application.name property in application.yml. It might be an application.properties file BUT rename it to YML as I know that works. below I not only set the application name I also set the registrationMethod to "route" which is the default and then turn off security as it is enabled by default.

    name: employee-service

    registrationMethod: route

    enabled: false

So that's all we really need to do here. Of course we will need to add code to our service to do what it needs to do BUT all the config required to enable this service to automatically register itself with the "Service Registry" in PCF is done.

13. Before we deploy this to out PCF instance we have to be sure we have a "Service Registry" service created as shown below using the CF CLI mine is already created.

14. Create a manifest.yml file for the service to be deployed, notice how it binds to the service registry "apples-service-registery", this will ensure it automatically gets registered on deployment with the Service Registry service

- name: apples-employee-service
  memory: 512M
  instances: 1
  host: apples-employee-service-${random-word}
  path: ./target/EmployeeService-0.0.1-SNAPSHOT.jar
    - apples-service-registery

15. Push the service application to PCF as shown below


16. Login into your PCF instance App Manager UI, in this demo I am using PWS instance run.pivotal.io and find your "Service Registry" service and click on it as shown below

17. Click on the "Manage" link as shown below

18. Verify your service is registered as shown below

More Information



Categories: Fusion Middleware

Application Management Pack for EM 13c Now Available

Steven Chan - Mon, 2016-07-11 15:24

Application Management Pack (AMP) for Oracle E-Business Suite is now available.  This E-Business Suite plug-in for Oracle Enterprise Manager Cloud Control 13c can be used to manage Oracle E-Business Suite, 12.0, 12.1, and 12.2 environments.

What's new in this release?

  • Enterprise Manager 13c Compatibility
EM 13c includes several enhancements including a new Fusion Middleware technology stack, Alta skin user interface and (EBR) Edition Based Redefinition support.

  • Hybrid Cloud Management
Monitor and manage E-Business Suite environments on Oracle Cloud and On-premise. These capabilities are delivered as Enterprise Manager command line interface (EMCLI) to manage, provision, migrate, backup, restore, clone, patch, and lift-and-shift E-Business Suite on Oracle Cloud. 
  • System Management
    • Real User Experience Insight (RUEI)  regions integrated within E-Business Suite Summary Dashboard
    • Automated host aliasing

  • Change Management
    • Patch Recommendations: Deploy recommended E-Business Suite technology stack patches, including Database and WebLogic Server patches, using EM patch plans. 
    • Customization Management: View the inventory of customizations in an Oracle E-Business Suite environment. View or download a spreadsheet of discovered customizations by customization type. Customization Discovery & Reporting process now includes discovery of database objects. 
    • Cloning: Save cloning interview process as templates that can be used for future purposes for Smart Clone for R12, 12.2.x procedure, and add custom parameters to the cloning procedure. 


The Oracle Application Management Suite for Oracle E-Business Suite can be downloaded and installed by using the Self Update feature of Oracle Enterprise Manager.

Related Articles

Categories: APPS Blogs

SharePlex Compare and Repair commands

Yann Neuhaus - Mon, 2016-07-11 13:31

Reading Franck Pachot blog about comparing source and target in a Dbvisit replication (http://blog.dbi-services.com/compare-source-and-target-in-a-dbvisit-replication/), I decide to write a small article about how we can do same thing with SharePlex. Indeed SharePlex provides built-in commands to compare and repair synchronization.
Below is our configuration file. We are just replicating table article to article_rep. Both tables contain 2000000 rows


#source tables target tables routing map
titi.article titi.article_rep atlasrep2@o.SPLEXSTR2

From the source let’s insert 2 rows to verify that replication is working fine.

SQL> insert into article values (2000001,'2000001_designation',2000000001);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into article values (2000002,'2000002_designation',2000000002);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from article where idart in (2000001,2000002);

     IDART DESIGNATION                          PRIX
---------- ------------------------------ ----------
   2000001 2000001_designation            2000000001
   2000002 2000002_designation            2000000002

From the target let’s verify that the 2 rows are replicated

SQL> select * from article_rep where idart in (2000001,2000002);

     IDART DESIGNATION                          PRIX
---------- ------------------------------ ----------
   2000001 2000001_designation            2000000001
   2000002 2000002_designation            2000000002

To compare source and target in SharePlex, the command compare is used. Let’s see a demonstration.
From the source 

sp_ctrl (atlas:2104)>  compare titi.article to titi.article_rep at atlasrep2@o.SPLEXSTR2 for o.SPLEXDB

  comparing 1 of 1 objects

  compare started; job id 7

sp_ctrl (atlas:2104)>

And now let’s see the status of our replication

sp_ctrl (atlas:2104)> show compare

   The syntax to get status for the compare command has changed
   Instead of 'show compare' use the 'compare status' or 'repair status' command

Job ID    : 7
PID       : 8644
Host      : atlas.localdomain
Started   : 06-JUL-16 11:34:48
Job Type  : Compare
Status    : Done - 1 object completed

ID     Tablename                            Status     Time       Total Rows %Comp Total Time
------ ------------------------------------ ---------- ---------- ---------- ----- ----------
1      "TITI"."ARTICLE"                     In Sync           N/A    2000002   100       0:31

sp_ctrl (atlas:2104)>

We see that tables are in sync. When running the compare command, SharePlex obtains a brief exclusive lock on the source table to get read consistency for its row selection.
On the target system, SharePlex obtains an exclusive lock on the target table and retains the lock for the duration of the comparison of that table.
Now let’s Sync out our replication
From the target let’s delete a row (note that we have one directional replication only from source to target)

SQL> delete from article_rep where idart=2000002;

1 row deleted.

SQL> commit;

Commit complete.


And from the source let’s insert a row

SQL> insert into article values (2000003,'2000003_designation',2000000003);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from article where idart=2000003;

     IDART DESIGNATION                          PRIX
---------- ------------------------------ ----------
   2000003 2000003_designation            2000000003

SQL> select count(*) from article;



From the target we can verify that the last row with idart=2000003 is present but the number of rows is now different between the 2 tables. Indeed we deleted the row with idart=2000002 in the target and this deletion is not replicated in the source. Now replication is sync out

SQL> select * from article_rep where idart=2000003;

     IDART DESIGNATION                          PRIX
---------- ------------------------------ ----------
   2000003 2000003_designation            2000000003

SQL> select count(*) from article_rep;


Let’s run again compare command on the source and let’s see the output now

sp_ctrl (atlas:2104)> compare titi.article to titi.article_rep at atlasrep2@o.SPLEXSTR2 for o.SPLEXDB

We can see that the status is Sync Out (note that comparison of 2000000 rows was very quick)

sp_ctrl (atlas:2104)> show compare

   The syntax to get status for the compare command has changed
   Instead of 'show compare' use the 'compare status' or 'repair status' command

Job ID    : 9
PID       : 12583
Host      : atlas.localdomain
Started   : 06-JUL-16 13:24:48
Job Type  : Compare
Status    : Done - 1 object completed

ID     Tablename                            Status     Time       Total Rows %Comp Total Time
------ ------------------------------------ ---------- ---------- ---------- ----- ----------
1      "TITI"."ARTICLE"                     Out Sync          N/A    2000003   100       0:25

To sync In  the replication we just have to use the repair command. This command is magic.

sp_ctrl (atlas:2104)> repair  titi.article to titi.article_rep at atlasrep2@o.SPLEXSTR2 for o.SPLEXDB

  repairing 1 of 1 objects

  repair started; job id 10

In the Log file on the target we can see that SharePlex is using ORA_HASH to compare

[oracle@atlasrep2 log]$ less declt_10-1_SPLEXDB_192.168.1.40_p9883.log
declt    2016-07-06 13:36:45.228937 9883 1352263552 NOTE: setting up HASH column info (de_table_setup_ora_hash_compare,L2915)
declt    2016-07-06 13:36:45.228971 9883 1352263552 Key Compare : Off
declt    2016-07-06 13:36:45.229009 9883 1352263552 Job ID      : 10
declt    2016-07-06 13:36:45.229017 9883 1352263552 Source Table: "TITI"."ARTICLE"
declt    2016-07-06 13:36:45.229122 9883 1352263552 Source Node : atlas.localdomain
declt    2016-07-06 13:36:45.229130 9883 1352263552 Target Table: "TITI"."ARTICLE_REP"
declt    2016-07-06 13:36:45.229135 9883 1352263552 Target Route: atlasrep2@SPLEXSTR2
declt    2016-07-06 13:36:45.229140 9883 1352263552 Batch Size  : 100000 rows
declt    2016-07-06 13:36:45.229145 9883 1352263552 Repair      : On (2 Pass)
declt    2016-07-06 13:36:45.229150 9883 1352263552 sp_declt PID: 9883
declt    2016-07-06 13:36:45.229155 9883 1352263552 Read Buffer Size : 1 mbytes
declt    2016-07-06 13:36:45.237727 9883 1352263552 Current status: ready to fetch batch,of rows; including the order by sorting.
declt    2016-07-06 13:36:45.237782 9883 1352263552 running sql statement : (de_select_prepare_to_fetch,L1384)
declt    2016-07-06 13:36:45.237795 9883 1352263552   SELECT /*+ PARALLEL (A,2) */ ROWID,ORA_HASH("IDART"||'~'||"PRIX"), ORA_HASH("DESIGNATION") FROM "TITI"."ARTICLE_REP" A ORDER BY 2, 3
declt    2016-07-06 13:36:54.064711 9883 1352263552 Current status: got batch of rows
declt    2016-07-06 13:37:04.527252 9883 1352263552 NOTE: total number of rows fetched = 2000002 (../src/deqtr/de_select.cpp,L1630)
declt    2016-07-06 13:37:04.533329 9883 1352263552 Notice: starting the second phase in the repair process
declt    2016-07-06 13:37:04.616704 9883 1352263552 Leaving de_msg_clt_preCompareMessaging1_5,L1297
declt    2016-07-06 13:37:06.659513 9883 1352263552 Leaving de_msg_clt_preCompareMessaging2,L614
declt    2016-07-06 13:37:06.659716 9883 1352263552 No new key column order, using default column order for TITI.ARTICLE_REP
declt    2016-07-06 13:37:06.721957 9883 1352263552 Current status: ready to fetch batch,of rows; including the order by sorting.
declt    2016-07-06 13:37:06.721996 9883 1352263552 running sql statement : (de_select_prepare_to_fetch,L1384)

When issuing again the show compare command, we can see that the status is now Repaired.

sp_ctrl (atlas:2104)> show compare

   The syntax to get status for the compare command has changed
   Instead of 'show compare' use the 'compare status' or 'repair status' command

Job ID    : 10
PID       : 13008
Host      : atlas.localdomain
Started   : 06-JUL-16 13:36:37
Job Type  : Repair
Status    : Done - 1 object completed

ID     Tablename                            Status     Time       Total Rows %Comp Total Time
------ ------------------------------------ ---------- ---------- ---------- ----- ----------
1      "TITI"."ARTICLE"                     Repaired          N/A    2000003   100       0:29

From the source we can verify the number of rows

SQL> select count(*) from article;


From the target we also can verify that we have the same number of rows and that the row deleted (idart=2000002) is now present.

SQL> select count(*) from article_rep;


SQL> select * from article_rep  where idart=2000002;

     IDART DESIGNATION                          PRIX
---------- ------------------------------ ----------
   2000002 2000002_designation            2000000002

We will retain that SharePlex provides powerful commands compare/repair to fix out of synchronization. Just note that during execution of these commands tables are locked (briefly on the source and more on the target) meaning that no transaction will not be possible during this period.
Note that the compare and repair commands can also be used for initial load (not recommended because of locks).


Cet article SharePlex Compare and Repair commands est apparu en premier sur Blog dbi services.

How to Avoid The “ORA-00979 not a group by expression” Error

Complete IT Professional - Mon, 2016-07-11 06:00
Have you tried to run a query and got the “ORA-00979: not a group by expression” error? Learn what it is and how to resolve the error in this article. Generating the ORA-00979 Error This error can be generated when your run SELECT queries on your database. Here’s an example of a query that will […]
Categories: Development

PeopleSoft Integration Broker (IB) Security

Securing the PeopleSoft Integration Broker (IB) ensures the security of messaging both within PeopleSoft applications and among third-party systems. The following are several of the key tasks that Integrigy performs during our PeopleSoft security configuration assessments - take a look today at your settings:

  • Ensure all inbound requests are required to use Secure Socket Layer security/Transport Layer Security (SSL/TLS)
  • Ensure that the default the PSKEY  password has been changed - The PSKEY is keystore contains all root and node certificates used by the Integration Gateway and PIA. Using the default or weak password is not best practice.
  • Ensure the IB node ANONYMOUS is appropriately privileged.  If IB connections do not specify a node name and credentials, IB will try to use the ANONYMOUS node and the “default user ID” tied to that node. This default user must not be a highly privileged user and should be granted the least number of privilege possible.
  • Review all other nodes for permissions appropriate for the business services supported by the node. Best practice is to use a unique UserID for each node that only has appropriate permissions to only to the required objects or related sets of operations.

The following attributes are also reviewed that govern IB activity :

Integration Broker Profile Values





IB Profile Status. If enabled, IB will show performance information.

For production or Internet facing set to off.


Enables logging


For production or Internet facing set to off.


Log Level  (if logging is enabled)

1= Standard gateway exception errors.

  1. 2 = All errors and warnings (Default.)
  • 3 = Errors, warnings and important information.
  • 4 = Errors, warnings, important and standard information.
  • 5= Errors, warnings important, standard and low importance information

Default: 2


Checks for recursion within messages (number of levels) to ensure that messages do not reference themselves.

Value between 3 and 50

Default: 20


Determines if Master processing returns statistics in the Output Information section after a Post.

For production or Internet facing set to off.


Pre-848 Tools Release

Default is N


By default, only one domain may be active in the Integration Broker system. However, PeopleSoft provides the option to enable the activation of multiple domains.

Off unless required.


Determines if the application server URL for a synchronous slave template uses the application server IP address:  e.g. URL format from <machine name>:<jolt port> to IP address



If you have questions, please contact us at info@integrigy.com

Michael A. Miller, CISSP-ISSMP, CCSP


PeopleSoft Database Security

PeopleSoft Security Quick Reference

Oracle PeopleSoft
Categories: APPS Blogs, Security Blogs

Reset your Datasources

Darwin IT - Mon, 2016-07-11 05:09
In most SOASuite and Oracle ServiceBus projects the Database Adapter is used. And often it is used to invoke PL/Sql functions and procedures. Actually, it's my favorite interaction method with the database, since the re-entrancy of the database adapter wizard is best there. To do a re-entrant update of a select or DML operation when for instance a column is added, is simply put often quite problematic.

But the thing is with Pl/Sql that when you update a pl/sql package the package state is altered and when calling it from another session you'll get a 'ORA-04068: existing state of packages has been discarded' error. And this is will occur for every connection in the pool of your datasource.

The solution is to reset the datasource. This can be done easily in the WebLogic Administration Console (http://adminserver:port/console). But nowadays you can do it as easily in Enterprise Manager Fusion Middleware Control. This can be quite convenient for developers since often you have EM already open because of your SOASuite tests.

To do this open the Weblogic Domain menu and select the option 'JDBC DataSources':
 Select the DataSource you want to reset, for the example I choose the 'EDNDataSource', but probably you'd not do this for one, but for a custom DataSource:

 Click the Control tab, select the DataSource in the table and click Reset:

Reset will drive WebLogic to recreate all the connections in the DataSource. This prevents popping up the message multiple times.

The functionality in Configuring, Monitoring and Controlling the Datasource  is similar as in the WebLogic Admin console. Only the layout is a little different.

Question on multiple DML in FORALL

Tom Kyte - Mon, 2016-07-11 04:46
Hi, I have below question on FORALL 1) <code> forall 1 .. l_var.count delete tab1 where id=l_var(i); forall 1 .. l_var.count delete tab2 where id=l_var(i); forall 1 .. l_var.count delete tab3 where id=l_var(i); </code> 2)...
Categories: DBA Blogs

Error when import large data from into Oracle Exadata

Tom Kyte - Mon, 2016-07-11 04:46
Hi, We hit this and no clue how it suddenly happen. Issue cronology as below (using Toad version 10.2, 10.5 and 11). 1) Export out from Exadata DB version table A with 4million records - ended successfully. 2) Import into Exadata DB ...
Categories: DBA Blogs

Adding subpartitions

Tom Kyte - Mon, 2016-07-11 04:46
Hello Sir I have a table partitioned on RANGE (daily) (interval partitioning) - there are about 300 partitions . Now I want to add a sub-partition to each of these based on a value in a different column. Should I come up with a script with 300 A...
Categories: DBA Blogs

Distinct Values from Multidimensional Collection

Tom Kyte - Mon, 2016-07-11 04:46
Hi Tom, Is there a way to retrieve Unique records from a multidimensional collection? DISTINCT and MULTISET operators seem to work on collections only when they have 1 field. I have the below <b>TYPE TYP_TABLE_REC IS RECORD ( SCHEMA_NA...
Categories: DBA Blogs

Best Way to Design a Table

Tom Kyte - Mon, 2016-07-11 04:46
I've to design Professional Tax table for Payroll(INDIA) , For every state it has its own prof tax according to their gross salary Consider One state Say MAHARASHTRA IF PR-GROSS < 2500.00 TAX= 30.00 IF PR-GROSS < 3500.00 TAX= 60.00 IF PR...
Categories: DBA Blogs

oracle SQL — Replace comma separated string with another set of values

Tom Kyte - Mon, 2016-07-11 04:46
I have the following table: <code>B_ID I_ID R_ID W00001 1234 1235,1237 B00001 1235 1236,1235 T00001 1236 1235,1235,1235 X00001 1237 1234,1236,1238 M00001 1238 1238</code> I need output like below using sql <code>B_ID I_ID New...
Categories: DBA Blogs

Clob and minus - alternatives for Diff Deltas

Tom Kyte - Mon, 2016-07-11 04:46
Hello Great Asktom Team, We can't do a minus query with a clob column. So what are the alternatives. I have ETL process which finds deltas of table based on minus. Can't do that with a column having data type clob So how to do it in pure...
Categories: DBA Blogs

Table creation script

Tom Kyte - Mon, 2016-07-11 04:46
Hai Tom sir...I have doubt. 1)How can i create this table.see below. RED GREEN YELLOW AAA 1 2 3 BBB 4 5 6 CCC 7 8 9 Please create this one.. and more question only... 2)How can in write a quer...
Categories: DBA Blogs

RFS not starting for standby

Tom Kyte - Mon, 2016-07-11 04:46
HI, what could be the reason for RFS not starting at standby. anything specific I have to look for. DR:- SQL> select inst_id,process,status,thread#,sequence#,block#,blocks from gv$managed_standby; SQL> INST_ID PROCESS STATUS ...
Categories: DBA Blogs

Loading SQL*Plus HELP into the Database

Hemant K Chitale - Sun, 2016-07-10 22:39
Oracle provides scripts to load the HELP command for SQL*Plus.

See $ORACLE_HOME/sqlplus/admin/help

The schema to use is SYSTEM, not SYS.

I demonstrate
(a) How to load SQLPlus Help  into the database
(b) How to customise the Help (e.g. add new commands)

[oracle@ora11204 help]$ cd $ORACLE_HOME/sqlplus/admin/help
[oracle@ora11204 help]$ ls -l
total 84
-rwxrwxrwx. 1 oracle oracle 265 Feb 17 2003 helpbld.sql
-rwxrwxrwx. 1 oracle oracle 366 Jan 4 2011 helpdrop.sql
-rwxrwxrwx. 1 oracle oracle 71817 Aug 17 2012 helpus.sql
-rwxrwxrwx. 1 oracle oracle 2154 Jan 4 2011 hlpbld.sql
[oracle@ora11204 help]$ sqlplus -S system/oracle @helpbld.sql `pwd` helpus.sql
View created.

58 rows created.

Commit complete.

PL/SQL procedure successfully completed.

[oracle@ora11204 help]$

The 'pwd`  (note the back-quote character, not the single quote character) is a way of specifying the current directory in Unix and Linux shells.   This specifies where the help datafile is located.  helpus.sql is the help data in English (US-English).

The scripts create a table called "HELP" in the SYSTEM schema.  SQL*Plus's "HELP" command then uses this table.

Examples :

SQL> connect hemant/hemant
SQL> help


Accesses this command line help system. Enter HELP INDEX or ? INDEX
for a list of topics.

You can view SQL*Plus resources at

HELP|? [topic]

SQL> help set


Sets a system variable to alter the SQL*Plus environment settings
for your current session. For example, to:
- set the display width for data
- customize HTML formatting
- enable or disable printing of column headings
- set the number of lines per page

SET system_variable value

where system_variable and value represent one of the following clauses:

ARRAY[SIZE] {15|n} NULL text
COLSEP {_|text} [SIZE {n | UNLIMITED}]
EDITF[ILE] file_name[.ext] SQLC[ASE] {MIX[ED] |
[TABLE [schema.]tablename] SQLN[UMBER] {ON|OFF}
ESCCHAR {@|?|%|$|OFF} SQLP[ROMPT] {SQL>|text}
LONG {80|n} WRA[P] {ON|OFF}
[HEAD text] [BODY text] [TABLE text] ORDERED|DEFAULT}|

SQL> help show


Shows the value of a SQL*Plus system variable, or the current
SQL*Plus environment. SHOW SGA requires a DBA privileged login.

SHO[W] option

where option represents one of the following terms or clauses:
PARAMETERS [parameter_name]
RECYC[LEBIN] [original_name]
SPPARAMETERS [parameter_name]

SQL> help connect


Connects a given username to the Oracle Database. When you run a
CONNECT command, the site profile, glogin.sql, and the user profile,
login.sql, are processed in that order. CONNECT does not reprompt
for username or password if the initial connection does not succeed.

CONN[ECT] [{logon|/|proxy} [AS {SYSOPER|SYSDBA|SYSASM}] [edition=value]]

where logon has the following syntax:

where proxy has the syntax:
NOTE: Brackets around username in proxy are required syntax


Remember !  These are SQL*Plus commands, not SQL Language commands.  So you won't see help about CREATE or ALTER or SELECT and other such commands.

Since, it uses a plain-text file (helpus.sql in this case) to load the help information, it is possible to extend this.

For example, I copy helpus.sql as helpcustom.sql and add these lines into the scrip file :

INSERT INTO SYSTEM.HELP VALUES ('DBINFO', 2, 'This Hemant''s Test Database');
INSERT INTO SYSTEM.HELP VALUES ('DBINFO', 3, 'A Playground database');

INSERT INTO SYSTEM.HELP VALUES ('OWNERINFO', 2, 'Test Database owned by Hemant');
INSERT INTO SYSTEM.HELP VALUES ('CONTENTS', 2, 'Various Experiments by Hemant');

INSERT INTO SYSTEM.HELP VALUES ('WHO IS HEMANT', 3, 'https://hemantoracledba.blogspot.com');


and then I run the command :

sqlplus -S system/oracle @helpbld.sql `pwd` helpcustom.sql

And view the results :

SQL> connect hemant/hemant
SQL> help dbinfo

This Hemant's Test Database
A Playground database
Running on Linux

SQL> help ownerinfo

Test Database owned by Hemant

SQL> help who is hemant

Hemant K Chitale

SQL> help startup


Starts an Oracle instance with several options, including mounting,
and opening a database.

STARTUP options | upgrade_options

where options has the following syntax:
[FORCE] [RESTRICT] [PFILE=filename] [QUIET] [ MOUNT [dbname] |
[ OPEN [open_options] [dbname] ] |

where open_options has the following syntax:

and where upgrade_options has the following syntax:

SQL> help shutdown


Shuts down a currently running Oracle Database instance, optionally
closing and dismounting a database.



And, so, the SQL*Plus HELP command can be customised !


Categories: DBA Blogs

Oracle Multitenant feature name

Yann Neuhaus - Sun, 2016-07-10 15:46

We all know what is the multitenant option: a container database (CDB) with multiple user pluggable databases (PDB). This requires Enterprise Edition plus option. But you can use the same architecture without the option and even in Standard Edition: a CDB with only one user PDB. It is called “single-tenant” or “lone PDB”. How do you call this new 12c architecture?

I call “multitenant architecture” the following set of features, available in all editions:

  • dictionary separation between system metadata/objects and user metadata/object
  • unplug / plug features to transport PDBs
  • create a new PDB as a clone of another existing PDB
  • ability for a session to switch between PDB and CDB$ROOT explicitly with ‘ALTER SESSION’ or implicitly through metadata and object links
  • ability to read data for several containers in one query

But it may look strange to use the “multitenant” term when in Standard Edition or Enterprise edition without option.

Container database (CDB)

One idea would be to simply call it ‘CDB architecture’, because it’s the architecture of the container database, and because a database without this architecture is called “non-CDB”:

@franckpachot Oracle docs says „CDB architecture“ and „Multitenant Architecture“ – I prefer CDB, because there’s Non-CDB, too :)

— Markus Flechtner (@markusdba) July 8, 2016

consolidated database (CDB)

However, as very often with Oracle, the names change and the acronyms remain. Old names are found in the software, before new name is made public to market it. We all know that CDB stands for “container database” because a CDB is a container that contains at least 3 containers (CDB$ROOT, PDB$SEED and your PDB) and more when you have the multitenant option. However I see no mention of that name in ORACLE_HOME/rdbms/admin scripts:
[oracle@CDB ~]$ grep -i "container database" $ORACLE_HOME/rdbms/admin/* | wc
0 0 0

It seems that the original meaning of CDB was “consolidated database”:

[oracle@CDB ~]$ grep -i "consolidated database" $ORACLE_HOME/rdbms/admin/* | wc
58 465 7276
[oracle@CDB ~]$ grep -i "consolidated database" $ORACLE_HOME/rdbms/admin/*.bsq
/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/dcore.bsq:REM Consolidated Database.
/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/dcore.bsq:REM SEED Pluggable Database in a Consolidated Database.
/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/doptim.bsq:Rem the caching in library cache in a Consolidated Database.
/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/dsec.bsq: * Consolidated Database
/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/dsec.bsq: * Consolidated Database

So, the idea behind CDB is the same as behind multitenant: it’s consolidation of several pluggable databases. And then, consolidation is not really what is done by single-tenant where we have only one user PDB per CDB and where this architecture requires 3 containers instead of one non-CDB.

Pluggable databases

Another idea would be to call it “pluggable databases architecture” because this is the real evolution. User tablespaces are transportable for a long time, since 8i introduction of extended rowid and locally managed tablespaces. 12c brought the same for system tablespaces so that PDBs are fully transportable physically, thanks to separation of dictionary.

The real point of this new architecture is the total separation of system data/metadata and user data/metadata, the separation of system DBA role and application DBA role, and this is pluggable databases. Multitenancy is just one thing that is possible with this new architecture. It’s important today because it’s required for the cloud (for consolidation and easy provisioning).

Oracle Multitenant

Actually, the feature was called ‘Oracle Pluggable Database’ until was released publicly with a new name: Oracle Multitenant. And this is the name we can see in Database Feature Usage Statistics. Well, if you look at the first patchset of 12cR1,, you will see the old name ‘Oracle Pluggable Databases’ but this is a bug (Patch 20718081 changes back the name).

Here is what you see from Database Feature Usage Statistics after an upgrade from to, in EM Express:



SQL> select name, version, detected_usages, currently_used, aux_count from dba_feature_usage_statistics where name like '%Multitenant%' or name like '%Pluggable%';
------------------------------ ----------------- --------------- ----- ----------
Oracle Multitenant 98 FALSE 1
Oracle Pluggable Databases 32 FALSE 1

So, bug aside, the name of the feature is ‘Oracle Multitenant’ and this is true for any container database, because the feature is considered as used as soon as V$DATABASE.CDB=’YES':
select count(*) into feature_boolean from v$database where cdb = 'YES'

The usage of the multitenant option is detected by the AUX_COUNT which is the number of user PDBs:
select count(*) into aux_count from v$pdbs where con_id > 2
CON_ID=0 is for the CDB, CON_ID=1 is for CDB$ROOT, CON_ID=2 is for PDB$SEED and user PDBs start at CON_ID > 2

So the name is “multitenant” whatever the number of PDBs.

So what?

I prefer to stick with “multitenant architecture” even when used without the multitenant option. It’s a good way to keep in mind that, even if it brings lot of interesting features for single-tenant as well, Oracle has taken the step of dictionary separation with the motivation of Cloud, Consolidation and Multitenancy. Charged options are a good trigger to increase priority of evolution requests…
However, this “multitenant architecture” brings very interesting features to Standard Edition and Enterprise Edition even without option. And it you doubt, I’ll try to convince you in San Francisco, September 18th.


Cet article Oracle Multitenant feature name est apparu en premier sur Blog dbi services.

SQL questions for improving skills

Tom Kyte - Sun, 2016-07-10 10:26
Hi Tom, My question is little weird. Where can i find good SQL questions (scenarios) for practice. I tried google but did not find good list. Can you please suggest any website or book/magazine? Thanks, DP
Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator