Loading...

I hit with that error message during the export&import process on 12c database on Linux

While I am checking import log, I noticed below error message:

ORA-13249: Error in R-tree: [mdrcrtscrt]
ORA-13231: failed to create index table [MY_OBJECT$] during R-tree creation
ORA-13249: Stmt-Execute Failure: CREATE TABLE "MY_SCHEMA"."MY_OBJECT$"(NODE_ID NUMBER,NODE_LEVEL NUMBER,INFO BLOB) LOB (INFO) STORE 
ORA-39083: Object type INDEX:"MY_SCHEMA"."GI_LOCATION_1" failed to create with error:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-13249: internal error in Spatial index: [mdidxrbd]
ORA-13249: Error in Spatial index: index build failed

While I try to create an index with sys user I hit below errors:

SQL> CREATE INDEX MY_SCHEMA.GI_LOCATION_1 ON MY_SCHEMA.GIS_TABLE
(LOCATION)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
NOPARALLEL;

ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-13249: internal error in Spatial index: [mdidxrbd]
ORA-13249: Error in Spatial index: index build failed
ORA-13249: Error in R-tree: [mdrcrtscrt]
ORA-13231: failed to create index table [MY_TABLE$] during R-tree creation
ORA-13249: Stmt-Execute Failure: CREATE TABLE "MY_SCHEMA"."MY_TABLE$"(NODE_ID NUMBER,NODE_LEVEL NUMBER,INFO BLOB) LOB (INFO) STORE AS SECUREFILE (NOCACHE) PCTFREE 2 TABLESPACE USERS NOLOGGING
ORA-29400: data cartridge error
ORA-01031: insufficient privileges
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 10

So first Grant to your schema

SQL> grant unlimited tablespace to MY_SCHEMA;
Grant succeeded.

SQL> grant CREATE TABLE to MY_SCHEMA;
Grant succeeded.

SQL> grant CREATE SEQUENCE to MY_SCHEMA;
Grant succeeded.

Then, try to recreate an index

SQL> DROP INDEX MY_SCHEMA.GI_LOCATION_1;
Index dropped.

SQL> CREATE INDEX MY_SCHEMA.GI_LOCATION_1 ON MY_SCHEMA.GIS_TABLE
(LOCATION)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
NOPARALLEL;

Index created.
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

I faced this issue while working with preparing standby database with E-Business Suite R12.1 with 11gR2-RAC on ASM

I have E-Business Suite R12.1.2 with 11.2.0.4 RAC database which is working on ASM

I needed to create standby server. I followed below command on the primary database to can create RMAN backup for can create standalone standby database.

Here is the my rman command which need to run on Primary rac database:

run
{
BACKUP DEVICE TYPE DISK FORMAT ‘/u01/dataguard_rman_bckp/CTRL_%U’ CURRENT CONTROLFILE FOR STANDBY;
ALLOCATE CHANNEL C1 device type  DISK  format ‘/u01/dataguard_rman_bckp/TSM_DB_%d_%t_%s_%p’;
ALLOCATE CHANNEL C2 device type  DISK  format ‘/u01/dataguard_rman_bckp/TSM_DB_%d_%t_%s_%p’;
ALLOCATE CHANNEL C3 device type  DISK  format ‘/u01/dataguard_rman_bckp/TSM_DB_%d_%t_%s_%p’;
ALLOCATE CHANNEL C4 device type  DISK  format ‘/u01/dataguard_rman_bckp/TSM_DB_%d_%t_%s_%p’;
ALLOCATE CHANNEL C5 device type  DISK  format ‘/u01/dataguard_rman_bckp/TSM_DB_%d_%t_%s_%p’;
ALLOCATE CHANNEL C6 device type  DISK  format ‘/u01/dataguard_rman_bckp/TSM_DB_%d_%t_%s_%p’;
BACKUP as COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
RELEASE CHANNEL C1;
RELEASE CHANNEL C2;
RELEASE CHANNEL C3;
RELEASE CHANNEL C4;
RELEASE CHANNEL C5;
RELEASE CHANNEL C6;
}

On standby server we did not set db_file_name_convert and log_file_name_convert because both primary and standby server has same diskgroup at ASM

Here is my restore command on standby:

run
{
ALLOCATE AUXILIARY CHANNEL C1 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL C2 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL C3 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL C4 DEVICE TYPE DISK;
DUPLICATE TARGET DATABASE FOR STANDBY
NOFILENAMECHECK;
RELEASE CHANNEL C1;
RELEASE CHANNEL C2;
RELEASE CHANNEL C3;
RELEASE CHANNEL C4;
}

