Finding Mutual friends in Python

Lets take a List of Friends in Users as:

users=[
        {"id":0,"name": 'Saqib'},
        {"id":1,"name": 'Abid'},
        {"id":2,"name": 'Muza'},
        {"id":3,"name": 'Mustaq'},
        {"id":4,"name": 'Sai'},
        {"id":5,"name": 'Taha'},
        {"id":6,"name": 'Umair'}
        ]

We have 7 Names that have a unique ID as identifier. These can be friends with each other or may know some among themselves. Another List holds the relationship between these friends.

For example, the tuple (0, 1) indicates that with id 0 (Saqib) and with
id 1 (Abid) are friends.

friendship = [(0,1),(0,2),(0,3),(0,4),(0,5),(0,6),
              (1,2),(1,3),(1,4),(1,5),
              (2,3),(2,4),(2,5),
              (3,4),(5,6)]

Lets create a new List as User[“Friends”]. Set this to null.

for user in users:
    user["friends"] = []

Lets add the Friendship based on the Friendship List

# Adding friendhsip bi-directionally   
for i, j in friendship:
    users[i]["friends"].append(users[j]['name']) # add i as a friend of j
    users[j]["friends"].append(users[i]['name']) # add j as a friend of i  

Build the Mutual Friend list between Name and Friend exactly as per the friendship list.
Function to return mutual friends between two friends

def mutual(user, other_user):
   return list(set(users[user]["friends"]).intersection(set(users[other_user]["friends"])))
friend_stat = []
i=0
for user in users:
    userID = user["id"]
    for friend in user["friends"]:
        # Get ID of the Friend
        
        otherID = next(index for (index, d) in enumerate(users) if d["name"] == friend)
        if mutual(userID,otherID):
            mutuals = mutual(userID,otherID)
            friend_stat.append({ "id": i, "Name": user["name"], "Friend":str(friend),"Mutual":mutuals , "Mutual_Friend_Count" : len(mutuals)})
            i+=1

print friend_stat

