SQL & PL/SQL :: Partition Creation Based On Other Table Values
Feb 12, 2013
i am trying to create Procedure which will create the partitions based on the other table date values one per each day.
CREATE OR REPLACE PROCEDURE PARTITION_TEST(PART_DATE_TABLE IN VARCHAR2, TABLE_NAME IN VARCHAR2,SCHEMA_NAME IN VARCHAR2)
AS
V_PART_NM VARCHAR2(20);
V_PART_CNT NUMBER;
V_DATE DATE;
V_SCHEMA_NAME VARCHAR(15);
[Code]..
It is not creating the partitions and even not giving any errors.
I have 2 tables , which can be mapped with a common column and the second table's rest of the columns should become like columns(like pivot) and in the second table itself 1 more column called value , this sum of the value becomes a value to the pivot column value. For this I am using CASE structure and I am writing the individual case for each column , but I am sure after some extent case don't work at all and I would like to do it dynamically( when ever the new entry will come into the source table , my proc has to pick automatically the new value and put the new value as part of pivot structure) , so pls share your inputs and if possible provide some sample code.
I need to partition a existing table based on varchar2 field (which is actaully date value but storing as character in the table). Using below statement for creating table, but getting error.
I have a stored proc SP_INSERT_TRAINEES.Here sTraineeNo is provided as input which has count of trainee needs to be inserted in table aaa_foc.user_profile. The sequence is used to generate ids as :
I want the values for ids to be inserted as 001,002,003,.......010,011,................099,100,101,............999.So that the values in the table would be like TRAINEE001,002.......
I have tried to use LPAD to it but the values are getting insertes as TRAINEE1,2,3...........
The code is given below:
CREATE OR REPLACE PROCEDURE AAA_FOC9.SP_INSERT_TRAINEES (sTraineeNo IN NUMBER, nReturned_O OUT NUMBER) IS ln_insert_cnt PLS_INTEGER :=0; nSequence NUMBER:=0; [code]......
I have partition based table one the basis of year month. And we have 8 local indexes on this table. Every month we have to create a new partition and load data into this partition and the volume of the data is around 14million and the load process is taking long time due to indexes. Is it possible to drop the indexes from particular partition?
basically this view has columns based on the previous tables column ( MEASURE_ID) values and the values will be corresponding value in column Percentage.
I have two tables lets say TAB_A and TAB_B. I altered table B to include a new column from table A I wrote a merge statement as follows to merge the data
MERGE INTO TAB_AUSING TAB_BON (TAB_A.SECURITYPERSONKEY=TAB_B.SECURITYPERSONKEY)WHEN MATCHED THEN UPDATE SET TAB_A.PPYACCOUNT=TAB_B.PPYACCOUNT;
I know INSERT is for inserting new records UPDATE to my knowledge is to modify currently existing records (loosely) MERGE is one I rarely used, until this particular scenario. The code works perfectly fine, but I was wondering how could I write an update statement? Or in this scenario should I even be using an update statement?
I have more than 100 records in CSV format. I have to import these records to a particular table which already contains data. I have to do a multiple update at a time, based on the condition . ie., if field1 is '1' then update field2 as 'A0001' and if field1 is '5' then update field2 as 'A0007' . The values are not in an order. Is it possible.
How can we partition a table based on date if it does not have a date column.
Actually I have to compare two tables on daily basis and fetch few rows from those two tables and enter it to a third table.But both these tables does not have a date column.
I am confused if i need to alter those tables and add date column or if there is some way in which i can compare the data from the two tables for that particular day only and not the whole table data.
I used Region, Process by to search the report which appears as shown above. Then I use Choose Auditors column to select my Auditor and copy paste it into the report under To be Audited By col. Is there a way to automate the process. I am here using a tabular form in APEX. My main aim is to assign auditors based on Region, not equal to Processed by.
I have a range date partition table month wise which i want to truncate the partition and load for each month data i get from source.
How can i get the partion name based on data value.
e.g
source data
SALES_IDSALES_DT
101/02/2008 101/03/2008 101/04/2008 101/05/2008
target table has same structure as source but has more data but i want to delete only those which is coming from source if it is there in target and then load. so i want to get partition name so that i can truncate and load them.
I think that performance better partition table than non-partition table. How to assure partition table is better than non-partition table at SELECT operation?
I have compare a specific query EXPLAIN PLAN at partition table and non-partition table. both tables data is same. Is it true way or not?
How to truncate the partition based on the given input. I've below table partitioned on dt field. How can i truncate the whole partition which contain the date 2013/04/30.
CREATE TABLE TST_SUMMARY ( CUST_ID NUMBER(38) NOT NULL ,
I have a table that partitioned into six partitions. each partitions placed in different table space and every two table space placed it on a different hardisk
when I will do query select with the non-partition keys condition, how the search process ? whether the sequence (scan sequentially from partition 1 to partition 6) or partition in a hardisk is accessed at the same time with other partition in other hardisk. ( in the image, partition 1,4 accessed at the same time with partition 2,5 and 3,6)
I'm using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production and TNS for Linux: Version 11.2.0.3.0 - Production.Requirement is to create a script to add a LIST partition to some selected tables in a schema (tables do not have data, they are not partitioned). There are about 300 such tables (can vary) and their names are maintained in a separate table. Example -Existing table
-CREATE TABLE test_part(id number (2), name varchar2(20), audit_userid number (9)); Expected table -CREATE TABLE test_part(id number (2), name varchar2(20), audit_userid number (9)) PARTITION BY LIST (audit_userid) (PARTITION p1_audit_userid VALUES (1));
Ultimate goal is to add more partitions based on the amount of data to be populated.
Can i alter the table to create partition on non partition table, i have tried and could not create it. Do we have some other means to do it as this is the live table and cannot drop them else will lose the data.
Can I add range sub partition to a hash partition table. Example like this.
CREATE TABLE test ( test_id VARCHAR2(10 ) , test_TYPE VARCHAR2(5) , CREATE_DATE date ) partition by hash (test_id, test_type) Partitions 3 SUBPARTITION BY RANGE (CREATE_DATE);
When Tried, I am getting syntax error as invalid option.
When I am trying to insert record from tbl_mittal into tbl_temp table. I am facing "ORA-14400: inserted partition key does not map to any partition" error
SQL> insert into tbl_temp select * from tbl_mittal; insert into tbl_temp select * from tbl_mittal * ERROR at line 1: ORA-14400: inserted partition key does not map to any partition
AS tbl_mittal is having hugh number of records so I am providing only few rows from tbl_mittal table as test data.
I have one hirarchical query which return the parent to child hirarch level data. it has 11 level child data. i want to create column based on number of child in hirarchy. though i know it is 11 but it can change also.Is there any way i can create the column dynamically
How to find the size pf a partition in a partition table?I guess we need to query views like dba_tab_partitions but I am not very sure. will running dbms_stats.gather_table_stats('schema_name,'table_name,'partition_name')
I have a data in one table with 6 columns where user may be updating values in all of these 6 columns or he may enter 3 or 4 columns based on that inserts should take place, this is similar to my previous thread , i am using if condition to check column for null if its not null then i will make a insert , but is there any other easier way to do this.
insert into ot_po values ('ss-po',1,ph_sys.nextval); insert into ot_inspect_head values (inh_sys.nextval,'ss-ins',1,'ss-po',1); commit; select * from ot_inspect_item
--Now if the inspection user issues the update statement , it will delete this row --from ot_inspect_item and reinserts the values with values based on --ii_flex_01,ii_flex_02,ii_flex_03 [code]...
I have a scenario, where I need to find the bucket values with the flag as "Y". The problem is i have 5 buckets
Bkt1 Bkt2 Bkt3 Bkt4 Bkt5 Flag ------------------------------------------ 1000 500 230 100 677 Y 789 100 122 666 888 N 783 110 142 166 788 N 781 110 112 616 988 Y 709 130 121 661 878 N
Here I need to write a SQL query in Oracle 10g fetching the respective bucket value(for all the 5 buckets) along with the flag as "Y" and more importantly should have the bucket name as well.This is needed as in the UI we need to display the respective bkt value which is "Y" as bold.
So in the final List I need to have the VO which should contain the respective bucket name, respective value and flag as "Y"
Some thing like this I need to get in my final ArrayList in java
Bkt1 1000 Y Bkt1 781 Y Bkt2 500 Y Bkt2 110 Y Bkt3 230 Y Bkt3 112 Y Bkt4 100 Y Bkt4 616 Y Bkt5 677 Y Bkt5 988 Y
I think that performance better partition table than non-partition table. How to assure partition table is better than non-partition table at SELECT operation?
I have compare a specific query EXPLAIN PLAN at partition table and non-partition table. both tables data is same. Is it true way or not?
I am an Oracle beginner and I am having some trouble with the following insert query.
I am inputting values into text boxes and then this is carried out as a trigger upon clicking a button.
INSERT INTO client VALUES(':student.txtclientid', ':student.txtclientname', ':student.clientaddress', 13564338); INSERT INTO enrolment VALUES(':student.txtclientid', ':student.lstoccurrence', null, null);
The above text boxes are all working fine as I have viewed the values using the message command. My proplem is that if i leave the fields blank it inserts ':student.txtclientname' into the row, otherwise it returns "Could not insert record"
Personally, I don't need the top value(s), but it would be nice.I was trying a few routes with rownum, and I can get it to pull 1 set of pairs with a where location= condition, but I can't seem to successfully combine the two.
