Convert Partitioned Table to Non-partitioned Table and vice versa

You might come across the situation that you need to convert Partitioned Table to Non-partitioned Table and vice versa.

You can RUN below command to identify weather the Table is partitioned or not

SQL> SELECT TABLE_NAME, PARTITIONED FROM USER_TABLES WHERE TABLE_NAME=‘EMP’;

TABLE_NAME PAR
--------------------- ---
EMP YES

I suggest using data pump to convert Partitioned Table to Non-partitioned Table. Below are the steps
Take a backup of the table that you want to convert to NON-Partitioned table

$ expdp SCOTT/xxxxx directory=EXP_DIR dumpfile=emp_expdp.dmp log=emp_expdp.log tables=EMP

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."EMP":"YEAR_2001" 15.398 MB 40023 rows
. . exported "SCOTT"."EMP":"YEAR_2002" 23.456 MB 89898 rows
. . exported "SCOTT"."EMP":"YEAR_2003" 89.675 MB 100453 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

Import the table into TEST schema using PARTITIONS_OPTIONS parameter

$ impdp TEST/xxxxx directory=EXP_DIR dumpfile=emp_expdp.dmp log=imp_emp.log remap_schema=SCOTT:TEST partition_options=merge

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “TEST"."EMP":"YEAR_2001" 15.398 MB 40023 rows
. . imported "TEST"."EMP":"YEAR_2002" 23.456 MB 89898 rows
. . imported "TEST"."EMP":"YEAR_2003" 89.675 MB 100453 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at Sun July 5 08:44:21 2020 elapsed 0 00:00:10

This command will merge all partitions while importing as single table.

RUN below command to verify and you should see the out as
SQL> SELECT TABLE_NAME, PARTITIONED FROM USER_TABLES WHERE TABLE_NAME=‘EMP’;

TABLE_NAME PAR
---------------------- ---
EMP NO

DROP PARTATION

If you need to drop the partition use below command

Method 1: Drop the specific partition
SQL> ALTER TABLE EMP DROP PARTITION YEAR_2001;

Method 2: DELETE all rows part of partition and remove partition
SQL> DELETE FROM EMP PARTITION (YEAR_2001;);
SQL> ALTER TABLE EMP DROP PARTITION YEAR_2001;

Method 3:- Drop partition and update indexes
SQL> ALTER TABLE EMP DROP PARTITION YEAR_2001 UPDATE INDEXES;

ADD/MODFY partition

You can use ALTER TABLE ADD PARTITION, MODIFY PARTITION statement on a table

For ex:-
SQL>ALTER TABLE EMP MODIFY PARTITION BY RANGE (JOIN_DATE)
(PARTITION YEAR_2001 VALUES LESS THAN (to_date('01-JAN-2002','dd-mon-yyyy')),
PARTITION YEAR_2002 VALUES LESS THAN (to_date('01-JAN-2003','dd-mon-yyyy')));

SQL> ALTER TABLE EMP ADD PARTITION YEAR_2003 VALUES LESS THAN ( '01-JAN-2004' ) ;

Please refer oracle documentation for detailed PARTITIONS options and methods..etc

Thanks

http://oracleracexpert.com, Oracle ACE

Webinar: Cloning an Oracle Home or Oracle Install

This webinar helps to Clone Oracle Home or Oracle Install while migrating the databases.

Date and time: Dec 15th 2022 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

This Webinar covers following Topics.

• What is Cloning
• When Cloning useful
• Different methods of Cloning
• How to perform Cloning
• References
• Q&A 

To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com.

Note that registrations are limited and first come and first serve basis.

You will receive an email confirmation with meeting session link.

For Presentation link "Click here"

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

SSL Connection Error: SSL is required but the server doesn’t support it.

When users using connecting MYSQL using MySQL Workbench they received following error

By default MySQL Workbench, use SSL and users may receive above error. It is recommended to use certificates to avoid above error. In case, if you do not have the certificates then you can follow below steps to skip the certificate and connect to the MySQL Database

 



Option 1:

• Open MySQL workbench
• Right click on MySQL instance and select "Edit Connection"
• Go to the "SSL" tab under Connection Method
• Choose "If Available" instead of "Required" in the drop-down for the "Use SSL"
• Click the "Test Connection" and you should connect without errors.

Option 2:

• 
  
• Right click on MySQL instance and select "Edit Connection"
• Go to “Advanced” tab and enter "useSSL=0" in the 'Others' tab
• Click the "Test Connection" and you should connect without errors

Option 3:

 MySQL Workbench version 8.0.26 and below doesn’t use SSL Options by default

Thanks & Regards

http://oracleracexpert.com, Oracle ACE

Failed to connect to MySQL at with user root

When users connecting to MySQL using Workbench or from command prompt they may receive the error when they don’t have access to connect from the machine.

It is mainly due to security setup that allows to connect to “root” from specific IP address or localhost



In this scenario, ask your admins to create a new user account and setup the required access instead of using “root”

If the root account is not configure to authenticate with a password, you may need below command

MySQL> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';


If user need to login as root, you they can follow the steps to grant access to local host and or IP Address

MySQL> GRANT ALL ON [DB_Name].* to 'root'@'127.0.0.1' IDENTIFIED BY '[PASSWORD]';
MySQL> GRANT ALL ON [DB_Name].* to 'root'@ ‘IP Address’ IDENTIFIED BY '[PASSWORD]';

Instead of using “GRANT ALL ON”, use required privileges only.

Thanks & Regards

http://oracleracexpert.com, Oracle ACE

ORA-14552: Cannot Perform a DDL Commit or Rollback inside a query or DML

You will receive this error when you use COMMIT or ROLLBACK and make sure you don’t use DML in a function to avoid this error.

In case if you need to use COMMIT or ROLLBACK then change your function as autonomous transaction by using “PRAGMA AUTONOMOUS_TRANSACTION" in your function

For Ex:-
CREATE OR REPLACE FUNCTION Function_Autonomous
return number
as
v_number number;
pragma autonomous_transaction;
BEGIN
…………
END;
/

14552, 00000, "cannot perform a DDL, commit or rollback inside a query or DML "
*Cause: DDL operations like creation tables, views etc. and transaction
control statements such as commit/rollback cannot be performed
inside a query or a DML statement.

*Action: Ensure that the offending operation is not performed or
use autonomous transactions to perform the operation within
the query/DML operation.

Thanks & Regards

http://oracleracexpert.com, Oracle ACE