My 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.
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 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 RelatedI'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 RelatedWhat 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]....
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].......
Is there any way i can select * from all the tables owned by particular schema at once.
View 22 Replies View RelatedI want to do a comparision for the missing rows between two diffrent tables
TBL1 and TBL2 both with the same structure but with diffrent data some data is identical. though my data is huge i wanted to make sure the technique i am using
What are some performance issues you might run into when having to query 3 or 4 tables at a time.
View 1 Replies View RelatedI have a requirement to get the data in "SQL" directly and should not use plsql/ can't redirect the output to a .sql file. I have a master table Main_tab which does have all "child table" names.
I want to build a query such that it will give all the data from child tables.
create table main_tab ( tab_name varchar(10));
insert into main_tab values ('one');
insert into main_tab values ('two');
insert into main_tab values ('three');
create table one ( a char);
insert into one values ('a')
create table two ( b char);
insert into two values ('b')
create table three ( c char);
insert into three values ('c');
Now I have got the query to combine all the table data using
select decode (rownum,1,'','union all ') || 'select * from ' || tab_name example from main_tab
but I am expecting the output to get as
a
b
c
which is the data from tables one,two and three respectively.
I don't want to save the above select data in to .sql and then execute it , instead I want to run it on "fly" in SQL itself. Is there such a possibility to do so?
I have 3 tables as below. I am looking to get data from these 3 table at a time using a pl/sql block but unable to do. Is there any way that I can get the data in a single query.
PS:All the columns are same in all the 3 tables
table1_COLLECTIONS_a
table1_COLLECTIONS_b
table1_COLLECTIONS_c
In SQL...I want to display the manager details and the corresponding employee details and i donot want to use joins or co-related subqueries
I am trying it to use CASE or DECODE..
Here is the Query..
SELECT *
FROM (SELECT empno,
ename,
sal,
job,
CASE
WHEN empno IN (SELECT mgr FROM emp)
[code].....
The output is as follows..
7839KING5000PRESIDENT7839 KING Manager 5000 0 10
7698BLAKE2850MANAGER 7698 BLAKE 7839 2850 0 30
7782CLARK2450MANAGER 7782 CLARK 7839 2450 0 10
7566JONES2975MANAGER 7566 JONES 7839 2975 0 20
7902FORD3000ANALYST 7902 FORD 7566 3000 0 20
7788SCOTT3000ANALYST 7788 SCOTT 7566 3000 0 20
I am able to display only the Manager details or employee details and not both at the same time..
I have to move data from old tables into new tables. I use a sqlplus script and execute multiple 'insert from select'. I thought the SQL statements are executed in sequence, one after the other. I run into this problem: Let's take this script for an example
insert into A (a,b,c,....
select a,b,c......
from X;
insert into B (a,b,....
select a,b,....
from A joined with Y;
commit;
After executing the script in our development DB
select count (*) from A
returns 200
When the script is executed in production, A table record count is 5
select count (*) from A joined with Y; (used in the second insert)
returns 200.
It seems that the two statements are executed at the same time and table A is not fully populated when the select in statement two is executed.I suspect this is due to parallel execution in production.
Will executing
ALTER SESSION DISABLE PARALLEL DML;
ALTER SESSION DISABLE PARALLEL QUERY ;
i have source table having 1000 records, i want insert first 100 rows in table1,second 200 rows in table2 and remaining row in table3.
View 12 Replies View RelatedI Have to write a procedure which takes XML data and inserts into some tables.
If the XML format is fixed then i can use extract function for parsing and can insert into the tables.
But the problem is there is not fixed format for the xml.
Are there any built in packages which takes the xml data for parsing..
I'm having trouble to get the weekend records from 2 tables. table 1 has a start date and an End Date. Table 2 has END_INTV_TIME ( HOUR FORMAT AS 0100-1am,0200-2am.......1300-1pm,1400-2pm.....2400).
create table t (traffic_sample_id number,
site-id_nbr varchar2(12),
start_date varchar2(6),
end_date varchar2(6)
[code]....
My requirement is to get the weekend days and i tried using the function
( TO_DATE (start_date,
'mmddrr'
)
+ TRUNC (rn / 24),
'Day'
)
{code}
But the problem is its going just after the table1 START_DATE and getting me the weekend day based on start date, but when i join the tables the problem is with the END_INTV_TIME, the recods start at 1000 (10am) (bin_data_id =1037804) so according to my logic the weekend records will be 48 records from the first record, but i want to take into consideration the END_INTV_TIME as well when its (0100 1am) then my weekend starts ie(1037843 bin_data_id) instead my weekend record is coming from 1037852 (1000 10am) ,
I have a form which has three detail portions. I want that when I press SAVE, it should insert data in two tables & then run the specific code & then insert data in other two tables.
I am using Developer 6i. Couldn't find out the proper trigger or related thing.
how can I insert data into tables on another user. They both are in the same table-space.
View 1 Replies View Related