IMPDP Hangs – Wait For Unread Message On Broadcast Channel

If IMPDP hangs, and if checking in v$sessionwait shows it is waiting on “wait for unread message on broadcast channel”, check the alert log. You may see something like:

statement in resumable session ‘SYSTEM.SCHEMAIMPORT.1′ was suspended due to
ORA-01658: unable to create INITIAL extent for segment in tablespace TESTDATA

Tablespace full in other words, and will need to be extended for the IMPDP to continue.

Moving data from one Tablespace to another (including partitions/locally managed indexes)

Scenario: A tablespace is holding 200gb but only 50gb is used. To be able to reclaim the unused 150gb back to the O/S, the tablespace would need to be dropped. Before dropping the tablespace, the existing 50gb’s worth of data and indexes needs to be moved so that it is not lost

A New tablespace is created called TS_NEWPLACE

To move individual tables and associated indexes use the following command:

ALTER TABLE SCHEMA.TABLE1 MOVE TABLESPACE TS_NEWPLACE;

To Move the associated index for TABLE1 use the following command. A rebuild will more or less recreate the index in its new destination:

ALTER INDEX SCHEMA.INDEX1_TABLE1 REBUILD TABLESPACE TS_NEWPLACE;

To move a partitioned table with locally managed indexes (i.e partitioned indexes per table partition), use the following commands:

ALTER TABLE SCHEMA.SEARCHES MOVE PARTITION SEARCHES_2006_01 TABLESPACE TS_NEWPLACE NOLOGGING;
ALTER INDEX SCHEMA.I_SEARCHES_SEARCH_DATE REBUILD PARTITION SEARCHES_2006_01 TABLESPACE TS_NEWPLACE NOLOGGING;

ALTER TABLE SCHEMA.SEARCHES MOVE PARTITION SEARCHES_2006_02 TABLESPACE TS_NEWPLACE NOLOGGING;
ALTER INDEX SCHEMA.I_SEARCHES_SEARCH_DATE REBUILD PARTITION SEARCHES_2006_02 TABLESPACE TS_NEWPLACE NOLOGGING;

ALTER TABLE SCHEMA.SEARCHES MOVE PARTITION SEARCHES_2006_03 TABLESPACE TS_NEWPLACE NOLOGGING;
ALTER INDEX SCHEMA.I_SEARCHES_SEARCH_DATE REBUILD PARTITION SEARCHES_2006_03 TABLESPACE TS_NEWPLACE NOLOGGING;

ALTER TABLE SCHEMA.SEARCHES MOVE PARTITION SEARCHES_2006_04 TABLESPACE TS_NEWPLACE NOLOGGING;
ALTER INDEX SCHEMA.I_SEARCHES_SEARCH_DATE REBUILD PARTITION SEARCHES_2006_04 TABLESPACE TS_NEWPLACE NOLOGGING;

ALTER TABLE SCHEMA.SEARCHES MOVE PARTITION SEARCHES_2006_05 TABLESPACE TS_NEWPLACE NOLOGGING;
ALTER INDEX SCHEMA.I_SEARCHES_SEARCH_DATE REBUILD PARTITION SEARCHES_2006_05 TABLESPACE TS_NEWPLACE NOLOGGING;

The above allows individual partitions to be moved regardless of the other partitions. NOLOGGING is used to minimize the amount of REDO which is generated.

To change an index from a Globally managed index to a partitioned index use the following commands:

DROP INDEX SCHEMA.INDX_SEARCH_DATE;

Then recreate the index as a locally managed index:

CREATE INDEX SCHEMA.INDX_SEARCH_DATE ON SCHEMA.SEARCHES (search_date) TABLESPACE TS_NEWPLACE LOCAL;

Talking about Stupid DBA Tricks

 Useful to know from Glenn Berrys Blog….

Quote

Stupid DBA Tricks

Well, I just learned a valuable lesson about something you should not do as a DBA…

About a month ago, a client of mine called me to report that their main database server was running critically short of disk space on the C: drive.  Whoever had installed the operating system, had decided to make the C: partition a little too small (only 20GB), and enough stuff had been installed since then that the C: drive was, in fact down to less than 50MB free.

I went through and uninstalled SQL Server BOL (which was fine), cleaned out the Windows\Temp directory, and we were still quite low on disk space. So then, I got the bright idea of cleaning out the SQL Server Install directory, specifically "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Install”. I figured that SQL Server was already installed, so why would it need that stuff?

