RE: Turn off Partitioning

by old one, I don’t mean the old oracle home (which presumably everyone is making recoverable already in accordance with your service levels), I specifically mean the existing orainventory and location (for as many as you have, especially if you have one of the obsolete webclients still kicking around that you haven’t decided is worth enough money to replace but still does something for you. They can be completely disjoint from everything else depending on vintage, and last I knew, the way to get rid of them was re-implement all your client facing technology stack.)  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham Sent: Wednesday, August 18, 2021 11:08 AM To: jlewisoracle_at_gmail.com; 'ORACLE-L' Subject: RE: Turn off Partitioning  

One tiny last bit: In many releases over the years something *could* go bump in the night and fubar orainventory and/or put a new partial one in a new place upstream of the old one in location searching, so before all y’all add a new home, make sure you back up the old one and know what the latest twists of path searching order do as the net from Oracle, Operating System, and shell layers and whether the environment needs to be dotted or just the script invoked. Sigh. They probably have that all fixed by now, but my paranoia is well earned.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Wednesday, August 18, 2021 6:54 AM To: ORACLE-L
Subject: Re: Turn off Partitioning    

That's a good point - if you've used partitioning there will be entries (deleted) in tabpart$, indpart$ et. al. So maybe after a re-install you'd need to create an empty database and move the data from the old to the new (transportable tablespaces seems the best option for that).  

Another thought that's just occurred to me that if you're licensed for the diagnostic and performance tools, and even if you do nothing but keep a history of optimizer statistics there are some sys tables that will be partitioned (by setting an event when the tables are created, I think) so if you remove partitioning from the excutable what happens to them. (Perhaps the "part_off" library has a run-time check that may allow only SYS to create and use partitioned tables).    

Regards

Jonathan Lewis    

On Wed, 18 Aug 2021 at 11:40, Howard Latham <howard.latham_at_gmail.com> wrote:

If there is a record of you using a chargeable feature you are risking a big back payment they are not kind to big or small if you can rebuild the dB.

Sent from another Galaxy.  

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Mikhail Velikikh <mvelikikh_at_gmail.com> Sent: Wednesday, August 18, 2021 11:22:25 AM To: Jonathan Lewis <jlewisoracle_at_gmail.com> Cc: ORACLE-L <oracle-l_at_freelists.org> Subject: Re: Turn off Partitioning  

chopt is a way to go as it is documented here: https://docs.oracle.com/en/database/oracle/oracle-database/19/ladbi/chopt-tool.html#GUID-057E4EFC-74ED-43B3-B03B-C83C5A5D3C7F

The exact commands it uses are in $ORACLE_HOME/bin/chopt.ini.

Here is an example for partitioning (it is basically as Jonathan said with an additional command to make oracle):  

[partitioning]
option=Oracle Partitioning
enable=run:/usr/bin/make -f /u01/app/oracle/product/21.3.0/dbhome_1/rdbms/lib/ins_rdbms.mk part_on ORACLE_HOME=/u01/app/oracle/product/21.3.0/dbhome_1;run:/usr/bin/make -f /u01/app/oracle/product/21.3.0/dbhome_1/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/u01/app/oracle/product/21.3.0/dbhome_1 disable=run:/usr/bin/make -f /u01/app/oracle/product/21.3.0/dbhome_1/rdbms/lib/ins_rdbms.mk part_off ORACLE_HOME=/u01/app/oracle/product/21.3.0/dbhome_1;run:/usr/bin/make -f /u01/app/oracle/product/21.3.0/dbhome_1/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/u01/app/oracle/product/21.3.0/dbhome_1      

On Wed, 18 Aug 2021 at 09:34, Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:    

It's a very long time since I did one, but the most secure way to deal with the problem is to create an Oracle executable that does not include the bits you don't want to pay for; but many years ago - for UNIX systems - I would use "make" to create new libraries and executables.  

If you go to $ORACLE_HOME/rdbms/lib you will find a file called ins_rdbms.mk, this is the control file for make-ing Oracle. If you know where the log files are for the Oracle installation you will find there a file called something like "make.log". At the top of the file you'll find a line like:  

/usr/bin/make -f ins_rdbms.mk clean rat_on part_on dm_on olap_on sdo_on rac_off dnfs_off ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1/  

In this line "part_on" is one of the targets for make, and if you search ins_rdbms.mk you'll find the rule that applies for part_on; just below that there's a rule for part_off.  

So you could execute make, changing the command line you find to use part_off instead of part_on (and changing the ORACLE_HOME/as appropriate. However, there are 3 things I would do first:  

1. Wait for a sanity check for someone who's done this some time in the last 10 years; I haven't done it since 8i
2. Talk to whoever installs Oracle at your site to make sure that my (potentially outdated) information about make is still okay
3. Do a complete Oracle software install to a new Oracle Home - because the install might allow you to pick No-partitioning in the setup pages, and it's far better to do this type of thing with the official mechanisms if they exist.
4. If you have to mess about with make, do it on the second Oracle Home.

Regards

Jonathan Lewis        

--
http://www.freelists.org/webpage/oracle-l