All Questions

Tagged with
Filter by
Sorted by
Tagged with
0
votes
0answers
19 views

Oracle - partition table by 3 columns

I have, in Oracle database, fact table based upon which reports are generated. Table contains sales data - large volume. Users generating reports are usually using grouping by (sale_day, customer, ...)...
1
vote
2answers
46 views

Index on a partitioned key

we have a list partitioned table. Let partitioned column be c1. We have an index on that column(c1). We are planning to drop the index. Our doubt is that if we drop the index, does it affect the ...
0
votes
0answers
30 views

Range vs List partitioning for YYYYMM year month field Oracle 12c

I have a table which holds monthly data differentiated by a column year month (YYYYMM, eg 201911). We have been planning to partition the table by month however we are unable to decide between Range ...
-4
votes
1answer
47 views

Monthly to Daily Partitions in Oracle

I have a table with monthly partitions on it. I was asked to change those monthly partitions to daily partitions. For that, can I do even if the data exists ? What prerequisites do I need to take care....
1
vote
1answer
76 views

Performance Recommendations for Large Volume of Data

Oracle Table: Fields: 55 Roughly 10 are Used for Querying Single Record Length: 512 bytes Records: 3-5 million records per day Period of Time: 13 months to be available, > 13 months to be purged ...
1
vote
1answer
50 views

Table not analyzed after partition has been split

