Recognized Way Of Replacing Entire DB With Impdp
Nov 15, 2012
I did an expdp on the prod DB and have been doing a straight impdp as a test, just to have data to work with, but it spews 214 errors. Mostly these:
ORA-31684 lots
ORA-39083 some
ORA-39151 lots
ORA-39082 lots
ORA-39111 lots
ORA-39112 lots
I can see that I can use REUSE_DATAFILES & TABLE_EXISTS_ACTION to overwrite tables by default, but is there a recognised way of replacing the entire DB with the impdp? Do I just create the instance, (with the init file) and not build/init the tables, or what? I'll experiment, but I'm just interested if there is a DBA best practice for this sort of thing.
View 3 Replies
ADVERTISEMENT
Jul 24, 2013
The idea is to use some constant value in PL/SQL code with requirement to feed it to Oracle as value but not bind variable. Such constants used in multiple places in the code, so wants to declare it but from DB point of view it should be value. In my case Oracle will choose much better execution plan with real value for the table.
I tried to use constant, e.g:
CODEdeclare
const1 constant number := 1;
beging
[Code].....
But in sqlarea it represented as: SELECT SUBSCRIBER_ID FROM SUBSCRIBERS WHERE STATUS = :B1
View 5 Replies
View Related
Feb 16, 2011
1) I mistakenly changed my 'oracle_home' some days ago, as below: my computer --> environment variable ...
2) Then i realised that and edit the oracle home and saved as below: C:oracleproduct10.2.0db_1
But i am now not able to open sqlplus. it shows me the below error. 'sqlplus' is not recognized as internal or external command.
View 5 Replies
View Related
May 15, 2013
My environment settings:
Oracle Database 11.2.0.3.0 64 bits
Weblogic 10.3.5
APEX 4.2.2.00.11
When I try to access the development environment of the apex or any other screen, this error occurs intermittently.These errors (ORA-01821: date format not recognized) and (ORA-02063: preceding line from) are related to existing dblink's.
Parameters from apex database:
SQL> select * from nls_instance_parameters;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_SORT SPANISH
NLS_DATE_LANGUAGE AMERICAN
[code]....
Parameters from dblink database:
SQL> SELECT * from NLS_instance_PARAMETERS;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_SORT SPANISH
NLS_DATE_LANGUAGE
[code]....
View 0 Replies
View Related
Dec 30, 2010
While i like to start CSS service to create new ASM instance in my own pc for testing purpose gettting the below errors "'localconfig' is not recognized as an internal or external command, operable program or batch file.".
View 1 Replies
View Related
Jul 26, 2011
I have an already populated table that refers to a wrong foreign key in another table. I have table abc that has the column fk_id. this column currently refers to column x_id1 in the table abc while it should refer to column x_id2 in xyz. I am trying to replace the data but I guess I miss something in the logic or the right way of doing it:
following is my trial:
create table abc
(
pk_id number(4) primary key,
abc_name varchar2(10),
fk_id number(4)
);
create table xyz
(
x_id1 number(4),
x_id2 number(4),
x_name varchar2(10)
);
commit;
insert all
into abc values (10,'ab1',1)
into abc values (11,'ab2',2)
into abc values (12,'ab3',5)
into abc values (13,'ab4',7)
into abc values (14,'ab5',9)
into xyz values (1,1,'d1')
into xyz values (2,2,'d2')
into xyz values (5,3,'d3')
into xyz values (7,4,'d4')
into xyz values (9,5,'d5')
select * from dual;
commit;
--this following select returns 5 rows
select * from abc, xyz where abc.fk_id = xyz.x_id1;
-- the following update only updates 3 rows and sets the other 2
-- to null!
update abc set fk_id = (select x_id2 from xyz where xyz.x_id1 = abc.fk_id);
commit;
select * from abc;
View 3 Replies
View Related
Apr 23, 2010
Is there a way to replace a field with another if the particular value returned is null?
View 11 Replies
View Related
Jul 25, 2012
In forms 11 g, whenever, We did any menu change, and deploy in server, same changes are not reflecting in clients machine immediatly. I even stop forms services and restart.After some time... generally after 1 day, it reflect the new changes.In forms 10G, I never face this issue. As soon as i close the old menu and re-open, new changes reflect.
View 2 Replies
View Related
Jun 22, 2010
I am facing a simple problem, but could not resolve as yet, i want to replace two string 'M/S' and ' " ' with null, i know this command SELECT REPLACE(' " M/S Private linker " ','M/S',NULL) FROM dual Which command i should use
View 3 Replies
View Related
Apr 13, 2013
I am looking to replace the vowels in a string, and get rid of the empty spaces afterwards.
Our Lab Book suggested that we use the REPLACE function, but I have only been able to get it to work using the TRANSLATE function.
Also, I can`t figure out how to get the empty spaces out of the string when output, I tried to use the TRIM command unsuccessfully.
1) Is it possible to use REPLACE instead of TRANSLATE to replace the vowels?
2) How would i get rid of the empty spaces when the vowels have been replaced?
CREATE OR REPLACE PROCEDURE replacingvowels (vowels IN VARCHAR2, var IN VARCHAR2)
IS
vowelreplace VARCHAR2(50);
BEGIN
[code]....
View 5 Replies
View Related
May 21, 2013
I am doing some ETL that I need to run "faster". The function in which I am interested removes low ascii code characters from a string. Please see the timing below and the definitions of the of the functions below those. I am selecting just the first 100K rows for testing and timing purposes only. In production, we are doing millions of records several times a day, thus the desire for "faster". Selecting with no functions is very fast, 0.2 seconds. We would really really want to convert at least 100K rows per second.
The best I can do is get it down to around five seconds using clear_nonlegal. That is, ironically, the one that I thought would be the slowest. It's making thirty-one calls to REPLACE. I would have guessed that the other two would be much faster. I am guessing that REPLACE is just much better optimized than TRANSLATE and, of course, my homegrown PL/SQL, which isn't optimized at all.
So, my question is thisif there is a way I can optimize my custom function, or maybe know of a better already optimized standard SQL and/or Oracle function that would do the job? I am thinking about trying to use a Java stored procedure, but I have never done that before, I am not currently set up for it, if it would be any faster anyway. Is Java faster with string manipulation the PL/SQL? I am thinking it would be really fast to call a C method,
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as aggs@AGGSTEST
SQL> set timing on
SQL> SELECT COUNT(*)
2 FROM (SELECT DISTINCT keyword_dest_url
3 FROM se_keywords sek
[code]...
View 18 Replies
View Related
May 16, 2013
why the REPLACE function is not replacing. I assume it has something to do with the ASCII value being zero.
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as aggs@AGGSTEST
SQL>
SQL> SELECT str,
2 e9,
3 REPLACE(str, '%E9', e9) replace,
4 regexp_replace(str, '%E9', e9) regexp_replace,
5 utl_url.unescape(str, 'UTF8') utl_url,
6 ascii(e9) ascii
7 FROM (SELECT 'Soir%E9e' str,
8 chr(to_number('E9', 'xx')) e9
9 FROM dual);
STR E9 REPLACE REGEXP_REPLACE UTL_URL ASCII
-------- --- ------- -------------- ------- -----
Soir%E9e é Soire Soirée Soirée 0
View 5 Replies
View Related
Nov 23, 2012
If i have a table, my_table, with 20 columns, and i have something like:
declare
...
begin
for r in (select * from my_table) loop
insert into my_table (...) values (...)
end loop;
end;
So, I want to insert a row into that table, with values for all columns being those returned by the for loop, but without a column (i'm giving the value for that column).For example, the first 19 rows should contain the values from the currently record in 'for.. loop', and the last column to contain a static value.Is it possible to do this, without specifying in the 'values' clause, something like (r.col1, r.col2, .... r.col19, 'G')? meaning other insert method.
View 7 Replies
View Related
Jan 11, 2011
Need to create or replace a Standard Sunguard package. When I'm executing, It is asking some series of parameters. when I cancel It is executing but the Web page is not opening giving error like PL/SQL: could not find program unit being called . Not understanding the exact error.
View 5 Replies
View Related
Dec 15, 2010
The following query calls the function get_meter_desc 8 times.
SELECT iu.instd_unit_id, iu.fk_cust_id, bill_cd,
iu.mfg_prod_cd, iu.mfg_prod_seq_no, ccequip.fk_mkt_cd,
eff_tmstmp, cust_ord_id, ord_dt, iu.xnac_co_cd,
iu.xnac_div_cd, smmr_wvr_cd, warr_expn_dt,
iu.auto_replen_ind, iu.ms_stat_cd,
inst_dist_id, instn_dt, iu.last_auto_dt,
iu.replen_freq_vlu, cpc_pln_cd, std_sply_ind,
emcv_total_qty, tot_actl_cpy_qty, init_emcv_tot_qty, ms_tran_cd,
[code]...
How can I replace the function call by the join in the main query?
View 19 Replies
View Related
May 13, 2011
Backup your entire database, without archived logs, while the database is open for user activity. This backup should be the base for an incremental backup strategy
View 1 Replies
View Related
Jan 31, 2013
Getting starting with diving deeper into APEX and reading the developers guide all 800 pages of it! Thing is I'd like to get started with some of the more interesting stuff.
I have created a form page based on the "Form on a Table with Report". The report part looks fine. What I'd like to do now is use an API I've built to change how the insert and update works. So when the submit button is pressed it rather performs an API call to the procedure/function.
1. Should the API be a function or procedure. In my playing around I saw that a procedure might work better or easier to implement
2. How would I go about creating a call for the button to use instead of the current processes?
----------------------------
Apex 4.2
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Linux version 2.6.32-279.19.1.el6.i686
----------------------------
View 4 Replies
View Related
Sep 13, 2013
I'm trying to make changes to a PL/SQL procedure in an application process and no matter what I do in the PL/SQL text (even removing semi-colons and messing up syntax) no changes register when I call the procedure from a page process. I've also tried creating a new application process with a similar procedure of a different name, and no page processes will allow me to call the procedure, saying that it needs to be declared. APEX 4.1.1
View 1 Replies
View Related
Nov 17, 2010
I need to select all names that can possibly have ether accents or french characters what is the easiest way?
View 1 Replies
View Related
Jun 20, 2013
I have a "simple" query that needs to extract all of the XML messages stored in a CLOB column in a table for a data deidentification project. This query
select c.cc_data_area.getClobVal() as cc_xml
from ci_cc c;
This query returns an ORA-22806: not an object or REF
cc_data_area is the clob column, and contains messages longer than 4000 characters.
View 1 Replies
View Related
Sep 20, 2012
We have a requirement, to highlight an entire column of a classic report based on a query to red. So if the search of the report returns column1, column2 as the output. Column 2 (text) should be highlighted in red across all results rows.
In some forum responses, I see using some id format, one can try to highlight a single column in a row. Not sure how can this be done across the whole column of multiple rows.
View 9 Replies
View Related
Feb 18, 2013
I want to change the current visual attribute color of my form at the application level, reason being i don't want to generate all the forms again from scratch.I know that we can change this using visual attributes but is there a way to change this through some form setting?(Oracle 10g)
View 3 Replies
View Related
Aug 24, 2011
I have 2 tables. Cbxrd and Cbxrdlog. If the Cbxrd table having creation date column. when it inserts the row. if the column is null value. then the entire insert script row will be inserted into Cbxrdlog table sqltext column.
i have attached the trigger script. when i execute the table but i shows error like
"Ora-0756 Quoted String not properly terminated".
"Ora-04098: trigger os_wm_sit_owner.cbx trigger is invalid and failed revalidation.
View 5 Replies
View Related
Jun 29, 2011
I am trying to insert a row in a table and getting the below error.
SQL> insert into tbl_force_charging(ANI, date_time, durations,src, circleid)
2 values ('9569333585','29-JUN-11 03.19.41.000000000 PM','1027','51010','BIR'
)
3 ;
values ('9569333585','29-JUN-11 03.19.41.000000000 PM','1027','51010','BIR')
*
ERROR at line 2: ORA-01830: date format picture ends before converting entire input string
Table Structure is
Name Null? Type
----------------------------------------- -------- ---------------------------
ANI VARCHAR2(10)
DATE_TIME DATE
DURATIONS VARCHAR2(10)
SRC VARCHAR2(10)
CIRCLEID VARCHAR2(10)
SQL>
View 11 Replies
View Related
Nov 11, 2013
Grid version: 11.2.0.4Platform : OEL 6.4
To shutdown the entire crs stack, I ran crsctl stop cluster -all from one node. After the command execution, the below mentioned processes were still running on all nodes.
[root@intsmdp01 ~]# ps -ef | grep d.binroot 38562 33228 0 19:12 pts/2 00:00:00 grep d.binroot 80820 1 0 Nov09 ? 00:20:56 /crs/product/11.2.0/bin/ohasd.bin rebootgrid 81448 1 0 Nov09 ? 00:01:22 /crs/product/11.2.0/bin/mdnsd.bingrid 81459 1 0 Nov09 ? 00:01:55 /crs/product/11.2.0/bin/gpnpd.bingrid 81473 1 0 Nov09 ? 00:17:26 /crs/product/11.2.0/bin/gipcd.binroot 81484 1 20 Nov09 ? 09:10:27 /crs/product/11.2.0/bin/osysmond.bin
So , I had to manually run crsctl stop crs on all nodes.
[root@intsmdp01 ~]# /crs/product/11.2.0/bin/crsctl stop crsCRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'intsmdp01'CRS-2673: Attempting to stop 'ora.crf' on 'intsmdp01'CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'intsmdp01'CRS-2673: Attempting to stop 'ora.mdnsd' on 'intsmdp01'CRS-2677: Stop of 'ora.crf' on 'intsmdp01' succeededCRS-2673: Attempting to stop 'ora.gipcd' on 'intsmdp01'CRS-2677: Stop of 'ora.mdnsd' on 'intsmdp01' succeededCRS-2677: Stop of 'ora.drivers.acfs' on 'intsmdp01' succeededCRS-2677: Stop of 'ora.gipcd' on 'intsmdp01' succeededCRS-2673: Attempting to stop 'ora.gpnpd' on 'intsmdp01'CRS-2677: Stop of 'ora.gpnpd' on 'intsmdp01' succeededCRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'intsmdp01' has completedCRS-4133: Oracle High Availability Services has been stopped.[root@intsmdp01 ~]#
Is there a way to bring down the entire crs stack in all the nodes in the cluster from one node ?
View 3 Replies
View Related
Feb 26, 2013
when i run this query i am facing date format error.
select sbrueregister.UEIMSI,sbrueregister.fapid,sbrfapslid.slid,sbrfapslid.ACTIVATION_TS,sbrfapslid.DEACTIVATION_TS from SBRFAPSLID INNER JOIN sbrueregister ON sbrfapslid.fapid=sbrueregister.fapid where sbrfapslid.slid='1234567890' and sbrueregister.registeredat between TO_DATE('2013-02-1.12.0. 10. 123000000','YYYY-MM-DD HH.MI.SS.SSSSSS')and TO_DATE('2013-02-1.12.9.10.123000000', 'YYYY-MM-DD HH.MI.SS.SSSSSS');
ORA-01830: date format picture ends before converting entire input string
View 1 Replies
View Related
Feb 21, 2010
I am using
SELECT TO_CHAR(TO_DATE((:BILL_VALUE),'J'),'Jsp') FROM sys.dual;
its givinig right result for
1023411->One Million Twenty-Three Thousand Four Hundred Eleven
but if i will change it to 10234111 then this query giving an error
ORA-01830: date format picture ends before converting entire input string.
Is there any limitation or is there any other built in function to get the number to a word.
View 2 Replies
View Related
Dec 3, 2010
I have a scenario where I have to get all the available dates of a resource. I am using the below query to get it.
Select Avail_Date AS MONTH
, Resource_Id
FROM res_tsk
, (SELECT Rownum - 1 + TRUNC (sysdate) avail_date
FROM Dual
[code].......
The result of this is:
Month Dates Resource_ID
12/3/10 0:00 NULL
12/4/10 0:00 NULL
12/5/10 0:00 NULL
12/6/10 0:00 100033868
As I am doing a outer join, if the resource is not available on a particular day the resource_id is coming as NULL as it is not available. Is there any way to populate this NULL resource_id with the original resource_id as the resource_id is same for all the result set.
I need the output to be
Month Dates Resource_ID
12/3/10 0:00 100033868
12/4/10 0:00 100033868
12/5/10 0:00 100033868
12/6/10 0:00 100033868
View 3 Replies
View Related
Jun 17, 2010
i want to replace 4 digit number in a given string with the same number incremented by 10000.
That mean in the given sting 1201 should be replace by 11201 (Icremented BY 10000).
Input String:
<query><matchAll>true</matchAll><row><columnId>1201</columnId><dataType>31</dataType><op>Like</op><val>North America - Houston</val></row><row><columnId>1212</columnId><dataType>31</dataType><op>!=</op><val>Agreement Date Mismatch</val></row><row><columnId>1212</columnId><dataType>31</dataType><op>!=</op><val>Facility Type Mismatch</val></row><row><columnId>1224</columnId><dataType>31</dataType><op>Like</op><val>y</val></row></query>
Required output :
<query><matchAll>true</matchAll><row><columnId>11201</columnId><dataType>31</dataType><op>Like</op><val>North America - Houston</val></row><row><columnId>11212</columnId><dataType>31</dataType><op>!=</op><val>Agreement Date Mismatch</val></row><row><columnId>11212</columnId><dataType>31</dataType><op>!=</op><val>Facility Type Mismatch</val></row><row><columnId>11224</columnId><dataType>31</dataType><op>Like</op><val>y</val></row></query>
View 7 Replies
View Related
Aug 7, 2012
I've read a lot about the different types of backup available with Oracle (hot and cold backup). However, I was thinking of a different way of performing this task. I'm currently using Windows Server 2008 R2 and Oracle 11g Standard Edition. I'd like to schedule an entire backup of my server via the utility "Windows Server Backup" (available for free).That way, I could recover my entire server with all the programs and files in case the latter crashes.I'm wondering if this solution could be used as a way of backing up (and recovering) the Oracle database. Should I still set up a regular hot backup with the Archivelog mode enabled in case some operations/transactions were being done at the time of the crash (for the data integrity)?
View 2 Replies
View Related
