import cx_Oracle

# Database connection parameters

dsn = cx_Oracle.makedsn('host', 'port', sid='sid')  # Replace 'host', 'port', and 'sid'

username = 'your_username'  # Enter your username

password = 'your_password'  # Enter your password

# SQL to retrieve list of partitions

sql_list_partitions = """

SELECT owner, table_name, partition_name

FROM all_tab_statistics

WHERE partition_name IS NOT NULL

"""

# SQL to update table statistics for a specific partition

sql_update_stats = """

BEGIN

    DBMS_STATS.GATHER_TABLE_STATS(

        ownname => :1, 

        tabname => :2, 

        partname => :3, 

        cascade => TRUE

    );

END;

"""

try:

    # Establish a database connection

    with cx_Oracle.connect(username, password, dsn) as connection:

        cursor = connection.cursor()

        # Retrieve list of partitions

        cursor.execute(sql_list_partitions)

        partitions = cursor.fetchall()

        # Update statistics for each partition

        for owner, table_name, partition_name in partitions:

            print(f"Updating statistics for partition: {owner}.{table_name} Partition: {partition_name}")

            cursor.execute(sql_update_stats, [owner, table_name, partition_name])

        print("Completed updating statistics for all partitions.")

except cx_Oracle.Error as error:

    print(f"Error: {error}")