Big mistake.  Oh, there was no immediate effect. SQL Server was just fine, blissfully unaware of what I had just done to it. The problem came up when I tried to install SQL Server 2008 SP1 CU4 on that server this morning. The CU went through, and appeared to complete with no issues. After it was done, I tried to logon to the server in SSMS, and I was told that the server did not exist, or access was denied.

So, I started up SQL Configuration Manager, and sure enough the SQL Server service was not running. I started it, and it appeared to be fine, so I tried to logon again, and I got the same error. Looking at SQL Configuration Manager, the service was stopped. That did not look good.

Next, I opened up the SQL Error log, and saw this at the end:

2009-10-04 08:32:56.82 spid7s      Uploading data collector package from disk: C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Install\SqlTraceCollect.dtsx
2009-10-04 08:32:56.82 spid7s      Error: 4860, Severity: 16, State: 1.
2009-10-04 08:32:56.82 spid7s      Cannot bulk load. The file "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Install\SqlTraceCollect.dtsx" does not exist.
2009-10-04 08:32:56.82 spid7s      Error: 912, Severity: 21, State: 2.
2009-10-04 08:32:56.82 spid7s      Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 200, state 7, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2009-10-04 08:32:56.82 spid7s      Error: 3417, Severity: 21, State: 3.
2009-10-04 08:32:56.82 spid7s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
2009-10-04 08:32:56.82 spid7s      SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.

Oh-oh. That did not look good at all. After my initial panic subsided, I remembered about my clever move of deleting the files in the installation directory. I had a test server that had SQL Server 2008 SP1 CU4 installed, so I zipped up those install files, and e-mailed them to myself. Then I logged on to Outlook Web Access from the client’s DB server, and pulled down that zip file and copied the missing files to the install directory.

Finally, I restarted the SQL Server service, and he was able to complete the script level upgrade process of the Cumulative Update, and everything was fine. Minor catastrophe averted by some quick thinking. Now, I am older but wiser, and I won’t make that mistake again.

Still, one could argue that the CU setup program could check for the existence of those script files, and/or lay them down if it needed them. Of course I should not have deleted those files either…

Technorati Tags:

Export Import in Oracle – Examples

Sample export script to restrict data in 9i

exp abc_def_schema FILE=live_ie_am_130508.dmp LOG=live_ie_am_130508.log TABLES=table_a query=\"where fk_ad_id IN \(SELECT id FROM advert WHERE status=\’Published\’\)\" ROWS=y INDEXES=no GRANTS=N TRIGGERS=no CONSTRAINTS=n feedback=5000

Sample Import into 91 database

imp userid=abc_uk_pf/xxxxx55xx tables=(pafdata_new) file=pafdata_new_20090420_MP.dmp log=pafnew_imp_20090420.log rows=y indexes=y grants=y constraints=y

Sample export script to exclude data in 10g

expdp MOHSIN_P dumpfile=EXPDAT_ABC_UK_PS_DB2.dmp directory=EXPDP logfile=EXPDAT_ABC_UK_PS_DB2.LOG content=ALL schemas=ABC_UK_PS exclude=table:"in (‘ADVERT_MEDIA’, ‘EVENT_LOG’)"

Sample export script using PARAMETER file

parameter file
dumpfile= EXPDAT_ABC_UK_PS_DB2.dmp
directory=expdp
logfile= EXPDAT_ABC_UK_PS_DB2.LOG
content=all
schemas=abc_uk_ps
exclude=table:"in (‘ADVERT_MEDIA’,’EVENT_LOG’,’AM_CHANGE_LOG’)"

Expdp Mohsin_p parfile=parameterfile name

To move file across the network

Log into the Destination box
Move to the correct directory and ensure a file with the same name doesn’t already exist
If exists: rm dump_filenamevi

Ll –h gives list in human friendly form
Find Ip address of current node: ifconfig
The data link is usually the eth0 interface

Then on the source machine: scp zipfilename ipaddress:/pathdirectory

Eg:

scp ps.dmp.gz xxx.xxx.xxx.xxx:/u01/export/ps

To unzip
Go to the destination directory where the zip has been copied to and run:
gunzip ps.dmp.gz

Import DMP into Database 10g

Check the PAR file on the destination box to ensure the settings variables are correct.

Depending on what is being imported do the following:
Disable the constraints (to allow tabled to be dropped)
Then go through the different objects and drop whatever will be replaced. Ensure that the tables which were not dumped (i.e. MEDIA) are not dropped.
Do not drop DATA LINKS

impdp Mohsin_p parfile=parameterfile name

Compatibility Versions and Network Export

Add the VERSION=10.1.0.5 (or version compatibility level) to the export for backward compatibility.

