![]() L_create_date := ADD_MONTHS(SYSDATE, -24) Create and populate a larger table that we will later partition. INSERT INTO lookup (id, description) VALUES (3, 'THREE') INSERT INTO lookup (id, description) VALUES (2, 'TWO') INSERT INTO lookup (id, description) VALUES (1, 'ONE') Create and populate a small lookup table. Partitioning an Existing Table using EXCHANGE PARTITIONįirst we create a sample schema as our starting point. ![]() Online Conversion of a Non-Partitioned Table to a Partitioned Table in Oracle Database 12c Release 2 (12.2).Create Constraints and Indexes (Dependencies).Remember, in many cases incorrect partitioning is worse than no partitioning! The contents of the article should not be used as an indication of when and how to partition objects, it simply shows the method of getting from A to B. This article presents a simple method for partitioning an existing table using the DBMS_REDEFINITION package, introduced in Oracle 9i. The following notes are provided for your reference.Īdjust the undo_retention parameter value as per the UNDO tablespace size and the size of the flash recovery area.įor 10g and earlier, the commit_write initialization parameter must be set to "BATCH,NOWAIT." Keeping the default value "IMMEDIATE,WAIT" will cause contention at the database level, which affects the performance of the system.įor 11g, set the commit_write initialization parameter to "NOWAIT.Home » Articles » Misc » Here Partitioning an Existing Table using DBMS_REDEFINITION Restart the database after setting all the values. Please refer to the appropriate documentation for release-specific limitations. See the Oracle Database Administrator's Guide for information about setting initialization parameter values in 10g. The following initialization parameter must be set for a RAC-specific environment: max_commit_propagation_delay=0 (Only for RAC-specific environment) ALTER SYSTEM SET db_writer_processes=4 SCOPE=SPFILE ĪLTER SYSTEM SET fast_start_mttr_target=300 SCOPE=SPFILE ĪLTER SYSTEM SET open_cursors=2000 SCOPE=SPFILE ĪLTER SYSTEM SET pga_aggregate_target=512M SCOPE=SPFILE ĪLTER SYSTEM SET processes=500 SCOPE=SPFILE ĪLTER SYSTEM SET query_rewrite_enabled='FALSE' SCOPE=SPFILE ĪLTER SYSTEM SET sessions=500 SCOPE=SPFILE ĪLTER SYSTEM SET shared_pool_size=500M SCOPE=SPFILE ĪLTER SYSTEM SET timed_statistics=TRUE SCOPE=SPFILE ĪLTER SYSTEM SET undo_management='AUTO' SCOPE=SPFILE ĪLTER SYSTEM SET undo_retention=900 SCOPE=SPFILE ĪLTER SYSTEM SET session_cached_cursors=500 SCOPE=SPFILE ĪLTER SYSTEM SET commit_write='BATCH,NOWAIT' SCOPE=SPFILE Please refer to the Notes section for additional details about setting the initialization parameters. If you are not using a unicode character set, refer to Metalink article #225938.1, "Database Character Set Healthcheck," for upgrade instructions.Įnsure that the appropriate initialization parameters are set before you create your database schema within the Oracle database. The above query should return at least 1 row if the database character is set to UTF8. To find out the current database character set, run the following SQL script with DBA credentials: select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET' and value like '%UTF8%' If you plan to use Oracle Adaptive Access Manager for non-English setup and you want to use an existing database for your Oracle Adaptive Access Manager repository, determine its database character set before proceeding with the setup. If you plan to use Oracle Adaptive Access Manager for non-English setup (i.e., localized), specify a unicode character set for your database during the Oracle database installation. This section contains information if you are planning to use Oracle Adaptive Access Manager for non-English setup.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |