Where do I create a create_file_dest file for a pdb?

I’m trying to learn how to create a PDB from PDB$SEED and I just have one question about this video that has been great at spoon feeding me information. What program is this guy using to mkdir for his create_file_dest file? Also, where is he creating this file? Or more importantly, where should one create this file and how do I know that i’m in the right place? He just exits and creates a directory.. but where?

enter image description here

Go to Source
Author: ellie-lumen

Oracle Procedure to dynamically insert and update comma separated string into dest table

I am using below query to split the given string in DISP_DATA tbale, data column and push it into destination table.

Destination table PUSH_DATA_TEMP Cols:
DISP_PK-> Primary key should be uniqe incrementing numbers per id.
PID-> ID for every string

insert into push_data_temp (pid,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11)
with rcte (id, data, lvl, result) as (
  select id, data, 1,
    regexp_substr(data, '("[^"]*"|[^, "]+)', 1, 1, null, 1) )
  from disp_data
  union all
  select id, data, lvl + 1,
   regexp_substr(data, '("[^"]*"|[^, "]+)', 1, lvl + 1, null, 1) 
  from rcte
  where lvl <= regexp_count(data, '("[^"]*"|[^, "]+)')
select *
from (
  select disp_pk,id, lvl, replace(result,'""','') as final
  from rcte
pivot (max(result) as col for (lvl) in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11));

However i have requirement now to wrap it in pl/sql procedure and split & insert/update rows if already existing, all dynamically.

  1. I need to make the columns above dynamic, so the string could get split in 11 columns as above or 15. Maximum number is 30.

So i need to write a procedure, that splits and pushes string into columns dynamically.

  1. So it should insert rows and if the row already exists, update the columns, both dynamically. Probably with loops. So condition for inset and update for each row is mandatory.
    ID/PID is the id of the row.


  1. For id_pk, it should be unique number generated incrementing by 1.
    ID/PID, needs to be passed majorly as parameter which identifies each string.

Sample data:
The first string for PID=100 has string split into 11 columns and the second with PID=110 will split into 12 columns and so on.

create table disp_data(id number, data varchar2(4000));

Insert into disp_data(id,data) values(100,
'"Project title as per the outstanding Requirements","The values are, not with respect to the requirement and analysis done by the team. 
Also it is difficult to, prepare a scenario notwithstanding the fact it is difficult. This user story is going to be slightly complex however it is up to the team","Active","Disabled","25 tonnes of fuel","www.examplesites.com/html.asp&net;","","","","","25"');

Insert into disp_data(id,data) values(110,
'"Project title afor BYU heads","The values are, exactly up to the requirement and analysis done by the team. 
Also it is difficult to, prepare a scenario notwithstanding the fact it is difficult. This user story is going to be slightly complex however it is up to the team","Active","Disabled","25 tonnes of fuel","www.examplesites.com/html.asp&net;","Apprehension","","","","25","Stable"');


Go to Source
Author: Vini

Oracle index analyze stats – managed by whom?

a question about managing indexes.

In what cases do indexes stop being analyzed?

What’s the way to get indexes analyzed automatically?

Do I still need to manage index analysis somehow manually if they break? How can they break? How do I know they are broken? How to fix? In what intervals?

Perhaps there’s no auto-management? This would be very surprising. But if this is the case, how to manage indexes manually? Thanks.

Go to Source
Author: AlikElzin-kilaka

Oracle 12c – Compile A Trigger In SqlDeveloper and SqlPlus

When I try to compile a trigger on SqlDeveloper with right click on trigger and select compile, it’s compiling. But when I try to compile a trigger on SqlPlus with this command: alter trigger SCHEMA_NAME.TRIGGER_NAME compile; it’s not compiling but it gives an output about successfully compiling. What is the meaning of this?

Go to Source
Author: jrdba

Language setting for ‘TZD’ format in TO_CHAR (timestamp with time zone)

Which setting controls the language of ‘TZD’ or ‘TZR’ in the TO_CHAR(timestamp with time zone) function?

select to_char(systimestamp at TIME zone ‘AMERICA/EDMONTON’, ‘TZD’) from dual;

returns the English value for TZD “MDT” (Mountain Daylight Time), which is expected as the default value.

However, I can’t find which parameter or setting to use for it to return the French value of TZD, i.e. “HNR”

  • I’ve tried adding “, ‘nls_date_language=FRENCH'” to the TO_CHAR example above, as well as altering the session parameters NLS_LANG and NLS_DATE_LANGUAGE, but to no avail.

Marc L.

Go to Source
Author: Marc Lalonde

How to add a new listener on a new port and restrict it to one instance only

I am running oracle 11.2.3 and we have a server with multiple instances running. All of them are registered to port 1521 with the default listener. Now we are required to make one instance available on a new port. I was wondering if there is a way to add a new listener to a new port and restrict it to register only one instance so that this listener can not make connections for other instances.

Go to Source
Author: user211005

Load Generators Giving 100% utilization – Storm Runner

We are trying to hit our AUT(Application Under Test) with almost 5000 users. We have tried twice but once almost 2500 – 3200 users are in the application, Both of the Load Generators spike to 100% utilization.
We are using Storm runner for performance testing the application.The Protocol of scripts is Multi Protocol:- Web HTTP/HTML and Oracle NCA.
The configuration of both the Load Generator:
16 core processor. 32GB Memory.
LG1 has processor Platinum 8272CL
LG2 has processor E5-2673 v4

Please do let us know how to resolve this or else is there any way out to calculate accurately as in how many Load Generator do we need. We have also done a lot of research by us but it fails astonishingly in main test.

Go to Source
Author: Aashish Sharma