Tag Archives: disable

Example shows how “DISABLE TABLE LOCK” works

Posted on by
Reply

Following example shows what happens when locks are disabled on the TABLE.

-- disable lock on a table
SQL> alter table t disable table lock;
Table altered.

-- shows one can’t drop table as table locks are disable
SQL> drop table t;
drop table t
*
ERROR at line 1:
ORA-00069: cannot acquire lock — table locks disabled for T

-- shows one can’t truncate table as table locks are disable
SQL> truncate table t;
truncate table t
*
ERROR at line 1:
ORA-00069: cannot acquire lock — table locks disabled for T

-- DML is okay to run
SQL> delete from t;
1 row deleted.

-- new columns can be added
SQL> alter table t add tt varchar2(1);
Table altered.

-- columns can’t be modified
SQL> alter table t modify tt varchar2(10);
alter table t modify tt varchar2(10)
*
ERROR at line 1:
ORA-00069: cannot acquire lock — table locks disabled for T

-- columns can’t be dropped
SQL> alter table t drop column tt;
alter table t drop column tt
*
ERROR at line 1:
ORA-00069: cannot acquire lock — table locks disabled for T

-- enable locking on the table
SQL> alter table t enable table lock;
Table altered.

-- table now can be dropped
SQL> drop table t;
Table dropped.

Advertisements

How to find and enable/disable parallel DML, DDL, Query?

Posted on by
Reply

In the view v$session there are columns that can tell if parallel DDL, DML, Query is enabled/disabled.
PDML_ENABLED and PDML_STATUS – indicates is Parallel DML operation is enabled/disabled, the default is DISABLED.
PDDL_STATUS – Indicates if parallel DDL is enabled/disabled, by the default value is ENABLED
PQ_STATUS – Indicates if parallel query is enabled/disabled, the default value is ENABLED

The values can set at session level.

Example:
SQL> select PDML_ENABLED, PDML_STATUS, PDDL_STATUS, PQ_STATUS FROM V$session where sid = (select sid from v$mystat
where rownum = 1);

PDM PDML_STA PDDL_STA PQ_STATU
— ——– ——– ——–
NO DISABLED ENABLED ENABLED

-- enables parallel DML
SQL> alter session enable parallel dml;

Session altered.

-- Display parallel DML is set to enabled
SQL> select PDML_ENABLED, PDML_STATUS FROM V$session where sid = (select sid from v$mystat where rownum = 1);

PDM PDML_STA
— ——–
YES ENABLED

-- To disable parallel DML
SQL> alter session disable parallel dml;

Session altered.

-- To disable parallel DDL
SQL> alter session disable parallel ddl;

Session altered.

-- To disable parallel query
SQL> alter session disable parallel query;

Session altered.

-- Print the parallel DDL, DML and Query for the current session
SQL> select PDML_ENABLED, PDML_STATUS, PDDL_STATUS, PQ_STATUS FROM V$session where sid = (select sid from v$mystat where rownum = 1);

PDM PDML_STA PDDL_STA PQ_STATU
— ——– ——– ——–
NO DISABLED DISABLED DISABLED

How to use DBMS_FLASHBACK?

Posted on by
1

In 10g using dbms_flashback one can flashback using timestamp or SCN. If a user accidentally deletes rows or updates rows one can use this to review the data without need to have flashback turned on in the database. Note: SYS can’t use dbms_flashback to flashback, Oracle will return “ORA-08185: Flashback not supported for user SYS” when SYS will try to enable flashback.

Example:
— shows flashback is off
SQL> select flashback_on from V$database;

FLASHBACK_ON
——————
NO

— declare a variable to store current SCN
SQL> variable current_scn number
— Get the current SCN and initialize current_scn
SQL> execute :current_scn := sys.dbms_flashback.get_system_change_number();

PL/SQL procedure successfully completed.

— print the current scn
SQL> print current_scn;

CURRENT_SCN
———–
389907

— query the table, shows no rows
SQL> select * FROM test;

no rows selected

— insert the row and commit
SQL> insert into test values ( 1 );

1 row created.

SQL> commit;

