Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle APEX the server requires a username and password the server says XDB?

LauryMay 15 2015 — edited May 17 2015

Hi,

After having installed the version of APEX 5.0 and trying to access the APEX admin page using EPG, I get prompted with a pop-window that asks for a username and pasword for the XDB?

Does somone has experience with it? What can be wrong in the configuration?

Thanks by advance.

Kind Regards.

This post has been answered by Laury on May 17 2015
Jump to Answer

Comments

APC
Answer
It's the use of the AUTONOMOUS TRANSACTION pragma. Your current transaction cannot see the result of that DDL because it occurs outside of the current transaction. The clue is in the name.

Of course, you cannot execute the DDL in a trigger without using that pragma, so you're pretty well stymied. There is a solution in 11g but that's not going to help you. Unfortunately, your only option is to pre-create the required partitions ahead of the need. For instance, you could have a DBMS JOB to create a partition for the next month which runs on the last day of each month (or whatever date makes business sense).

Cheers, APC

blog: http://radiofreetooting.blogspot.com
Marked as Answer by 676821 · Sep 27 2020
Laurent Schneider
I second Andrew arguments. In 11g, you have automatic partition creation with interval partitioning.

In 8i, DBMS_JOB rules !

Something like (picture)

table T (partition p2007, partition p2008, partition MAXVALUES)

trigger TR if values > (select max(highvalue) from user_tab_parts) then DBMS_JOB.CREATE_JOB('at midnight, split partition MAXVALUES in MAXVALUES/P2009 ');
and insert row in partition MAXVALUES


HTH
Laurent

Edited by: Laurent Schneider on Jan 14, 2009 3:24 PM
Laurent Schneider
or, much easier and cleaner,

every sunday, check for values in MAXVALUE partition and move those rows in new partitions...
OrionNet
Hello,

You don't need trigger to identify missing partitions and add them, see following procedure

using v_missing_months you can create n number of partitions ahead of time. You can also use this to create partition one month in advance and it will work for any "RANGE" partitioned table. Then you can scheduled this procedure to run using dbms_jobs or from cron job (using unix)
DECLARE
      TYPE partrec IS RECORD (
         table_name        varchar2 (30),
         partition_name    varchar2 (30),
         upperbound        varchar2 (83),
         tablespace_name   varchar2 (70)
      );

      CURSOR p_cur
      IS
         SELECT   table_name, partition_name, high_value, tablespace_name
             FROM user_tab_partitions
            WHERE high_value_length > 2
                ORDER BY partition_position DESC;

      CURSOR part_cur (i_table_name IN varchar2)
      IS
         SELECT   table_name, partition_name, high_value, tablespace_name
             FROM user_tab_partitions
            WHERE table_name = i_table_name
         ORDER BY partition_position DESC;

      prec                         partrec;
      v_last_partition             varchar2 (50);
      v_next_partition             varchar2 (50);
      v_last_part_date             varchar2 (50);
      v_next_part_date             date;
      v_part_tmp_date              date;
      v_part_date                  date;
      v_initial_date               date;
      i_initial_date               date;
      v_sql                        varchar2 (300);
      v_initial_part               varchar2 (30);
      i_initial_part               varchar2 (30);

      c_dummy_partition   CONSTANT varchar2 (5)   := 'DUMMY';
      parent_tname                 varchar2 (30);
      v_missing_months             number;
      i                            number;

   BEGIN

      EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT="YYYYMMDD"';
      --
      -- Adding partitions to existing range partitioned tables
      --
      FOR cur IN p_cur
      LOOP
         OPEN part_cur (cur.table_name);
         FETCH part_cur
          INTO prec;
        CLOSE part_cur;
         v_last_partition := prec.partition_name;
        -- DBMS_OUTPUT.put_line ('Last partition Name=' || v_last_partition);
         v_last_part_date := SUBSTR (prec.upperbound, 10, 20);
        -- DBMS_OUTPUT.put_line ('Last partition date' || v_last_part_date);
         v_part_date := TO_DATE (v_last_part_date, 'YYYY-MM-DD HH24:MI:SS');
        -- DBMS_OUTPUT.put_line ('Last Partition Date = ' || v_part_date);
         v_part_tmp_date := v_part_date;
         v_missing_months := MONTHS_BETWEEN (SYSDATE, v_part_date);

        -- NOTE : MODIFY following line to create partition in one month or n month in advance.
         v_missing_months := v_missing_months + 12; 

        -- DBMS_OUTPUT.put_line
                  --        (   'No of monthly partitions  will be created is:'
                     --      || CEIL (v_missing_months)
                     --     );

         FOR i IN 1 .. v_missing_months
         LOOP
            IF (v_part_date = LAST_DAY (v_part_date + i))
            THEN
               v_part_date := v_part_date + i;
               v_next_part_date := ADD_MONTHS (v_part_date, i);
            ELSE
               v_next_part_date := ADD_MONTHS (v_part_date, i);
            END IF;
            v_part_tmp_date := v_part_tmp_date + i;
            DBMS_OUTPUT.put_line (   'Next partition Date = '
                                || TO_CHAR (v_next_part_date, 'YYYYMM')
                             );

            v_next_partition :=
               REPLACE (v_last_partition,
                        SUBSTR (v_last_partition, -6),
                        TO_CHAR (v_next_part_date - i, 'YYYYMM')
                       );

            DBMS_OUTPUT.put_line ('New Partition Name = ' || v_next_partition);
            v_sql :=
                  'ALTER TABLE '
               || prec.table_name
               || ' ADD PARTITION '
               || v_next_partition
               || ' VALUES LESS THAN ( TO_DATE('''
               || v_next_part_date
               || ' 00:00:00'', ''YYYYMMDD HH24:MI:SS''))'
               || ' tablespace '
               || prec.tablespace_name;

  DBMS_OUTPUT.put_line (v_sql);
           EXECUTE IMMEDIATE v_sql;
            COMMIT;
        END LOOP;
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (SUBSTR (SQLERRM, 1, 300));
         RAISE;
   END;
Regards
676821
Thanks a lot for all the replies.

Altough they were all helpful, for some reason, I was asked not to use JOBS (if possible).

After APC confirmed my first solution would not work I gave it some thought and came up with another approach which might not be the most appropriate solution in this case, but the result was achieved without JOBS.

Solution I´m using:

The lower partition, which is created with the table, will hold the current month´s records and the previous ones;

A BEFORE INSERT TRIGGER will call an AUTONOMOUS TRANSACTION PROCEDURE that check if next month´s partition (relative to the record that´s being inserted) is already created. If it´s not, the procedure then creates it;

The insertion now will work, as the partition created outside the transaction is for next month. This way the partition for the current month is always going to be available.

PS.: It might be important to point out that this solution only works in this situation because the business rules ensure that there will never be a record with a date greater than the current date. If this rule didn´t exist the solution would not be suitable.

Thanks again.

Bruno
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 14 2015
Added on May 15 2015
6 comments
4,608 views