Oracle

How to move a datafile from one location to another in Oracle: At times , to fix the space crunch in a filesystem or to follow the filesystem standards or proper naming conventions , a DBA might have to move/rename an oracle datafile. The datafile movement can be performed without …

SQL Queries for Dummies! Yes – Some of the basic Oracle queries/commands which might turn out extremely helpful to the beginners of Oracle database. 1. To check the name of the database: SQL> select name from v$database; 2. To check the version of a database: SQL> select * from v$version; 3. …

Read Only tables in Oracle 11g: Read-only table is a  good enhancement introduced in Oracle 11g , using which we can safeguard the tables from DML operations. This new feature helps to switch a table to READ ONLY mode by executing a single ALTER TABLE command. SQL > Alter table table_name …

Export/Import of schema statistics in Oracle: As database administrators, most of us might have encountered a scenario where the performance of sql queries shows a sudden degradation in our database. But there are no hard and fast rules about how to deal with performance issues. Mostly, it is through trial …

Dropping table permanently: SQL> drop table test_tab purge; Table dropped. SQL> select OBJECT_NAME, ORIGINAL_NAME, DROPTIME from recyclebin; no rows selected. Purging of table from recycelbin: SQL> drop table test_tab; Table dropped. SQL> select OBJECT_NAME, ORIGINAL_NAME, DROPTIME from recyclebin; OBJECT_NAME           ORIGINAL_NAME           DROPTIME —————————— —————-     —— BIN$dD1ZsEvnDTHgRAADujKrxQ==$0 TEST_TAB 2010-09-23:12:40:58 SQL> purge table …

What happens when we drop a table in Oracle 10g? Whenever we drop a table in Oracle 10g, internally Oracle will rename the table and all the associated objects like index, constraints etc with a prefix of “BIN$” and stores in the recyclebin. We  can either read the data directly …

Can we restore the data of a dropped table in Oracle , even if we don’t have a valid backup? If your Oracle database version is 10g or above , then the answer is definitely “YES”. This became possible with the introduction of a new feature in Oracle 10g – …

ADDM or Automatic Database Diagnostic Monitor is a tool which helps in automatic database performance monitoring. Run the below script to run ADDM. @?/rdbms/admin/addmrpt.sql The following is a sample  output you get if you Run the ADDM  when there is no significant database activity. DETAILED ADDM REPORT FOR TASK ‘TASK_416’ …