I am trying to write a code to identify the delimiter in the file ( which is in the form of table(id, raw) in system)then I take this delimiter and pass it as a parameter to SP which perform cleaning of this file(table) and creates another clean table.
The problem I am facing is until now the file was coming with one fixed (TAB) delimiter, but now it has come with different (SPACE), now here I want to develop the code to identify the delimiter place it in a variable an pass this as parameter to cleaning SP.
--here i want to develop code to identify delimiter from hosts_equiv file which has data as below
select * from hosts_equiv where left(raw,1) not in ('#','*') and isnull(raw,'')<>''
Raw id
---------------- ---
hiper USER1 1
hiper2 USER1 2
APX user2 3
Need to identify delimiter between e.g. hiper USER1 and pass it as a parameter to the raw_parse sp
declare Tab varchar(10)
set Tab = char(9)
exec raw_Parse
'hosts_equiv', -- From table ( entire file content is stored as table with Id record no sequence generated)
'CleanedHosts_Equiv', -- To table name, when passed it will clean the from table and places the cleaned data in to_table
Need a query to find the count of presence of delimiter "," in column VAL for below mentioned table.
Table:
CREATE TABLE N_B_S_1 ( NUM NUMBER, VAL VARCHAR2(20) )
Data:
INSERT INTO N_B_S_1 VALUES(1,'A,B,C'); INSERT INTO N_B_S_1 VALUES(2,'D'); INSERT INTO N_B_S_1 VALUES(3,'ER,NF,G,H,XK'); INSERT INTO N_B_S_1 VALUES(4,'LQW,MBV'); INSERT INTO N_B_S_1 VALUES(5,'KS,YJ,WE,RQ,PM'); COMMIT;
Whenever there is a change in the c_flag, we need to identify the change and do some processing. How do I identify the change from either 1 to 0 or 0 to 1. The change can be either way. The point of importance is to identify the change.
CREATE TABLE tab1 ( id NUMBER(10) NOT NULL, description VARCHAR2(50) NOT NULL, Start_Date DATE, id_num NUMBER(10), c_flag varchar2(2)); [code]....
I need to get a way to find, The client fired the SQL Query is still connected to the database or not. For e.g.
I fire an SQL query using SQL Developer, Query approximately takes 5 mins to complete. As soon as I fire the query SQL devloper hangs and I close it using task manager.
Is there any way I can identify such session where SQL developer fired the query is terminated but the query fired is still running.
I have to identify such Orphan session and terminate it.
I need a job to get executed for every 1hour.Like i need a query to identify invalid objects in database per schema ,invalid type and this is a job to run every hour.How to schedule it.I only know that dba_jobs have the info.
i did everything writen but when i do *SQL>alter database recover managed standby database disconnect from session;*
i go and look in the standby database AlertLog file ,and thats whats writen
*ORA-01157: cannot identify/lock data file 1 - see DBWR trace file ORA-01110: data file 1: 'F:ORACLEPRODUCT10.2.0ORADATADBSYSTEM01.DBF' ORA-27041: unable to open file OSD-04002: غير قادر على فتح الملف O/S-Error: (OS 3) The system cannot find the path specified.
[code]....
strange thing that it realises the primary database in drive F and it goes to it but i dont understand what could be the reason of this ,although im doing this command while primary database is shutdown!
I'm using SAP ECC6.0, Oracle 10G, HPUX B11.23.Recently I had performed database restoration from my backup tape. However, during the process of bringing up the Oracle and SAP database, I observed that there could be some archive logs went missing. I had tried to check them from my backup tape, but could not find it.
In short, now my SAP database is up and running, but I'm having another problem when I executed a "CheckDB" job in DB13. The job is unable to complete successfully
DB13 job log: 30.08.2010 19:44:40 Job started 30.08.2010 19:44:40 Step 001 started (program RSDBAJOB, variant &0000000000061, user ID BASIS) 30.08.2010 19:44:40 Execute logical command BRCONNECT On host drqaecc 30.08.2010 19:44:40 Parameters: -u / -jid CHECK20100830194440 -c -f check
Our server got crash in server1 and we recovered the same DB to server2 using RMAN back up. But while running our scheduler for email alert we got the error message as ORA-01157: cannot identify/lock data file 201 - see DBWR trace file ORA-01110: data file 201: 'D:ORADATAKFDBTEMP01.DBF' Our temp file is already in E folder.
I'm rying to import schema's from a dump file that came from a different environment.
What I have is:
1. dump file 2. log file of the export
I'm trying to import the file(containing three schemas) with remap_schemas, and it fails, gives a lot of ORA-00959: tablespace 'string' does not exist.
Now, I've read in OTN:
[URL]
that what you need to do in that case is to use the REMAP_TABLESPACE option,to redirect the objects to a different tablespace.
I don't see a name of the tablespace I'm getting the error for in the export log.I don't know if I have more tablespaces I have to redirect with REMAP_TABLESPACE.
I don't want to perform this 3 times, have an error, by that find out what's the next tablespace needing redirection and only then starting over...
How can I know from the dump file and the log file,what is the tablespace names i need for the redirection to my names? Or its just that the tablespace giving me the error is the only one in the dump file?
I was wondering if there is an Oracle function available to split a string based on a delimiter character. For example, if I have a table consisting of:
I am using sqlloader for loading the data into database by using csv file.My csv file is delimited by comma in that i am having a column which is having the , and line feeds targeted to load into a long data type.for example as below
descri,dfdfdfd,dfdfdf, sdfsdf, dfsdfd,
i want to move this column data into a single table column.But due to because of delimited "," it is splitting into number of columns
This opens up clearly on excel. items like Product,Count are shown on separate columns along with the value.I am still looking into delimited formatting.
I am developing a TAB delimited oracle reports(6i).In which I am doing the following
1. One query for both text and excel reports 2. two layouts, one for standard another for excel(I have parameter to select whihc format user want to see) 3. In excel layout I putting one fielde for each row. And assigning each row with a formul;a column 4. In formula column I am sending data for that row with tab delimited as below return(:Name || chr(9) || :Age || chr(9) || :depat) ; 5. I have registered in Apps 6. When I run the report in apps the output comes with tab space for each column 7. But I save the same file and try to open in excel with delimiter as 'TAB' its not spreading across cell. It is just coming in a single cell
I'm trying to eliminate duplicate string for more than 1 occurrences along with its delimiters, but couldn't get it working. Here is what I tried.
SQL> column str format a30 SQL> column replaced format a30 SQL> with x as 2 (select 'a#~#b#~#a#~#d' as str from dual union all 3 select 'a#~#b#~#c#~#a' as str from dual union all 4 select 'b#~#a#~#c#~#a' as str from dual) select str, regexp_replace(str, '[^a|#~#a]{2,}','',1,2) replaced from x; 5 6 7
The problem here is , When you try to import to a table which has same columns . I skipped the first line when loading . The issue here is the second field is getting split in to the two columns . for e.g. :- Default goes to Child and Remaining goes to the Alias.
infarct there is a tab at the end of the each line. How to set the Sql loader settings correctly so that I can populated the end column in CHILD column only.!!!!
I have data in a table and another in XML file,I used SQL query to retrive the data placed on the table, and link this query with XML query that retrieves the data stored in the xml file. The data stored in the table and xml file sharing a key field, but the xml contents are less than what in the table.I want to show only the data shared between the two queries, how can I do that?
e.g.:
Table emp:
e_id | e_name | e_sal 023 | John | 6000 143 | Tom | 9000 876 | Chi | 4000 987 | Alen | 7800
I am looking for the ways to identify the Footprint of the My ID on the database, as it is used to run very heavy production reports.may include utilization of resources.
I have to check some parameter values for an OID tuning I do not have sys access, I have only schema user access
Now how do I see the values of SGA_TARGET, db_cache_size etc parameter
I manage to see these var in init.ora file under /dbs folder but there are 2-3 spfile also having containing these attributes, not sure which reflect the actual.
Also can I alter also these value via schema user or do I need SYS access?
a table structure is modified every now and then because of which the few packages get uncompiled. is there any way to monitor which user has changed table structure.
I have a database of branch A whose files are located in E: Drive. I want to download it in branch B.I placed all the files of Branch A in D: Drive of Branch B. When i start the database i was getting controlfle error. I made required corrections in initorcl.ora. Now when i start the Database,its mounted, but I am getting ORA-01157: Cannot identify datafile 1. I tried to rename the file, but the "alter database rename file1 to file2;" option is not working.
I have employee interface table something like this.
emp_idemp_name Job_title supervisor_name 1AJ Engineer BJ 2CK Analyst ND 3BJ Manager TR 5TR VP IT JD 6ND S Manager MD 7MD VP Telecom SK 8SK VP Eng JR
I want to idenitfy the VP for each employee. The logic I have to apply is check for hte supervisor of each employee to see if the supervisor has designation starting with 'VP'. If no, I have check the supervisor of the supervisor and so on. I tried using a recursive query using connect_by_root but in the above example for employee ND it lists the VP as both MD and SK. I need it to show on MD who is the lower in the hierarchy.
I am a Java person but since my app uses the Oracle DB I am to do this task.
The TM lock that occurred 3 times appears to be disastrous.Historically, this could be caused by missing indexes on foreign key columns.
How can I be alerted when this event occurs, so that I can do some real-time investigation into the sessions and the SQL that hit it? I suppose I could schedule a job to query v$lock/dba_waiters/dba_blockers every few minutes, but is there a better way? Any standard edition scripts for this?