As you can see, I removed the first four columns because the eventkey is the same. In this case, there is only the applicant which is different.So the rest should be blank.
I have a large table with 450 column and we are only using nearly 170 columns and our BD block size is 8k.The DBA informed that there is an row chaining happening in the Database.My question is if we have data available in 170 column .why row chaining is happening.
The DBA informed us to remove the unnecessary columns .. Does those empty columns have any impact on the chaining.If we increase the size of DB block to 32k . does it will resolve the issue.
I have few tables in Oracle 9i/10g , and they already have data in them. I am trying to migrate the data coming from various source systems into these Oracle tables. There is a chance that after loading I might get some unwanted data into these tables.
How do I remove just that data which I have loaded recently, and do not disturb the original data it already has.
Need to backup those tables and reload the data back if there is any problem, but I am looking at a different approach. I just don't want to change the existing system, as lot of users use the system.
I have a CLOB column in one of my tables (Table1), which stores very large (150MB+) XML files. I have new/another table (Table2) in the DB where I have an XMLType column. I want to take the CLOB data (xml) from table1 and remove some part of that and store the rest into to the XMLType column of Table2.
I want to remove the data inside the XML tags
<Attachments>
very long data goes here... which I don't need, which should be replaced with a single word
</Attachments>
store the CLOB to XMLType column after removing the unwanted data.
We would like to remove the partitions from a particular table. The table in question has 12 partitions. Based on some initial investigation, I've come up with the following options. because the table we going to remove partition will have millions of records so on considering the db downtime we are looking for a alternative way. Is there a better way?
Copy data into another table, drop all partitions, then copy the data back into the original table Copy data into another table, drop the original table, then rename the new table and rebuild the indexes.
Our application has been installed at customers in North America, Europe and South America for several years, in some cases, over 10 years. At least one of our customers has hundreds of gigabytes of data. We are considering options for cleaning out the old data.
The database runs on a variety of systems (Linux, Windows, Unix) and in several version (Oracle 9, 10, and 11). We need a solution that works in all environments.
Two of our main criteria for a successful solution are that: -It maintains application data referential integrity. Our application makes little use of foreign key constraints, so the cleanup process will apply critical business rules to candidate data to determine if it can be deleted or not. -The operation of the cleanup program does not impact use of the system in production.
For various reasons (license cost, installation issues) the partitioning option is not available to us.
Alternative 1: Flag records for cleanup
This requires adding a 1-character column to each table. That is a one-time operation done during implementation. The procedure applies the business rules and sets the flag according to whether a row is to be deleted or not. Rows marked for deletion can be checked, reset, exported, etc. Finally, a separate process deletes all marked rows.
Advantage of this is that the deletion process will use a full table scan to find the marked records. There is no index navigation, so hopefully less overhead. Disadvantage is that its updating application data which might affect user's perceived system response. There is some undefined concern that locking or other table activity involved with updating the flags could impact users.
Alternative 2: Build a list of keys for data to be deleted
We will build a list table during implementation. The first process examines the application data, applies the deletion rules and writes key information to the list for data that can be deleted. The list can be checked, reset, rebuilt and listed rows can be exported as required. Finally, the cleanup process uses the list to find and delete the data.
Advantage is that it doesn't update the application data as its building the list. Disadvantages are that it that there is some overhead in building and checking the list. The list requires more space than the flags in alternative 1 but we can handle that in various ways. The procedure needs to navigate key structures during the delete step as well as in the list-building phase.
1) Split values from "INST" Column : suppose 23 2) Find all values from "NUM" column for above splitted value i.e 23 ,
Eg:
For Inst : 23 , It's corresponding "NUM" values are : 1234,1298
3) Save these values into
A table Y : INST, NUM are column names.
INST NUM 23 1234,1298
1) I have a thousand records in Table X , and for all of those records i need to split and save data into Table Y.Hence, I need to do this task with best possible performance.
2) After this whenever a new data comes in Table X, above 'split & save' operation should automatically be called and append corresponding data wherever possible..
I have a column which holds the data in the below format.
Source Data : >SNO_SDSDQ-8192-BN>SNO_54-99-24120-8192 >SNO_SDSDQ-8192-BN>SNO_54-99-24120-8192>SNO_54-90-16489-008G >SNO_SDPMDB-008G-11>SNO_54-90-18008-008G>SNO_54-62-08791-008G>SNO_20-81-00327 >SNO_SDPMDB-008G-12>SNO_54-90-17830-008G>SNO_54-62-08598-008G>SNO_20-81-00327
[code]..
Problem Statement : Split the above data into individual components and create columns / aliases dynamically.If the column is present then place the data under the created column.
Example 1) : >SNO_SDSDQ-8192-BN>SNO_54-99-24120-8192
Result Column Name : SKUCol_54-99 Data :SNO_SDSDQ-8192-BNSNO_54-99-24120-8192
Example 2) >SNO_SDSDAA-002G-101-J>SNO_54-90-16002-002G>SNO_54-62-05781-002G>SNO_20-81-00135-5
Column Name : SKU54-90Col_54_62Col_20_81 Data :SNO_SDSDAA-002G-101-JSNO_54-90-16002-002GSNO_54-62-05781-002GSNO_20-81-00135-5
Column Name can be derieved from the components like SNO_54-62-05781-002G
i.e. SNO_54-62-05781-002G ==> SNO_-05781-002G "54-62" will give the Col_Name
The First Column will always have data starting with SNO_SD% This column is constant and will be named as SKU.
I need this data to be placed in the same record / row but under different columns as per the data set.Basically, Can the data be split into multiple parts based on delimeters and the columns are created based on the unique data in the parts that form the data in the column.
when we have a primary key on 4 columns and we have, say 20 million rows and we want to add one extra row. How does oracle check whether the data on the primary key is unique to the record being added compared to the 20 million rows. Does it actually compare the record being added to all the rows present in the table?
When I create a data block based on a table using Data Block Wizard, the block's Query Data Source Columns property is automatically populated with column definition entries corresponding to the columns of the base table.
I tried making changes to these entries, for example by changing the data types to wrong data types or even deleting them, and I found that those changes had no effect on the block at all. The form was still working as I wanted.
What is exactly the role of the block's Query Data Source Columns property.
The F1 key help says "The Query Data Source Columns property is valid only when the Query Data Source Type property is set to Table, Sub-query, or Procedure". So, explain in each context of Query Data Source Type.
Here columns should be created based purchase dates dynamically with respect to quantity. Query out put will be like matrix format. So i feel that PIVOT & SYS_CONNECT_BY_PATH will not serve my requirement.
I have one query regarding how to retrieve the CLOB data.
The requirement is something that in the select statement there are around 20+ columns which i need to retrieve from around 5 tables after joining.
Since the result set after joining also will get duplicate values i need to use distinct keyword to filter the resultset. But in the 20+ columns there are 2 CLOB data columns which i need to retrieve.
Whenever i use DISTINCT i'm getting ORA-00932: inconsistent datatypes: expected - got CLOB error. I know that DISTINCT keyword cannot be used for CLOB datatypes.
I am working on a form having three data blocks Travel_Inconvineance (Master) TI_Card_Info (Detail) TI_Limit_Indemnity (Detail)
On working TI_Card_Info i have 2 columns Plastic_Card_Id and Plastic_Card_Type which i want to be paste on TI_limit_Indemnity data block as i enter in TI_Card_Info.
As the title of this topic illustrates, i'm having trouble dumping relevant data from columns of different tables. I am using isql*plus. I have three tables appropriatly related. A 'course' table, 'student' and 'next_of_kin' tables. I have many students enrolled on various courses but only a hanfull of courses offer the module option 'Database Systems'. I have no 'module' table but i know the three course names which provide the module option. I intend on producing a report hich lists all students enrolled on the courses which provid the module option 'Database Systems'.
I have attempted the report but i keep getting a 'cartesian product' displaying all next_of_kin names instead of the appropriate. Also i am struggling to come up with the right WHERE statement to depict only the three courses which provide the module option 'Database Systems' as defined by 'courseNo' in both 'course' and 'student' tables.
Here is the most recent attempt:
--set echo off --set pagesize 24 --set feedback off --set linesize 78 col A format 99999999 heading 'Student No'
I created one collection report and in the report when i click on the next >> then the report will show the next columns to enter data in some columns but the data which i entered before going to next will not get retain (data is getting lost) when i come back by clicking << previous but i want the data to get retained even when i click on next >> or previous << in report so that i can enter large amount of data at a time in the report columns by clicking >> & << and click on submit button to save all the data.
I have a report with single row having large number of columns . I have to use a scroll bar to see all the columns. Is it possible to design report in below format(half columns on one side of page, half on other side ofpage :
Column1DataColumn11DataColumn2DataColumn12DataColumn3DataColumn13DataColumn4DataColumn14DataColumn5DataColumn15DataColumn6DataColumn16DataColumn7DataColumn17DataColumn8DataColumn18DataColumn9DataColumn19DataColumn10DataColumn20Data I am using Apex 4.2.3 version on oracle 11g xe.
