The database has a program which performs a huge sort operation (e.g. end of the year reporting process). This may cause the TEMP tablespace to grow and occupy most of the space on the file system. In this example, the report process may run once or twice a year and there is no need to maintain/keep a huge tempfile. The TEMP tablespace was created with datafiles (dictionary managed tablespace temporary) as AUTOEXTEND ON MAXSIZE UNLIMITED to avoid the Error: ORA-1652 Text: unable to extend temp … [Read more...] about How to shrink a Temporary Tablespace datafile in Oracle
How to Compress and Decompress .bz2 files in Linux Using bzip2 Command
Introduction In this article we are going to learn How to compress files using bz2 file compression tool (bzip2 linux command) and unzip bz2 compressed file. bzip2 is a open source compress tool similar to zip & gzip compression tool used to compress large sized files to reduce it’s size. bzip2 can compress files not directories. bzip2 gives more compression as compared to gzip and zip. bzip2 Linux command was developed by Julian Seward on year 1996 and released under BSD style license. Why … [Read more...] about How to Compress and Decompress .bz2 files in Linux Using bzip2 Command
How To Shrink A Temporary Tablespace in Oracle Database
Question: How to resize the TEMPFILE(s) for a temporary tablespace after they have grown larger than needed? Large sort operations can cause temporary tablespaces to grow very large and as such there may be a need to 'downsize' after such operations. Until Oracle 11g there was no SQL command to release the unused allocated temporary space. One workaround for this problem is to create a new empty temporary tablespace with a smaller size, assign this new tablespace to the users and then drop … [Read more...] about How To Shrink A Temporary Tablespace in Oracle Database
Counting Rows Of A Table In MySQL Server
Let's understand what are the available options to get an accurate number or an estimation of rows in a MySQL table. The option to get an accurate count of rows in a table is to perform: SELECT COUNT(*) FROM [table]; In MyISAM this operation, when no WHERE clause is provided, is very quick, as the storage engine knows at any time how many rows are in the table. When dealing with InnoDB storage engine, instead, there is no counter available. This is due to the transactional nature of this … [Read more...] about Counting Rows Of A Table In MySQL Server
How to Rebuild GRUB on MSDOS Partition Type in CentOS/RHEL/OEL 7
Question: How to Rebuild Grub on Linux servers with MSDOS Partition type. (proc) (hd0) (hd0,msdos5) (hd0,msdos3) (hd0,msdos2) (hd0,msdos1) 1. Execute the following commands in Grub prompt to set the root device and to load the boot image and initialize boot loader. > set root=(hd0,msdos1) > linux16 /vmlinuz-3.10.0-327.el7.x86_64 root=/dev/sda5 > initrd16 /initramfs-3.10.0-327.el7.x86_64.img Then boot the server. 2. Once the server boots up, rebuild the Grub config file. You can edit … [Read more...] about How to Rebuild GRUB on MSDOS Partition Type in CentOS/RHEL/OEL 7
How to Disable AUTOEXTEND Mode on a datafile in Oracle Database
Question: How do you take a datafile out of autoextend mode? Oracle datafiles were given the ability to extend automatically if needed from a long time. If the given datafile is unable to allocate the space needed, it can increase the size of the datafile to make space for objects to grow. To determine if the datafile has autoextend capabilities, query the dba_data_files table. For example: SQL> select file_name, autoextensible from dba_data_files; FILE_NAME … [Read more...] about How to Disable AUTOEXTEND Mode on a datafile in Oracle Database
Oracle – How To Check if Autoextensible Datafiles Set To Maxsize Unlimited
Question: How to check whether the datafiles are set to AUTOEXTEND and UNLIMITED? We can query the DBA_DATA_FILES view to check whether the data file is in AUTOEXTEND mode. SQL> select tablespace_name, file_name, autoextensible from dba_data_files; To confirm whether the maxsize is set to UNLIMITED we have to check for the maximum possible size, as there is no concept of an 'UNLIMITED' file size. Oracle limits the number of blocks in a datafile to approximately 4 million blocks (unless … [Read more...] about Oracle – How To Check if Autoextensible Datafiles Set To Maxsize Unlimited
Failed to Download Metadata for Repo ‘repo_name’
The Problem All the dnf commands fail with the following error: # dnf repolist Oracle Linux 8 BaseOS Latest (x86_64) 0.0 B/s | 0 B 00:00 Failed to download metadata for repo 'ol8_baseos_latest' Error: Failed to download metadata for repo 'ol8_baseos_latest dnf was not able to refresh the cache as it could not reach the yum server itself, Hence pointing to cache being expired. # yum clean metadata Cache was expired … [Read more...] about Failed to Download Metadata for Repo ‘repo_name’
How to configure Linux Resource Groups (cgroups) for MySQL
The following provides the procedure for configuring and using resource groups under Linux: 1. To use cgroups, you must install the "libcgroup" package on your system. # yum install libcgroup 2. Create a config file for cgroups (/etc/cgconfig.conf) and add below configuration. # vi /etc/cgconfig.conf mount { cpu = /cgroup/cpumem; cpuset = /cgroup/cpumem; memory = /cgroup/cpumem; } # High priority group group mysqldb { cpu { # Allocate the relative share of CPU resources … [Read more...] about How to configure Linux Resource Groups (cgroups) for MySQL
Oracle SQL Script to Detect Tablespace Fragmentation
The following is a script that will determine how many extents of contiguous free space you have in Oracle as well as the total amount of free space you have in each tablespace. From these results, you can detect how fragmented your tablespace is. The ideal situation is to have one large free extent in your tablespace. The more extents of free space there are in the tablespace, the more likely you will run into fragmentation problems. The size of the free extents is also very important. … [Read more...] about Oracle SQL Script to Detect Tablespace Fragmentation