New pages
Jump to navigation
Jump to search
(newest | oldest) View (newer 50 | older 50) (20 | 50 | 100 | 250 | 500)
- 23:33, 8 November 2019 Monitoring RMAN Job Progress (hist) [295 bytes] Admin (talk | contribs) (Created page with "<blockquote><pre> SELECT sid , serial# , context , sofar , totalwork , round(sofar/totalwork*100,2) "%_Complete" FROM v$session_longops WHERE opnam...")
- 23:33, 8 November 2019 RMAN Archivelog Deletions (hist) [263 bytes] Admin (talk | contribs) (Created page with "<blockquote><pre> rman nocatalog<< DATA connect target delete noprompt archivelog until time 'SYSDATE-n'; DATA </pre></blockquote> <blockquote><pre> rman nocatalog<< DATA con...")
- 23:33, 8 November 2019 Flashback Table (10g) (hist) [2,352 bytes] Admin (talk | contribs) (Created page with "The ability to flashback transactions to a point in time is quite a handy feature. Here is how it all works .... First of all, the oracle init.ora parameter db_flashback_ret...")
- 23:32, 8 November 2019 Flashback Schema (10g) (hist) [873 bytes] Admin (talk | contribs) (Created page with "Technically there is no schema level flashback feature available. However, provided your recovery period does not include any data dictionary changes DDL) then you could actua...")
- 23:32, 8 November 2019 Flashback Row (10g) (hist) [2,586 bytes] Admin (talk | contribs) (Created page with "The ability to flashback transactions to a point in time is quite a handy feature. Here is how it all works .... First of all, the oracle init.ora parameter db_flashback_rete...")
- 23:31, 8 November 2019 Check Space Fast Recovery Area (hist) [363 bytes] Admin (talk | contribs) (Created page with "<blockquote><pre> SELECT name , floor(space_limit/1024/1024) "Size_MB" , ceil(space_used/1024/1024) "Used_MB" , floor(space_limit/1024/1024) - ceil(space_used/1...")
- 23:30, 8 November 2019 RMAN - Point in Time Duplicate (11.2.0.2.0 (hist) [1,264 bytes] Admin (talk | contribs) (Created page with "I've spent weeks struggling with a point-in-time rman duplicate where for some obscure reason the duplicate would fail to apply point-in-time logs after midnight. FINALLY -...")
- 23:29, 8 November 2019 RMAN - Diagnosing Failures (11g) (hist) [219 bytes] Admin (talk | contribs) (Created page with "The commands you can use through RMAN to list, seek advice on and in some cases repair Database Failures .. <blockquote><pre> RMAN> list failure [detail]; RMAN> advise failur...")
- 23:28, 8 November 2019 Automatic Guaranteed Restore Point (11g) (hist) [1,365 bytes] Admin (talk | contribs) (Created page with "-- Script to schedule the creation of a guaranteed restore point on a database -- and to remove the oldest guaranteed restore point when there are more than 2 -- restore point...")
- 23:27, 8 November 2019 Restore in between Upgrades (hist) [3,866 bytes] Admin (talk | contribs) (Created page with "'''Requirement''' Had to restore a database to a point-in-time from a hotbackup. Followed Steps: * Copied datafiles to target * Created a New Controlfile * Recovered using...")
- 23:27, 8 November 2019 Snapshot Standby Database (11g) (hist) [4,939 bytes] Admin (talk | contribs) (Created page with "'''Introduction''' <br> One of the best new features of Oracle 11g is the introduction of Snapshot Standby Databases. This feature allows you to take a normal Physical Standb...")
- 23:26, 8 November 2019 Recovery-Tasks (hist) [1,915 bytes] Admin (talk | contribs) (Created page with "=== Database Restores/Refreshes/Recovery === Snapshot Standby Database (11g) Restore in between Upgrades | Restore in between Upgrades...")
- 23:09, 8 November 2019 Database Size (2) (hist) [161 bytes] Admin (talk | contribs) (Created page with "<blockquote><pre> SELECT TARGET_NAME , ROUND (SUM (FILE_SIZE) / 1024 / 1024 / 1024) GB FROM MGMT$db_datafiles_all GROUP BY TARGET_NAME </pre></blockquote>")
- 23:09, 8 November 2019 Database Size (1) (hist) [1,111 bytes] Admin (talk | contribs) (Created page with "<blockquote><pre> select a.DTTM , trim(substr(a.target_name, instr(a.target_name,'.')+1,20)) HOST_NAME , trim(substr(a.target_name, 1, instr(a.target_name,'.')-1))...")
- 23:08, 8 November 2019 Clearing Alerts (hist) [421 bytes] Admin (talk | contribs) (Created page with "<blockquote><pre> SELECT 'execute em_severity.delete_current_severity(p_target_guid =>'''||sev.target_guid||''', p_metric_guid =>'''||sev.metric_guid||''' , p_key_value...")
- 23:08, 8 November 2019 Datafile Size (hist) [345 bytes] Admin (talk | contribs) (Created page with "In my case, I used the following scripts to list the datafiles incorrectly set to unlimited, i.e. growing to 32768M. <blockquote><pre> SELECT target_name , file_name...")
- 23:07, 8 November 2019 Tablespace Allocated and Used (hist) [844 bytes] Admin (talk | contribs) (Created page with "<blockquote><pre> SELECT A.RT , A.ALLOCATED , B.USED FROM (SELECT TO_CHAR(ROLLUP_TIMESTAMP,'YYYY-MM') RT , AVG(AVERAGE) ALLOCATED FROM MGMT$...")
- 23:07, 8 November 2019 User Defined Metrics (hist) [287 bytes] Admin (talk | contribs) (Created page with "<blockquote><pre> SELECT * FROM mgmt$metric_current WHERE metric_name like '%UDM%'; </pre></blockquote> <blockquote><pre> SELECT * FROM mgmt$metric_hourly WHERE target...")
- 23:06, 8 November 2019 List DBs on AIX (hist) [1,173 bytes] Admin (talk | contribs) (Created page with "<blockquote><pre> select target_name , host_name from mgmt$target where target_type='oracle_database' and host_name in (select target_name...")
- 23:06, 8 November 2019 UDM Collection Interval (hist) [581 bytes] Admin (talk | contribs) (Created page with "You may have noticed that User Defined Metrics (UDM) in Oracle Enterprise Manager (OEM) have a minimum collection time of '''5''' minutes. If you wish to change this interva...")
- 23:04, 8 November 2019 High Level Steps to Migrate Datafiles (Disk Groups) (hist) [2,038 bytes] Admin (talk | contribs) (Created page with "Just some notes for my own reference. Had to migrate all my datafiles on a database from one Disk Group (DATA) to Another (DATAR), using RMAN to copy the datafiles. '''Pre-...")
- 23:03, 8 November 2019 Check Disk Groups (hist) [108 bytes] Admin (talk | contribs) (Created page with "<blockquote><pre> SELECT NAME, HEADER_STATUS, PATH, OS_MB FROM V$ASM_DISK ORDER BY NAME; </pre></blockquote>")
- 23:01, 8 November 2019 DBLinks not in Use (hist) [264 bytes] Admin (talk | contribs) (Created page with "<pre> select db_link from dba_db_links where db_link not in (select db_link from dba_db_links where db_link in (select referen...")
- 23:00, 8 November 2019 Trigger to Startup Pluggable Databases and Services (hist) [1,196 bytes] Admin (talk | contribs) (Created page with "The following trigger starts up all pluggable databases once the container database has been started and calls a stored procedure which starts up services which have been set...")
- 23:00, 8 November 2019 Dynamically Create/Drop Restore Points (hist) [1,260 bytes] Admin (talk | contribs) (Created page with "Task Objective: To create a Guaranteed Restore Point Dynamically and remove the earlier Restore Point if more than 2 exist. <blockquote><pre> set serveroutput on declare v_k...")
- 22:58, 8 November 2019 Enable Resumable (hist) [2,781 bytes] Admin (talk | contribs) (Created page with "As a DBA one thing I've seen quite a few times is tablespaces being maxed out by large data loads, which in turn leads to the data load being rolled back and developers having...")
- 22:58, 8 November 2019 Lookup v$ Views (hist) [549 bytes] Admin (talk | contribs) (Created page with "It's often difficult to remember which v$ view contains information you might be after. One way of quickly looking up the various views, is to run a query on the v$fixed_tabl...")
- 22:57, 8 November 2019 Create User Script (hist) [1,481 bytes] Admin (talk | contribs) (Created page with "There are many ways of cloning a user account or two from one database to another. Here are a few simple SQLs to dynamically generate scripts from the source database, which c...")
- 22:57, 8 November 2019 Sys Aux Statistics (hist) [376 bytes] Admin (talk | contribs) (Created page with "Note to-self: Hardware Stats are stored in <blockquote><pre> SYS.AUX_STATS$ </pre></blockquote> To gather the statistics: <blockquote><pre> exec dbms_stats.gather_system_st...")
- 22:56, 8 November 2019 Process Details with SQL Text (hist) [965 bytes] Admin (talk | contribs) (Created page with "<blockquote><pre> SELECT s.sid , p.pid , p.spid , s.process , s.status , s.username , s.osuser , s.program , a.name , s.client_inf...")
- 22:56, 8 November 2019 Logon Audit Trigger (hist) [1,557 bytes] Admin (talk | contribs) (Created page with "This is my version of a logon audit trigger. It picks up every iteration of a logon, even to the point of when a session switches from ACTIVE/INACTIVE mode. The more convent...")
- 22:55, 8 November 2019 Object with Block Corruption (hist) [722 bytes] Admin (talk | contribs) (Created page with "Identifying which object is corrupt in a block corruption .... ORA-01578: ORACLE data block corrupted (file # 54, block # 18689) <pre> select * from dba_extents where fil...")
- 22:55, 8 November 2019 Check Generated Archivelog Sizes (hist) [854 bytes] Admin (talk | contribs) (Created page with "Script may or may not work for everyone. The difficulty with this one is that it has to get the archivelog sizes from v$archived_log which could contain duplicate rows as ind...")
- 22:55, 8 November 2019 Check Database Size (hist) [295 bytes] Admin (talk | contribs) (Created page with "<blockquote><pre> SELECT sum(megabytes) FROM ( SELECT sum(bytes)/1024/1024 megabytes FROM dba_temp_files UNION SELECT sum(bytes)/1024/1024 megabytes FROM...")
- 22:54, 8 November 2019 Check Oracle Open Cursors by Session (hist) [236 bytes] Admin (talk | contribs) (Created page with "<blockquote><pre> select o.sid, o.sql_text, o.address, o.hash_value, o.user_name, s.schemaname, o.sql_id from v$open_cursor o, v$session s where o.saddr = s.saddr a...")
- 22:53, 8 November 2019 Check Oracle Blocking Locks (hist) [752 bytes] Admin (talk | contribs) (Created page with "<blockquote><pre> SELECT to_char(sysdate,'yyyy-mm-dd hh24:mi') dttm , ' --- BLOCKER --- ' , lo.session_id , lo.process , lo.os_user_name , dob.object...")
- 22:52, 8 November 2019 Check Temp Tablespace Usage (hist) [300 bytes] Admin (talk | contribs) (Created page with "<blockquote><pre> SELECT distinct s.username , s.sid , s.serial# , s.osuser , u.tablespace , u.contents , u.segtype , u.extents , u.blo...")
- 22:52, 8 November 2019 Check Undo Tablespace Usage (hist) [109 bytes] Admin (talk | contribs) (Created page with "<blockquote><pre> SELECT sum(blocks) , sum(bytes)/1024/1024 FROM dba_undo_extents; </pre></blockquote>")
- 22:51, 8 November 2019 Check Database UpTime in Unix (hist) [56 bytes] Admin (talk | contribs) (Created page with "<blockquote><pre> ps -ef | grep pmon </pre></blockquote>")
- 22:51, 8 November 2019 Scripts to monitor database is up (hist) [3,555 bytes] Admin (talk | contribs) (Created page with "Scenario is I do not have access to Oracle Enterprise Manager or any other monitoring utility and I need to make sure the database is up. Simply checking pmon is not enough,...")
- 22:05, 8 November 2019 Administrative-Tasks (hist) [1,919 bytes] Admin (talk | contribs) (Created page with " Scripts to monitor database is up Check Database UpTime in Unix Check Undo Tablespace Usage | C...")
- 22:02, 8 November 2019 Linux Filesystem Alerting Script (hist) [5,788 bytes] Admin (talk | contribs) (Created page with "This convoluted beauty does a df -h on both a Production and Standby Filesystem (oracle datafile and backup directories) and warns when the threshold of 85% is exceeded and al...")
- 22:01, 8 November 2019 SQL Across Multiple Servers with Password Prompts (hist) [791 bytes] Admin (talk | contribs) (Created page with "An example of a shell script which connects to multiple unix hosted databases and runs a simple SQL. This version prompts for the username/password. <blockquote><pre> #!/bin...")
- 22:01, 8 November 2019 SQL Across Multiple Servers (hist) [601 bytes] Admin (talk | contribs) (Created page with "An example of a shell script which connects to multiple unix hosted databases and runs a simple SQL. This version emails the logfile to people on the mailing list. <blockquot...")
- 21:59, 8 November 2019 Load Multiple (over 100,000) Files (hist) [1,853 bytes] Admin (talk | contribs) (Created page with "Requirement is to load 100,000+ CSV files via SQL Loader. Files are located in an Unix directory. Initially attempted to load the files through a single controlfile and it a...")
- 21:57, 8 November 2019 Change Apex 4.0 Admin Password (hist) [1,524 bytes] Admin (talk | contribs) (Created page with "To change the Admin user password on Apex 4.0 run the script '''apxxepwd.sql'''. This will change the encrypted password stored in table apex_040000.wwv_flow_fnd_user. '''Th...")
- 21:57, 8 November 2019 Check last DML time - if available (hist) [98 bytes] Admin (talk | contribs) (Created page with "<blockquote><pre>select scn_to_timestamp(max(ora_rowscn)) from scott.employees;</pre></blockquote>")
- 21:56, 8 November 2019 CHANGE text on the current line (hist) [907 bytes] Admin (talk | contribs) (Created page with "A very basic feature of SQL *Plus which I had forgotten through the passage of time. Documented here so I don't forget again .. You use this to change values on the current...")
- 21:55, 8 November 2019 The glogin.sql File (hist) [672 bytes] Admin (talk | contribs) (Created page with "Whenever SQL *Plus starts up, it looks for a file named glogin.sql which is usually stored under $ORACLE_HOME/sqlplus/admin. You can modify this file to store settings, like...")
- 21:55, 8 November 2019 Create Table As (hist) [197 bytes] Admin (talk | contribs) (Created page with "To create a table identical to another table, yet without any of the data, simply use the following syntax: <blockquote><pre> CREATE tableA AS (SELECT * FROM tableB WHERE 1=2...")