[{'Friend': 'Abid',
  'Mutual': ['Taha', 'Muza', 'Sai', 'Mustaq'],
  'Mutual_Friend_Count': 4,
  'Name': 'Saqib',
  'id': 0},
 {'Friend': 'Muza',
  'Mutual': ['Taha', 'Sai', 'Abid', 'Mustaq'],
  'Mutual_Friend_Count': 4,
  'Name': 'Saqib',
  'id': 1},
 {'Friend': 'Mustaq',
  'Mutual': ['Muza', 'Sai', 'Abid'],
  'Mutual_Friend_Count': 3,
  'Name': 'Saqib',
  'id': 2},
 {'Friend': 'Sai',
  'Mutual': ['Muza', 'Abid', 'Mustaq'],
  'Mutual_Friend_Count': 3,
  'Name': 'Saqib',
  'id': 3},
 {'Friend': 'Taha',
  'Mutual': ['Muza', 'Umair', 'Abid'],
  'Mutual_Friend_Count': 3,
  'Name': 'Saqib',
  'id': 4},
 {'Friend': 'Umair',
  'Mutual': ['Taha'],
  'Mutual_Friend_Count': 1,
  'Name': 'Saqib',
  'id': 5},
 {'Friend': 'Saqib',
  'Mutual': ['Taha', 'Muza', 'Sai', 'Mustaq'],
  'Mutual_Friend_Count': 4,
  'Name': 'Abid',
  'id': 6},
 {'Friend': 'Muza',
  'Mutual': ['Taha', 'Saqib', 'Sai', 'Mustaq'],
  'Mutual_Friend_Count': 4,
  'Name': 'Abid',
  'id': 7},
 {'Friend': 'Mustaq',
  'Mutual': ['Saqib', 'Sai', 'Muza'],
  'Mutual_Friend_Count': 3,
  'Name': 'Abid',
  'id': 8},
 {'Friend': 'Sai',
  'Mutual': ['Saqib', 'Muza', 'Mustaq'],
  'Mutual_Friend_Count': 3,
  'Name': 'Abid',
  'id': 9},
 {'Friend': 'Taha',
  'Mutual': ['Saqib', 'Muza'],
  'Mutual_Friend_Count': 2,
  'Name': 'Abid',
  'id': 10},
 {'Friend': 'Saqib',
  'Mutual': ['Taha', 'Sai', 'Abid', 'Mustaq'],
  'Mutual_Friend_Count': 4,
  'Name': 'Muza',
  'id': 11},
 {'Friend': 'Abid',
  'Mutual': ['Taha', 'Saqib', 'Sai', 'Mustaq'],
  'Mutual_Friend_Count': 4,
  'Name': 'Muza',
  'id': 12},
 {'Friend': 'Mustaq',
  'Mutual': ['Saqib', 'Sai', 'Abid'],
  'Mutual_Friend_Count': 3,
  'Name': 'Muza',
  'id': 13},
 {'Friend': 'Sai',
  'Mutual': ['Saqib', 'Abid', 'Mustaq'],
  'Mutual_Friend_Count': 3,
  'Name': 'Muza',
  'id': 14},
 {'Friend': 'Taha',
  'Mutual': ['Saqib', 'Abid'],
  'Mutual_Friend_Count': 2,
  'Name': 'Muza',
  'id': 15},
 {'Friend': 'Saqib',
  'Mutual': ['Muza', 'Sai', 'Abid'],
  'Mutual_Friend_Count': 3,
  'Name': 'Mustaq',
  'id': 16},
 {'Friend': 'Abid',
  'Mutual': ['Saqib', 'Sai', 'Muza'],
  'Mutual_Friend_Count': 3,
  'Name': 'Mustaq',
  'id': 17},
 {'Friend': 'Muza',
  'Mutual': ['Saqib', 'Sai', 'Abid'],
  'Mutual_Friend_Count': 3,
  'Name': 'Mustaq',
  'id': 18},
 {'Friend': 'Sai',
  'Mutual': ['Saqib', 'Abid', 'Muza'],
  'Mutual_Friend_Count': 3,
  'Name': 'Mustaq',
  'id': 19},
 {'Friend': 'Saqib',
  'Mutual': ['Muza', 'Abid', 'Mustaq'],
  'Mutual_Friend_Count': 3,
  'Name': 'Sai',
  'id': 20},
 {'Friend': 'Abid',
  'Mutual': ['Saqib', 'Muza', 'Mustaq'],
  'Mutual_Friend_Count': 3,
  'Name': 'Sai',
  'id': 21},
 {'Friend': 'Muza',
  'Mutual': ['Saqib', 'Abid', 'Mustaq'],
  'Mutual_Friend_Count': 3,
  'Name': 'Sai',
  'id': 22},
 {'Friend': 'Mustaq',
  'Mutual': ['Saqib', 'Abid', 'Muza'],
  'Mutual_Friend_Count': 3,
  'Name': 'Sai',
  'id': 23},
 {'Friend': 'Saqib',
  'Mutual': ['Muza', 'Umair', 'Abid'],
  'Mutual_Friend_Count': 3,
  'Name': 'Taha',
  'id': 24},
 {'Friend': 'Abid',
  'Mutual': ['Saqib', 'Muza'],
  'Mutual_Friend_Count': 2,
  'Name': 'Taha',
  'id': 25},
 {'Friend': 'Muza',
  'Mutual': ['Saqib', 'Abid'],
  'Mutual_Friend_Count': 2,
  'Name': 'Taha',
  'id': 26},
 {'Friend': 'Umair',
  'Mutual': ['Saqib'],
  'Mutual_Friend_Count': 1,
  'Name': 'Taha',
  'id': 27},
 {'Friend': 'Saqib',
  'Mutual': ['Taha'],
  'Mutual_Friend_Count': 1,
  'Name': 'Umair',
  'id': 28},
 {'Friend': 'Taha',
  'Mutual': ['Saqib'],
  'Mutual_Friend_Count': 1,
  'Name': 'Umair',
  'id': 29}]

Final Single Code

