Estimate Defragmentaton Benefits for a Bigfile Tablespace
Database: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production Version 19.3.0.0.0 I write this post in order to follow step by step how a bigfile tablespace is created and managed in 19c. The main purpose is to find a way to estimate the space benefits of defragmenting the tablespace. I will use two methods: Method 1. By calculating the theoretical size of the tables. It is outlined below in steps 19-20. The limitation of this calculation is that right now it works for tables only Method 2. By using the Segment Advisor. Outlined below in steps 21-22 1. Check the datafile does not exist at OS level: 2. Create the tablespace as user SYSTEM: 3. Check DB file getting created: 4. Login as ‘radu’ user. Create test tables in the test_tbs tablespace: 5. Check size of the DB file. Should not be changed: 11M 6. Run test SQL: Output: 7. Populate first test table with 9 million rows. Example of block used to populate the table: 8. Check size of the DB file –> 291M 9. Run the control SQL from step 6. Used –> 284M 10. Populate second test table with 9 million rows. 11. Check size of the DB file –> 526M 12. Run the control SQL from step 6. Used –> 500M 13. Delete all the rows from the first test table and commit. 14. Check size of the DB file –> 526M. Unchanged 15. Run […]
It is now possible to clone a database in Autonomous Cloud
Database cloning, very handy feature, is now available for Oracle’s Autonomous Cloud. Quite easy to implement, too: The options are:
Starting Oracle SQL Developer with Custom Parameters
I recently worked at a Client that had the Windows desktop setup in a non-English language. Naturally, I did not get admin rights on the Client owned desktop so I could not change the sqldeveloper.conf SQL Developer config file which was stored in a ‘protected’ location. Using SQL Developer in a foreign, non-English language was a pain so, after some […]
DEVONthink Templates
In DEVONthink, it is quite easy to create and update own templates. They are all stored in:
Oracle Autonomous Transaction Processing: Client Setup
a. First step is the database creation. Quite straightforward: b. Next step is to a connection setup connection from local client: from the main datbase page we click the DB Connection button and download the client credentials: A zip archive will de downloaded into your local client machine. In my case, the file is called: Wallet_raduatp.zip c. SQL Developer Connection: […]
Remove Last Character of Each Line
I had to modify a file for loading into an Oracle database with SQL*Loader. The file was using as field delimiter the | pipe character and each line was ending in a |. So ideally, I would like to remove the pipe chracter that ends each of the 384 016 850 lines of my text file. One option is to use vim macros but I chose to use sed. The Linux command that would work is: but I noticed with surprise that this command would not work in mac OS! After a bit of investigation, I found out that the sed implementation on macOS is a bit different so the command on macOS is: note the extra characters that make the difference!
Creating a Table for Exchange With a Partitioned Table
https://docs.oracle.com/database/122/VLDBG/maintenance-partition-tables-indexes.htm#GUID-2DE24EE4-00EB-4D58-A428-8E6CBD7E7F34
Turn On ‘Do Not Disturb’ mode on mac OS X
Keep pressed option key (alt) and click on the Notifications icon which is usually found on top right corner. Easy!
Disable Mac OS X Gatekeeper
Ocassionally, there is a need to install an app created by a developer that is not identified by the security policy in place on the Firm’s MacBook. Here is the short procedure I identified it works for me: in terminal type: sudo spctl --master-disable verify with: spctl --status open the application and quit it in terminal, type: sudo spctl --master-enable verify the status: spctl […]
Oracle Live SQL Tool
https://livesql.oracle.com/apex/f?p=590:1000:100225800561367