On my table I split a subpartition successfully. After that I rebuild the indexes and gather statistics. ALTER TABLE T_MO_DPI SPLIT SUBPARTITION ACTIVE_RELATIONS VALUES (24) INTO ( SUBPARTITION ...
0
votes
1answer
437 views

How to alter an existing partitioned table to add sub-partitions to it?

This forum has helped me earlier on many occasions and now I need someone to help me with this. I am looking to convert a pre-existing partitioned (range) table - to sub-partitioned (composite range-...
0
votes
1answer
49 views

Oracle partitioning scenario with primary key absolutely required

Is there any partitioning scenario in which the partitioned table is absolutely required to have primary key?
0
votes
2answers
164 views

With partitioned table, How can I use hints to group each partition separately

Suppose I have the following table data(partitioned_key_index, some_dummy_measure) Assume that partitions are of an equal large size. with Oracle 11g. The end result should be like this ...
0
votes
1answer
120 views

Internal error code, kksgaItLvl_Setup [closed]

A simple query fails with the error. Has anyone faced this issue or has any solutions? SELECT MAX(some_value) FROM some_table WHERE some_id = 62328188199 AND some_name = '...
1
vote
3answers
150 views

Partial local index on partitioned Oracle table

We have a big transactional table, partitioned on transaction date (day). Each day, several million records are added. We need to do selects on this table, over a period of the last five days, and ...
2
votes
3answers
133 views

Size of Partitions in Partitioned Tables

According to the following link, it is advisable to use hash partitioning when, "The sizes of range partitions would differ quite substantially or would be difficult to balance manually." This implies ...
0
votes
1answer
707 views

Rename column in a partitioned table

I want to rename a column in a partitioned table. Will oracle do this for all partitions? Is there anything special for this change?
5
votes
3answers
248 views

switching from normal list partitioning to reference partitioning in oracle 11g

I have a main parent table and multiple child tables which have normal partitioning in it, wherein the partition column is repeated in all child tables. I want to switch to reference partition(oracle ...
0
votes
2answers
1k views

Local indexes vs Global indexes for partitioned tables in Oracle

I have partitioned a table that is growing almost at a rate of 7-8 million rows a day. The partitioning has been done using a timestamp column as data can be archived or discarded a few weeks later. I ...
0
votes
1answer
3k views

Dropping empty partitions in Oracle 11g DB

I have task to drop empty (0 rows) partitions and subpartitions from data warehouse. I'm preparing script that: Will count the rows of partition/subpartition When row count will be 0 the partition ...
2
votes
1answer
175 views

Where can I find the algorithm used in Oracle Range Partitioning

I am new to database and would like to know where can I find the resource to study and understand about different algorithms used in table partitioning For example, what algorithm is used between ...
1
vote
1answer
448 views

drop table causes ORA-08103 with another one

In my process I have 2 tables. target table, load table. The target table holds the current data, and a lot of queries are fired against it. When new data coming we use a FULL LOAD and the goal ...
2
votes
3answers
3k views

Feasibility of Partitioning existing tables with huge data in Oracle

We have a set of 24 tables that take up about 1tb+ data which is why DBA suggested to use partitioning to better handle usage. The only issue being the partitioning of the existing tables is ...
2
votes
1answer
768 views

Oracle 12c, Will dropping partition which has 25% of total data, rebuild the indexes?

I have Oracle 12c database and partitioned table. There is a old partition which has 68734MB which is 27% of total. I need to drop this partition. This is a production database and I does not have a ...
1
vote
0answers
1k views

Gather stats - Partitioned Table

Database: Oracle 12c I have a process that selects data from Fact Table, summarizes it and pushes it to Summary Table. Summary table is Range Partitioned (Trade Date) and List Partitioned (File Id). ...
3
votes
1answer
1k views

How to shrink space on table with a function-based index?

I have a partitioned table for which I'd like to remove unnecessary free space. Usually I would run commands like this: ALTER TABLE {table name} MODIFY SUBPARTITION {subpartion name} SHRINK SPACE; ...
0
votes
2answers
646 views

Partition an unpartitioned primary key index to become locally partitioned by primary key

I tried: alter index clave_primaria_de_b local; and I read: ALTER INDEX But I don't know how to do it.
1
vote
0answers
287 views

No Tablespace found during dump import

Original Question: Oracle dump imported with zero rows I have exported the oracle dump from source by using exp username/password file=filepath/filname.dump I tried to import the exported dump ...
0
votes
1answer
530 views

Range Partitioning Oracle

I need to understand how partitioning can be applied to tables for an oracle 11.2.0.4.0 database. Please start a guide with me that hopefully with helps others who would like to learn as well. After ...
2
votes
2answers
438 views

How to sort the data fetched from horizontal partitioned tables

I have a telco billing software system. In it there are daily logs of users' calls. The logs are horizontally partitioned by date (month). Each partition is stored in a separate database and may be ...
0
votes
1answer
35 views

In Oracle how do I put certain tables or partitions in a different group so the rest of the db can be brought up with OR without them?

I've done this with MS SQL Server in the past where data that is not required for disaster recovery for instance is in a separate file group and file and not cloned to save significant space and ...
0
votes
1answer
84 views

Unable to view partition table in a schema

I am using oracle 11g enterprise edition, I have created a schema as "TESTSCHEMA" and in it i have created a partition table as "sales_test". The query for the table, create table sales_test (...
2
votes
1answer
197 views

Drop 15 days records in monthly partition

I have created a monthly partition on my table like below: PARTITION BY RANGE (PARTITION_KEY) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) ( PARTITION MayPartition VALUES LESS THAN (TO_DATE('...
1
vote
1answer
3k views

How to add Subpartition to all partitions

I believe adding sub-partition in Oracle 11g is easy ALTER TABLE PART_TEST modify partition OCT19 add subpartition OCT19AXCS values ('AXCS'); I have close to 250 partitions..namely jan07 feb07 ... ...
3
votes
1answer
9k views

SQL Error: ORA-14300: While partitioning and subpartitioning

I have tried this earlier and it works: I have T1 which has no DATE or STATE which needs to be partitioned on DATE and sub-partitioned on STATE Hence for the ID present in T1, fetch the DATE and ...
-1
votes
1answer
63 views

Alter - Subpartition a partitioned table

I have a table which is partitioned by RANGE on DATE key CREATE TABLE T1 DOC_ID VARCHAR NOT NULL. STATE VARCHAR ... DOC_DATE ... PARTITION BY RANGE (DOC_DATE)( PARTITION feb16 VALUES LESS ...
1
vote
1answer
8k views

How to change initial extent of an existing partition

I'm having trouble with something that should be rather simple ... I'm sure I'm missing something obvious. I'm trying to change the initial extent of an existing table partition. For some reason, I ...
0
votes
1answer
639 views

Very large subpartitions after table redefinition

I'm trying to redefine a table to be partitioned. The original table has about 1.5M rows and about 150MB size. select segment_type, segment_name, bytes / 1014 / 1024 as SIZE_MB from user_segments ...
1
vote
1answer
1k views

DBMS_REDEFINITION with WHERE clause

I wanted to know if DBMA_REDEFINITON package allows a WHERE clause to filter contents before migration. I have a partitioned table and wants to copy data and constraint to another table using ...
3
votes
1answer
10k views

Drop partition and rebuild global indexes “online”

I am using Oracle 11g. I have a requirement to drop a partition and rebuild global indexes. The query below does job well but BLOCKS all DML operations on the table until the indexes are rebuilt. ...
1
vote
1answer
879 views

Package DBMS_REDEFINITION missing from Oracle 11g?

I'm trying to partition an existing table in Oracle and the research I've done seems to say that using the DBMS_REDEFINITION package seems to be the best way to do this. The problem is I doesn't seem ...
0
votes
0answers
57 views

Optimizing DB for performance by archiving/partitioning

I have a table in my Database which would be loaded with huge amounts of data each day. These are basically status of the orders customers has placed. The UI in my application which calls the DB for ...
0
votes
1answer
47 views

How do I create a table best suited for storing address information

I have to create an address table in Oracle. Table is expected to hold millions of row. I created a table looking like this Table Address ID | Address Line1 | Address Line2 | Address Line3| City ...
2
votes
1answer
6k views

Can I exchange partition between two table with partition with same name?

I am trying to restore one partition from a backup table. Both tables have partitions with the same name. My goal is to grab the data from the backup table partition x and put it in original table ...
0
votes
1answer
3k views

Oracle 11g - drop maxvalue partition, keep data

I have some tables with data like this: CREATE TABLE "PARTTEST4" ("year" number(4,0) NOT NULL ENABLE) PARTITION BY RANGE ("year") (PARTITION "P_OLD" VALUES LESS THAN (2010), PARTITION "...
4
votes
1answer
3k views

How to maintain a global index on a large table undergoing regular partition purges?

Problem I've got giant table haystack weighing in at nearly 3 TB, partitioned by month (date) and subpartitioned by client. I need to create a global index that spans all clients to facilitate ...
3
votes
1answer
901 views

Why can an Oracle SET UNUSED COLUMN cause internal UPDATE SET X=X and huge log generation?

On Oracle 11.2.0.4.1, we tried to run a simple ALTER TABLE PARTITIONED_LARGE SET UNUSED COLUMN XXXXX; on a large ref-partitioned child table to decommission a column. The table has hundreds of ...
1
vote
2answers
5k views

Compressing partition - what about index?

To save space on disk I want to compress some partitions of my tables: alter table TEST_TAB move partition TEST_TAB_2014 compress for all operations; I have also indexes on this partition. Should I ...
2
votes
0answers
1k views

Fast restore of dropped partitions

I want to test the restore of erroneously dropped partitions of a subpartitioned table. This restore would consist of three steps: restore the database (or at least the necessary tablespaces) to the ...
1
vote
2answers
2k views

Will enabling row movements on list-partitioned table cause performance problems(oracle 11g r2)

Our incident management system has run for years, and the large quantity of closed incident tickets makes the table rather huge and slows down the search queries. Our database version is oracle 11g ...
1
vote
1answer
925 views

Is there any way to use “like” operator during oracle table partitioning

I am using Oracle 11g database and I have 45 million records. I want to partition the table, but if it's possible for partitioning to support the like operator then it will be much helpful for me. In ...
0
votes
3answers
570 views

Active/current (1-5%) and historical records in same table: index or partitioning?

I am trying to discover the correct architecture for the following situation. An application stores 'source data' in multiple tables. Each of these 'source data' tables contains actual, current, ...
0
votes
1answer
3k views

Oracle 11g r2: Cannot create partition on timestamp field

I have data with a timestamp field of the form '2014-01-01 00:00:00.99' in one of the columns. I want to create a new partition for the table every month. For this I did something like this. CREATE ...
1
vote
0answers
98 views

Oracle estimate cardinality on partitioned table

How does Oracle estimate cardinality on a partitioned table if the query gets the list of partitions from a subquery or a join? For example, if I have a query like this select * from ...