Most of the time DBAs has to perform their tasks quickly and with 100% accuracy. If we wanted to kill multiple oracle processes at time we can use below mentioned command. This command will filter the idle oracle sessions(local=no) and terminate.
There are two ways to kill an oracle process (UNIX) or thread (windows). First, kill the session from database with sys as sysdba privilege using the command alter system kill session ‘sid, serial#’. Secondly, kill the process or thread from OS level. The first one has some limitations as it needs oracle database access.
This error is related to rollback (Oracle 8i and below) / UNDO (9i and above) segments. Oracle uses UNDO segments for maintaining the read consistency of the database by recording the block level changes to the UNDO. UNDO is used for reconstruct the read-consistent snapshot of the data.
Oracle 8i and below, the rollback segments were used for maintaining the read consistency and for the transaction rollback. Oracle 9i onwards the UNDO replaced the rollback segments with some automatic features. This feature includes like creation, monitoring, performance, sizing of the rollback segments. It is easier for the DBAs to minimize the issues with UNDO when using the automatic undo management.
UNDO is an oracle method to rollback or undo the changes to the database. UNDO keeps track of the oracle transactions, mainly before the commit of the transaction. Block level changes are recorded ‘in time’ to provide the read consistency.
This is a database resource error. The error is because of the number of oracle processes reached its maximum limit. The maximum limit is mentioned in your database parameter file (spfile/pfile). Corresponding init parameter is PROCESSES.
Oracle database statistics are very import in Cost Based optimizer (CBO). Sometimes the execution plan differs from production database to test databases even though the table, index structures are same. The execution plan might be good in production database compare to the test database. DBAs might get the complaints from the testers. In this situation DBAs can export the schema or table statistics to the test database.
Import (imp) is the oracle utility to read export dump files and insert them into database. Export dump files can only be read by imp utility. Using SHOW=Y option, the content of the dump file can be generated into readable ASCII format. If we use this option with Y as the value the content of the dump will not be inserted into the database whereas it will spool the sql statements to the log file. SHOW option can be used only with FULL=Y, FROMUSER, TOUSER and TABLES mode.
If the instance crash happens during the oracle hot backup due to the power failure or shutdown abort the database will not open in normal way. It will ask for the media recovery. It throws error like ORA-01113: file xx needs media recovery, ORA-01110 during the database startup.
One of the biggest challenges for DBAs is the lack of disk space especially for the backups. Most often DBAs have to move the backup files from one location to other location to get room for new backups. Here we explained about a common solution for taking the export dump with compressed file size. Compression happens parallel with the export.
Export is the oracle utility to take the export the logical data as backup. There are many options with this tool and among them Compress=[y]/n is very useful. This is not for compressing export dump file. If you take export of a table with compress=Y option, during the import the table the total number of current extents will be consolidated into one single extent. It means there would be only one extent after the import. The default value for the compress option is Y.
Data pump introduced in oracle 10g and it is entirely different from the normal export/import. Similar to export/import using data pump we can migrate the data from one database to another database running on different operating system. DBMS_DATAPUMP package can be used to implement API and you can access the data pump utility programmatically.
There is no direct way to schedule the script in crontab for nth day of every month. Which means suppose if you wanted to execute the shell script for every 2nd Saturday or every 3rd Monday, etc. (i.e: The execution occurrence will be one in a month; nth Sunday, Monday, Teusday, Wednesday, Thursday, Friday or Saturday) Crontab will not support this. You have to write your own code to execute this. A sample code is given below.
Partitioning is a technique which allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity. Oracle provides different varieties of partitioning strategies applicable to different kinds of business requirement. Partitioning is entirely transparent and can be applied to almost any kind of applications
Introduction: The data volume keep on increasing in the databases day by day which result the need of more storage for the databases. Oracle 11g provides advance compression feature to reduce the storage on an average of 3X actual data volume. It is kind of go green feature which can be applied on structured data and unstructured data like documents, images and multimedia. Using this feature database can store more data, faster queries executions, etc.