SQL & PL/SQL :: Convert Data With Different Charsets Into Other Databases?
Mar 28, 2011
we want to use database link to connect a Database for operating the select,update or ... commands,our destination database is WE8ISO8859P1 and current database is AR8MSWIN1256 cahrset, but when we operate a command to view data,all NonEnglish characters appear odd wich we can not recognize the appeared text, also if we use convert function no change would make, view right charachters with our database link.
it doesnt work with convert founction
select convert(menu_name,'US7ASCII','WE8ISO8859P1'),
convert(menu_name,'ar8mswin1256','WE8ISO8859P1'),
convert((convert(menu_name,'US7ASCII','WE8ISO8859P 1')),'ar8mswin1256','WE8ISO8859P1'),
menu_name
from T$R_MENU@"TO201.US.ORACLE.COM"
WHERE MENU_ID=601011;
result is
EU?iY ?C?ICa? OCOaI?
E???? ?C?IC?? ?C??I?
EU?iY ?C?ICa? OCOaI?
E???? ?C?IC?? ?C??I?
View 1 Replies
ADVERTISEMENT
Mar 27, 2012
I have the task to migrate the total databases(Exact copy to be moved to another server).The current server is going for format.After I did the following steps I am getting the tablespaces(databases)-4 sizes same ,but I am facing issue like some default tablespaces i.e temp,system are not matching.
temp tablespace
***************
current server - 4.0(approximately)
Migrating server - 160 MB
System tablespace
*****************
current server - 580 MB
Migrating server - 220 MB
Also I checked the tables are also matching for the 4 databases.Also Provide the solution or method which is correct.
steps done for migrating(By me)
********************************
EXPORTING DATA USING DATAPUMP
*********************************
1 From command prompt MKDIR 'c:oraclexeapp mp';
2 From SQL prompt conn system/kotak;
3 create or replace directory dmpdir as 'c:oraclexeapp mp';
4 grant read,write on directory dmpdir to kotak;
5 From command prompt
expdp system/kotak@xe full=Y directory=dmpdir dumpfile=xe.dmp logfile=expdpxe.log;
IMPORTING DATA USING DATAPUMP
*****************************
in another server machine
1 From SQL prompt conn system/kotak;
2 create or replace directory dmpdir as 'c:oraclexeapp mp';
3 grant read,write on directory dmpdir to kotak;
4 From command prompt set ORACLE_SID=xe;
5 impdp system/kotak@xe full=Y directory=dmpdir dumpfile=xe.dmp logfile=impdpxe.log;
IN OUR PROCESS we created the below tablespaces and user before IMPORTING created 4 tablespaces
1. kotak
2. kotakdb
3. wired_data
4. ferrari
Created Users which are there in 219 server
1. KOTAK
2. KOTAKDB
3. FC_80
4. DEMOINTERNETBANK
5. DEMOINTERNETBANKDB
6. CREDITCARD
View 1 Replies
View Related
Oct 12, 2012
I want copy some tables from one database to another. Which of following is the fastest way:
- INSERT /*+APPEND*/ via database link
- Transportable Tablespaces
- datapump IMP/EXP (I supposed it's the slowest way)
View 2 Replies
View Related
Oct 4, 2011
I want to compare the table contents across the two different databases , one is on netezza and other is on Exadata.
View 2 Replies
View Related
Nov 22, 2012
We want to replicate the data between the databases.We have 4 databases in a network.If there will be any change in database 1,e.g. updation in any table,it should automatically replicate on other 3 databases.or user will change something in database 2 ,it should replicate on other 3 databases and vice versa. All 4 databases have same schema and same configuration.
View 1 Replies
View Related
Apr 18, 2009
I am current using Pro*C to upload data from a flat file to database.
Now I have to transfer information(data) between databases. As a rookie, I do not know an efficient of way doing it.
View 4 Replies
View Related
Jun 1, 2010
1) how to to compare sybase table data with oracle database table data?
2) how to connect sybase database from oracle database?
View 5 Replies
View Related
Mar 30, 2011
I have one primary database server and one physical standby database serve. but i am unable to fix "ORA-16607" while enabling configuration. during log switch redo data is being applied to the phy standby side but some thing wrong happens in BROKER
here are details........about my configuration
Primary database name PRIM
Physical Database Name STAN
net service PRIM
net service STAN
DGMGRL> show configuration
Configuration
Name: test
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
prim - Primary database
stan - Physical standby database
Current status for "test": Warning: ORA-16607: one or more databases have failed
======================================================
Alert log file First para show no problem for for redo to be transmitted to stndby
======================================================
LNS1 started with pid=48, OS id=5408
Thu Sep 21 21:32:03 2006
Thread 1 advanced to log sequence 59
Current log# 1 seq# 59 mem# 0: /u01/app/oracle/oradata/PRIM/redo01.log
[code]......
View 2 Replies
View Related
Sep 29, 2010
I have one primary database and one physical standby database on my data guard environment.
Now i want one more physical standby database in my data guard environment, meaning i want 2 physical standby. since this is a production environment, i need to take great care. However, downtime will be mandatory.
[u]init.ora for production:[/u]
pup2.__db_cache_size=6241124352
pup2.__java_pool_size=33554432
pup2.__large_pool_size=134217728
pup2.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
pup2.__pga_aggregate_target=5771362304
[code]......
init.ora for my first primary standby
pup2.__db_cache_size=6509559808
pup2.__java_pool_size=33554432
pup2.__large_pool_size=67108864
pup2.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
pup2.__pga_aggregate_target=5502926848
[code].......
View 5 Replies
View Related
Mar 11, 2013
oracle 11.2.0.3 I have insert only tables that receive srecords from multiple processes at a rate of about 200/second. Each transaction can have up to 100 records. I have another set of processes that queries this table for the latest data. These processes run anywhere from once a minute to once an hour. Processes do not get all of the data. They get data based on a type field.
Both of these are from java middle tiers. The process that queries data (The subscriber) does so at the request of many remote servers (there will be vast numbers). I am not allowed to expose these downstream databases to the internet (they are not oracle DBs anyway) so I cannot use streams or golden gate
So basicallyInsert Process: multiple sessions that combined insert records up to 200/second. There will be between 1-100 records per commit.
Query Process: Downtream process makes a request to my middle tier. This middle tier runs a query to get the latest data and passes it back. This design is set and I cannot change it.
1. right now we capture the insert time of the record. However, at this rate of inserts some processes will commit faster than others. So I cant use a 'greater than my insert time' query.
2. streams/golden gate won't work. can't register these DBs.
3. don't want to serialize my inserts because since I am not sure I can keep up with the insert rate. I don't even know what the specs will be for the production hardware. I have to actually deliver this before its decided. So I am being conservative.
4. I really want to avoid updates on this table if possible. In part due to my limited ability to test.
5. due to the number of downstream processes it is possible that it will request data and for some reason fail to insert the data locally. So the downstream application will keep track of the latest data it received. This means that a subscriber may need to request the same data again.
Is there a way to set up change data capture with multiple subscribers to handle this? if my subscribers are just queries? All the queries come from the same servers(there will be several, but all the same thing). If so, when I performance test this are there any wait issues I should keep an eye on?
View 1 Replies
View Related
Apr 9, 2013
I have recently configure Data guard with Database 10g (10.2.0.4-64 bits) on Windows 2007 server.My Data Gurad Configuration show Success status with 2 databases on same (or local) location.My questions are
1-When I query SHOW PARAMETER LOG ARCH
DG_CONFIG(PRMDB)
ONLY 1 (PRIMARY DATABASE IS DISPLAYED ONLY NOT 2 DTATABASES
e.g. DG_CONFIG(PRMDB, STLDB)
2- How to check the log applied interval or time (either transaction by transaction, timing etc)
View 11 Replies
View Related
Nov 14, 2013
How can find the number of standby databases configured for primary database from os level
View 9 Replies
View Related
Aug 21, 2010
I have a Table as below
Attribute_namePrimary Seconday
DNS 204.109.167.1204.109.167.2
DNS 204.109.167.2204.109.167.3
NAT 138.20.37.136138.20.37.137
NAT 138.20.20.116138.20.20.117
For a Specific requirement
I need the select data output like below
[b]DNS NAT Type[/b]
204.109.167.1138.20.37.136Primary
204.109.167.2138.20.20.116Primary
204.109.167.2138.20.37.137Secondary
204.109.167.3138.20.20.117Secondary
View 2 Replies
View Related
Dec 3, 2011
In one table I have data. Check the excel sheet i have attached.
One procedure to convert the data as mentioned in the excel sheet .
View 7 Replies
View Related
Sep 25, 2013
I want to convert row data into column and I mentioned here,
This is my table format, A B C------------------col1 1 101col2 2 102col3 3 103
The above query need to like this, COL1 COL2 COL3----------------------------------1 2 3101 102 103
View 5 Replies
View Related
Nov 4, 2011
I have the following data.
COURSE_SEQ_NO INSTRUCTOR_ID SE SEMESTER_CODE SEMESTER_YEAR QNO ANS ANS_COUNT
------------- ------------- -- ------------- ------------- ---------- --- ----------
74 10225 A 3 2011 1 E 3
74 10225 A 3 2011 1 VG 1
74 10225 A 3 2011 2 E 3
74 10225 A 3 2011 2 VG 1
[code]....
How can I convert it into matrix report like below
QNO E VG
1 3 1
2 3 1
3 3 1
. . .
. . .
. . .
17 3 1
View 5 Replies
View Related
Nov 20, 2011
how to convert rows data into column...
View 7 Replies
View Related
Dec 26, 2011
is it possible convert data from forms 6i to pdf format?
View 4 Replies
View Related
Nov 15, 2007
i am trying insert data from one DB to other DB table. one field data type is LONG in first DB table, Same field data type in other DB is CLOB.
i used TO_LOB function to convert from LONG to CLOB data type.
My problem is, i used this TO_LOB function, i got illegal operation of LONG Data type.
View 3 Replies
View Related
Jun 13, 2012
I have problem to convert simply varchar to date data type.
For example: 2012-05-28 22:36:08 and I would like to get format 28.5.2012 22:36:08
However I try to do it I get always some errors.
select '2012-05-28 22:36:08',
to_date( '2012-05-28 22:36:08', 'dd.mm.yyyy HH24:MI:SS') ,
to_char('2012-05-28 22:36:08', 'dd.mm.yyyy HH24:MI:SS')
from dual
View 7 Replies
View Related
May 10, 2013
Is there a function that allows the following?
select SOME_FUNCTION('N','E','S','W') from dual;
That returns
N
E
S
W
Currently I'm just doing the following
WITH direction AS
(SELECT 'N' dir FROM DUAL
UNION
SELECT 'E' FROM DUAL
UNION
SELECT 'S' FROM DUAL
UNION
SELECT 'W' FROM DUAL)
SELECT *
FROM direction;
View 4 Replies
View Related
Oct 11, 2012
I have a below requirement let me know the optimized query.
Create table test_123 ( sr varchar2(1000));
Insert into test_123 values ('1,2,3,4,5');
Insert into test_123 values ('6,7,9,10,11');
Insert into test_123 values ('9,2,3,8,5');
I need output as :
Sr_output
1
2
3
4
5
6
7
8
9
10
11
I need all the values in the column sr in a single column and mutiple rows.
View 2 Replies
View Related
Aug 16, 2012
I want to convert a clob datatype to varchar data type.I have a clob data type in the below format.
-----------------------------------------------------------------------------------------
<HTML><HEAD><TITLE>Tata Communications Limited</TITLE><BODY leftMargin=20 topMargin=10 rightMargin=20 marginheight=10 marginwidth=10><Table border="0" cellpadding="0" cellspacing="0"
-----------------------------------------------------
I want to insert all the values into varchar datatype.provide the sample code how to convert.
View 1 Replies
View Related
Jun 28, 2011
I have a data synch procedure where I am extracting data from an Oracle table and inserting it into a SQL Server table. The Oracle column is VARCHAR2 and the receiving SQL Server column is Binary. I am receiving a conversion error. I have tried HEXTORAW in the Oracle procedure but it does not work. What data type can I use.
View 5 Replies
View Related
Mar 11, 2013
I have a Employee table of the following format:
Emp_id | Emp_name | Salary
101 | James | 1000
102 | Julia | 200
I have to convert or transpose the table data as follows using a SQL statement/function -
Emp_id | 101 | 102
Emp_name | James | Julia
Salary | 1000 | 2000
How do I achieve this?
View 4 Replies
View Related
Dec 5, 2012
I have the situation like i want display the data from table which is storing in rows into a columns....
My table structure will be like this(Here i am taking only one part but have N no. of parts in my system)
Partno Purchase date Qty
111 02-DEC-2012 15
111 03-DEC-2012 25
111 04-DEC-2012 20
*** **** ****
111 31-DEC-2012 28
So i am expecting my query out put should be in column wise, that should make columns dynamically based on dates.
like example
__Partno 01-Dec-12 02-Dec-12 03-Dec-12 04-Dec-12 ---- ---- --- 31-Dec-12__
111 0 15 25 20 28
Here columns should be created based purchase dates dynamically with respect to quantity. Query out put will be like matrix format. So i feel that PIVOT & SYS_CONNECT_BY_PATH will not serve my requirement.
My system comparability is
oracle 10g database.
oracle 6i forms.
View 4 Replies
View Related
Mar 25, 2013
I have two databases (say DATABASE 1 with user USER1 with table A and DATABASE2 with user USER1 with table A). I want to select the table A in USER1 DATABASE2 from DATABASE1 USER1.
View 5 Replies
View Related
Apr 12, 2011
We are migrating database from HP to AIX. Data is all on SAN.I can do it through RMAN convert. What I want to know is if it is possible to move data at storage level without using Convert command?
View 2 Replies
View Related
Nov 4, 2011
Enterprise Edition Release 10.2.0.5.0
SQL> SELECT * FROM NLS_DATABASE_PARAMETERS where parameter = 'NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ----------------------------------
NLS_CHARACTERSET AL32UTF8
SQL>
There is table (VIN_TEMP) in my company database containing following records. It seems like this table should contain some greek language special chracter values instead of this weird data.
SQL> select * from vin_temp;
ATTR
--------------------------------------------------------------------------------
���9999
���9998
���9997
���9997
[code]....
Client are reporting these records as invalid and requesting us to fix. As i investigated i found out, this table was created and loaded few year back. Client sent us one time files which we loaded into this table. I was able to find the code which was actually used to load this table, but unfortunately i was not able to find the raw files where we load this data from...
It seems like Previous Developer specified character set "UTF8" statement, in his sql loader script, to load this data. It seem those file contain some Greek language special character data which was not support by "UTF8" charater set and result in creating those invalid data. My Job is to fix these invalid records and convert them back to its original values which were present in the raw file. I tried to contact client and see if i can find out the raw files but no luck. I tried to use convert function as mention to convert this data from "UTF8" to our current character set format but no luck.
SQL> SELECT attr, dump(attr), convert(attr,'UTF8', 'AL32UTF8') from vin_temp;
ATTR DUMP(ATTR) CONVERT(ATTR,'UTF8','AL32UTF8'
------------------ ---------------------------------------------------------------- -----------------------------
���9999 Typ=1 Len=13: 239,191,189,239,191,189,239,191,189,57,57,57,57 ���9999
���9998 Typ=1 Len=13: 239,191,189,239,191,189,239,191,189,57,57,57,56 ���9998
���9997 Typ=1 Len=13: 239,191,189,239,191,189,239,191,189,57,57,57,55 ���9997
���9997 Typ=1 Len=13: 239,191,189,239,191,189,239,191,189,57,57,57,55 ���9997
[code]....
Here is the script to create table with those type of invalid records.
create table VIN_TEMP
(
attr VARCHAR2(50 CHAR)
);
insert into VIN_TEMP (attr) values ('���9999');
insert into VIN_TEMP (attr) values ('���9998');
[code]....
View 1 Replies
View Related
Jul 10, 2011
How To Convert Data from Reports 6i to oracle Tables.
View 2 Replies
View Related
