PL/SQL :: Create Script That Makes Added Year Worth Of Partitions Less Manual?

May 23, 2013

mucking on an Oracle 11.2 database, simple range partitioning issue. Seems using a "complex" formula inside the AT clause annoys it? Or am I doing something wrong?

I create the table with RANGE partition just fine:

CREATE TABLE my_part_tab
   ( id        number,
     sdate     date
     )
PARTITION BY RANGE ( sdate )
      (  PARTITION P2013Q1 VALUES LESS THAN ( TO_DATE('01-jan-2013','dd-mon-yyyy') ),
         PARTITION P2013Q2 VALUES LESS THAN ( TO_DATE('01-jul-2013','dd-mon-yyyy') ),
         PARTITION P2013Q3 VALUES LESS THAN ( TO_DATE('01-oct-2013','dd-mon-yyyy') ),
       
[code]...

Table created.(and yes, I'm aware of INTERVAL option that'll do this next part "automagically", however, INTERVAL and REFERENCE partitioning are incompatible, and the child table is using REFERENCE partition). to make things easier on DBA for future, I'm trying to create a script that makes added a year's worth of partitions less manual.So far, I have the following working:

ACCEPT lYear PROMPT "Add Paritions for which calendar year?"
ALTER TABLE my_part_tab SPLIT PARTITION PMAX
   AT ( TO_DATE('01-apr-&lYear','dd-mon-yyyy') ) INTO ( PARTITION P&lYear.Q1,PARTITION PMAX );
ALTER TABLE my_part_tab SPLIT PARTITION PMAX
   AT ( TO_DATE('01-jul-&lYear','dd-mon-yyyy') ) INTO ( PARTITION P&lYear.Q2,PARTITION PMAX );

[code]...

But no luck ...Same issue with other variations:

ALTER TABLE my_part_tab SPLIT PARTITION PMAX
   AT ( (ADD_MONTHS(TO_DATE('01-jan-&lYear','dd-mon-yyyy'),12)) ) INTO ( PARTITION P&lYear.Q4,PARTITION PMAX );
ALTER TABLE my_part_tab SPLIT PARTITION PMAX
   AT ( (TO_DATE('01-jan-'||TO_CHAR(TO_NUMBER('&lYear')+1,'fm9999'),'dd-mon-yyyy')) ) INTO ( PARTITION P&lYear.Q4,PARTITION PMAX );

View 11 Replies


ADVERTISEMENT

SQL & PL/SQL :: Prompt To Pull Year Worth Of Data

Dec 30, 2011

I am writing a query where I'd like to pull one year's worth of data. Ideally I want to prompt for the END DATE and have the query go back in time one year from that date.

Here is what I've got after doing some research online... but It's not quite working for me.

select *
from mrtcustomer.profile
where reg_type = 'B'
and contact_type = 0
and active_ind = 'Y'
[code].....

View 4 Replies View Related

Create Table As With Partitions?

Jan 8, 2011

If I try create table from the following syntax

create table a as select * from table b;

Then I could get only base table structure alone, I would like to get partition syntax as well.

View 1 Replies View Related

PL/SQL :: Create Partitions On Table

Sep 13, 2012

My developer came with a requirement of creating partitions on a table which has 40 million records. His exact requirement is to create as many as partitions in such a way that 1 partition should not exceed 5k-10k records and these records should be inserted/updated on the same date (i.e. using a column as source_timestamp field). How to accomplish this?

View 2 Replies View Related

Create Partition without Range (data Not Matching Other Partitions)

Feb 21, 2013

I would like to cretae 3 partitions for a table

partition 1 = < 2011
partition2 = <2012
partition 3 = any other value not staisfying partition 1 and partition 2

i would like to know how to create a partition 3 without specifying the range so that the data which are not coming under the range of other partitions (Partition 1 & 2) will automatically goes to this partition 3.

Also in the above case if i insert data of year 2009 then where will it go , to partition 1 (or) 2 since both of them matches the condition (<2011 and <2012) (or) do i need to use between clause while creating partitions?

View 3 Replies View Related

SQL & PL/SQL :: Create Procedure Have Month And Year As Argument

Aug 5, 2010

Need to create a procedure that will retrun me monthly count of records

create table sample ( S_name varchar2(20), S_Date Date);

insert into sample values('1','01-JAN-2005');
insert into sample values('1','1-JAN-2006');
insert into sample values('2','2-JAN-2007');
insert into sample values('3','4-JAN-2005');
insert into sample values('4','11-JAN-2004');
insert into sample values('3','2-JAN-2005');
insert into sample values('2','12-JAN-2006');
insert into sample values('1','21-JAN-2005');
insert into sample values('2','11-JAN-2005');
insert into sample values('7','01-JAN-2005');
[code]....

View 5 Replies View Related

PL/SQL :: Create Constraint That Checks To Ensure (Year) Entered Is Less Or Equal To Current?

Aug 6, 2013

, I'd like to create a constraint (when creating a table) that checks to ensure that the 'Year' entered is less than or equal to the current year (based off SYSDATE). Per the code below, I keep getting the same error, "missing right parenthesis". I've spent more than an hour trying different ways to get this to work, but I've been failing miserably. I am using Oracle 11g Express. 

CREATE TABLE TEST (Name VARCHAR2(7) PRIMARY KEY,  Year NUMBER(4),  CONSTRAINT TEST_YEAR_CK CHECK (Year <= (TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')))FROM DUAL); 
*ERROR at line 4:ORA-00907: missing right parenthesis

View 7 Replies View Related

SQL & PL/SQL :: Move Data From Old Partitions To Corresponding New Partitions?

Jul 1, 2010

Initially 2008 to 2010 and 2009 to 2011 partitions are created, After that two more new partitions are created 2008 to 2009 and 2009 to 2010.

How can we move data from old partitions to corresponding new partitions.

View 9 Replies View Related

PL/SQL :: Create View Which Is Aggregate Count Of Member Records / Grouped By Business_unit / Gender / Age Per Year

Oct 13, 2012

Using Oracle 11g...We have a table in our database of data with the following information:

MASTER_RECORD,
MEMBER_RECORD,
BUSINESS_UNIT,
GENDER,
DOB (date),
age [at time of month_record],
MONTH_RECORD (date) [31-MON-YEAR for recorded active month]

The table has ~55 million records. Existing index is only on MASTER_RECORD.There is now a need to create a view which is an aggregate count of member records, grouped by business_unit,gender, age per year. eg:

business_unit, gender, age, month_record, num_of_members -> for every combination
unit5, F, 25, 31-JUN-2011, 622
unit3, M, 18, 31-MAY-2011, 573

The view can be created now, but, is not fast enough to be reasonably considered a view. This table is re-created every month from a procedure, so there is flexibility on how it is created. Use interval partitioning by year( something I have not experienced using), create an index on the month_record,then create view.

View 2 Replies View Related

Why A Certain Date Makes Query Faster

May 25, 2012

I have a query with a few tables in join, and I filter data with something like

where mytable.initial_date > sysdate-30

If I use any date, it takes about 5 seconds to run, but if I use

to_date('01010001','ddmmrrrr')

instead of any other dates, it takes just a few milliseconds. Now, it's just a curiosity, but why that date makes the query VERY fast? Does Oracle treat it in some special way? Maybe it knows every date is greatest than that date and doesn't consider the filter?

View 7 Replies View Related

Forms :: Delete Record Makes No Changes

May 12, 2011

I am using 10g and want to let an user to delete a single record from a multirow form via delete button. This button is supposed to do the same, what the "delete record" button on the default menu does. (I want a custom menu, so I can't use that button)

My delete button ist assigned to a control-block and the when-button-pressed-Trigger of that button looks like this:
-------------
DECLARE
TEMP VARCHAR(30);
BEGIN
-- Record chosen?
IF :<BLOCK>.ID IS NOT NULL THEN

[Code]...

The problem is, that the alert asks the user about the previously chosen record correctly, but if the user clicks BUTTON1 the Form says: No changes to save (FRM-40401).

View 2 Replies View Related

Application Express :: Select List That Displays Next 12 Months Worth Of Mondays

Aug 24, 2012

I recently needed a select list that would allow you to choose from a years worth on Mondays starting with the current week's Monday. It will return the date of that Monday. I thought others might be able to use this too... Plus I wanted to be able to find this in the future.

SELECT DISTINCT
to_char(trunc(next_day(trunc(SYSDATE,'day'),2)-1+LEVEL,'iw'),'FMMonth DD, YYYY') AS Display
,trunc(next_day(trunc(SYSDATE,'day'),2)-1+LEVEL,'iw') as Return
FROM dual
CONNECT BY LEVEL <=((add_months(trunc(SYSDATE,'day'),12)-1)-(trunc(SYSDATE,'day')))
ORDER BY 2;

As of today (Friday, August 24th) the above would return:

DISPLAY RETURN
August 20, 2012 20-AUG-12
August 27, 2012 27-AUG-12
September 3, 2012 03-SEP-12
September 10, 2012 10-SEP-12
September 17, 2012 17-SEP-12

[Code]....

View 2 Replies View Related

SQL & PL/SQL :: Materialized View Makes System Very Slow?

Apr 15, 2010

I have created a MV joining 10 tables with FAST REFRESH ON COMMIT It made my sytem very slow.

View 18 Replies View Related

PL/SQL :: Select Statement For Last Name Added

Nov 12, 2013

i want a select statement to retrieve the last name added into the "ename" column .i mean :if i insert "jack" into "ename" column , i want to write  a select statement to retrieve that last name while i do not know it is jack or any other name ? 

View 2 Replies View Related

PL/SQL :: How To Get Added Hours From Results

Jun 14, 2012

I am not getting added hours from results.

SELECT audittimestamp + interval (SELECT      EXTRACT(TIMEZONE_HOUR
FROM systimestamp) FROM DUAL) hour from tab1I want to add Timezone_hour to my timestamp.

View 7 Replies View Related

Oracle 8.5.1 - Stored Procedure Not Added To DB

Mar 21, 2007

After compiling a simple valid SP in Toad for Oracle 8.5.1:

CREATE OR REPLACE PROCEDURE proc_test
AS
DECLARE v_name VARCHAR2(20) := 'Ed Edson';
BEGIN
DBMS_OUTPUT.PUT_LINE('Hi, my name is ' || v_name);
END;

The SP is not added to the schema. Also the three status fields (status, created and Last Update in the Procedure Editor remain <Unknown>. It is not a rights or privilege issue, because the creation works perfectly in SQL*Plus!

View 1 Replies View Related

How Newly Added Log Member Get Sync With Existing One

Jun 29, 2011

If one of the redolog member corrupted and overcome this problem, I had removed the corrupted redolog member. Later I had added a new member to this group.

I would like to know, is the newly added log member will get sync with existing log member? How the newly added log member get sync with existing log member?

View 1 Replies View Related

Server Utilities :: Added A Datafile In Undo Tablespace

Apr 28, 2011

When I am importing, I get these errors

IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (XXXXXXXXXXXXXXXX) violated
Column 1 2
Column 2 OFFLINE
[code]....

I added a datafile in undo tablespace (its an ASM database). I doubt that since I added the datafile to undo tablespace, I am getting this error.

View -1 Replies View Related

Server Administration :: Added Lots Of Space To A Tablespace

Sep 6, 2011

Mistakenly I added lot of datafiles with autextend on option.. I realized later and then tried to resize the datafiles to a minumum space but got the below error

ORA-03214: File Size specified is smaller than minimum required.

How to resolve this problem to reclain the space back?

View 9 Replies View Related

Unable To Change Password In SQLPLUS After Trigger Added

Aug 17, 2012

One of my Customer had followed the document applied pertaining to mitigate against vulnerability CVE-2012-3132.

New Document Mitigation steps for CVE-2012-3132 [ID 1482694.1]

This was applied to a database and it stopped the 'password' command from succeeding in SQLPLUS. Once the trigger was disabled, it worked fine.

ORA-00604: error occurred at recursive SQL level 1
ORA-06531: Reference to uninitialized collection
ORA-06512: at "SYS.NAME_SECURITY", line 165
ORA-06512: at line 2

Why is this behavior seen? By the way, he was able to change the password using TOAD. Only sqlplus is the issue.

View 2 Replies View Related

Application Express :: Where Does APEX Save Libraries Added Through Apex_javascript.add_library

Sep 25, 2013

i recently installed an apex plugin but after deinstalling the plugin some of the functionality was still there.I suspected the plugin uploaded a js library and a css file.I found the functions apex_javascript.add_library and apex_css.add in the plugin.Where does apex save the files that are uploaded using those functions?I want do remove then. 

Oracle DB:    11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionAPEX:   4.1.1.00.23

View 4 Replies View Related

Application Express :: Showing New Rows Added To Tabular Form After Submit?

Jan 30, 2013

How i can show the new added rows to tabular form after submit? what I want is to show my customer new added rows that recently added. but according to how I sort the tabular form, the new added rows distributed in multi pagination in tabular form. I'm not good in English, excuse me for my mistakes. I use apex 4.2.0 On Oracle 11g r2 on windows Server 2008

View 2 Replies View Related

Performance Tuning :: Unable To Update Newly Added Column In Existing Table

May 21, 2013

I am facing some challenge while running update query on newly added column in existing table.

Environment Details
Oracle 9i, version 9.2.0.6
Os Unix Aix 6.1

No of records in table : 12572770

Below are the step i followed.

1. In table testtablename, I have added new column COLUMNNAME29 with datatype VARCHAR2(8).
2. After adding the new column, i executed the update query to populate the data form COLUMNNAME1 to COLUMNNAME29.
3. The query is executed using COLUMNNAME24 in where clause, to drive query in index based.

SQL> desc testtablename
Name Null? Type
----------------------------------------- -------- ----------------------------
COLUMNNAME1 VARCHAR2(8)
COLUMNNAME2 CHAR(1)
COLUMNNAME3 CHAR(1)
COLUMNNAME4 VARCHAR2(8)
COLUMNNAME5 VARCHAR2(11)

[Code]...

Table altered.

SQL> select index_name, column_position, column_name from dba_ind_columns where table_name = 'TESTTABLENAME' order by index_name,column_position;

INDEX_NAME COLUMN_POSITION COLUMN_NAME
------------------------------ --------------- --------------------------------------------------
IDX_TESTTABLENAME 1 COLUMNNAME24

Problem faced & My analysis

1. The update query is hanging in database, it's not progressing (In single update, approximately 40000 records will get update)
2. No oracle error thrown in alert log or in session where the query being executed.
3. The event for the query is "db file sequential read".
4. When i update the newly added column COLUMNNAME29 with static value "1", the update completed successfully in few seconds.
5. Then i changed the static value to "1111" and executed the update statement, which result to query hanging in database.
6. I tried to update the existing column(COLUMNNAME1) in table with static value "1111", the update completed successfully.

Below are the queries completed successfully

Update Testtablename
Set Columnname29 = '1'
Where Columnname24 >= To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' )
And Columnname24 < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')

[Code]...

Below are the queries hanging in database

Update Testtablename

Set Columnname29 = Columnname1
Where Columnname24 >= To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' )
And Columnname24 < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')

Update Testtablename

Set Columnname29 = '1111'
Where Columnname24 >= To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' )
And Columnname24 < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')

Below is character set in database

SQL> select * from v$nls_parameters;
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA

[Code]....

View 15 Replies View Related

How To Bypass Manual Query Execution

Apr 13, 2013

when i run a form no information shows up until i click execute query... i need the info to be their automatically to browse with the previous and next button

View 3 Replies View Related

Oracle SGA Components Manual Allocation

May 13, 2013

Phyical memory : 420G My database version : 11.2.0.3 running on linux machine.

Memory_target = 200G . I would like to allocate this value to following SGA components. I don't want to automatic memory management enabled. how to split 200G for following components. Is there any percentage for each components ?

db_cache_size
db_recycle_cache_size
db_keep_cache_size
large_pool_size
java_pool_size
shared_pool_size
pga_aggregate_target
streams_pool_size
log_buffer

View 10 Replies View Related

Server Administration :: Manual Database Creation

Jan 21, 2011

manual DB creation.I gone through the below steps:

1) created appropriate folders.

2) Copied a PFILE from an existing DB and changed the contents like DB name etc...

3)Then I Created a text file called %ORACLE_HOME%DATABASEinitmuktha.ora with the following contents: ifile=c:pragpfileinitmuktha.ora

4) Then i set Oracle SID (Through Environment variable in MY COMPUTER --> PROPERTIES...)

5) Then I Created a Windows service for the DB.

6) startup nomount pfile= c:pragpfileinitmuktha.ora

