i want to create table from other database table by using dblink.but while creating the new table i am getting a error of temp space.
the data is the source table have around 2 millions of records.
i am using below syntax to craete table:
CREATE TABLE UDT_Fcst_arch_72 PARALLEL (DEGREE 4)
AS SELECT * FROM UDT_Fcst_arch@PRD
Oracle 10.2.0.4
I partitioned a source table of around 100 million rows (62GB) in DEV server. The target database was created new. It was range partioned on a date column as follows:
PARTITION BY RANGE (ENTRY_DATE_TIME)
(
PARTITION ppre2012 values less than (TO_DATE('01/01/2012','DD/MM/YYYY')) TABLESPACE WST_LRG_D,
PARTITION p2012 values less than (TO_DATE('01/01/2013','DD/MM/YYYY')) TABLESPACE WST_LRG_D,
PARTITION p2013 values less than (TO_DATE('01/01/2014','DD/MM/YYYY')) TABLESPACE WST_LRG_D,
PARTITION p2014 values less than (MAXVALUE) TABLESPACE WST_LRG_D
)
That is yearly basis. Anything before 2012 went to ppre2012, then p2012, p2013 and so forth. There is 20 million rows in p2012. and around 75 million rows in ppre2012. We needed both the source (un-partitioned) and target (partitioned) tables in DEv for comparision. The queries are normally on the current year partition. Just to state taht I am a developer and don't have full visibility to the production instance.
Now that our tests are complete, we would like to promote this in production. Obviously in production we would not not need both source and target tables. In all probability this will be performed over a weekend window. Therefore I would like to suggest the following .
1) use expdp to export source table
2) drop the source table
3) create a new source table "partitioned" with no indexes
4) use impdp to get data back into table
5) create global index (it is a unique index to enforce uniquness) and the rest of indexes as local
6) perform dbms_stats.gather_table_stats(user,'SOURCE', cascade=>true). This takes around 2 hours in dev
My point is that whether importing 100 million rows will not cause issues with undo segments. Can we import data say first to the current partition p2012 (20 million rows) first.
I have to drop some partitions in table on production environment (to get free space). The environment have to be continuously available. I was considering of use ALTER TABLE ... DROP PARTITION ... UPDATE INDEXES but it is slow, because of use clause UPDATE INDEXES. Is there another possibility to remove these data?
View 2 Replies View RelatedWhat is the difference between CREATE EXTERNAL TABLE Vs CREATE TABLE .?
Is CREATE EXTERNAL TABLE included in CREATE TABLE?
I am trying to execute dynamic SQL in Stored Function and I don't know how to do this.
Explanation:
In the function I am calling pr_createtab is procedure which will create a physical table and return the table name in the out variable v_tbl_nm.
I need to query on this dynamic table and return the result as return result. But i am not able to do it.
Here T_web_loylty_report_table is a type.
CREATE OR REPLACE function CDW_DSS.f_ReturnTable(i_mrkt_id in number, i_cmpgn_year in number)
return T_web_loylty_report_table is
v_tbl_nm varchar2(50);
i_cntry_cd varchar2(20);
v_sql_str varchar2(32567);
[code]......
We have a table in the client database that has two columns - column parent and column child. The whole hierarchy of DB table dependencies is held in this table.If Report 1 is dependent on Table A and Table A in turn is dependent on two tables Table M and Table N. Table N is dependent on table Z it will appear in the db table as,
Hierarchy Table
Parent Child
Report1Table A
Table ATable M
Table ATable N
Table NTable Z
Requirement :
From the above structure, we need to build a table which will hold the complete hierarchy by breaking it into multiple columns.The o/p should look like this
-ParentChild 1Child 2 Child 3
-Report1Table ATable M
-Report1Table ATable N Table Z
Child 1, Child 2, Child 3 ....and so on are columns.The number of tables and the no of hierarchical relationships are dynamic.
SQL Statements to create hierarchy table:
create table hierarchy (parent varchar2(20), child varchar2(20));
insert into hierarchy values ('Report1','Table A');
insert into hierarchy values ('Report1','Table B');
insert into hierarchy values ('Table A','Table M');
insert into hierarchy values ('Table B','Table N');
insert into hierarchy values ('Report2','Table P');
insert into hierarchy values ('Table M','Table X');
insert into hierarchy values ('Table N','Table Y');
insert into hierarchy values ('Report X','Table Z');
Approached already tried :
1) Using indentation : select lpad(' ',20*(level-1)) || to_char(child) P from hierarchy connect_by start with parent='Report1' connect by prior child=parent;
2)Using connect by path function :
select *
from (select parent,child,level,connect_by_isleaf as leaf, sys_connect_by_path(child,'/') as path
from hierarchy start with parent='Report1'
connect by prior child =parent) a where Leaf not in (0);
Both the approaches give the information but the hierarchy data appears in a single column.Ideally we would like data at each level to appear in a different column.
what command is used to create a table by copying the structure of another table including constraints ?
View 2 Replies View RelatedI am facing the row lock issue in production. I have been trying to resolve the issue but i coud'nt. I traced out by using different queries which sql query is locking which but everything looks good.
And i also checked for connections open and close everything is in good place but unable to resolve the issue. we are running a batch file which runs in every night some of the records are processing and if any one record is failed it is blocking another records.My oracle version is oracle 10.2.0
I faced a problem in our production primary DB like the listener was running but it was not connecting through the remote system, when i accessed the primary database.
But when i restarted the listener it started connecting. I want to know the reason for this crash/freeze.
Oracle version: Oracle Database 11g release 11.2.0.1.0 SE1 64 - bit production
Would like to know if I can replicate Production database (10.2.0.2) to Test (10.2.0.4).
tell me the process to do that on windows environment.
I wonder if I can do it with two different oracle versions?
We have Development, Staging/UAT ( installed on XX.XX.XX.10 ) and Production ( installed on XX.XX.XX.20) Environment respectively. I have queries regarding getting the data from Production environment into Staging environment. The overall PROD database size is around 250 GB.
STAGING DATABASE DETAILS
SID : STG_DB
Staging Schema Name :schema_UAT
Replication Schema Name :schema_PrdReplica ( This is the schema where the production data gets loaded daily)
PROD DATABASE DETAILS
SID : PROD_DB
Prod Schema Name : schema_PROD
What is happening now:
----------------------------
There is a script (Stored Proc) written on staging ( STG_DB.schema_PrdReplica ) environment which executes daily in NIGHT and does replication. Currently we use DBMS_DATAPUMP to get the ENTIRE data/Meta Data (Everything) from Production to Staging. It is ta king significantly more time. It takes approx 8 Hours to replicate the everything from PROD_DB.schema_PROD to STG_DB.schema_PrdReplica
What I am expecting :
-----------------------------
I want to reduce the replication time.
I have heard about Level 0 (Full BackUp ) and Level 1 ( Incremental Cumulative ) Backups in RMAN. I am planning to take PROD_DB.schema_PROD Full Backup (Level 0) on Sunday and will restore that on STG_DB.schema_PrdReplica immediately. And on weekdays ( Mon - Fri ) I will take Level 1 ( Incremental Cumulative ) and will restore that on STG_DB.schema_PrdReplica
I am assuming by doing so, the overall replication time will be reduced. How can I implement this with script assuming that two different servers are on different machines.
I was planning to a production cut over from Aix to linux .I thought GG as an option so that i can have two DBs run parallel and replicate and do a cutover to linux during the change window.
Now the problem i see is that only half the tables have primary key.. so I THINK golden gate cannot be used an an option.
I have been asked to create a new instance of an application server. As part of this, my management has asked me to replace any old JREs or JDKs.
We run 10g, so I installed the 10203_vista_w2k8_x86_production_client on my new server. I would like to replace the 1.4.2 JDK that is bundled with the Oracle Production Client with 1.6.0.
How we can mask a column value present in the production which is very sesitive like bank a/c no,Credit card nos etc while replicating to the test/development envirnoments in PL/SQL.Things that need to be considered is referential integrity,data types,length of the column and performace while execuing the code.
View 2 Replies View RelatedI am trying to use the APEX (as a substitute for my PHP forms base application) for a new client. Can I use the APEX and Oracle XE in production, without spending any money for a license from Oracle - i.e. for listener, Web server, etc,?
I already have setup the APEX that works fine on my laptop, able to see and work with the form on the browser, so can I take the same to a server as a production?
Oracle 11g default server process is dedicated select distinct server from v$session; dedicated
Which server process is used in the oracle 11g Production? if the database is connect to an online application?
Need patch strategy specially for production environment?? We have production environment and we are planning to patch our databases using PSU pacthes with no downtime. Our production environment has standby. We have one option that we can make standby to primary. Is there any other options that we can use?
View 2 Replies View RelatedI have a requirement to upgrade Oracle Data Integrator(ODI) from 10.1.3.5 to 11.1.1.6.3 We have a Clustered production environment where N1 will be up when N2 be down and viceversa.
Here N1 and N2 are the ODI servers as well as DB(11g Release 2) servers. They both access the SHARED CLUSTERED database. From ODI we will generally point the Oracle clustered IP(Virtual IP) which will internally point either N1 or N2 whichever is active. ODI application wise we are clear about the procedure.
Having some issues on DB related activities.
1. Should I break the cluster definitely? Cant I do the activity without breaking the cluster?
2. Do I need to point N1, N2, Clustered IP (Virtual IP) while doing the activities?
3. Since its a clustered database, do I need to db related activities once or twice? (Twice means, manually on both the servers)
4. As they are using same file structures (RAC), If the Virtual IP points N1 by default, assume that I create two new users a
We just got a new Dell R720 server that will host our Oracle DB. The server hasn't even been turned on yet but we know that the load on the server will be very low for a long time.
One of our problems is that we need to run a VERY important application. Since it is not very resource consuming compared to it's importance we chose to run it on a not so new Xeon 5110 1.60 GHz - 4GB RAM server. He said it's not a good idea and that we should buy a new server. (money is very low)
The software vendor suggested to virtualize our R720 server, host a vm running our database, and along with it other smaller machines like the one I described above. I suggested the use of Oracle VM, Oracle Linux for the database host and transforming the physical servers servers in VM with P2V.
Our IT Manager didn't like that, he said that it's not recommended to run a database on a virtual machine. But our software vendor said that many of their clients run their solution this way.
I want to create temp table, for this i am using:
CODEcreate global temporary table help_temp
as
select * from help;
but this is creating only the table structure, not copying the table data.
can we create table with copying of another table with some specific condition.
example.suppose we have one table which name is emp with three columns.
empid
empname
empjoindate
i want create a table emptemp by using emp table where empjoindate between two dates.
I have to create a table which contain history of a main table. like this:
if the main table is
========================
nametypelengthnot null
Avarchar5Y
Bvarchar5N
Cvarchar5N
Dvarchar5N
========================
[code]....
I've plan to so this by create a trigger in main_table. my problem is my main table have a lot of fields and I can't write a code to control it 1 by 1 like :
if old.A <> new.a
insert into history("A",old.A,new.a)
if old.B <> new.B
insert into history("B",old.b,new.b)
......
I decided to select column name from the data dictonary using this SQL:
SELECT column_name FROM user_tab_columns WHERE table_name = '<<Table Name>>';
and then do a loop over the resultset and use the column name I've got , like this (its just an idea, may be not a write syntax):
BEGIN
.....
FOR i IN 1..:result.COUNT LOOP
if ld.colname[i] <> :new.colname[i]
INSERT INTO history
VALUES ( colname[i], ld.colname[i], :new.colname[i]);
END LOOP;
END;
but I can't write a "old.colname". I try with " old.'colname' ", " ld.'colname' " but it won't work.how to create a history file like I've describe.
: I noticed three data files on my standby show's they are corrupted. What will be the easy and fast way to sync them up from my production database?
View 1 Replies View RelatedI have a JSP that works with an Oracle 9i database.
On my local windows workstation where I developed the JSP the application processes very quickly working with the Oracle database. The JSP application on the Production Intranet web server connecting to the same Schema processes very slow sometimes during the day. During off hours the Production Intranet JSP with Oracle processes quicker.
There is only one user for the application and I dont understand why the same application using the same database runs so much slower sometimes on the production server compared to my local workstation.
I am trying to refresh the validation database with the old production backup i.e. our requirement. I have given the rman script i have executed and the output error message amd the rman configuration setting. Plus in the next post ill post the current sucessfully running RMAN script for your reference.
rman TARGET sys/passwd@Production CATALOG rman_pmxp/rman@catlog AUXILIARY sys/passwd @validation
RMAN> show all
2> ;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 90 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'H:\backups\DB_Sunday_Backup\Prod_%d_%F.rman.ctl';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%F';
[code]...
Where to download the oracle software for building a production server? What are steps ?
View 1 Replies View RelatedI got following error when i am gathered stats on Schema level.
SQL> EXEC dbms_stats.gather_schema_stats(ownname=>'KDB', estimate_percent=>dbms_stats.auto_sample_size,cascade=>TRUE, force=>TRUE);
BEGIN dbms_stats.gather_schema_stats(ownname=>'MFDB', estimate_percent=>dbms_stats.auto_sample_size,cascade=>TRUE, force=>TRUE); END;
*
ERROR at line 1:
ORA-20003: Specified bug number (9196440) does not exist
ORA-06512: at "SYS.DBMS_STATS", line 15342
ORA-06512: at "SYS.DBMS_STATS", line 15688
ORA-06512: at "SYS.DBMS_STATS", line 15766
ORA-06512: at "SYS.DBMS_STATS", line 15725
ORA-06512: at line 1
SQL> EXEC dbms_stats.gather_schema_stats(ownname=>'CDC_DATA', estimate_percent=>dbms_stats.auto_sample_size,cascade=>TRUE,DEGREE=>10);
BEGIN dbms_stats.gather_schema_stats(ownname=>'CDOMIG_DATA', estimate_percent=>dbms_stats.auto_sample_size,cascade=>TRUE,DEGREE=>10); END;
*
ERROR at line 1:
ORA-20003: Specified bug number (9196440) does not exist
ORA-06512: at "SYS.DBMS_STATS", line 15342
ORA-06512: at "SYS.DBMS_STATS", line 15688
ORA-06512: at "SYS.DBMS_STATS", line 15766
ORA-06512: at "SYS.DBMS_STATS", line 15725
ORA-06512: at line 1
I would like to know that how can i automate the export from production to test server. I need direction to create process to import data from production (server A) to test server (server B).
View 6 Replies View RelatedI have a below requirement to compare the development and production objects.if any association_type or association_role are not exists in production then i need to return a message like "the Type Object found in Development,but not Production"
Below is the tree structure
development
ProcessingSite(Association type1)
TreatingSite(role1)
MoodedActivity(role2)
MaterialName(role3)
production
ProcessingSite(Association type1)
TreatingSite(role1)
MaterialName(role2)
Processing Site is an association_type and it is having 3 association_roles. we can observe same association_type in the production, but Mooded Activity(association_role) is not available. in this case we need to return "Type Object found in Development,but not Production".
I have to migrate the current production database to test 10.2.0.4 on windows. Any non-export way to upgrade 9i to 10 g?
i have following steps
1) ALTER DATABASE BACKUP CONTROLFILE TO TRACE
2) shutdown oracle 9i database on server A
3) copy database file, controlfile, redolog, and other files to new server B
4) alter the controlfile backup with new location of bdump, udump, and log file and data file locations
5) user oradim ORADIM -NEW -SID SID [-INTPWD PASSWORD ]-MAXUSERS USERS
-STARTMODE AUTO -PFILE ORACLE_HOME\DATABASE\INITSID.ORA
6) start dabase in upgrade mode
7) run catpat.sql and util102.sql
8) take backup
9) open database for users