Oracle DBA Daily Experiences

DECLARE @role_name VARCHAR(100)
DECLARE @member_name VARCHAR(100)
DECLARE @rolescur CURSOR
DECLARE @rolememberscur CURSOR

DECLARE @loginname VARCHAR(100)
DECLARE @username VARCHAR(100)
DECLARE @logincur CURSOR

PRINT '– Grant user access'
SET @logincur = CURSOR FOR SELECT l.name, u.name
FROM master..sysxlogins l, sysusers u
WHERE l.sid = u.sid AND l.name <> 'sa'
OPEN @logincur
FETCH NEXT FROM @logincur INTO @loginname, @username
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'EXEC sp_grantdbaccess ''' + @loginname + ''', ''' + @username + ''''
FETCH NEXT FROM @logincur INTO @loginname, @username
END
CLOSE @logincur
DEALLOCATE @logincur

— cursor for roles (user and built-in)
SET @rolescur = CURSOR FOR SELECT name FROM dbo.sysusers WHERE ([issqlrole] = 1 OR [isapprole] = 1) AND name <> 'db_owner'

— loop through roles
OPEN @rolescur
FETCH NEXT FROM @rolescur INTO @role_name
WHILE @@FETCH_STATUS = 0
BEGIN

PRINT '— ' + @role_name
— cursor for members in the roles
SET @rolememberscur = CURSOR FOR SELECT u.name
from sysusers u, sysusers g, sysmembers m
where g.name = @role_name
and g.uid = m.groupuid
and g.issqlrole = 1
and u.uid = m.memberuid

OPEN @rolememberscur
FETCH NEXT FROM @rolememberscur INTO @member_name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'exec sp_addrolemember ''' + @role_name + ''', ''' + @member_name + ''''
FETCH NEXT FROM @rolememberscur INTO @member_name
END
CLOSE @rolememberscur
DEALLOCATE @rolememberscur

— PRINT @role_name
FETCH NEXT FROM @rolescur INTO @role_name
END

CLOSE @rolescur
DEALLOCATE @rolescur

If one needs to restore archive logs to disk and not apply them, for example in case when cloning a database manually and one is missing the archive logs from the source database and you need to restore the archive logs on the source machine. So using the following command one can restore the archive logs.

connect target /
connect rcvcat rmancatalog/rmancatalog@catdb

run {
restore archivelog from logseq=36747 until logseq=36753 thread=1;
}
from logseq – is the starting log sequence #
until logseq – is the end log sequence #

# Using ls and sort one can sort the files by their size, here is what the options means
# ls -l – means print the file in long format, prints access, woner, group, size, last update and name
# sort -n -k5 – sort using numeric, -k5 field number to sort by
$ ls -l | sort -n -k5