In my Production DB. 5 Datafiles created in same tablespace. Datafile size is of 25GB. Data stored in all Datafile. Data is just 5GB in all datafile. I want to move data from 5 datafiles to single or couple of datafiles.
This is in Oracle 11g.
I have imported data into database using sqlloader into flat table. Now I need to move the data from this table to another table. This is production system and I must keep it online. So I decided to make script that will move data in small chunks and commit frequently to avoid waits and table locks.
Regarding the script I have question. I can to the bulk load of rowids. Is it possible to optimize the insert and delete in similar way instead of doing insert/delete in loop for each rowid ?
declare
type t_rowids is table of rowid;
rowids t_rowids;
begin
loop
select rowid bulk collect into rowids from ims_old.values_f2 where rownum < 1000;
[Code]....
Oracle Data Replication. I am using Oracle 10g release 10.1 and I want to do replication of my data from one machine to another machince.
View 2 Replies View Related"All data you create in this tablespace will be encrypted using an AES256 encryption key. You cannot encrypt an existing tablespace. To encrypt data, first create an encrypted tablespace, then use alter table move, CTAS or datapump import to move your data into the encrypted space. Remember to drop the old tablespace BUT not including datafiles. Use an OS schred program to remove the old datafile. If you are on ASM you may use the including datafiles option since you can’t schred files from the OS inside an ASM instance."
But i want to know why we should NOT drop the including datafiles, when dropping tablespace (so 'drop tablespace my_tbs including contents and datafiles'). So what option should we use when dropping tablespace?
Why we should use OS capabilities to remove the datafiles?
What happens if i remove the datafile when i drop the tablespace?
My form has two list boxes and two buttons add and remove. As and when i click add button, the selected value from left hand side list item should get populated to right hand side list item. And When I click Remove button, it should do vice versa.
View 10 Replies View RelatedAm moving data from 10g enterprise edition to 11g standard edition using normal import commandafter completing i just go through the Log.i found a thing which makes me confusing
in some tables alone a row is not inserted stating the ORA-12899 while checking with the database it shows the column is varchar(100)in the log the error showing it tries to insert 101 character. how it happening to a single row
how can i solve it
I haved drop a datafile on Primary,and the archived log has transported to Standby,but the datafile is still exist in Standby.
Primary
alter tablespace tps_hxl
drop datafile '/u02/app/oracl/oradata/hxl10.dbf';
Till now i was pretty sure that standby redologs are applied to database without any delay, but everything looks that the process is different. i did on primary server table and add some records to this table.
Next i switch to standby and open redologs (with hex editor) to look for this "create table" and "insert" commands. and i succeeded. so this is prove that standby log are supplied by primary database.
Next i cancelled recovery process and open database in read only mode and there was no records in this standby db, moreover there wasn't any table on standby. so i started to be confused, because i suspected that this table and it content will be on standby database.
Next i started recover process again for standby database and do some switch log on primary server. after that i back to standby, cancel recovery process and open database in read only mode. there was table and contents for it.
so my question is:
does standby redologs should be applied online to database or they are only applied after promoting standby database to primary database?
it looks like contents only from archivelog is applied to standby database. it is correct?
database is 9i.
I am using perl script to dynamically generate the control file.If I have data in the control file as well as in the datafile, how would i write the control file in that case. Is the below one correct?
load data
INFILE '*'
INFILE '/export/home/test/test.csv'
INSERT INTO TABLE EMP fields
terminated by "," optionally enclosed by '"'
trailing nullcols
( empno, empname, sal, deptno )
[code]....
Is there any way that if my control file contains half of the data and my data file contains the other half of the data can i club this data into a logical record in the control file to populate the DB?
My exact 2nd requirement is, my DB contains 5 cols and for 1 col the data is common(countryName) which i have to pass to the control file dynamically and the .csv file contains the data for the other four cols. How could i combine these in the ctrl file and populate the DB?
so if the DB contains CountryName, empid, ename, sal and dept..I will get the CountryName to the ctrl file and csv contains the data for empid, ename, sal and dept. How would i combine these data into a logical record and populate the DB?
I have flash backed by primary database to an older SCN, from then on I am receiving the following errors on my standby database when I do media recovery :
ERROR at line 1:ORA-00283: recovery session canceled due to errorsORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1:
'/u01/app/sbydb11g/system01.dbf' PRIMARY DATABASE RMAN> list incarnation; List of Database IncarnationsDB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time------- ------- -------- ---------------- --- ---------- ----------1 1 ORCL 1343967574 PARENT 1 13-AUG-092 2 ORCL 1343967574 PARENT 754488 23-MAY-133 3 ORCL 1343967574 PARENT 1713927 14-JUN-134
[code]...
I have to recreate my standby database due to huge archive gap. I have full datafile backup of primary.
View 24 Replies View Relatedin my catalog database I see in Rc_datafile mismatch sizes - column bytes shows data different from the real sizes of files .
Does this view updated after copy of database into catalog ?
We have an Oracle Server database of Size 50 GB having 10 GB Data. And Planning to have a new Database Server of 200GB . So my question is after moving all the 10 GB data to 200 GB Database Server, will the performance of the system come down? Will it reduce the speed?
View 9 Replies View RelatedI need to move data from non-partinioned table to partitioned. The volume is about 60 millions rows. What is the fastest way to do that? I think about pareller insert and nologging. What do you think about this? May data pump be faster?
View 4 Replies View Related I have a table called Daily_usage (only 1 day data) which contains daily transaction records. After a day I have to move this data to another table named Daily_30days_usage table. this table contained the 30 days data. After 30 days the 31st day data should be deleted from Daily_30days_usage table.
How can I implement this requirement without INSERT statement?
My company is thinking of moving from ora 8i to Postgre? What's your opinion on this?
Note: Our database is quite large, over 120mill records, and about 20-50 clients constantly connected.
I need to move everything from database 'X' to database 'Y' (assume: both are Oracle 11.2).Which should be the most appropriate way to achieve that?I thought of Transportable Tablespaces- URL..... but I'm worried about it's limitations especially this:SYSTEM Tablespace Objects - You cannot transport the SYSTEM tablespace or objects owned by the user SYS. Some examples of such objects are PL/SQL, Java classes, callouts, views, synonyms, users, privileges, dimensions, directories, and sequences This means all PL/SQL code- Packages/Procs/Functions will be affected.
View 27 Replies View Related I'm trying to run a report that has a moving date and I need to find data that's within 12 months of that certain date.
So for example... customers come in everyday all year long. I wanted to find the number of unique customers in a year. But the year is moving... So 1 year from 1/15/2011 is 1/14/2012. And 1 year from 1/16/2011 is 1/15/12. So I had something like this but doesn't quite work..
SELECT ...
NVL(COUNT(DISTINCT CASE WHEN TX.DATE_OF_FIRST_VISIT BETWEEN TO_DATE(TX.DATE_OF_FIRST_VISIT,'MM-YYYY') AND ADD_MONTHS(TO_DATE(TX.DATE_OF_FIRST_VISIT,'MM-YYYY'),12) THEN (TX.CLINIC_ID||TX.PATIENT_UNIQUE_ID)END),0) AS "YEAR_1"
I read this article:
[URL]......
Does it work with copying datafiles from 10g2 to 11g2? I want to move one huge tablespace (which contains one table) from 10g2 to 11g2, what is the best method to do that?
I need to prepare script to move all objects from one tbs to another tbs. Should I move all the objects individually using "alter table" Command. I got all the objects information using "DBA_SEGMENTS" view.
I have more number of tables,indexes in that tablespaces.
I can not use exp for tablespace backup.
One of our auditing recommendation is to move table AUD$ to a separate tablespace from system. Why this recommendation is important and how to do this action ?
View 2 Replies View RelatedWe need to move schema from one tablespace to another tablespace.
we are going to use following method:
alter table <tab> move tablespace <new_tbsp>;
my question is do we need to disbale primary keys and foreign keys before move and enable them at end.
I need to find information on the cost and difficulty in implementing the change from MS Access to Oracle.
View 3 Replies View RelatedI've moved a package to a new schema and all the packages in the original schema that reference the moved package now fail to compile. The moved package has had a public synonym created and the execute privileges assigned to the original schema by role. what am i missing? Using 11gR2 version 11.2.0.3.0
View 2 Replies View Relatedthis query
SELECT
shrtgpa_pidm,
shrtgpa_term_code,
sum(shrtgpa_hours_earned) over (
partition by shrtgpa_pidm
order by shrtgpa_term_code
[Code]....
retrurns this rsult
82724 201010 4.5 201010
82724 201020 9 201020
82724 201110 13.5 201110
82724 201120 17.5 201120
82724 201310 20.5 201310
I need to be able to displayu the 4.5 in 201020 9 in 201110 in another words I need to move the results one term down
how I can do this?
I am thinking in a sequence? so 201010 will be seq 1
201020 will be seq 2
201110 will be seq 3
etc..etc..the I can do an update
Can this be done with aggr sql func? If not how I can add a sequence in the query It needs to break by shrtgpa_pidm
I need to end with these results
201020 4.50
201110 9.0
201120 13.50
201210 17.50
201220 21.50
here is the code to withe a table with data
create table testgpa
(
testgpa_pidm NUMBER(8) NOT NULL,
testgpa_term_code VARCHAR2(6 CHAR) NOT NULL,
testgpa_hours_earned NUMBER(9,3) NOT NULL
)
[Code]....
I have looked at the code you pointed me to, and have attempted to get it to work using a package, but I cant even get the package to compile..
CREATE OR REPLACE PACKAGE BODY trigger_api AS
PROCEDURE tab1_row_change (p_numass IN varchar2,
p_datcre IN date) IS
BEGIN
INSERT INTO tempjob (numass, datecre) VALUES (p_numass, p_datcre);
END tab1_row_change;
[code]....
Doing this process from code is not an option and MUST happen automatically via triggers.The mutating trigger error can sometimes be avoided: URL....
I am trying to create a trigger which does the following : A flag in the initial able is set to Y. When this happens, the record needs to be inserted into a history table and then DELETED from the calling table.
It must happen in triggers, but I keep getting the mutating error.I have tried to use a Compound trigger, but with no luck and just dont really understand how to get this to work.
Doing this process from code is not an option and MUST happen automatically via triggers.
I have a requirement to move the databases to a new server.
Existing set up:
database1-40GB,
database2-40GB,
database3-180GB
Oracle version - 11.2.0.2
OS - UNIX AIX-5.3
New Server (setup):
database1-40GB,
database2-40GB,
database3-180GB
Oracle version - 11.2.0.3
OS - UNIX AIX-6.1
My query is what is the best way to move the database to new server(UNIX AIX 6.1)?
a) can i upgrade the database to 11.2.0.3 before moving to new server or its better to move the database in 11.2.0.2 and then upgrade there?
b)is export and import of database can work for moving this?
c) RMAN backup and restoration can be done for moving databases?
d)simply moving all the related files to new server and start upgrading it from there?
I need to calcaulate the salary avarage for three days prior, leaving the current row. That should happen to every row moving back words.I have given all the details.
create table Employee(
ID VARCHAR2(4 BYTE) NOT NULL,
name varchar(20),
Start_Date DATE,
Salary Number(8,2),
mv_avg number(8,2)
[code]....
I need to move database ORCL into our existing central database CNTR (both are on same OS and oracle version) I started exp each schema from ORCL and imp in CNTR.
But there is one schema EXMP in database ORCL which also exists in CNTR database with same tables, indexes . The data under schema EXMP in ORCL should be added to schema EXMP in CNTR.