My standby database db_unique_name is PRODs and my primary database db_unique_name is PROD.

While restore done v$datafile bring us datafile path as is +ORADATA/PROD/datafile but my restore log mention datafile has been restoring to +ORADATA/PRODs/datafile

As you can notice Control file shows the wrong path for my datafiles

So how we can solve this issue? Here are the steps:

1. Firstly, list of your datafile by:

Select name from v$datafile

2. Connect RMAN on standby database(or your restored database)

RMAN> catalog start with ‘+ORADATA/PRODS/datafile/’;

List of Cataloged Files
=======================
File Name: +ORADATA/PRODS/datafile/SYSTEM.319.658752015
File Name: +ORADATA/PRODS/datafile/SYSAUX.320.658752015
List of Files Unknown to the Database
=====================================
File Name: +ORADATA/PRODS/datafile/SYSTEM.319.658752015
File Name: +ORADATA/PRODS/datafile/SYSAUX.320.658752015
File Name: +ORADATA/PRODS/datafile/EXAMPLE.321.658752017
File Name: +ORADATA/PRODS/datafile/UNDOTBS1.322.658752017
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files…
cataloging done

Confirm the copy of the database and all the datafiles have been cataloged by rman correctly.

RMAN> list copy of database;

List of Cataloged Files
=======================
File Name: +ORADATA/PRODS/datafile/SYSTEM.319.658752015
File Name: +ORADATA/PRODS/datafile/SYSAUX.320.658752015
File Name: +ORADATA/PRODS/datafile/EXAMPLE.321.658752017
File Name: +ORADATA/PRODS/datafile/UNDOTBS1.322.658752017

Have rman adjust the standby control file to use the newly cataloged standby database datafiles.

RMAN> switch database to copy;
datafile 1 switched to datafile copy xxxx

3. Shutdown database

4. Startup database (mount mode)

5. Confirm your data file is the correct path on your controlfile

Select name from v$datafile

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

I face wiht this error while i try to move one of my file system to my ASM

I have 11.2.0.2  database which is working on 12c ASM

I needed to copy one file from file system to asm. Here is the my steps:

[oracle@prod]</tmp> asmcmd -p

ASMCMD [+] > ls
ORADATA/
ORAFRA/

ASMCMD [+] > cd oradata
ASMCMD [+oradata] > ls
ASM/
ORCL/

ASMCMD [+oradata] > cp /tmp/thread_1_seq_148.268.755819361 +ORADATA/thread_1_seq_148.268.755819361

copying /tmp/thread_1_seq_148.268.755819361 -> +ORADATA/thread_1_seq_148.268.755819361
ASMCMD-08016: copy source->’/tmp/thread_1_seq_148.268.755819361′ and target->’+ORADATA/thread_1_seq_148.268.755819361′ failed
ORA-19505: failed to identify file “+ORADATA/thread_1_seq_148.268.755819361”
ORA-17502: ksfdcre:4 Failed to create file +ORADATA/thread_1_seq_148.268.755819361
ORA-15046: ASM file name ‘+ORADATA/thread_1_seq_148.268.755819361’ is not in single-file creation form
ORA-06512: at “SYS.X$DBMS_DISKGROUP”, line 397
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)
The cp command failed because the ASM file name was not in a form that can be used to create an single file.File name should not contain the file number/incarnation

Here is the solution:

ASMCMD [+oradata] > cp /tmp/thread_1_seq_148.268.755819361 +ORADATA/thread_1_seq_148

Reference:
ASMCMD cp command fails with ORA-15046 [ID 452158.1]

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

I have dataguard configuration such as,2 node RAC(11.2.0.4) on Aix 7.1 as primary, standalone standby database.

I noticed that Primary can transport archive log to standby but standby cannot apply archive log and giving errors at standby alert.log such as:

MRP0: Background Media Recovery terminated with error 1111 and MRP0: Background Media Recovery process shutdown (PROD00DG)

On primary alert.log
RC8: Archive log rejected (thread 1 sequence 75698) at host ‘PROD’
FAL[server, ARC8]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance PROD001 – Archival Error. Archiver continuing.

I noticed that MPR has been stopped and standby.

On standby:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

On Standby  alert.log