Similar to SQLPLUS, add the Tns link to export over the network. The export will be saved on the source database server NOT the server the EXPDP is being run from.

expdp mohsin_p@Tns_DB dumpfile=EXP.dmp directory=EXPORT logfile=EXPDAT.LOG content=ALL schemas=SCHEMA_NAME VERSION=10.1.0.5

Lock/Block Monitoring with Alerting

Over the past few days, I have been working on creating a mechanism in CMS whereby it will alert us when a lock has been blocking another process for a given period.

The scenario that we are trying to alert on is when a SPID is a lead blocker on other Child SPIDs which would lead to a connection from one user timing out in the DB Application.

After a lot of testing, I have put the code to do this into DB1 as a Scheduled Job which will run every 3 minutes.

Breakdown of what this code does:

  • Finds Parent Locks
  • Queries to find out whether Parent lock is the blocker of another SPID – not itself (Sometimes it is ok to have a lock/block as SPIDs can sometime spawn processes to do part of the work)
    • If no locks found then Deletes everything from the Blocking_Log table
  • If blocks found which have been held for more than 5 minutes then send email
    • If Lock details already exist in Blocking_Log table AND timestamp of last alert is less than 1 hour ago then do not send email
    • If it is a new block then add to Blocking_Log
  • Send Email with details of locking, SQL Buffer of the Spids involved, and Pages/objects that these SPIDs are locking/blocking.

Usage

1. Create the logging table in the DB_Monitoring Database [Blocking_Log_Table.txt]
2. Create the Agent Job in MSDB [Lock_Job.txt]
3. Amend Step 2 of the Lock_Monitoring Job to contain the correct DBMail Profile for the particular server 4. Create the Directory E:\Backup\Lock_Monitoring

Blocking_Log_Table.txt