# Assuming a bunch of friends
users=[
        {"id":0,"name": 'Saqib'},
        {"id":1,"name": 'Abid'},
        {"id":2,"name": 'Muza'},
        {"id":3,"name": 'Mustaq'},
        {"id":4,"name": 'Sai'},
        {"id":5,"name": 'Taha'},
        {"id":6,"name": 'Umair'}
        ]

# Assuming some kind of chemistry
friendship = [(0,1),(0,2),(0,3),(0,4),(0,5),(0,6),
              (1,2),(1,3),(1,4),(1,5),
              (2,3),(2,4),(2,5),
              (3,4),(5,6)]

# Creating a new item in Users List
for user in users:
    user["friends"] = []

# Adding friendhsip bi-directionally   
for i, j in friendship:
    users[i]["friends"].append(users[j]['name']) # add i as a friend of j
    users[j]["friends"].append(users[i]['name']) # add j as a friend of i  

# Get intersections of two listsmutual friends between two friends
def mutual(user, other_user):
   return list(set(users[user]["friends"]).intersection(set(users[other_user]["friends"])))

# Construct Mutual Friend List from data.
friend_stat = []
i=0
for user in users:
    userID = user["id"]
    for friend in user["friends"]:
        # Get ID of the Friend
        otherID = next(index for (index, d) in enumerate(users) if d["name"] == friend)
        # get mutual friends between two friends
        if mutual(userID,otherID):
            mutuals = mutual(userID,otherID)
            friend_stat.append({
              "id": i, 
              "Name": user["name"],
              "Friend":str(friend),
              "Mutual":mutuals ,
              "Mutual_Friend_Count" : len(mutuals)
              })
            i+=1

Creating Simple Charts in Python

We Have sample data for creating simple Line graphs. You can use this tutorial to generate CSV.

  TicketID Priority TicketTime Year Month Week Hour
0 TICKET000 Priority-1 2016-01-01 00:00:00 2016 1 53 0
1 TICKET001 Priority-5 2016-01-01 01:00:00 2016 1 53 1
2 TICKET002 Priority-3 2016-01-01 02:00:00 2016 1 53 2
3 TICKET003 Priority-4 2016-01-01 03:00:00 2016 1 53 3
4 TICKET004 Priority-3 2016-01-01 04:00:00 2016 1 53 4
5 TICKET005 Priority-4 2016-01-01 05:00:00 2016 1 53 5
6 TICKET006 Priority-2 2016-01-01 06:00:00 2016 1 53 6
7 TICKET007 Priority-5 2016-01-01 07:00:00 2016 1 53 7
8 TICKET008 Priority-4 2016-01-01 08:00:00 2016 1 53 8
9 TICKET009 Priority-3 2016-01-01 09:00:00 2016 1 53 9

Now, Lets look in for Chart creation.

We can use the matplotlib package.

import pandas as pd
import datetime as dt
import matplotlib.pyplot as plt

Reading CSV as

# Reading Data from CSV
df=pd.read_csv('GeneratedTickets.csv',low_memory=False)

Convert the Date Fields.

# Converting the DateField in CSV to datetime.
df["TicketTime"]=pd.to_datetime(df["TicketTime"])

Make additional Fields in the Pandas DataFrame.

# Extracting Year, Month, Week, Hours from Date Field
df['Year'] = df['TicketTime'].dt.year
df['Month'] = df['TicketTime'].dt.month
df['Week'] = df['TicketTime'].dt.week
df['Hour'] = df['TicketTime'].dt.hour