7) Then I ran the database creation command.

The DB was created but when i log in again, it is looking for the pfile in the another DB's location rather than from "%ORACLE_HOME%DATABASE". Where i missed out to create new oracle_home folder?

View 6 Replies View Related

Server Administration :: Manual DB Creation In UNIX

Feb 7, 2011

I am trying to create a DB manually.

1) How to create a UNIX services as we use oradim in windows.
2) While trying to use DBCA command it shows the below message.

SET DISPLAY ENVIRONMENT THEN RE-RUN.

My login is already in admin group only.

View 7 Replies View Related

Server Administration :: IFile In Manual DB Creation?

Feb 5, 2011

While creating a Manual DB creation, syntax as below:

%ORACLE_HOME%DATABASEinitprag.ora which will have a seperate file as below:
ifile=c:pragatpfileinitprag.ora

1) Why we need to divert the pfile into another location?

2) Can we keep the pfile inside the location - %ORACLE_HOME%DATABASE?

View 1 Replies View Related

Data Guard :: Executing A Manual Failover?

Oct 5, 2013

CentOS 6.4 64bits   Oracle......: 11.2.0.4 64bits   

I am running some small tests here in my test env, using dataguard. I have configured the Primary and Standby with Maximum Availability... they're running just fine. Now i want to execute a failover test (i have already ran a Switchover test with the Broker successfully).

My question is very simple, at my point of view: What are the required steps to execute a successful manual failover? For example, i have my env as follows:   

- Primary: prim1  
- Standby: stdb1   

Suppose that the primary database crashes in an unrecoverable way... is this case a manual failover would be necessary.

To do so, i would have to execute the following command, in my Standby database:      

-- stdb1 is the standby database...       DGMGRL> failover to stdb1      

The above command is correct? Are there any required configurations after the failover? I read the Oracle Docs, and it says

View 2 Replies View Related

Application Express :: Manual Update Of Two Reports

Sep 7, 2012

I have two SQL reports on the same page.

Those SQL reports are built with apex_items. Afterwards I'm updating them with a manual process (apex_application.g_fxx for ..loop). I'm using different IDs on both reports for the apex_items.

It's working fine for the first report displayed. But not for the second one. If I swap the two reports, it's always the first that will be updated.

Does it mean that we cannot use the apex_application to update more than one report and that I have to use a page per report?

I'm using APEX 4.0.0.00.46

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved