• Database Overview - from idevelopment online version
• Extract ddl with "dbms metadata.get ddl"
• Connecting as another user via proxy in SQL*Plus
• Password cracker and role and priv tools from Pete Finnigan
• Using pivot tables in SQL - XML and XLS (Excel) also)

Sounds simple enough and indeed pre 12.1, is was as easy as either

0@@

or this one is safer in case you have an underscore character in your SID name!!

1@@

but now we have the added complication of CDB/PDB (container / pluggable databases) so a fair bit more work is needed!

2@@

Very handy if you have a global tnsnames.ora accessible to all clients.<br /> So from a client install, this has access to all databases.<br /> Any files created can be created on the client so no need to scp to all the machines to recover spool files etc.<br /> Downside: Cannot connect as sysdba across tns so password will need to be known for whatever user is used.

3@@

4@@

5@@

It can be used to execute any sql command as a specified user provided this procedure is created in sys schema.<br />

• not got this working properly yet *

6@@

Can run an SQL command file on all SID/SERVICE found in a tnsnames.ora file. I use this from a client laptop having a global tns file on it.<br /> This way it's not necessary to copy the file onto every host before executing it. Any files created by the script will be created on the client.

7@@

set lines 200 set pages 200 col obj format a40 select owner||'.'||object_name obj , object_type from dba_objects where 1=1 and status = 'INVALID';

@?/rdbms/admin/utlrp.sql

8@@

or

9@@

10@@

DBA_AQ_AGENT_PRIVS DBA_COL_PRIVS DBA_PRIV_AUDIT_OPTS DBA_REPGROUP_PRIVILEGES DBA_ROLE_PRIVS DBA_RSRC_CONSUMER_GROUP_PRIVS DBA_RSRC_MANAGER_SYSTEM_PRIVS DBA_SYS_PRIVS DBA_TAB_PRIVS DBA_WM_SYS_PRIVS DBA_WORKSPACE_PRIVS

begin

   dbms_scheduler.create_program ( program_name   => 'myshelljob'
                                 , program_action => '/home/oracle/scripts/shell.sh'
                                 , program_type   => 'EXECUTABLE'
                                 , comments       => 'Run a shell'
                                 , enabled        => TRUE
                                 );

end; /

begin

   dbms_sheduler.create_job ( job_name     => 'myshelljob'
                            , program_name => 'myshelljob'
                            , start_date   =>
                            );

/

11@@

Useful to see what is happening when process limit is reached

12@@

Mine all SQL statements in a 10 minute period

13@@

14@@

15@@

16@@

or attach to the impdp job and it shows percentage done.

17@@

18@@

A companion script for 'how many log switches in a day'

19@@

Unable to connect to database with sqlplus / as sysdba because audit file cannot be created?

• tech.e2sn.com
• blog.dbi-services.com

Instead of just killing the pmon process, there is a kinder (and more useful) way to kill the database.<br /> Use the hidden, undocumented 'prelim' switch parameter to sqlplus to make a connection to the database without creating SGA session data structures.<br /> It can be very useful for hang analysis so the possible cause can be found after the database has been restarted.

20@@

disconnect attempts to kill the o/s processes too. kill doesn't.

21@@

22@@

23@@

Check to see if any ordinary users have privileges / access / grants / rights they should not have

24@@

25@@

table_name is the name of the directory…

26@@

table_name is the name of the package…

27@@

Maybe this one is better Extract ddl with "dbms metadata.get ddl"

SELECT LPAD(' ', 2*level) || granted_role “USER PRIVS” FROM (

 SELECT NULL grantee,  username granted_role
 FROM dba_users
 WHERE username LIKE UPPER('%&uname%')
 UNION
 SELECT grantee, granted_role
 FROM dba_role_privs
 UNION
 SELECT grantee, privilege
 FROM dba_sys_privs)

START WITH grantee IS NULL CONNECT BY grantee = prior granted_role;

or

SELECT path FROM (

 SELECT grantee,
        sys_connect_by_path(privilege, ':')||':'||grantee path
 FROM (
   SELECT grantee, privilege, 0 role
   FROM dba_sys_privs
   UNION ALL
   SELECT grantee, granted_role, 1 role
   FROM dba_role_privs)
 CONNECT BY privilege=prior grantee
 START WITH role = 0)

WHERE grantee IN (

  SELECT username
  FROM dba_users
  WHERE lock_date IS NULL
  AND password != 'EXTERNAL'
  AND username != 'SYS')

OR grantee='PUBLIC'

or

for a migration from 10g…

28@@

or for a migration on 11g…!

29@@

• Moving a datafile from File System to ASM (does not have to be ASM!) - oracle.com
• Moving Data Files Between Oracle ASM (does not have to be ASM!) Using RMAN - oracle.com
• Moving a data file online between filesystems - oracle-base.com

30@@

31@@

32@@

33@@

34@@

35@@

If you're lucky enough to be on 12c, it's one command - no need to offline the datafile!

36@@

Trick? Just use ARGV to pass them in…

37@@

or

38@@

or export the variable from shell and access it via the ENV hash

39@@

40@@

or to convert a specific day of the year to epoch seconds

41@@

or for a specific date (careful though - month is 0-11 not 1-12!…<br />

42@@

or more long-winded but delimited by anything…<br /> Supply the date/time in any delimited format. Eg. YYYY-MM-DD:HH:MI

43@@

Paste this code into epoch.vbs

44@@

and call from a DOS box like this

45@@

46@@

47@@

48@@

49@@

50@@

You cannot just use sysdate as that is timezone sensitive… (epoch seconds are in UTC/GMT)

51@@

How long has this instance been up (in minutes)?

52@@

53@@

exec plato.help; or exec plato.complete('HTM');

54@@

55@@

56@@

57@@

or, as tablespaces can have block sizes differing from that of the init.ora parameter…

58@@

datafiles

59@@

tempfiles

60@@

Found a more concise, smarter way of doing it here - oracle-wiki.net<br /> Switching logs too often? Redolog files too small?<br />

• Show current log groups

61@@

• Create additional (bigger) groups

62@@

or if you use multiple members…

63@@

• Switch archivelogs until the new ones are current and the old ones are inactive

64@@

If the old logs are not yet inactive, checkpoint the database

65@@

• Drop the old groups

66@@

67@@

68@@

Temporary tablespace is used for sorting query results. Find them with this.

69@@

Although this can be set lower than existing size, try not to. It makes the reports look weird!

70@@

Indexes could be a notable exception if they are created in an index tablespace

71@@

'unlimited' will default to the maximum datafile size for the current db_block_size

72@@

73@@

There are different interpretations of what constitutes the size of a database…<br /> Is it the space allocated to the datafiles or only the space taken up by the data? Are tempfiles included? Are redo logs included? etc…

74@@

Database is completely stuck because the temporary tablespace has grown to fill all available room.<br /> Look around for at least some log files to delete in order to get connected (audit files, sqlnet logs, listener logs)<br /> Once connected, change the default temporary tablespace to a small one so we can remove the original.

75@@

76@@

I found trying to drop the datafile like this didn't work, just hung there (probably active tranactions)…

77@@

So did it like this…

78@@

… an alternative would be to find the sessions using the temp space and kill them…

79@@

Then to be neat and tidy, rename the temp2 tablespace back to temp.

80@@

Although the last statement will not work until any transactions using it have been rolled back or committed.

set lines 100 set pages 100 col name format a60

select df.name , b.status , to_char(time, 'hh24:mi:ss dd/mm/yyyy') time from v$datafile df , v$backup b where 1=1 and df.file# = b.file# and b.status = 'ACTIVE' order by b.file#;

Script to identify sessions causing RMAN to hang because of 'waiting for snapshot control file enqueue' message

81@@

82@@

session doing the blocking

83@@

sessions being blocked

84@@

info on session doing the blocking

85@@

set lines 100 set pages 999 col username format a20 col sess_id format a10 col object format a25 col mode_held format a10 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 1=1 and v.object_id = d.object_id and v.object_id = l.id1 and v.session_id = s.sid order by oracle_username , session_id;

select do.object_name , row_wait_obj# , row_wait_file# , row_wait_block# , row_wait_row# , dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#) from v$session s , dba_objects do where 1=1 and s.ROW_WAIT_OBJ# = do.OBJECT_ID and sid = &sid;

86@@

87@@

88@@

89@@

90@@

Using awk

91@@

Using perl

92@@

93@@

select arch.thread# “Thread” , arch.sequence# “Last Sequence Received” , appl.sequence# “Last Sequence Applied” , (arch.sequence# - appl.sequence#) “Difference” from (

      select thread#
      ,      sequence#
      from   v$archived_log
      where  1=1
      and    ( thread#,first_time ) in (
                                       select thread#, max(first_time)
                                       from   v$archived_log
                                       group  by thread#
                                       )
      ) arch
      ,
      (
      select thread#
      ,      sequence#
      from   v$log_history
      where  1=1
      and    ( thread#,first_time ) in ( select thread# ,max(first_time)
                                         from   v$log_history
                                         group  by thread#
                                        )
      ) appl