Slave exiting with ORA-1111 exception
Errors in file /oracle11g/app/oracle/diag/rdbms/xxxx/17891406.trc:
ORA-01111: name for data file 1285 is unknown – rename to correct file
ORA-01110: data file 1285: ‘/oracle11g/app/oracle/11.2.0/dbs/UNNAMED01285’
ORA-01157: cannot identify/lock data file 1285 – see DBWR trace file
ORA-01111: name for data file 1285 is unknown – rename to correct file
ORA-01110: data file 1285: ‘/oracle11g/app/oracle/11.2.0/dbs/UNNAMED01285’
Recovery Slave PR00 previously exited with exception 1111
MRP0: Background Media Recovery process shutdown (PROD00DG)
at standby trace:

MRP0: Background Media Recovery terminated with error 1111
ORA-01111: name for data file 1285 is unknown – rename to correct file
ORA-01110: data file 1285: ‘/oracle11g/app/oracle/11.2.0/dbs/UNNAMED01285’
ORA-01157: cannot identify/lock data file 1285 – see DBWR trace file
ORA-01111: name for data file 1285 is unknown – rename to correct file
ORA-01110: data file 1285: ‘/oracle11g/app/oracle/11.2.0/dbs/UNNAMED01285’
*** 2018-03-21 19:41:03.428
Completed Media Recovery
Managed Recovery: Not Active posted.

*** 2018-03-21 19:41:04.133
Slave exiting with ORA-1111 exception
ORA-01111: name for data file 1285 is unknown – rename to correct file
ORA-01110: data file 1285: ‘/oracle11g/app/oracle/11.2.0/dbs/UNNAMED01285’
ORA-01157: cannot identify/lock data file 1285 – see DBWR trace file
ORA-01111: name for data file 1285 is unknown – rename to correct file
ORA-01110: data file 1285: ‘/oracle11g/app/oracle/11.2.0/dbs/UNNAMED01285’

I have checked below query ouputs:

a.) select file#, error from v$recover_file;
b.) select file#, name, status from v$datafile;

Outputs are:
SQL> select file#, error from v$recover_file;

FILE# ERROR
———- —————————–
1268
1281
1282
1283
1284
1285 FILE MISSING
SQL> select file#, name, status from v$datafile;

file# name status
—— ———– ———
1285 /oracle11g/app/oracle/11.2.0/dbs/UNNAMED01285 RECOVER

After make some search, I have found MOS Recovering the primary database’s datafile using the physical standby, and vice versa [ID 453153.1]

A backup of the one datafile from the primary can be made and then used to restore on the standby database, as indicated in the following note:

The document walks you through the process starting about one-half way down, in the section titled:
“Recovering the Standby’s Datafile”

I followed below steps:

1. Backup related file at primary

On primary:

$ rman target /

RMAN> backup datafile 1285 format ‘/tmp/1285_pr.bk’ tag ‘PRIMARY_1285′;

2. Transfer the file to the standby site using an operating system utility such as scp, NFS, ftp etc

3. At the standby site, catalog the backuppiece and confirm it’s available for use:

On standby:

$ rman target /

RMAN> catalog backuppiece’/tmp/1285_pr.bk’;
RMAN> list backuppiece’/tmp/1285_pr.bk’;
RMAN> list backup of datafile 1285;

4. Stop redo apply on the physical standby database

On standby:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

For my issue my redo apply has been already stopped.

5. On the standby site restore the datafile:

On standby:

$ rman target /
RMAN> restore datafile 1285;

At steps 5 I got error:

RMAN> restore datafile 1285;

Starting restore at 01-DEC-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=438 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 2018-03-21 21:46:50
RMAN-06085: must use SET NEWNAME command to restore datafile /oracle11g/app/oracle/11.2.0/dbs/UNNAMED01285

So I need to run below command for can restore datafile 1285:

RUN {
SET NEWNAME FOR DATAFILE 1285 to ‘+ORADATA’;
RESTORE DATAFILE 1285;
SWITCH DATAFILE 1285;
}

6. Check the status of files: Restart redo apply on the physical standby database

On standby:

a.) select file#, error from v$recover_file;
b.) select file#, name, status from v$datafile;

On standby:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
from log:

Successfully added datafile 1285 to media recovery
Datafile #1285: ‘+ORADATA/PROD00dg/datafile/tPRODspace_2016_ernst.1574.800921533’

Successfully added datafile 1285 to media recovery
Datafile #1286: ‘+ORADATA/PROD00dg/datafile/tPRODspace_2016_ernst.1574.800921533′


SQL> select thread#, max (sequence#) from v$archived_log where APPLIED=’YES’ group by thread#;