Grouping Data: We will group by Priority, Week, Month, Hours – {

Number of observations in each – Priority, Month, Week, Hour can be done using .size() method of data frame.

df_grpBypr= df.groupby(['Priority','Month','Week','Hour']).size().reset_index(name="TicketCount")

Extracting on Jan Month and First Week.

df_grpBypr=df_grpBypr[df_grpBypr[ df_grpBypr['Month'] == 1] ['Week'] ==1]

Looking for Priroity-1 Data only.

df_Priority = df_grpBypr[ df_grpBypr['Priority'] == 'Priority-1']

Creating a line chart.

plt.title('Weekly Priority Trend')
plt.xlabel('Hours')
plt.ylabel('Tickets Recieved')
plt.plot(df_Priority['Hour'], df_Priority['TicketCount'],marker='o', linestyle='--', color='r', label='Circle')
plt.show()

Cant Upload the pictures

Create Random Data with Date Field in Python

First we have to import necessary modules

import pandas as pd
import csv
from random import randint

Now we will create data for one month. we can create data directly by using date_range method of Pandas. This will create data with 1 Day interval as default. However, we can specify 2D for 2 Days, 3D for 3 Days and similarly. We can use different freq values.
Some can be found here

Alias Description
B business day frequency
C custom business day frequency (experimental)
D calendar day frequency
W weekly frequency
M month end frequency
SM semi-month end frequency (15th and end of month)
BM business month end frequency
CBM custom business month end frequency
MS month start frequency
SMS semi-month start frequency (1st and 15th)
BMS business month start frequency
CBMS custom business month start frequency
Q quarter end frequency
BQ business quarter endfrequency
QS quarter start frequency
BQS business quarter start frequency
A year end frequency
BA business year end frequency
AS year start frequency
BAS business year start frequency
BH business hour frequency
H hourly frequency
T, min minutely frequency
S secondly frequency
L, ms milliseconds
U, us microseconds
N nanoseconds

Lets now create for One Month, here i am choosing hourly increment in data generation. You can use the above list for choosing your desired one. Also, as already specified above,in below example you can add freq=’2H’ for 2 Hourly data.

# Creating data from 1st Jan 2016 - 720 hours {frequency}
dateRng = pd.DataFrame(pd.date_range('1/1/2016', periods=720, freq='H'))

Now, since we have created the date fields. Lets create CSV file and store in the folder from where we are executing the script.

# Creating File as GeneratedTickets
f = open("GeneratedTickets.csv", 'wt')

File is opened in background and ready to be updated.
we will add columns and generate data into it.

try:
writer = csv.writer(f)
writer.writerow(('TicketNumber','Priority', 'TicketTime'))
# Creating each Line
for index, row in dateRng.iterrows():
writer.writerow(('TICKET00'+ str(index), 'Priority-'+str(randint(1,5)),row.loc[0]))
finally:
f.close()

Complete Script Body:

# Importing Libs
import pandas as pd
import csv
from random import randint

# Creating data from 1st Jan 2016 - 720 hours {frequency}
dateRng = pd.DataFrame(pd.date_range('1/1/2016', periods=720, freq='H'))

# Creating File as GeneratedTickets
f = open("GeneratedTickets.csv", 'wt')
try:
    writer = csv.writer(f)
    writer.writerow(('TicketID','Priority', 'TicketTime'))
    # Creating each Line
    for index, row in dateRng.iterrows():
       writer.writerow(('TICKET00'+ str(index), 'Priority-'+str(randint(1,5)),row.loc[0]))
finally:
    f.close()

Pandas in Python – Basics

pandas is an open source Python library for data analysis. Python has always been great for prepping and munging data, but it’s never been great for analysis – you’d usually end up using R or loading it into a database and using SQL (or worse, Excel). pandas makes Python great for analysis.

pandas is a dependency of statsmodels, making it an important part of the statistical computing ecosystem in Python.

Data Structures

pandas introduces two new data structures to Python – Series and DataFrame, both of which are built on top of NumPy (this means it’s fast).

# Importing the Pandas Module
import pandas as pd

Series

A Series is a one-dimensional object similar to an array, list, or column in a table. It will assign a labeled index to each item in the Series. By default, each item will receive an index label from 0 to N, where N is the length of the Series minus one.

# create a Series with an arbitrary list
s = pd.Series([7, 'Heisenberg', 3.14, -1789710578, 'Happy Eating!'])
s
0                7
1       Heisenberg
2             3.14
3      -1789710578
4    Happy Eating!
dtype: object

Alternatively, you can specify an index to use when creating the Series.

s = pd.Series([7, 'Heisenberg', 3.14, -1789710578, 'Happy Eating!'],
              index=['A', 'Z', 'C', 'Y', 'E'])
s
A                7
Z       Heisenberg
C             3.14
Y      -1789710578
E    Happy Eating!
dtype: object

The Series constructor can convert a dictionary as well, using the keys of the dictionary as its index.

d = {'Chicago': 1000, 'New York': 1300, 'Portland': 900, 'San Francisco': 1100,
     'Austin': 450, 'Boston': None}
cities = pd.Series(d)
cities
Austin            450
Boston            NaN
Chicago          1000
New York         1300
Portland          900
San Francisco    1100
dtype: float64

You can use the index to select specific items from the Series.

cities['Chicago']
1000.0
cities[['Chicago', 'Portland', 'San Francisco']]
Chicago          1000
Portland          900
San Francisco    1100
dtype: float64

Or you can use boolean indexing for selection.

cities[cities < 1000]
Austin      450
Portland    900
dtype: float64

That last one might be a little weird, so let’s make it more clear – cities < 1000 returns a Series of True/False values, which we then pass to our Series cities, returning the corresponding True items.

less_than_1000 = cities < 1000
print(less_than_1000)
print('\n')
print(cities[less_than_1000])
Austin            True
Boston           False
Chicago          False
New York         False
Portland          True
San Francisco    False
dtype: bool


Austin      450
Portland    900
dtype: float64

You can also change the values in a Series on the fly.

# changing based on the index
print('Old value:', cities['Chicago'])
cities['Chicago'] = 1400
print('New value:', cities['Chicago'])
('Old value:', 1000.0)
('New value:', 1400.0)
# changing values using boolean logic
print(cities[cities < 1000])
print('\n')
cities[cities < 1000] = 750

print cities[cities < 1000]
Austin      450
Portland    900
dtype: float64


Austin      750
Portland    750
dtype: float64

What if you aren’t sure whether an item is in the Series? You can check using idiomatic Python.

print('Seattle' in cities)
print('San Francisco' in cities)
False
True

Mathematical operations can be done using scalars and functions.

# divide city values by 3
cities / 3
Austin           250.000000
Boston                  NaN
Chicago          466.666667
New York         433.333333
Portland         250.000000
San Francisco    366.666667
dtype: float64
# square city values
np.square(cities)
Austin            562500
Boston               NaN
Chicago          1960000
New York         1690000
Portland          562500
San Francisco    1210000
dtype: float64

You can add two Series together, which returns a union of the two Series with the addition occurring on the shared index values. Values on either Series that did not have a shared index will produce a NULL/NaN (not a number).

print(cities[['Chicago', 'New York', 'Portland']])
print('\n')
print(cities[['Austin', 'New York']])
print('\n')
print(cities[['Chicago', 'New York', 'Portland']] + cities[['Austin', 'New York']])
Chicago     1400
New York    1300
Portland     750
dtype: float64


Austin       750
New York    1300
dtype: float64


Austin       NaN
Chicago      NaN
New York    2600
Portland     NaN
dtype: float64

Notice that because Austin, Chicago, and Portland were not found in both Series, they were returned with NULL/NaN values.

NULL checking can be performed with isnull and notnull.

# returns a boolean series indicating which values aren't NULL
cities.notnull()
Austin            True
Boston           False
Chicago           True
New York          True
Portland          True
San Francisco     True
dtype: bool
# use boolean logic to grab the NULL cities
print(cities.isnull())
print('\n')
print(cities[cities.isnull()])
Austin           False
Boston            True
Chicago          False
New York         False
Portland         False
San Francisco    False
dtype: bool


Boston   NaN
dtype: float64

DataFrame

A DataFrame is a tablular data structure comprised of rows and columns, akin to a spreadsheet, database table, or R’s data.frame object. You can also think of a DataFrame as a group of Series objects that share an index (the column names).

For the rest of the tutorial, we’ll be primarily working with DataFrames.

Reading Data

To create a DataFrame out of common Python data structures, we can pass a dictionary of lists to the DataFrame constructor.

Using the columns parameter allows us to tell the constructor how we’d like the columns ordered. By default, the DataFrame constructor will order the columns alphabetically (though this isn’t the case when reading from a file – more on that next).

Alternatively, you can specify an index to use when creating the Series.

import pandas as pd
#Create Lists of Data
Name =['Alex','Warren','Michael']
Age = [25,34,38]
Country=['USA','UK','Australia']
# Creating Columns
Columns = ['Name','Age','Country']
#Create a dictionary Type
Raw_Table= {'Name':Name,
             'Age':Age,
             'Country':Country}
# Creating a DataFrame with RawTable and Columns in Dictionary and List type.
df=pd.DataFrame(Raw_Table,columns = Columns)
print(df)
# Print Type
print type(df)
  Name Age Country 
0 Alex  25  USA 
1 Warren 34  UK 
2 Michael 38  Australia
<class 'pandas.core.frame.DataFrame'>

Basics of Statistics

Statistics can be defined as a collection of techniques used when planning a data collection, and when subsequently analyzing and presenting data.

Dating back to ancient times people have needed knowledge about population size, to carry out a census of the armies or calculate expected taxes. The word statistics is derived from the word “status” (originally coming from Latin); and it was exactly the status of society, which was the subject of the first statistics! Later emerged probability theory (in connection with games!), demographics and insurance science as areas, in which statistical thinking was essential. In today’s digital age it is easy to collect as well as process and disseminate data, and therefore statistics is used for a variety of surveys throughout society.

Most statistical surveys can be divided into the following phases:

  1. Clarification of concepts
  2. Planning of data collection
  3. Data collection
  4. Analysis and presentation of data
  5. Conclusion Statistical methods (and statisticians!) are particularly useful in phases 2 and 4 of the survey.

There are two kinds of statistics:

  1. Descriptive statistics

  2. Analytical statistics

Descriptive statistics means describing data using tables, charts and simple statistical calculations such as averages, percentages, etc. This is what many people understand by the word “statistics”. It was also the kind of statistics that was produced in ancient times.

Analytical statistics is used to assess differences and relationships in data. For example, we could examine whether there is a relation between height and weight of a group of persons; or whether there is a difference between height of boys and height of girls, as well as provide an estimate of how large this difference is. Analytical statistics is a mathematical discipline, based on calculus of probability.

It is a relatively new discipline that has been developed throughout the twentieth century.

Oracle: Finding Columns with only null values

Consider we are have a table name mytable, which has a lot of  columns and many of them have null values. We can get information about the table using the Oracle built-in namespace.


USER_TAB_COLUMNS


USER_TAB_COLUMNS describes the columns of the tables, views, and clusters owned by the current user. Its columns (except for OWNER) are the same as those in ALL_TAB_COLUMNS. Columns


  • TABLE_NAME -Table,view or cluster name.
  • COLUMN_NAME – Column name.
  • DATA_TYPE -Datatype of the column.
  • DATA_TYPE_MOD – Datatype modifier of the column.
  • DATA_TYPE_OWNER – Owner of the datatype of the column.
  • DATA_LENGTH – Length of the column in bytes.
  • DATA_PRECISION – Length: decimal digits (NUMBER) or binary digits (FLOAT).
  • DATA_SCALE – Digits to right of decimal point in a number.
  • NULLABLE – Does column allow NULL values?
  • COLUMN_ID – Sequence number of the column as created.
  • DEFAULT_LENGTH – Length of default value for the column.
  • DATA_DEFAULT – Default value for the column.
  • NUM_DISTINCT – The number of distinct values in the column.
  • LOW_VALUE – The low value in the column.
  • HIGH_VALUE – The high value in the column.
  • DENSITY – The density of the column.
  • NUM_NULLS – The number of nulls in the column.
  • NUM_BUCKETS – The number of buckets in histogram for the column.
  • LAST_ANALYZED – The date of the most recent time this column was analyzed.
  • SAMPLE_SIZE – The sample size used in analyzing this column.
  • CHARACTER_SET_NAME – Character set name.
  • CHAR_COL_DECL_LENGTH – Declaration length of character type column.
  • GLOBAL_STATS – Are the statistics calculated without merging underlying partitions?
  • USER_STATS – Were the statistics entered directly by the user?
  • AVG_COL_LEN – The average length of the column in bytes.
  • CHAR_LENGTH – The maximum length of the column in characters.
  • CHAR_USED – C is maximum length given in characters,B if in bytes.
  • V80_FMT_IMAGE – Is column data in 8.0 image format?
  • DATA_UPGRADED – Has column data been upgraded to the latest type version format?
  • HIDDEN_COLUMN – The column exists in the table but cannot be selected. (mostly for internal use)
  • VIRTUAL_COLUMN – The column is an Oracle 11g Virtual column.
SELECT t.column_name FROM user_tab_columns t WHERE t.nullable = 'Y' AND t.table_name = 'YOUR_TABLE_NAME' AND t.num_distinct = 0

Vbsript to execute a Oracle PL/SQL Procedure

Rem Name of the PROCEDURE
Dim strProc  : strProc ="POPULATE_EMP_DATA"  
Rem Name of the DB SCHEMA
Dim strSchema : strSchema = "bhive_report"


'########## TARGET DB DSN
Dim TargetDSN : TargetDSN  = "devDSN"
Dim TargetDBuser : TargetDBuser = "devUSER"
Dim TargetDBpwd : TargetDBpwd = "devPASS"


Call runPROC(TargetDSN,TargetDBuser,TargetDBpwd,strProc,strSchema)


Rem Sub Routine to Execute Stored Procedure
Rem Generic accepts DSN name, username, password and procedure name.
Sub runPROC(vDSN,vUser,vPass,vProc,vSchema)
On Error Resume Next
Const adCmdStoredProc = 4
Dim strTargetConnection : strTargetConnection = "DSN=" & vDSN & ";uid=" & vUSER & ";pwd=" & vPASS&";"
Set objConn = CreateObject("ADODB.Connection")
objConn.Open strTargetConnection : Rem [ opening Target ORACLE connection ]
Set CmdStoredProc = CreateObject("ADODB.Command")
With CmdStoredProc 
Set .ActiveConnection=objConn
	.CommandText= vSchema & "." & vProc  
	.CommandType=adCmdStoredProc
	Set rsProc = .Execute
	If Err.Number  0 then
		msgbox "[ERROR]" & Err.Description
    Else
		msgbox "Executed Procedure Succesfully - " & .CommandText
	End If
End With
End Sub 

How to Archive Directories using TAR

When the names of files or members specify directories, the operation of tar is more complex. Generally, when a directory is named, tar also operates on all the contents of the directory, recursively. Thus, to tar, the file name `/’ names the entire file system.

To archive the entire contents of a directory, use `–create’ (`-c’) or `–append’ (`-r’) as usual, and specify the name of the directory. For example, to archive all the contents of the current directory, use `tar –create –file=archive-name .’. Doing this will give the archive members names starting with `./’. To archive the contents of a directory named `foodir’, use `tar –create –file=archive-name foodir’. In this case, the member names will all start with `foodir/’.

tar -cf archive.tar foodir

If you give tar a command such as `tar –create –file=foo.tar .’, it will report `tar: foo.tar is the archive; not dumped’. This happens because the archive `foo.tar’ is created before putting any files into it. Then, when tar attempts to add all the files in the directory `.’ to the archive, it notices that the file `foo.tar’ is the same as the archive, and skips it. (It makes no sense to put an archive into itself.) GNU tar will continue in this case, and create the archive as normal, except for the exclusion of that one file. Other versions of tar, however, are not so clever, and will enter an infinite loop when this happens, so you should not depend on this behavior. In general, make sure that the archive is not inside a directory being dumped.

When extracting files, you can also name directory archive members on the command line. In this case, tar extracts all the archive members whose names begin with the name of the directory. As usual, tar is not particularly clever about interpreting member names. The command `tar –extract –file=archive-name .’ will not extract all the contents of the archive, but only those members whose member names begin with `./’.

You can archive a directory by specifying its directory name as a file name argument to tar. The files in the directory will be archived relative to the working directory, and the directory will be re-created along with its contents when the archive is extracted.

To archive a directory, first move to its superior directory.

cd ..

Once in the superior directory, you can specify the subdirectory as a file name a

tar --create --verbose --file=music practice

OUTPUT:
---------
practice/
practice/blues
practice/folk
practice/jazz
practice/collection

Note that the archive thus created is not in the subdirectory `practice', but rather in the working directory–the directory from which tar was invoked. Before trying to archive a directory from its superior directory, you should make sure you have write access to the superior directory itself, not only the directory you are trying archive with tar. Trying to store your home directory in an archive by invoking tar from the root directory will probably not work.

Check Size of a File in BASH Script – alert.log oracle help

Size of a File in a directory location can be checked using a small shell script.

#!/bin/bash
str_LogName = "/u01/SAQIB/MyFile"
int_fileSIZE=$(du -b $str_LogName | awk '{ print $1 }')
echo $str_LogName

However, if we want to see if the size is greater than a particular amount say 3MB, we will use the following script.

#!/bin/bash
str_LogName = "/u01/SAQIB/MyFile"
#Size to be checked in Bytes
int_checkSIZE=3145728
int_fileSIZE=$(du -b $str_LogName | awk '{ print $1 }')
if [[ $int_fileSIZE -le  $int_checkSIZE ]];
then
        echo "Size is Lesser then 3 MB"
        # Size in Human Readable Format.
        echo du -bh $str_LogName

else
        echo "Size is More than 3MB:"
fi

 Now if we want to check the size of alert.log file in the database and see if its more than 3MB, copy it with a new name and recreate alert.log file.

#!/bin/bash
##########################################################################
#
# AUTHOR: Saqib Mujtaba
# SCRIPT: Check the size of Size of ALert.Log File and if its more than 3MB it creates a Backup and Nullifies the Older Original One.
# DATED : 18/02/2014
# 
#
##########################################################################
#Getting the SID name
str_SIDINSTANCE=$( cat /etc/oratab|grep -v "^#"|cut -d: -f1 -s|head -1)

#File Location of Alert.Log File <EDIT AS PER YOURS>
str_FileLocation="/u01/oracleSID/db/tech_st/11.1.0/admin/oracleSID_pm-ora-qwe-qa-wsx/diag/rdbms/oracleSID/oracleSID/trace/"
str_FileName="alert_oracleSID.log"
str_LogName=$str_FileLocation$str_FileName
#echo $str_LogName
#File Location of New Backup
str_NewLogName="alert_LOG_bkp_$str_SIDINSTANCE-$(date +"%m-%d-%Y-%H:%M").log"
str_NewLogLocation=$str_FileLocation$str_NewLogName
#echo $str_NewLogLocation
#Check alert.log File Size
int_fileSIZE=$(du -b $str_LogName | awk '{ print $1 }')
#CHECK TO SIZE IN BYTES
int_checkSIZE=3145728
#echo $int_fileSIZE " & " $int_checkSIZE
#Check Size is greater than 3 MB
if [[ $int_fileSIZE -le  $int_checkSIZE ]];
then
        echo "NO Need to Backup - Size is Lesser then 3 MB"
        echo du -bh $str_LogName

else
        #echo "Size is More:"
	cp -f $str_LogName $str_NewLogLocation
        if [[ $? -eq 0 ]];
        then
                rm -f $str_LogName
                touch $str_LogName
                echo "Alert Log Backup File [ $str_NewLogName ] Created Successfully - Thanks!!"
        else
                echo "Failed to Create Alert Log Backup"
        fi
fi
Oracle’s alert.log chronologically records messages and errors arising from the daily database operation. Also, there are pointers to trace files and dump files.

These messages include

  • startups and shutdowns of the instance

  • Messages to the operator console
  • Errors causing trace files.
  • Create, alter and drop SQL statements on databases, tablespaces and rollback segments.
  • Errors when a materialized view is refreshed.
  • ORA-00600 (internal) errors.
  • ORA-01578 errors (block corruption)
  • ORA-00060 errors (deadlocks)
alert.log is a text file that can be opened with any text editor. The directory where it is found can be determined by the background_dump_dest initialization parameter:
select value from v$parameter where name = 'background_dump_dest';

If the background_dump_dest parameter is not specified, Oracle will write the alert.log into the $ORACLE_HOME/RDBMS/trace directory.

 
 

Powered by WordPress.com.

Up ↑