Query to run Dynamic SQL Using XML on SQL> prompt

Query to run Dynamic SQL Using XML on SQL> prompt

SELECT table_name, extractvalue(dbms_xmlgen.getxmltype('select count(*) from ' || table_name),'//text()')
FROM user_Tables
WHERE table_name LIKE 'A%'

The query above is just sample. Put your query in getxmltype and you are all set.

vi ... How to Refresh Already Open File

Refresh file from version on disk:
:e!

Awk Script to Find Start Time End Time and Diff

[0]/ely =>cat f
awk '/Started Lighting for / { bid = toupper(substr($4, 1, 10)) ; start_time[bid] = $6}
/Lighting Successful at|Failed at / { bid = toupper(substr($1, 1, 10))
split(start_time[bid], st, ":")
if ($3 ~ "Successful")
{
end_time[bid] = $5
success_fail = "Success"
}
else
{
end_time[bid] = $4
success_fail = "Failed "
}
split(end_time[bid], et, ":")
start_sec = st[3] + st[2] * 60 + st[1] * 60 * 60
end_sec = et[3] + et[2] * 60 + et[1] * 60 * 60
total_time = end_sec - start_sec
hh = int(total_time / 3600)
rm = total_time % 3600
mm = int(rm / 60)
ss = rm % 60
printf("Bid: %s Start Time: %s %s at: %s Total Time: %02s:%02s:%02s \n", bid, start_time[bid], success_fail, end_time[bid], hh, mm, ss)
}' bb

[0]/ely =>cat bb
Started Lighting for x440019981O.iad at 16:33:31 (x440019981)
x440019981O Failed at 16:33:54 (Domestic)

Started Lighting for x250020161O.iad at 16:35:01 (x250020161)
x250020161O Lighting Successful at 16:35:43 (Domestic)

Started Lighting for x750016701O.iad at 16:38:12 (x750016701)
x750016701O Lighting Successful at 16:38:21 (Domestic)

Started Lighting for x260019961O.iad at 16:38:23 (x260019961)
x260019961O Lighting Successful at 16:38:50 (Domestic)

Started Lighting for x650019976O.iad at 16:40:05 (x650019976)
x650019976O Lighting Successful at 16:40:21 (Domestic)

Started Lighting for x360019763O.iad at 16:41:46 (x360019763)
x360019763O Lighting Successful at 16:42:26 (Domestic)

Started Lighting for x440019981O.iad at 16:44:27 (x440019981)
x440019981O Failed at 16:44:29 (Domestic)

Started Lighting for x370020017O.iad at 16:45:47 (x370020017)
x370020017O Lighting Successful at 16:46:06 (Domestic)

Started Lighting for x260019961O.iad at 16:46:48 (x260019961)
x260019961O Lighting Successful at 16:47:14 (Domestic)

Started Lighting for x920020035O.iad at 16:48:48 (x920020035)
x920020035O Lighting Successful at 16:49:09 (Domestic)

Started Lighting for x560019917O.iad at 16:53:20 (x560019917)
x560019917O Lighting Successful at 16:54:06 (Domestic)

Started Lighting for x030020010O.iad at 16:54:40 (x030020010)
x030020010O Lighting Successful at 16:55:38 (Domestic)

Started Lighting for x260019961O.iad at 16:55:41 (x260019961)
x260019961O Lighting Successful at 16:56:08 (Domestic)

Started Lighting for x460019558O.iad at 16:57:31 (x460019558)
x460019558O Lighting Successful at 16:57:43 (Domestic)

[0]/ely =>./f
Bid: X440019981 Start Time: 16:33:31 Failed at: 16:33:54 Total Time: 00:00:23
Bid: X250020161 Start Time: 16:35:01 Success at: 16:35:43 Total Time: 00:00:42
Bid: X750016701 Start Time: 16:38:12 Success at: 16:38:21 Total Time: 00:00:09
Bid: X260019961 Start Time: 16:38:23 Success at: 16:38:50 Total Time: 00:00:27
Bid: X650019976 Start Time: 16:40:05 Success at: 16:40:21 Total Time: 00:00:16
Bid: X360019763 Start Time: 16:41:46 Success at: 16:42:26 Total Time: 00:00:40
Bid: X440019981 Start Time: 16:44:27 Failed at: 16:44:29 Total Time: 00:00:02
Bid: X370020017 Start Time: 16:45:47 Success at: 16:46:06 Total Time: 00:00:19
Bid: X260019961 Start Time: 16:46:48 Success at: 16:47:14 Total Time: 00:00:26
Bid: X920020035 Start Time: 16:48:48 Success at: 16:49:09 Total Time: 00:00:21
Bid: X560019917 Start Time: 16:53:20 Success at: 16:54:06 Total Time: 00:00:46
Bid: X030020010 Start Time: 16:54:40 Success at: 16:55:38 Total Time: 00:00:58
Bid: X260019961 Start Time: 16:55:41 Success at: 16:56:08 Total Time: 00:00:27
Bid: X460019558 Start Time: 16:57:31 Success at: 16:57:43 Total Time: 00:00:12

Partitioning

Partitioning
Local Indexes
Index is partitioned for each partition. So one Index Partition will store index keys for only one partition
Global Partitioned Indexes
Partitioning Key for Index is independent of Partitioning Key for table. It can be applied to Regular table, Index Organized Tables, Partitioned Tables
Global Non Partitioned Indexes
It's regular non Partitioned Index. And can be applied to any table

Three Types of Basic Partitioning:
----------------------------------
Range : Partition For Feb 2010, Mar 2010, ...
List : Partition For say America, India, US, Russia
Hash : Partition using Hash Algorithm (I guess Oracle does not publish the algorithm)


Single Level Partitioning: Only one set of partitions
-------------------------
Composite Partitioning: Two level of partitions and can be combination of 3 basic
----------------------
paritioning type. Available composite partitioning techniques are range-hash, range-list,
range-range, list-range, list-list, and list-hash.

Partitioning Extension in Oracle 11g:
------------------------------------
Interval Partitioning: Define Interval and first partition. Oracle will create new partition when data is inserted for first time in new partition

REF Partitioning: Parent-Child Relationship: Child partitions will be created automatically based on parents partition and will have same charecteristics as parent partitions. In Child partitions, Oracle will not store index keys as data

Virtual Column Based Partitioning:
Paritioning based on metadata instead of column data. Let's say account number has first three digit as branch code, then we can have partitions for branch and account level data will go in respective branch partitions