THREAD#    MAX(SEQUENCE#)
———-         ————–
1                        75677
2                         72871

Reference:
Recovering the primary database’s datafile using the physical standby, and vice versa [ID 453153.1]
How to Recover from a Lost or Deleted Datafile with Different Scenarios [ID 198640.1]
MRP0: Background Media Recovery terminated with error 1274 [ID 739618.1]

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

As you know sometimes we need to change CPU count dynamically on our prod systems.

Our Unix team change our 2 node RAC system (11.2.0.4 version) server’s CPU from 20 to 30 on AIX 7.1

After that change one of the instance(node 2) goes down with below error messages:

ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00068: invalid value 6360 for parameter parallel_max_servers, must be between 0 and 3600
CKPT (ospid: 22135): terminating the instance due to error 2097

After makes some search, to be sure We raised Severity 1 Service Request

Heare is the answers:
Our The Default Value of the parallel_max_servers is calculated based on the Following Equation :

PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 5

http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams186.htm#REFRN10158

and the maximum value for this parameter is 3600 , so in some times this equation can lead to higher values than 3600

Please Perform the following Action Plan to resolve this issue :

1- Lower the Value of parallel_max_servers parameter to a reasonable value , for example (10) :

SQL> alter system set parallel_max_servers=300 scope=both sid=’*’ ;

or

2- Apply Patch 13743987

If a system has a high CPU count and there is no value set for parallel_max_servers then the system may try to set the default value of the parallel_max_servers parameter higher than is allowed.
eg:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00068: invalid value 6360 for parameter parallel_max_servers, must be between 0 and 3600
This can cause an instance shutdown if the number of system CPUs alters dynamically.
eg:
The alert log may show a dynamically detected change of CPU count:
Detected change in CPU count to 159
* Load Monitor used for high load check
* New Low – High Load Threshold Range = [152640 – 203520]
Errors in file /…/trace/+ASM2_ckpt_22135.trc:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00068: invalid value 6360 for parameter parallel_max_servers, must be between 0 and 3600
CKPT (ospid: 22135): terminating the instance due to error 2097
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

I face this issue when my Tivoli admin friends warn me about old backup which could be listed on Tivoli Storage even I delete my all old RMAN backups with RMAN commands on our 11.2.0.4 database have 11.2.0.4 database on AIX 6.1 which we use RMAN backup utility with RMAN catalog by using Tivoli tool

First I connect to RMAN catalog,

RMAN> show all;
RMAN configuration parameters for database with db_unique_name XXXX are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 8 DAYS;

then run below RMAN commands to delete my backup which is older than 8 days;

run
{
ALLOCATE CHANNEL C1 device type ‘sbt_tape’ ;
delete force noprompt obsolete;
RELEASE CHANNEL C1;
}

After this command  I checked below

RMAN>  LIST EXPIRED BACKUPSET;
the specification does not match any backup in the repository
RMAN> LIST EXPIRED BACKUP;
the specification does not match any backup in the repository

also, report obsolete doesn’t bring any data, but my Tivoli admin friends keep saying me they see still an old backup.

We noticed that When an RMAN delete command is run, RMAN deletes entries in the recovery catalog before confirmation from the Tivoli Storage Manager server.

We followed Remove Backups From Tivoli not available in RMAN catalog [ID 1209740.1] docs and We solved the problem.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

I faced with those errors on 12c database. While I try to drop some tablespace it throws errors such as:

SQL> DROP TABLESPACE MY_TABLESPACE INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE MY_TABLESPACEINCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-01116: error in opening database file 35
ORA-01110: data file 35:
'/u01/app/oracle/product/12.1.0/dbhome_2/dbs/MY_TABLESPACE02_A.DBF'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3

Even I try to take tablespace to offline I hit the same errors

SQL> alter tablespace MY_TABLESPACE offline;
alter tablespace MY_TABLESPACE offline
*
ERROR at line 1:
ORA-01116: error in opening database file 35
ORA-01110: data file 35:
'/u01/app/oracle/product/12.1.0/dbhome_2/dbs/MY_TABLESPACE02_A.DBF'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3

So here is the solution

SQL> alter database datafile '/u01/app/oracle/product/12.1.0/dbhome_2/dbs/MY_TABLESPACE02_A.DBF' offline drop;

Database altered.

SQL> DROP TABLESPACE MY_TABLESPACE INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

I hit this error during try to start my ASM instance

I am working standalone  Oracle 11.2.0.1 database with ASM option on AIX 6.1.