where 1=1 and arch.thread# = appl.thread# order by 1;

Run this on the standby

94@@

or run this on the Primary to see the latest generated archive…

95@@

and run this on the standby to check for received but not yet applied logs (this could be normal if a lag has been set)…

96@@

Compare the results from Primary and Secondary to detect possible network problems or other errors in shipping.

97@@

Modified to use (the better) scripts found on Rene Nyffenegger's site<br /> This script generates the required DDL for the alertlog table

98@@

This reads the log.xml file (which contains the same information as alert.log)<br /> Show the messages put in the alert log in the last 24 hours

99@@

There is also a fixed table X$DBGDIREXT which returns all file and directory names under diagnostic_dest/diag directory:

100@@

101@@

102@@

…but rather than copy the terabytes of data, create the destination tablespace with just the most recent months data<br />

103@@

This query produces something like this…

104@@

105@@

produces…

106@@

107@@

108@@

produces…

109@@

110@@

111@@

112@@

113@@

114@@

115@@

and read from it using netcat in another session

116@@

produces

117@@

Some things have to be done as the user and cannot be done as sys (eg. creating and dropping jobs or create and drop a database link)<br /> It is possible to login as the user by saving, changing and resetting his password (using identified by values) but using dbms_sys_sql is another option<br /> Create a PL/SQL procedure to drop a db link

118@@

Generate the calls to the procedure with the required parameters

119@@

and this reveals the lines to be executed

120@@

Drop the procedure when finished with it

121@@

or do it as an anonymous block in one go…

122@@

123@@

124@@

125@@

Test: Why is Julian day 0, 17th November 1858?<br /> Pretty comprehensive answer: here and here<br /> Also attempts to answer the question why typing 'cal 09 1752' on Unix systems looks so odd!

126@@

Can be used to save user passwords for example in a test environment before destroying them with a copy of production.<br /> Simple, quick and easy version. Run this then pick and choose the statements you want to run.

127@@

More industrialised version. Create a package.

128@@

Call it

129@@

A fine example of how to write scripts from SQL*Plus that interact with the shell underneath<br /> Found here at andreynikolaev.wordpress.com

130@@

Alert Log scraping with ADRCI

131@@

or …this will work with any OS (so long as the process is at the end of the <strong>ps -ef</strong> or <strong>ps -aux</strong> listing

132@@

Pick the columns you want in the ps listing

133@@

134@@

135@@

136@@

or

137@@

Using fixed line numbers

138@@

Using line with a /pattern/ on it

139@@

140@@

141@@

*Database Export - from DBA Oracle (Burleson)

142@@

http://stackoverflow.com/questions/12605415/parsing-tnsnames-ora-with-regex-to-just-get-name from here

143@@

144@@