TEMP1--ORINIGAL TABLE NAME
DESC IS:
SNO NUMBER;
SNAME VARCHAR2(100);
BACK UP TABLES ARE
TEMP1_1;
TEMP1_2;
TEMP1_3;
.
.
.
.
.
TEMP1_10
IN THE ABOVE 11 TABLES SNAME COLUMN HAVING THE DATA AS 'NARESH'
IN SOME OF THE TABLES AND NOT IN OTHERS.
REQUIRMENT:
I WANT THE TABLES ONLY HAVING THE
SNAME COLUMN 'NARESH'.
i want to know the use of index for searching data in table...tell answer to my query with table example....
View 2 Replies View RelatedMy tnslistner is not working that is why i am not able to login in my database. so i have planned to reinstall it and if possible get my database back from old files.
i do not have backup, is there any way to get tables and data back.
I want to take backup of selected data from tables of a schema (as huge data, that is not used, causing slow query performance) I planned to create a seprate backup schema and tablespace to store the data from these tables. Then write procedures that can move the data to and fro among table of those schema. And create partitioned index on those backup tables.
View 11 Replies View RelatedThis procedure is not working properly.
create or replace procedure bank_search_sp
(
p_tablename in varchar2,
p_searchname in varchar2,
p_bankcode out varchar2,
p_bankname out varchar2,
p_dist_code out number
)
as
v_tem varchar2(5000);
begin
v_tem :='select bankcode,bankname,dist_code from ' || UPPER (p_tablename) || '
where bankname like '''|| p_searchname||'';
execute immediate v_tem into p_bankcode,p_bankname,p_dist_code using p_searchname ;
commit;
end bank_search_sp;
the Procedure is getting created but i dont know what actually happens when it was executed ,This is the error shown..ORA-01756: quoted string not properly terminated
ORA-06512: at "PENSIONS.BANK_SEARCH_SP", line 14
ORA-06512: at line 1
I've a little question : Is it possible to use RMMAN to backup 2 tables incrementally ? I don't want to backup all the database but only these 2 tables.
View 4 Replies View RelatedI have to cleanup data from our tables (Production Environment) that contain millions of rows. The question is apart from the solution of the partitioned tables what alternative recommended solution suggests Oracle?
To delete these tables by using a cursor PL/SQL block or to import all the database and in the tables that we want to remove the old rows to use the QUERY option of the data pump utility.
I have used both ways and i have to admit that datapump solution is much much faster than the deletion that suffers from I/O disk.The question again is which method from these two is more reliable and less risky for the health of the database.
I came across an implementation where data from DB2 tables are moved to Oracle tables, for BI solutioning, using some oracle procedures called from MS SQL DTS packages which are scheduled jobs.Just being curious, can this be done using OWB or ODI rather than the above detour. I suppose there are some changes being done in those procedures before the data is being loaded into Oracle tables, can't this be done using OWB/ODI? Can it be scheduled too as jobs using OWB/ODI?
View 1 Replies View RelatedI need to check if a small collection contains a specific value. I know I can use the .exist method to let me know if the nth element exists. But is there a similar syntax for checking if an actual value exists?
So the below collection has 6 values (300,301,310,320,400,410) after the cursor values are fetched. I want to use something similar to the below exists syntax in order to search the collection for a value (rather than position) within an IF statement.
The below code shows the kind of thing I want to do. Currently, unless my test value (310) is in the range 1 to 6 the test will always return FALSE. But is there a similar syntax for testing against the value?
The example I have given is a simplification of what I need. In reality there will be more than one test value... returned by another cursor.
DECLARE
CURSOR c_type_id_usg
IS
[Code]....
-- get the list of sms type id usg values
OPEN c_type_id_usg;
FETCH c_type_id_usg bulk collect into l_type_ids;
CLOSE c_type_id_usg;
-- the above returns value 300,301,310,320,400,410
IF l_type_ids.exists(310)
then
dbms_output.put_line('I have found an entry ');
else
dbms_output.put_line('I have NOT found an entry ' );
end if;
END;
I've tried to write the procedure for search the string from whole database. If user give the string as an input the output will be tablename and column in which the string contained in the table,But it's showing an error .
CREATE OR REPLACE procedure sample.pr_search_table (p_search varchar )
is
type tb_type is table of varchar(30);
tb_table tb_type:= tb_type();
tb_column tb_type := tb_type();
v_temp varchar(30);
[code]...
I want to write a SQL statement to search valid city name from address field. Valid city names are in one table and address column is in another table.
View 5 Replies View RelatedI am using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production.
I have a situation where I need to find the number of occurrences of a string that is present in a table having comma separated values. To explain with an example:
create table test_data ( textfield varchar2(100));
insert into test_data values('DM,HM');
insert into test_data values('EM');
insert into test_data values('AM,CA,CD,FM,ST');
insert into test_data values('LS');
insert into test_data values('TQ,SP,AM,FM,ST,CA,CD');
insert into test_data values('TQ,SP,AM,FM,ST,CA,CD,LS');
insert into test_data values('DM,HM,LS');
The data in the table test_data looks like DM,HMEMAM,CA,CD,FM,STLSTQ,SP,AM,FM,ST,CA,CDTQ,SP,AM,FM,ST,CA,CD,LSDM,HM,LS Now I need to search"LS" in the table test_data. Basically, I need to find if "LS" is present at least once in any of the rows or not. I want to avoid looping here.
I have two tables emp and dept
sql>select emp_id from emp;
emp_id
------
123
345
6782
32
[code].....
I would like to search all the employee id's which are present in description column of dept.
I needed to search for some specific text in the DDL of each of the views in a particular schema. The text column of the user_views is LONG, and I looked at some old Tom's threads for converting long2clob, but found these processes to be really cumbersome, so I just opted to use DBMS_METADATA.GET_DDL instead even if it is a little slow.
One area I do not have a lot of experience with is searching clob fields. I tried this but not sure what would be the appropriate function to use for something like this:
SELECT *
FROM
(
[Code]....
WHERE contains(object_text, 'WHERE t.policy NOT LIKE') > 0; -- Show the names of all views that contain the matching text
What is the best possible method to take backup of 50 tables with data ? My DB version is 11gR2
View 14 Replies View RelatedI have an oracle autoback of controlfile and spfile. I am trying to restore the production database using this backup, into a new server with a different directory structure.
I have completed the following steps in the restore process.
1. Creating a pfile from spfile
2. Changing the the location of cdump, udump, bdump and the control files ( there are three )
3. Then creating a spfile from this pfile
I am stuck on the next step where to rename all the datafiles and tempfile and restore the database. Following is what I did.
After mounting the database in RMAN, tried to run the following.
RMAN> run
2> {
3> allocate channel c1 device type disk
4> ;
5> @/home/oracle/rman_scripts/newloc.rman
6> SET NEWNAME FOR DATAFILE 1 TO '/u02/oradata/dorian/system01.dbf';
7> SET NEWNAME FOR DATAFILE 2 TO '/u02/oradata/dorian/undotbs01.dbf';
8> SET NEWNAME FOR DATAFILE 3 TO '/u02/oradata/dorian/sysaux01.dbf';
9> SET NEWNAME FOR DATAFILE 4 TO '/u02/oradata/dorian/users01.dbf';
[code]....
I am getting the following error message.
Starting restore at 25-JAN-2012 21:26:48
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/25/2012 21:26:49
RMAN-06026: some targets not found - aborting restore
RMAN-06100: no channel to restore a backup or copy of datafile 30
RMAN-06100: no channel to restore a backup or copy of datafile 29
RMAN-06100: no channel to restore a backup or copy of datafile 28
[code]....
My application runs a batch procedure weekly once for searching 'A_Text' from a column in Table1 in a clob column in Table2 and inserts accordingly into another Table3.
code snippet is like this -
---
CURSOR cr_sn
IS
SELECT serial_number
[Code]....
TABLE1 will have at least 1.1 Million rows but not significantly more than this.
This procedure takes 24+ hours to complete. I tried -
1. putting parallel hint ( INSERT /*+ PARALLEL*/INTO Table3)
2. partitioning TABLE2 based on last_update_date and putting a where clause in the above query last_update_date ( last_update_date between date1 and date2)
I have created searching tool using oracle forms 6i. while searching the cursor move from text field (A) to other block query the data according to the field (A) and come back again to field (A). Everything is working fine but the problem is, when the cursor move back to the field (A) the existing text is highlighted and when user right something in it. It's overwriting the existing text.
I want when the cursor moving bank to the field (A) the text should not be highlighted and when the user writes something that will be added to the existing text.
i have three tables ot_cut_head,ot_cut_det and om_mc_master based on which fourth table ot_cut_opr and fifth table ot_cut_mc must get populated , Conditions are as follows
first one is based on job_no in ot_cut_head the selection criteria will be filtered,if the job number is like '%M' then type MISC will be chosen ,if job number is '%G' then GRAT TYPE will be picked from om_mc_master (Machine Master) and operations and machines based on this will be filtered.
Second all the cd_ps_desc will be taken from ot_cut_det and will be compared with om_mc_master to get their corresponding operation codes and machine codes , there can be 2 operations or 1 operation.
Finally if the match is found record will be inserted into ot_cut_opr and ot_cut_mc ,based on the criterias and what i want is the search criteria to be more flexible and if there are 2 operations 2 rows will be inserted and if one opeation is defined in om_mc_master ,then only one record will be inserted.
We have to make sure that if based on operation number stage will be populated ,if its first operation then stage will be 1 and if its second operation the stage will be 2.like previous operation also depends on them , the second operation will have the previous operation as first operation and so on.
CREATE TABLE om_mc_master ( mc_type VARCHAR2(12),mc_prof VARCHAR2(30),mc_prep_cd1 VARCHAR2(30),mc_mach_cd1 VARCHAR2
(30),mc_prep_cd2 VARCHAR2(30),mc_mach_cd2 VARCHAR2(30));
INSERT INTO OM_MC_MASTER VALUES ('MISC','TEE SCH','IR','HO','RE','HO');
insert into om_mc_master values('MISC','Vertical Brace','R','HM','I','HO');
insert into om_mc_master values('MISC','Pipe','IR','HO',NULL,NULL);
INSERT INTO OM_MC_MASTER VALUES ('GRAT','PL','RE','HO',NULL,NULL);
SQL> SELECT * FROM OM_MC_MASTER;
[code]....
I have table in Oracle with one column PRODUCT. Column PRODUCT have following values -
Account Management
Active Directory
Adobe Acrobat Reader
NT Account
Application Security
[code]....
I am designing application where I need to search for PRODUCT based upon user's input. Lets say user wants search on 'Laptop Account Broken'. I want to search for all products which contains any of words in user's input. So based upon user's input I want output like below.
Expected Output:
Account Management
NT Account
WebSite Account
HP Laptop
How to backup the tables in a schema? I need som halp on this subjekt. I hav Dev envrnment that I must to backup.
View 9 Replies View RelatedOne of the developer dropped 18 tables by mistake on USER@db schema. Developer asked whether I can restore those tables, but unfortunately they are not in the recycle bin. Developer is re-creating the table now though. But I am just curious whether we need to change these parameters (db_recycle_cache_size) in order support any similar requests in the future.
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recycle_cache_size big integer 0
recyclebin string on
We are using HP data Protector as oracle database backup tools.but we are facing below error. But we are able to login to the database through same user.
BR0301E SQL error -1033 at location BrDbConnect-2, SQL statement:
'CONNECT system/********'
ORA-01033: ORACLE initialization or shutdown in progress
BR0310E Connect to database instance ARP failed
I want to maintain the logical backup of all the application tables At regular intervals. Which option would i use?
View 1 Replies View RelatedMy scenario is use RMAN to restore and recover database to a new host.The structure of two hosts are same.
In Host A:
1. Take a full backup.
2. Import a schema which has 191 tables in order to generate archivelogs.
In Host B:
I put all the folders(backupset, autobackup, archivelog) to the same directory. But after recovered the database, i checked that schema just has 175 tables, the latest few tables are missing.
I need to export only the data from schemas or tables, how to do that with Oracle Data Pump? when we use schemas parameter this export all schema, not only the data right?
View 7 Replies View RelatedI created a data warehouse in oracle 10g n with three Dimension and one cube after that it crates 4 tables . How to use an insert sql statement to insert data in those tables n how to access them.
View 7 Replies View RelatedI have a problem with my Oracle 9i SQL Query and I'm struggling to get it done.
I have three tables namely Student, Lease and Room and want to retrieve data from these three tables.
I want the Student name, the Lease details and the Room No from these tables.
The problem with my SQL query is that I get all the information from the tables except from the Room table, where in the column it show Room_No but the values are not displayed, the query is given below.
SELECT STUDENT.STU_FNAME, STUDENT.STU_LNAME, LEASE.LSE_NO, LEASE.LSE_DURATION, LEASE.LSE_STARTS, LEASE.LSE_ENDS, ROOM.ROOM_NO
FROM STUDENT
LEFT OUTER JOIN LEASE ON LEASE.STU_ID = STUDENT.STU_ID
LEFT OUTER JOIN ROOM ON ROOM.PLACE_NO = LEASE.PLACE_NO;
I have two tables
table1
col1.....................................col2
primary key................ foreign key refer to col2 in tab2
table2
col1 ........................................... col2
foreign key refer to col1 in tab1 ............ primary key
now my question is how to insert data in to the two tables
I have two databases.
one is "ora" it is a 8i version
2nd is "orcl" it is a 11g version
"Oracle" is the my local database. i wrote following program for comparing the row by row data in both the tables.
Q)Is it BEST practice? If not let me know the best practice to compare data in tables?
Q) If am not using the order by clause its giving me wrong output even though both the data tables has same data. WHY?
DECLARE
v_emptest1 EMP_TEST1%ROWTYPE;
v_emptest2 EMP_TEST1@ora%ROWTYPE;
v_suc_flg NUMBER := 0;
v_cnt1 NUMBER:=0;
[code].......