/****** Object:  Table [dbo].[Blocking_Log]    Script Date: 10/05/2009 17:28:37 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Blocking_Log](
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [SPID] [int] NOT NULL,
      [Host_Name] [nvarchar](50) NOT NULL,
      [Program_Name] [nvarchar](75) NOT NULL,
      [Timestamp] [datetime] NOT NULL
) ON [PRIMARY]

Lock_Job.txt

USE [msdb]
GO

/****** Object:  Job [Lock Monitoring]    Script Date: 10/06/2009 13:47:54 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 10/06/2009 13:47:54 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Lock Monitoring', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Monitor locks]    Script Date: 10/06/2009 13:47:54 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Monitor locks', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'SET NOCOUNT ON 
DECLARE @Handle varbinary(64);
DECLARE @SPID INT;
DECLARE @Subj varchar(75);
DECLARE @waittime INT;
declare @SpidStatus varchar(100),@BlockerSpid int, @BlockedBy int,@DBname sysname,@CTEXT VARCHAR(8000)
SET @waittime = 300000

DECLARE [Blocked_Spids] CURSOR FOR 

select case when blocked != 0 then ''Not Lead Blocked''
when blocked = 0 then ''Lead Blocker''
Else ''Unknown''
End [Block],
spid,blocked, db_name(dbid) dbname from master..sysprocesses
where (blocked = 0 and spid in (select blocked from master..sysprocesses)) or blocked != 0 and waittime > @waittime  order by blocked asc, waittime desc
FOR READ ONLY

OPEN [Blocked_Spids]
WHILE 1 = 1
BEGIN
FETCH NEXT FROM [Blocked_Spids] INTO @SpidStatus, @BlockerSpid,@BlockedBy,@DBname
IF @@fetch_status <> 0 BREAK

SELECT RTRIM(LTRIM(STR(@BlockerSpid))) + '' ''+ @SpidStatus +'' In Database ''+@DBname + '' Is running following T-SQL and is Blocked by ''+ ltrim(STR(@BlockedBy))

SELECT ''SPID '' + convert(varchar(4),@BlockerSpid) + '' Logged in on '' + convert(varchar(10),Login_time,103) + '' @ '' + convert(varchar(8),Login_time,108) + '' from '' + [host_name] + '' using '' + [program_name] from sys.dm_exec_sessions
where session_id = @blockerspid

SELECT @Handle = sql_handle 
FROM master..sysprocesses 
WHERE spid = @BlockerSpid 

SELECT @CTEXT=text FROM ::fn_get_sql(@Handle);

IF @CTEXT IS NULL OR LEN(LTRIM(RTRIM(@CTEXT)))=0
BEGIN
SELECT ''DBCC INPUTBUFFER (''+LTRIM(STR(@BlockerSpid)) +'')''
DBCC INPUTBUFFER(@BlockerSpid)
END
ELSE 
SELECT @CTEXT
SELECT ''LOCK STATUS ON SPID ''+ STR(@BlockerSpid) 
EXECUTE sp_lock @BlockerSpid
END

deallocate [Blocked_Spids]

DECLARE @nolocks INT;

select @nolocks = count(*) 
from master..sysprocesses
where (blocked = 0 and spid in (select blocked from master..sysprocesses))

if @nolocks = 0
BEGIN
delete from DB_Monitoring..Blocking_log
END

', 
        @database_name=N'master', 
        @output_file_name=N'E:\backup\Lock_Monitoring\Locks_Trace.log', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Send Mail]    Script Date: 10/06/2009 13:47:54 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Send Mail', 
        @step_id=2, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'DECLARE @subj varchar (100)
DECLARE @leadspid INT;
DECLARE @blockcount INT;
DECLARE @AlreadyAlerted INT;
DECLARE @waittime INT;
DECLARE @dbname varchar (25);
DECLARE @program varchar (75);
DECLARE @host varchar (25);

SET @waittime = 300000

select @blockcount = count(*)  
from master..sysprocesses
where blocked != 0 and waittime > @waittime 

select @leadspid = spid,
@dbname = db_name(dbid) 
from master..sysprocesses
where (blocked = 0 and spid in (select blocked from master..sysprocesses))

select @host = host_name, @Program = Program_Name from sys.dm_exec_sessions
where session_id = @leadspid

select @AlreadyAlerted = count(*) from DB_Monitoring..Blocking_Log
where SPID = @leadspid and Host_name = @host and Program_Name = @Program
and Timestamp > dateadd(hour, -1, GETDATE())

if @AlreadyAlerted = 0 and @blockcount > 0 
BEGIN
Insert into DB_Monitoring..Blocking_log (SPID,Host_Name,Program_Name,Timestamp) values (@leadspid, @host, @program, getdate())



select @subj =  ''Blocking Alert: '' + @@servername + '' on Database '' + @dbname + '' (Lead Blocker SPID '' + convert(varchar(4), @leadspid) + '')''


EXEC msdb.dbo.sp_send_dbmail 
    @profile_name = ''Mail_Profile'',
    @recipients = ''abc@company.co.uk'',
    @file_attachments = ''E:\Backup\Lock_Monitoring\locks_trace.log'',
    @subject = @subj,
    @body = ''See attached file for Lock Information'',
    @body_format = ''TEXT''

END', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'continiuos', 
        @enabled=1, 
        @freq_type=4, 
        @freq_interval=1, 
        @freq_subday_type=4, 
        @freq_subday_interval=3, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=0, 
        @active_start_date=20090930, 
        @active_end_date=99991231, 
        @active_start_time=0, 
        @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO


Rename a Single Instance Oracle Database

Renaming a single instance db that uses a pfile
———————————————–
———————————————–

-Shutdown db (MOHSINDB)
————————-
export ORACLE_SID=MOHSINDB
sqlplus / as sysdba
SELECT dbid, name FROM v$database;
shutdown immediate

-Backup
——
stop the backup cron job on abc.def.company.net for MOHSINDB (there isnt one. so dont bother)

################################################################
Make sure the db is backed up using NetApps Snaps, ask networks to create a pre-rename snap
################################################################

–rename db using nid (nid updates db file headers. but does not touch the physical location of db files)
———————
#### In a xterm window:
export ORACLE_SID=MOHSINDB
sqlplus / as sysdba
startup mount;
exit

nid TARGET=SYS/password@MOHSINDB DBNAME=NEWNAME
#### nid automatically shutdown the database once completed

–create the directory structure required for the new dbname
————————————————————
################################################################
Ask SysAdmin to:
umount /oradata/MOHSINDB, /oraarch/MOHSINDB
change /oradata/MOHSINDB and /oraarch/MOHSINDB to /oradata/NEWNAME and /oraarch/NEWNAME in /etc/fstab
mount /oradata/NEWNAME, /oraarch/NEWNAME
##########################################################################################################

cd /oradata/NEWNAME
mv MOHSINDB NEWNAME

mkdir /u01/app/oracle/admin/NEWNAME
mkdir /u01/app/oracle/admin/NEWNAME/bdump
mkdir /u01/app/oracle/admin/NEWNAME/cdump
mkdir /u01/app/oracle/admin/NEWNAME/udump
mkdir /u01/app/oracle/admin/NEWNAME/adump

copy and modify pfile
———————
cd $ORACLE_HOME/dbs
cp $ORACLE_HOME/dbs/initMOHSINDB.ora $ORACLE_HOME/dbs/initNEWNAME.ora
vi initNEWNAME.ora
%s/MOHSINDB/NEWNAME/g
%s/SRCH2/NEWNAME/g

–change tns entry
——————-
vi $ORACLE_HOME/network/admin/tnsnames.ora
%s/MOHSINDB/NEWNAME/g

–create the password file
————————–
export ORACLE_SID=NEWNAME
orapwd file=’$ORACLE_HOME/dbs/orapwNEWNAME’ password=<pwd>

— relocate data files
———————–
sqlplus / as sysdba
startup mount;

–rename the files using the script generated by sql
select ‘alter database rename file ‘ || chr(39) || name || chr(39) || ‘ to ‘
|| chr(39) || regexp_replace(name, ‘MOHSINDB’ , ‘NEWNAME’) || chr(39) || ‘;’
as datafile_path from v$datafile;

–Rename log files:
select ‘alter database rename file ‘ || chr(39) || member || chr(39) || ‘ to ‘
|| chr(39) || regexp_replace(member, ‘MOHSINDB’ , ‘NEWNAME’) || chr(39) || ‘;’
as datafile_path from v$logfile;

alter database open resetlogs;
ALTER DATABASE RENAME GLOBAL_NAME TO NEWNAME;

create temporary tablespace TEMP3
tempfile ‘/oradata/NEWNAME/NEWNAME/temp03.dbf’
size 100M AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

alter database default temporary tablespace TEMP3;

drop tablespace TEMP1 including contents and datafile;
drop tablespace TEMP2 including contents and datafile;

exit

–start the db with new pfile and create the spfile
——————————————————
sqlplus / as sysdba
create spfile = ‘$ORACLE_HOME/dbs/spfileNEWNAME.ora’  from pfile = ‘$ORACLE_HOME/dbs/initNEWNAME.ora’;
shutdown immediate
startup
exit;

-Backup
——
#########################################################################################################
Make sure the db is backed up using NetApps Snaps, ask networks to create a post-rename snap
Keep snaps for few days then ask Networks to delete
#########################################################################################################

#########################################################################################################
Unregister mviews and re register them and do a complete refresh wherever required
#########################################################################################################

remove any old spfile, pfile and pwdfile
——————————————-
rm /oradata/MOHSINDB/MOHSINDB/orapwMOHSINDB
rm initMOHSINDB.ora

rm -r /u01/app/oracle/admin/MOHSINDB/bdump
rm -r /u01/app/oracle/admin/MOHSINDB/cdump
rm -r /u01/app/oracle/admin/MOHSINDB/udump
rm -r /u01/app/oracle/admin/MOHSINDB/adump
rm -r /u01/app/oracle/admin/MOHSINDB

–tns entries on other dbs
change tns entry on Other database to NEWNAME

Query(s) for lock investigation – Drilldown per schema/sid/object_name

In Case Toad or session browser is not accessible J

SELECT /*+ RULE */
       lk.SID, se.username, se.osuser, se.machine,
       DECODE (lk.TYPE,
               ‘TX’, ‘Transaction’,
               ‘TM’, ‘DML’,
               ‘UL’, ‘PL/SQL User Lock’,
               lk.TYPE
              ) lock_type,
       DECODE (lk.lmode,
               0, ‘None’,
               1, ‘Null’,
               2, ‘Row-S (SS)’,
               3, ‘Row-X (SX)’,
               4, ‘Share’,
               5, ‘S/Row-X (SSX)’,
               6, ‘Exclusive’,
               TO_CHAR (lk.lmode)
              ) mode_held,
       DECODE (lk.request,
               0, ‘None’,
               1, ‘Null’,
               2, ‘Row-S (SS)’,
               3, ‘Row-X (SX)’,
               4, ‘Share’,
               5, ‘S/Row-X (SSX)’,
               6, ‘Exclusive’,
               TO_CHAR (lk.request)
              ) mode_requested,
       TO_CHAR (lk.id1) lock_id1, TO_CHAR (lk.id2) lock_id2, ob.owner,
       ob.object_type, ob.object_name,
       DECODE (lk.BLOCK, 0, ‘No’, 1, ‘Yes’, 2, ‘Global’) BLOCK, se.lockwait
  FROM v$lock lk, dba_objects ob, v$session se
 WHERE lk.TYPE IN (‘TM’, ‘UL’)
   AND lk.SID = se.SID
   AND lk.id1 = ob.object_id(+)
   AND se.username = ‘ATD_NL_HORIZON’;
  –AND (lk.SID = ‘452’);
–and ob.object_name = ‘ACCESSORY’;