I this error after I reboot my server.

$export ORACLE_SID=+ASM
$sqlplus "/as sysasm"
SQL> startup
 ORA-01078: failure in processing system parameters
 ORA-29701: unable to connect to Cluster Synchronization Service

I checked services by using:

$crs_stat –t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.asm        ora.asm.type   OFFLINE   OFFLINE
ora.cssd       ora.cssd.type  ONLINE    OFFLINE
ora.diskmon    ora....on.type ONLINE    OFFLINE

Then I am going to start ora.cssd services by using below syntax

crsctl start resource ora.cssd

Than i run below command:

$crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.asm        ora.asm.type   OFFLINE   OFFLINE   oracle
ora.cssd       ora.cssd.type  ONLINE    ONLINE    oracle
ora.diskmon    ora....on.type ONLINE    ONLINE    oracle
$ sqlplus "/as sysasm"
SQL> startup
ORA-00099: warning: no parameter file specified for ASM instance
ASM instance started
Total System Global Area  283930624 bytes
Fixed Size                  2206088 bytes
Variable Size             256558712 bytes
ASM Cache                  25165824 bytes
ORA-15110: no diskgroups mounted
SQL> select group_number, name, total_mb, free_mb, state from v$asm_diskgroup;
GROUP_NUMBER                NAME                             TOTAL_MB    FREE_MB       STATE   
 ------------      --------------------------------          ----------  ---------     ----
 0                 ORADATA                                    0           0           DISMOUNTED
SQL> alter diskgroup ORAODM mount;
Diskgroup altered.
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

I face this issue while trying to take export by using data pump

My system is:

AIX:6.1
Db verison:11.2.0.3
Process: expdp

The error message is:

ORA-31626: job does not exist
ORA-31637: cannot create job xxx for user TEST
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.KUPV$FT_INT”, line 600
ORA-39080: failed to create queues “KUPC$xxx” and “KUPC$S_xx” for Data Pump job
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.KUPC$QUE_INT”, line 1606
ORA-04031: unable to allocate 56 bytes of shared memory (“streams pool”,”unknown object”,”streams pool”,”fixed allocation callback”)

Workaround:

alter system flush shared_pool;

alter system flush buffer_cache;

Permanent solutions:

select * from v$parameter where name like ‘%sga%’;

alter system set sga_target=3000M; #Here we are increasing sga_target size
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

You can hit ORA-00054: resource busy and acquire with NOWAIT specified error during some of your operations on your database such as alter some objects.

The main reason for this error is Table Level Locks(TM lock). The transaction holds two locks (Row Locks(TX) and Table Locks(TM) when the session opens. So What are those locks?

Row Locks (TX): Row-level locks are primarily used to prevent two transactions from modifying the same row.When a transaction needs to modify a row, a row lock is acquired.

Table Locks (TM): Table-level locks are primarily used to do concurrency control with concurrent DDL operations, such as preventing a table from being dropped in the middle of a DML operation.  When a DDL or DML statement is on a table, a table lock is acquired. Table locks do not affect concurrency of DML operations. For partitioned tables, table locks can be acquired at both the table and the subpartition level.

When you hit this error you can try to run delete command for your issue.
You can also use below syntax to can find details about locks and then can kill session which keeps your object as locked:
Select     oracle_username || ‘ (‘ || s.osuser || ‘)’ username
,  s.sid || ‘,’ || s.serial# sess_id
,  owner || ‘.’ || object_name object
,  object_type
,  decode( l.block
,       0, ‘Not Blocking’
,       1, ‘Blocking’
,       2, ‘Global’) status
,  decode(v.locked_mode
,       0, ‘None’
,       1, ‘Null’
,       2, ‘Row-S (SS)’
,       3, ‘Row-X (SX)’
,       4, ‘Share’
,       5, ‘S/Row-X (SSX)’
,       6, ‘Exclusive’, TO_CHAR(lmode)) mode_held
from       v$locked_object v
,  dba_objects d
,  v$lock l
,  v$session s
where      v.object_id = d.object_id
and        v.object_id = l.id1
and        v.session_id = s.sid
order by oracle_username
,  session_id

If you are on release 11g onward you can also use this syntax:

SQL>ALTER SESSION SET ddl_lock_timeout=100;

Session altered.

SQL>ALTER TABLE MY_TABLE MODIFY DEFAULT ATTRIBUTES TABLESPACE XXX;

 

Read Full Article
Visit website

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview