New answers tagged

0

There are many restrictions on PARTITIONs. But also, there are many reasons why they don't provide any benefit. What do you hope to gain? HASH partitioning does not provide any performance benefit, unless you are doing "table scans" now. VĂ©race's suggestion is a way that would pass the syntax issues, but, again, not likely to have performance benefit. ...


0

From here, below is an example that range partitions by YEAR - change as appropriate. Here is the part of the manual that explains how to alter tables and add partitions. You should be able to take it from here. CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', ...


1

Not in 11g. Not even in 12c, but 12c makes life a bit easier, you can delay the "rebuild" part until a more appropriate time. In 12c there is a new feature, called Asynchronous Global Index Maintenance. You can drop the partition, and that still leaves the global index usable, and marks the no longer needed index entries orphaned. Then later, you can clean ...


2

Another answer tells you how to use COPY with a query rather than a bare table to get the data from only the parent table. I'm not sure how that is going to help you, though, if your database has to be always available. The master table will be changing while you copy the data out of it, and so the copy you get will be out of date. But for TRUNCATE, you ...


2

Let's quickly check the COPY documentation: COPY { table_name [ ( column_name [, ...] ) ] | ( query ) } TO { 'filename' ...} ... Your friend will be the query part. It means that you cannot only copy from a table, but also an arbitrary query. And example can be: COPY (SELECT * FROM ONLY your_table) TO '/path/to/dump.sql'; Notes: you have ...


1

This is happening because you are trying to alter the table to add partitions when there is a cursor open (active select) on the table. The required locks conflict. You can fix that by first fetching the partition dates into an array, close the cursor and then create partitions from that array. Code sample is below. CREATE OR REPLACE FUNCTION ...



Top 50 recent answers are included