Commit complete.

— flashback to before insert was executed, so select should return no rows
SQL> execute dbms_flashback.enable_at_system_change_number(:current_scn);

PL/SQL procedure successfully completed.

— as expected select returned no rows
SQL> select * FROM test;

no rows selected

— disable flashback
SQL> execute dbms_flashback.disable;

PL/SQL procedure successfully completed.

Another way of flashback can be used is as follows
SQL> select count(1) from test as of scn :current_scn;

COUNT(1)
———-
0

-- Shows rows before the change and after using “AS OF SCN..”
SQL> select *
from (select count(1) from test),
(select count(1) from test as of scn :current_scn);
COUNT(1) COUNT(1)
———- ———-
1 0

Note: If there is a truncate executed on the table then data can’t be read from the table after enabling flashback Oracle will return “ORA-01466: unable to read data – table definition has changed” on truncated table.

How to enable/disable a scheduled job?

Posted on by
3

Using the package DBMS_SCHEDULER one can enable/disable jobs.

To disable job: This disables the job from running
SQL> exec dbms_scheduler.disable(‘GATHER_STATS_JOB’);

PL/SQL procedure successfully completed.

— check job status
SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = ‘GATHER_STATS_JOB’;

JOB_NAME ENABL
————————- —–
GATHER_STATS_JOB FALSE

To enable job:
SQL> exec dbms_scheduler.enable(‘GATHER_STATS_JOB’);

PL/SQL procedure successfully completed.

— check job status
SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = ‘GATHER_STATS_JOB’;

JOB_NAME ENABL
————————- —–
GATHER_STATS_JOB TRUE

How to run SQL statements in SQL*Plus if you have ‘&’ as part of your data?

Posted on by
Reply

If you have ‘&’ as part of your data that is part of the string and needs to inserted/updated/deleted and you are using SQL*Plus, you would need to do the following as ‘&’ has a special meaning in SQL*Plus as by default SQL*Plus considers it as a variable.

E.g: SQL> select ‘xxxx &abc’ from dual;
In the example SQL*Plus will prompt to enter a value for variable abc

So to ensure ‘&’ is taken in it’s literal value and it doesn’t prompt, one can do one of the following if you need to run SQL scripts from SQL*Plus

1) SET DEFINE OFF — Turns off substitution of variables
2) SET DEFINE # — Change the substitution character to # or to a character that is not part of the script
3) Change the above sample SQL to the following
SQL> select ‘xxxx &’ || ‘abc’ from dual;

Enabling Flashback Database

Posted on by
Reply

The following are the requirements for enabling Flashback Database:

– Your database must be running in ARCHIVELOG mode, because archived logs are used in the Flashback Database operation.
– You must have a flash recovery area enabled, because flashback logs can only be stored in the flash recovery area.
– For Real Application Clusters databases, the flash recovery area must be stored in a clustered file system or in ASM.

To enable logging for Flashback Database, set the DB_FLASHBACK_RETENTION_TARGET initialization parameter and issue the ALTER DATABASE FLASHBACK ON statement. Follow the process outlined here:

Start SQL*Plus and ensure that the database is mounted, but not open. For example:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

Optionally, set the DB_FLASHBACK_RETENTION_TARGET to the length of the desired flashback window in minutes:

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days

By default DB_FLASHBACK_RETENTION_TARGET is set to one day (1440 minutes).

Note: DB_RECOVERY_FILE_DEST_SIZE must be set before DB_RECOVERY_FILE_DEST is set.
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=536870912;

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/oradata/flashback_area';

Enable the Flashback Database feature for the whole database:

SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;

SQL> SELECT flashback_on FROM v$database;

FLASHBACK_ON
——————
YES

-- Turn off flashback, this can be executed when the database is in mount/open state
SQL> ALTER DATABASE FLASHBACK OFF;

Database altered.

-- prints current usage and limit set for flashback recovery, amount of space that can be recalimed and number of files
SELECT * FROM V$recovery_file_dest

-- Amount of space used for different types of files in flashback
SELECT * FROM V$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
———— —————— ————————- —————
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0