Simple Script in Batch Programming Using IF and For Loop With Tableau part

@echo off

setlocal EnableDelayedExpansion

for /l %%i in (01, 01, 31) do (

    SET lday=%%i

    SET month=01

    SET year=2025

    IF !lday! EQU 08 (

        SET /a lday=010

    )

    IF !month! EQU 08 (

        SET /a month=010

    )

    IF !lday! EQU 09 (

        SET /a lday=011

    )

    IF !month! EQU 09 (

        SET /a month=011

    )

    set /A lday=!lday!-1

    set /A month=!month!-0

    IF !lday! LSS 1 IF !month! EQU 1 (

        SET lday=31

        SET /A month=!month!-1+12

        SET /A year=!year!-1

    )

    IF !lday! LSS 1 IF !month! EQU 2 (

        SET lday=31

        SET /A month=!month!-1

    )

    IF !lday! LSS 1 IF !month! EQU 3 (

        SET lday=28

        SET /A month=!month!-1

    )

    IF !lday! LSS 1 IF !month! EQU 4 (

        SET lday=31

        SET /A month=!month!-1

    )

    IF !lday! LSS 1 IF !month! EQU 5 (

        SET lday=30

        SET /A month=!month!-1

    )

    IF !lday! LSS 1 IF !month! EQU 6 (

        SET lday=31

        SET /A month=!month!-1

    )

    IF !lday! LSS 1 IF !month! EQU 7 (

        SET lday=30

        SET /A month=!month!-1

    )

    IF !lday! LSS 1 IF !month! EQU 8 (

        SET lday=31

        SET /A month=!month!-1

    )

    IF !lday! LSS 1 IF !month! EQU 9 (

        SET lday=31

        SET /A month=!month!-1

    )

    IF !lday! LSS 1 IF !month! EQU 10 (

        SET lday=30

        SET /A month=!month!-1

    )

    IF !lday! LSS 1 IF !month! EQU 11 (

        SET lday=31

        SET /A month=!month!-1

    )

    IF !lday! LSS 1 IF !month! EQU 12 (

        SET lday=30

        SET /A month=!month!-1

    )

    IF !lday! LEQ 9 (

        SET lday=0!lday!

    ) else (

        SET lday=!lday!

    )

    IF !month! LEQ 9 (

        SET month=0!month!

    ) else (

        SET month=!month!

    )

    echo !year!!month!!lday!

    pause

)

endlocal 

Tableau Calcuation/LOD & Parameter with IF condition

Use this calculated field as a filter (set to TRUE)

{

FIXED [year], [month]:

  SUM(

    IF [year] = RIGHT(STR(YEAR([DATEPARA])), 4)

    AND [month] =

      IF MONTH([DATEPARA]) < 10

      THEN "0" + STR(MONTH([DATEPARA]))

      ELSE STR(MONTH([DATEPARA]))

      END

    THEN [TotalEXPEN]

    ELSE 0

    END                                                                     

)

}

Using Parameters (Selection  In Tableau)

For a dynamic approach, create two parameters:

1. Year Parameter (List of years, e.g., 2023, 2024)
2. Month Parameter (List of months, e.g., January, February)

Then modify the LOD:

tableau

{FIXED [year], [month]:

    SUM(

        IF [year] IN ([Year Parameter]) AND

           [month] IN ([Month Parameter])

        THEN [user]

        END

    )

}

 Example#

{

FIXED [year],[month]: SUM( IF STR([year])=LEFT(STR([STARTDATEPARA]), 4) AND STR([month])= MID(STR([STARTDATEPARA]), 7,1) THEN [DimComFrmParaCAL] END )

}

//NOTE STR("01/03/2024") becomes "2024-03-01" (or "2024-01-03" depending on your locale) in Tableau

//sum(IF STR([year])=LEFT(STR([STARTDATEPARA]), 4) THEN [CustCtr] END)

//sum(IF STR([month])= MID(STR([STARTDATEPARA]), 7,1) THEN [CustCtr] END)

//{FIXED [year], [month]: SUM(IF [year] = 2024 AND [month] =3 THEN [CustCtr] END)}

Common Steps for using Machine Learning Model

Common Steps to Use a Machine Learning Model

1) Load the data & Split data into X & y 

import pandas   
pandas.read_csv("./data/1.csv")
X = df.drop("target", axis=1) # using all columns besides target
y = df["target"] #  predicting y using X

2) Model selection  & Split the data into training and test sets

from sklearn.model_selection import train_test_split

X_train, X_test, y_train, y_test = train_test_split(X, y,test_size=0.2)

3) Instantiate the Model  

from sklearn.ensemble import RandomForestClassifier
m= RandomForestClassifier(n_estimators=50)

4) Fit the model using function 

m.fit(X_train,y_train); 

5) Make prediction

ypreds=m.predict(X_test)

7) To Evaluate Model use  score() function on test and train data

m.score(X_test, y_test)
m.score(X_train,y_train)

8) To improve the model by changing its hyperParameters

from sklearn.model_selection import cross_val_score

import numpy as np

# Use different numbers of  n_estimators  as hyperparameter 

np.random.seed(40)
for i in range(10, 100, 5):
print(f"Trying model with {i} estimators...")
m= RandomForestClassifier(n_estimators=i).fit(X_train, y_train)
print(f"Model accruacy on test data set: {m.score(X_test, y_test)}")

9) Save trained model to file using pickle

import pickle
pickle.dump(m, open("My_Random_forest_model.pkl", "wb"))

10) Load a saved model and make a prediction on a single example
saved_model = pickle.load(open("My_Random_forest_model.pkl", "rb"))
saved_model.score(X_train,y_train)
#######################Example of  a Machine Model###########
Items_List =final_df.ITEMS.unique() ##ITEMS is the column in dataframe having the Item_names
for x in range(0,1): #x in Items_List
    y='Item_names'
    fc=get_test()
    print(fc)
    fc=fc[['ds', 'yhat', 'yhat_lower', 'yhat_upper']]
    fc['Region_NAME']=y    
    fc.to_csv('./Future_ITEMS/'+y+'.csv')#Future_ITEMS is a folder
	
	
def get_test():
  data=final_df#.loc[final_df['ITEMS']==x]
  param_grid = {'changepoint_prior_scale': [0.001, 0.01, 0.1, 0.5],'seasonality_prior_scale': [0.01, 0.1, 1.0, 10.0],}
  # Generate all combinations of parameters
  all_params = [dict(zip(param_grid.keys(), v)) for v in itertools.product(*param_grid.values())]
  rmses = []  # Store the RMSEs for each params here
    for params in all_params:
            m = Prophet(**params).fit(data)  # Fit model with given params
            df_cv = cross_validation(m, initial='180 days', period='90 days', horizon = '365 days')
            print(df_cv)
            df_p = performance_metrics(df_cv, rolling_window=1)
            print(df_p)
            rmses.append(df_p['rmse'].values[0])
# Find the best parameters
tuning_results = pandas.DataFrame(all_params)
tuning_results['rmse'] = rmses
best_params = all_params[np.argmin(rmses)]
# Initiate model with best parameters
m = Prophet(changepoint_prior_scale=best_params['changepoint_prior_scale'], 
seasonality_prior_scale=best_params['seasonality_prior_scale']).fit(data)
# make prediction
future = m.make_future_dataframe(periods=1460,freq='D') 
forecast = m.predict(future)
return forecast

Forcasting by Using Model in Python

 Time Series Forecasting with ML Model

Following are the steps we need to following to Apply the model on the data. Below is the small example.

Step 1

Create the instance of the Prophet class 

Step 2 

Call  the Methods 

A) fit  method 

B) predict methods

Note:-The input to Prophet is always a dataframe with two columns: ds and y

a) The ds (datestamp) column having format like YYYY-MM-DD  or YYYY-MM-DD HH:MM:SS 

        b) y column should be numeric on which prediction is made

        c) settings for the forecasting procedure are passed into the constructor

Sample Example Code

import pandas as pd

from prophet import Prophet

df =pd.read_csv('1.csv')

print(df)

m = Prophet()

m.fit(df)

cast_future = m.make_future_dataframe(periods=365)

print(cast_future)

fcast = m.predict(cast_future)

print(fcast)

fcast[['ds', 'yhat', 'yhat_lower', 'yhat_upper']]

fig1 = m.plot(fcast)

fig2 = m.plot_components(fcast)

Practical Example for the  Model

import pandas as pd

import os,sys

source_folder = r".\\data"

data2=pd.DataFrame()

for file in os.listdir(source_folder):

    data= pd.read_csv(".\\data\\"+file+" ", dtype=str)

    data2=data2.append(data)

    print(file,"has been appended")

data2.head()

data2=data2.drop('Unnamed: 0',axis=1)

data2.head()

data2['test']=data2['HR'] .apply(lambda x: '{0:0>4}'.format(x))

data2.head()

data2['ds']=data2['DT'].str.cat(data2['test'],sep=" ")

data2.head()

data2['ds']=pd.to_datetime(data2['ds'])

data2['EXTRA'] = data2['EXTRA'].astype('float')

data2.info()

data3 = data2.rename(columns={'EXTRA': 'y', 'PLT': 'plts'})

data3.head()

data3=data3[['ds','y','plts']]

data3=data3.dropna()

data3

data3=data3.loc[data3['y']>=0] 

data3

from prophet import Prophet

def get_test(x):

    #for x in range(0,3):

    data4=data3.loc[data3['plts']==x]

    m = Prophet()

    m.fit(data4)

    cast_future = m.make_future_dataframe(periods=24000,freq='60min')  

    fcast = m.predict(cast_future)

    return fcast

list_olts =data3.olts.unique()

for x in range(0,3):

    y=list_olts[x]

    fc=get_test(y)

    fc=fc[['ds', 'yhat', 'yhat_lower', 'yhat_upper']]

    fc['PLT_NAME']=y    

    fc.to_csv('./Predicted_DATA/'+y+'.csv')

Reading Files In Python

#importing the required libraries

 import pandas as pd

import shutil

from shutil import copyfile 

from datetime import date,datetime, timedelta 

import os

source_folder = r"D:\\SourceFiles\\"

dest_folder=r"D:\\DestinationFiles\\"

###Loop throught the source_folder getting the datepart from it filename and subtract 1 from it

###and then recombine the again with yesterday.

for file in os.listdir(source_folder):

    r=file.rfind("_")

    date_string=file[r:][1:9]

    fname=file[:r][:]

    remainingpart=file[r:][9:]

    date_object = datetime.strptime(date_string, "%Y%m%d")

    date_object=date_object-timedelta(days=1)

    date_object=date_object.strftime("%Y%m%d")

    #print(fname+"_"+date_object+remainingpart)

    old_file=source_folder+file

    new_file=dest_folder+fname+"_"+date_object+remainingpart

    #print("oldfile_name",old_file, "Newfile name", new_file)

    os.rename(old_file, new_file)

#############Reading the files from the dest_folder one by one and rewrite to Final folder

for file in os.listdir(dest_folder):

    print(file)

    df = pd.read_csv("D:\\DestinationFiles\\"+file, dtype=str)

    print(df)

    df.to_csv("D:\\Final\\"+file, index=False)

#####Adding Columns of SysDATE and File_Name in existing DataFrame Using Lists

####Inserting BlankColumn in Beginning of DataFrame 

import pandas

from datetime import date,datetime, timedelta

df=pandas.read_csv("1.csv")

for i in range(0,len(df)):

    date_string=df['Date'].iloc[i]

    do = datetime.strptime(date_string, '%d-%b-%y')

    SysDATE=do.strftime('%Y%m%d')

    datelist.append(SysDATE)

    filelist.append("filename_"+SysDATE)

    

df['SDATE']=datelist

df['File']=filelist

df.insert(0,'Unnamed 0',' ')

######Batch Programming Example### DATE Handling#########

@echo ON

rem set year=%date:~-4,4%

rem set month=%date:~-7,2%

rem set day=1%date:~-10,2%-100

rem set /A lday=%day%-1

set year=2024

set month=11

set day=1

set /A lday=%day%-1

IF %lday% LSS 10 (SET lday=0%lday%) else (SET lday=%lday%)

echo %year%%month%%lday%

IF %lday% LSS 1 IF %month% EQU 1 (

SET lday=31 

SET /A month=%month%-1

)

IF %lday% LSS 1 IF %month% EQU 2 (

SET lday=31 

SET /A month=%month%-1

)

IF %lday% LSS 1 IF %month% EQU 3 (

SET lday=28 

SET /A month=%month%-1

)

IF %lday% LSS 1 IF %month% EQU 4 (

SET lday=31 

SET /A month=%month%-1

)

IF %lday% LSS 1 IF %month% EQU 5 (

SET lday=30 

SET /A month=%month%-1

)

IF %lday% LSS 1 IF %month% EQU 6 (

SET lday=31 

SET /A month=%month%-1

)

IF %lday% LSS 1 IF %month% EQU 7 (

SET lday=30 

SET /A month=%month%-1

)

IF %lday% LSS 1 IF %month% EQU 8 (

SET lday=31 

SET /A month=%month%-1

)

IF %lday% LSS 1 IF %month% EQU 9 (

SET lday=31  

SET /A month=%month%-1

)

IF %lday% LSS 1 IF %month% EQU 10 (

SET lday=30   

SET /A month=%month%-1

)

IF %lday% LSS 1 IF %month% EQU 11 (

SET lday=31 

SET /A month=%month%-1

)

IF %lday% LSS 1 IF %month% EQU 12 (

SET lday=30   

SET /A month=%month%-1

)

echo %year%%month%%lday%

IF %lday% LSS 1 IF %month% EQU 1 (

SET month=12 

SET /A year=%year%-1

)

echo %year%%month%%lday%

Comparing Two files and their Headers using Pandas and Lists

import pandas

import os

import re

old_col_list=[]

Source_Folder_OldFiles = "./data//Old_Columns_Files"

new_col_list=[]

Source_Folder_NewFiles = "./data//New_Columns_Files"

############################################### Loop through old files###############       

for file in os.listdir(Source_Folder_OldFiles):

    if(re.search("000000",file) and (

       file.startswith("abc_Re_")

       or file.startswith("def_Re_") 

       or file.startswith("ghi_Re_")

       or file.startswith("jkl_Re_")

       or file.startswith("mno_Re_")

       or file.startswith("pqr_Re_")

       or file.startswith("stu_Re_")

       or file.startswith("vwx_Re_")

       or file.startswith("yz_Re_") ) 

    ):

        old_file_df=pandas.read_csv(".//data//Old_Columns_Files//"+file+"")

        print(file)

        old_col_list.append(old_file_df.columns)

print(old_col_list[0])

print(len(old_col_list))

############################################### Loop through New or current day files###############       

for file in os.listdir(Source_Folder_NewFiles):

    if(re.search("000000",file) and (

       file.startswith("abc_Re_")

       or file.startswith("def_Re_") 

       or file.startswith("ghi_Re_")

       or file.startswith("jkl_Re_")

       or file.startswith("mno_Re_")

       or file.startswith("pqr_Re_")

       or file.startswith("stu_Re_")

       or file.startswith("vwx_Re_")

       or file.startswith("yz_Re_") ) 

    ):

        new_file_df=pandas.read_csv(".//data//New_Columns_Files//"+file+"")

        print(file)

        new_col_list.append(new_file_df.columns)

        

##########################  Loop the logic for all files###########################

for x in range(0, 2):

    print(new_col_list[x])

    print(len(new_col_list))

    ##comparing the elements of lists, that the cols of old files with the cols of new current file

    result = [a == b for a, b in zip(old_col_list[x], new_col_list[x])]

    #print(all(result),result[0],result[1:])        

    #False False [True, True, True, True, True, True, True, True, True, True, True, True, True, True, True, True, True]

    #find the matchig elements

    matches = [i for i in old_col_list[x] if i in new_col_list[x]]

    print(matches)

    #find the non matching elements and measure the len of list contain non match element

    no_matches = [j for j  in new_col_list[x] if j not in  old_col_list[x]]

    print(no_matches,len(no_matches))

DataFrame Summary with Functions

 DataFrame Summary with Functions

import pandas as pd

pd.set_option('display.max_columns', None)

pd.set_option('display.max_rows', None)

df = pd.read_excel("D:\\1.xlsx", "SheetName", index_col='Index_Column_rowlabel', usecols='B:AG',skiprows=2)  

df.loc['No_of_Cats':'No_of_Dogs',:]

df = df.drop('Animals', axis=1)

df=df.reset_index()

df.loc[(df['Index_Column_rowlabel']=='No_of_Cats') | (df['Index_Column_rowlabel']=='No_of_Dogs')]

df=df.rename(columns={"Mobile Voice":"Date_T"})

df=df.transpose() 

df.columns = df.iloc[0]

df

####################

import pandas as pd

pd.set_option('display.max_columns', None)

pd.set_option('display.max_rows', None)

df = pd.read_excel("D:\\1.xlsx", "sheetname",index_col='Movekp' usecols='B:AGG',skiprows=2)  

#get the/quering the specific information from the sheet using loc function of the pandas

newdf=df.loc[(df['Movekp']=='Sub in Ind') | (df['Movekp']=='Sub in Pk')]

newdf

#droping the column

newdf=newdf.drop('Venture', axis=1)

#rename the columns

newdf=newdf.rename(columns={'Move`':'Date'})

#take transpose of the dataframe

newdf=newdf.transpose()

newdf=newdf.reset_index()

#assigning the value of first row to columns

newdf.columns = newdf.iloc[0]

# remove first row

newdf=newdf.tail(-1)

#save to disk

newdf.to_csv("D:\\1_data.csv")

# Information about df

newdf.info()

#importing datetime library.

from datetime import date,datetime

# converting Date column to datetime type

newdf["Date"]=pd.to_datetime(newdf["Date"])

# set the date column as index of dataframe df

newdf=newdf.set_index('Date')

# plot the graph of the dataframe df that is line

newdf.plot()

Selection under condition in data frame ,along with Group by clause in Pandas dataframe

import pandas

df=pandas.read_csv(".//abc.csv")

df['date_t']=df.ds.astype(dtype='datetime64[ms]')

df['month'] = df['date_t'].dt.month

df['year'] = df['date_t'].dt.year

df.info()

df_tmp = df.groupby(['date_t','month','year'])['yhat'].sum().reset_index().sort_values( 'yhat',ascending = False)

r=df_tmp[((df_tmp['month'] == 1)|(df_tmp['month'] == 2)) & (df_tmp['year'] == 2002)].groupby(['month','year'])['yhat'].apply(lambda grp: grp.nlargest(3).mean())

r.to_csv('./Average_of_t3.csv')

Using tkinter Class in python To plot a graph.

from matplotlib.backends.backend_tkagg import FigureCanvasTkAgg

from matplotlib.figure import Figure 

import tkinter

# Create the default window 

win = tkinter.Tk() 

win.title("Welcome") 

win.geometry('800x600') 

  

# Create the list of options 

options_list=Flist.tolist()

# Variable to keep track of the option 

# selected in OptionMenu 

value_inside = tkinter.StringVar(win) 

  

# Set the default value of the variable 

value_inside.set("Select an Option") 

  

. 

my_menu = tkinter.OptionMenu(win, value_inside, *options_list) 

my_menu.pack() 

  

 

def print_answers(): 

    print("Selected Option: '{}' ".format(value_inside.get())) 

    

    data_view = data3.query("`ABC name`=='{}' ".format(value_inside.get())).groupby(['ABC name','Hour']).agg({'Tra': 'sum'})

    print(data3)

    fig1=data_view.plot(kind="bar",title='Graph').get_figure();

    global plot1 

    plot1=FigureCanvasTkAgg(fig1,root)

    

    plot1.draw() 

  

    # placing the canvas on the Tkinter window 

    plot1.get_tk_widget().pack() 

    return None

  

def print_clear(): 

    print("clear the figure")

    plot1.get_tk_widget().forget()

    return None

    

# Submit button 

submit_button = tkinter.Button(root, text='Submit', command=print_answers) 

submit_button.pack() 

clear_button = tkinter.Button(root, text='Clear', command=print_clear) 

clear_button.pack() 

Adding Colums to CSV and Write to Disk , Combine it, Then Query and Plot the Result in Python.

 import os

import pandas as pd

source_folder = r"D:\\original data"

dest_folder = r"D:\\Data_With_Time\\"

data2=pd.DataFrame()

for file in os.listdir(source_folder):

    if file.startswith("abc_file")  and file.endswith(".csv"):

        r=file.rfind("_")

        h=file[r:][9:13]

        dt=file[r:][1:9]

        fn=file

        data= pd.read_csv("D:\\original data\\"+file+" ")

        data['SHour']= h

        data['SDate']= dt

        data['filename']=fn

        data['DATA_TRA']=data['DATA_TRA']/3000

        data2=data2.append(data)

        data.to_csv("D:\\updated_"+file)

data2.to_csv("D:\\combine.csv")

data3=pd.read_csv("D:\\combine.csv")

data3=data3[['Short name','Date','Hour','Tra']]

data3 = data3.query("`name of Equipment`=='PIPE'").groupby(['name','Hour']).agg({'Tra': 'sum'})

print(data3)

data3.plot(kind="bar",title='Graph')

 #Comparing the columns of two CSVs files

import os

import pandas

mylist=[]

source_folder = "./data"

for file in os.listdir(source_folder):

    if file.startswith("abc_")  and file.endswith(".csv"):

        #print(file)

        df=pandas.read_csv(".//data//"+file+"")

        mylist.append(df.columns)

#print(mylist[1])

res = [c == d for c, d in zip(mylist[0], mylist[1])]

print(all(res))

Adding new column in dataframe after geting the info from filename in python

 import os

import pandas as pd

source_folder = r"D:\\DATA"

dest_folder = r"D:\\pdata"

for file in os.listdir(source_folder):

    if file.startswith("ABC")  and file.endswith(".csv"):

        r=file.rfind("_")

        h=file[r:][9:15]

        data= pd.read_csv("D:\\DATA\\"+file+" ")

        data['Time']= h

        data.to_csv("D:\\pdata\\updated_"+file)

Including the Images with Attachement using Python

 import win32com.client 

import datetime

import pandas as pd

import matplotlib.pyplot as plt

from datetime import date, timedelta,datetime 

def get_week_Last_day(year, week):

    year_start = date(year, 1, 1)

    # the following line assumes your this_week_int starts at 0

    # if this_week_int starts at 1, change to week-2

    week_start = year_start + timedelta(days=-year_start.isoweekday(), weeks=week-1)

    week_end = week_start + timedelta(days=6)

     

    week_start=week_start.strftime("%d/%m/%Y")

    week_end=week_end.strftime("%Y%m%d")

    lastweek="From "+week_start+ " to "+week_end

    return week_end

now = datetime.now()

curday=now

week_num=curday.strftime("%U")

year_num=curday.strftime("%Y")

week_num=int(week_num)

year_num=int(year_num)

LDOW=get_week_Last_day(year_num,week_num)

lday=LDOW

#######################################################

df=pd.read_csv("E:\\MyWork\\Python\\Data\\Tables_"+lday+".csv")

df=df[['DATE','TableName','Records']]

df= df.query("TableName=='Apple.AppleTable'").groupby(['DATE','TableName']).agg({'Records': 'sum'})

print(df)

df.plot(kind="bar",title='AppleTables')

plt.savefig("E:\\MyWork\\Python\\Data\image1.jpg",bbox_inches='tight')

import win32com.client 

import datetime

import pandas as pd

import matplotlib.pyplot as plt

from datetime import date, timedelta,datetime 

import time

from PIL import ImageGrab

import os

#import exchangelib as ex

from exchangelib import Message,FileAttachment,HTMLBody

from exchangelib import Credentials, Account

credentials = Credentials('Your@hotmail', 'password123')

account = Account('tahirkhalid@hotmail', credentials=credentials, autodiscover=True)

img_name=['image1','image2']

filetoattach = "E:\\TEST_"+lday+".csv"

with open(filetoattach, 'rb') as f:

    dmartcsv=FileAttachment(name=dmart_filename,content=f.read(),is_inline=False,content_id=dmart_filename)

 

 

logo1 = "E:\\MyWork\\Python\\Data\\image1.jpg"

logo2 = "E:\\MyWork\\Python\Data\image2.jpg"

with open(dmartcopperlogo, 'rb') as fa,open(dmartgponlogo, 'rb') as fb, \

     logoImage1 =FileAttachment(name=logo1, content=fa.read(), is_inline=True,content_id=logo1)

     logoImage2 =FileAttachment(name=logo2, content=fb.read(), is_inline=True,content_id=logo2)

     html1= '<html><body>ABC '+wk+'<br>'+globals()["df"+str(img_name[0])].to_html()+'<br>

    <img src="cid:%s"><br></body></html>' % (logo1,)

     html2 ='<html><body>$$$$<br>'+globals()["df"+str(img_name[1])].to_html()+'<br>

     <img src="cid:%s"><br></body></html>' % (logo2,)

     body =HTMLBody(html1+html2)

     m = Message(account=account,subject="ABC",body=body,to_recipients=['your@hotmail.com'])

     m.attach(logoImage1)

     m.attach(logoImage2)

     m.attach(csv)

     m.send()

Read CSVs files Get the No of cols,rows & column Name of Each CSV files using List , Pandas and OS Libraries of Python

 #!/usr/bin/env python

import pandas as pd

import os

####Read the files  the one by one from the folder and store the names of file in the list

source_folder=r"D:\\Rawdata\\Pythod_Code_RawData\\"

File_name_list=[]

for file in os.listdir(source_folder):

    if  file.endswith(".csv"):

        File_name_list.append(file)

        

print("Total File in the List",len(File_name_list))        

    

####Read the files names from the list and make df of them and get no of the columns and rows of the df and column name of df

## and store them in the list name mylist.

mylist=[]

try:

    for i in range(0,len(File_name_list)):

           dest_file=source_folder+File_name_list[i]

           df1=pd.read_csv(dest_file,on_bad_lines='skip')

           dfcols=len(df1.axes[1])

           dfrows=len(df1.axes[0])

           mylist.append(File_name_list[i]) 

           mylist.append(dfcols)

           mylist.append(dfrows)

           mylist.append(list(df1.columns))

                   

except pd.errors.EmptyDataError:

       print('Empty csv file!')

#print the mylist list and convert the mylist in dataframe by passing  the name of the list in DataFrame object and write df to csv

for i in range(0,len(mylist)):

    print(mylist[i])

new_df = pd.DataFrame(mylist)

new_df.to_csv(".//Result//2.csv", index=False, encoding='utf-8-sig')

Querying the Database using Python having List of Users and List of SQL Quries

import pandas as pd

import os

import cx_Oracle

df=pd.read_csv('D:\Oracle_Account\DATA\Python_Work\SQL_STMT_Read\SQL_List.csv')

df2=pd.DataFrame()

dfusers=pd.read_csv('D:\Oracle_Account\DATA\Python_Work\SQL_STMT_Read\Rquired_Users.csv',dtype=str)

Userlist = dfusers.OurUsers.values.tolist()

Userlist

res=str(Userlist)[1:-1]

print(res)

type(res)

dsn_tns = cx_Oracle.makedsn('localhost', '1531', 'XE')

conn=cx_Oracle.connect(user='abc', password='def', dsn=dsn_tns)

for i in range(0,3):

    query=df.loc[i,'sql_quries']

    query=query.format(res)

    print(query)

    df2= pd.read_sql(query, conn)

    df2.to_csv("D:\\Oracle_Account\\DATA\Python_Work\\SQL_STMT_Read\\"+str(i)+".csv", index = True, header=True) 

conn.close()

Reading List Element Under the Condition in Python

# Reading List Element Under the Condition in Two Ways

mylist=["ABC","DEF","DEFG","ABBC"]

for i in mylist:

    if i[0:2]=="AB":

        print(i)

    if i.startswith("DE"):

        print(i)

    

Oracle Database Concepts

#Oracle Database Knowledge and Concepts.  

 To check the password which is used during installation check the below file
[root@ol7-19 ~]# grep password  -A1 $ORACLE_HOME/install/response/db_install.rsp

SQL> show con_name
     or Run alter session set container=PDB;
      session altered
SQL>set markup csv on;
    
CON_NAME
------------------------------
CDB$ROOT
SQL> show PDBs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        MOUNTED

SQL> alter pluggable database ORCLPDB  open;

Pluggable database altered

SQL> show PDBs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        READ WRITE NO

------------------------------------------------------------------
To print the database services register in oracle listener.

[root@ol7-19 ~]#  lsnrctl status

[root@ol7-19 ~]#sqlplus sys/password@LOCALHOST:1521/pdb1 as sysdba

SQL>show con_name

To unlock a user:
-------------------
Alter User ${} Account unlock
Alter user hr  account unlock;

To set a password for a user
------------------------------
Alter user hr Identified by  password


Query HR Schema
-----------------------
SQL>set markup csv on;
SQL>select * from hr.employees;


#count employee and give raise
sqlplus hr/hrpass@192.168.1.15/ddb1<<EOF
select count(*) from employees;
update employee set slary=salary*2
commit
quit
EOF

To check fairewall status
# systemctl status firewalld

To stop fairewall status
# systemctl stop firewalld

To disable fairewall status
# systemctl disable firewalld


To configure a instance
----------------------
1) initialization parameter file is used to configure an instance.
2) location of the file is $ORACLE_HOME/dbs directory
3) We can change initalization parameter file with ALTER SYSTEM command
4) A SPFILE is created automatically when you create a CDB.
5) PFILE is a text parameter file contain parameter values in name/value pair
6) parameter are a) sessions b) transactions c) processes
7) SGA_TARGET  parameter (shared memory structure) data and control information
8) Alter System and Alter session command to change the parameter values in SQL plus
9) SQL>create pfile='/home/oracle/pfile_2021.conf' from spfile;
10) Query the view V$PARAMETER
11)select name,value,ises_modifiable,issys_modifiable,ispdb_modifiable from V$PARAMETER
    where name in (nls_date_format', sec_max_failed_login_attempts');
12) ALTER Session set NLS_DATE_FORMAT='mon dd yyyy';
13 ) ISSYS_MODIFIABLE column tell you when the system level parameter change is made by using
     ALTER System command
14) ISSYS_MODIFIABLE values are
     1) immediate
     2) Deffered
     3) False ( parameter with false are called static parameter)
15) Scope caluse
Alter system set SEC_MAX_Failed_login_Attempts=5  Scope=SPFILE;
16) We only change the parameter values with Alter System command wher the
    ISSYS_Modifiable is true in V$Parameter View.
17 SQL> show parameter pool; or select name,value from v$parameter where name like %pool%
18) View V$SPParameter contain information about server parameter file
19) View V$Parameter2 contain information about  parameter which has currently effect of the session
20) View V$System_Parameter2: contain information about the initialzation parameter which has currently in effect of instance

Starting up Oracle Instance
1) startup nomount
2) V$Database view  is not availabe in nomount mode of instance
SQL>select dbid,name,open_mode fro v$database; (give us database not mounted)
SQL>alter database open;
3) startup Mount
    a) associating old started instance with the new started instance
    b) Locatingf and opening all the control files specified in parameter file
    c) Reading the control files to obtain the name of the datafiles and redo log files
     
    Datafiles
      a) system01.dbf
      b) user01.dbf
      c) sysaux01.dbf
4) startup Opend
    a) Open the data file and redo log file as register in  control files
5) Alert Log file
   location can be determine using select * from V$diag_info; view
   location $Oracle_base/diag/rdbms/db_name/SID/trace/alert_<sid>.log
   create, alter, drop datbase and tablespaces  
------------------------------------------------
Start up the instance and compare the logs
------------------------------------------------
T1
oracle@oracle$ . oraenv
oracle@oracle$ echo $Oracle_base
/u01/app/oracle
oracle@oracle$ sqlplus / as sysdba
SQL>startup

T2
oracle@oracle$ tail $ORACLE_BASE/diag/rdbms/orcldb/orcl/trace/alter_orcl.log
 | tee $ORACLE_HOME/example_of_successful_start_up_operation.log
Working with PDBs.
----------------
SQL>show pdbs;
Opening and Closing PDB:
------------------------
Alter Pluggable database  PDB_Name Open;
Alter Pluggable database  PDB_Name Closed;
PDB have 3 Modes
1) R/W
2) ReadOnly
3) MIGRATE
4) MOUNTED (The PDB is shut down/closed)
When instance is started
1) inialization parameter file is read by process
2) path $ORACLE_HOME/dbs directory
3) need to set 5 or 30 or upto 300
Two type of parameter files
1) Server parameter file(SPFILE)
    An SPFILE is automatically created when you create a CDB.
    binary file
    can not be edited  manually
    need ALTER SYSTEM Command for changes in the file
2) Text parameter file (PFILE)
     a) db can not write to this file
     b) manual changes in file required
     c) db instance  restart is required for any change
     d) not downtime free
---------------------------
Diagnostic Repository
---------------------------
Trace file:- When a process detects an error it dumps or write the error information to trace file
and the data is store in ADR automatic diagnostic Reporsitory for traces,alter log, health reports of DBs
---------------------

ADRCI utility to

---------------------

oracle@oracle$ adrci
ADR base= /u01/app/oracle
adrci> show homes
adrci> set home diag/rdbms/orcldb/orcl
adrci> show alert  
ADR home= diag/rdbms/orcldb/orcl:
output the results to files: /tmp/alert_orcl_1.ado
adrci>select shortp_policy,longp_policy from ADR_CONTROL;
adrci>help to see the list of avaiable commands
adrci>show incident
adrci>show incident -mode detail -p " incident_id=123"  then report to oracle support

Note ADR has purging mechanism for  incidents and alert log / trace files
1) Time based retention (ShortP_Policy 720h=30day/Long_Policy 8760h=365days)
2) The Size-base retention for an ADR

Dictionary cache
--------------------------
Data Dictionary
---------------------------
 Is a collection of database tables,view,reference info about the DB and
its structures and its users
Note dynamic view started with V$view_name
1) SQL>Select * from Dictionary order by table_name;
2) Select con_id,dbid,name,open_mode,restricted,total_size from v$pdbs
3) Select owner,table_name from all_tables where owner='HR';
--List currently Login able to login
select username,account_status from dba_users where account_status='OPEN'
--List of table created in a Schema
select table_name,tablespace_name from user_tables;
-- List the name of sequences
select sequence_name,min_value,max_value,incremented by from all_sequences
where sequence_owner in ('MDSYS','XDB')
select * from Dictionary;
DESCRIBE dba_indexes

Note all data dictionary view is updated automatically for consistancy

CDBs_ and DBA_  view are seen by DBA
All_  and User_ view can be Query by everyone.
Quering Dynamic Performance view in Oracle
------------------------------------------
To check session from a specific machine on yesterday
--------------------------------------
select * from V$session where machine='ABC2' and logon_time>sysdate-1;

To  check file states of datafile or dba_data_files
---------------------------------------------------
select * from v$DataFiles

To Find out session and user who locked the database objects
----------------------------------------------------------
select sid,ctime from v$lock where block>0;
To find the SQL statment ( and their associate numberof executions) is the CPU time
is> than 100,00 microseconds
select sql_text,executions from v$sql where cup_time>100,000;
To see the list of backgroud processes
select * from V$BGPROCESS

Shutdown the Database Instance
-----------------------------
*uncommited changes rolled back
*Database buffer cache written to datafiles
*Resources released
*No instance recover on startup

shutdown
shutdown transactional
shutdown immediate

--------------------
Creating PDBs
Ways to find the location of PDBs
-----------------------------
$ grep dataLocation $ORACLE_HOME/install/response/db_install.rsp
oracale.install.db.config.starterdb.fileSystemStorage.dataLocation=/u01/app/oracle/product/19.0.0/db_1/mydbfiles

The parameter name DB_CREATE_FILE_DEST tell the location of oracleManaged datafile (OMF)
2) SQL>show parameter db_create_file_dest;
3rway to find the location of files is to query dba_data_files view
SQL>show con_name
Con_Name
CDB$ROOT
3) select  FILE_NAME from dba_data_files
/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/undotbs01.dbf
/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/users01.dbf

SQL>alter session set container=PDB1;
session altered
SQL> select FILE_NAME from dba_data_files
/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/pdb1/undotbs01.dbf
/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/pdb1/users01.dbf
[root@oracle] su oracle
[root@oracle]$ cd
[root@oracle root] . oraenv
ORACLE_SID= [oracle] ? orcl
[root@oracle root]$ export PS1='printf"\033[30m$ \033[47m"'

To create PDB from SEED we can use the following statement

CREATE PLUGGABLE DATABASE {} ADMIN USER {} IDENTIFIED BY {PWD} ROLES={dba}

SQL>CREATE PLUGGABLE DATABASE pdb2 ADMIN USER admin2 IDENTIFIED BY Oracle_2021 ROLES=(CONNECT) FILE_NAME_CONVERT=
('/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/pdbseed', '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/pdb2')
[oracle@oracle]$sqlplus / as sysdba
[oracle@oracle]$ls /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/pdbseed/
[oracle@oracle]$ls /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/pdb2/
contains same files

SQL> select PDB_ID,PDB_NAME,STATUS,CON_ID,CREATION_TIME from CDB_PDBS;
SQL>show pdbs;
con_id, con_name, Open_Mode, Restricted
2       PDB_SEED   READ ONLY  NO
3       PDB1       READ WRITE NO
4       PDB2       MOUNTED
SQL>alter pluggable database PDB2 open;
pluggable database altered

Select Tablespace_name,status,con_id from CDB_tablespaces
4 new tablespaces have been created from our PDB
1) SYSTEM,
2) SYSAUX
3)  UNDOTBS
4   TEMP
Clonning the Database
1) Hot Clonning
The Source PDB can be open in READ WRITE mode and remain operational during the cloning process
Requires ARCHIVELOG and local undo mode
2) Cold Clonning
The source PDB should be opened in Read ONLY mode
exercise creating PDB3 from PDB1
1) Open PDB1 in READ ONLY mode
we need to  
2) Alter pluggable database pdb1 open Read only; --> open database in Readonly mod
3) CREATE PLUGGABLE DATABASE pdb3 from pdb1 create_file_Dest ='/u01/app/oracle/product/19.0.0/db_1/mydbfiles';
pluggable database created
select name from V$datafiles; give the file location for new PDB datab
select name,con_id, guid from v$pdbs
name con_id, guid
PDB1  3     BB8906
PDB1  5     C704
Cloning Remote PDB
----------------------
SQL create public database link  LINK_PDB1 conect ot  hr identified by "hr" using
'(Description=(Address=(Protocal=TCP)(HOST=localhost)(PORT=1521)(CONNECT_DATA=
 (SERVICE_NAME=pdb1)))';

CREATE PLUGGABLE DATABASE pdb4 from pdb1@link_pdb1
Listener is running on the remote side
$lsnr status
SQL>alter session set db_create_file_dest='/u01/app/oracle/product/19.0.0/db_1/mydbfiles';
session altered.
On the remote side Grant the permission to create pluggable database
SQL> Grant create pluggable database to hr;
SQL> alter session set container=CDB$ROOT;
SQL>create public database link  LINK_PDB1 conect ot  hr identified by "hr" using
'(Description=(Address=(Protocal=TCP)(HOST=localhost)(PORT=1521)(CONNECT_DATA=
 (SERVICE_NAME=pdb1)))';
SQL>alter pluggable database PDB4 open;
SQL>show pdbs;
SQL> drop public database link LINK_PDB1;
SQL>CREATE pluggable database pdb1_ref_cln1 from pdb1@link_pdb1 refresh mode every 60 Minutes
SQL>CREATE pluggable database pdb1_ref_cln1 from pdb1@link_pdb1 parallel 2 standbys=NONE Refresh Mode Manual;
SQL>Alter pluggable database pdb1_ref_cln2 referesh Mode NONE;
SQL> select * from pdb_plug_in_violations where name='pdb1_ref_cln2';
To unplug a PDB
1)Close PDB
2) Generate an XML metadata file (.xml ext)
alter pluggable database pdb4 unplug into '/home/oracle/pdb4.xml';
3) Drop PDBj
4) Copy files to Remote System if it is needed
To plugging a PDB using 3 Methods
1) NOCOPY Method
Create pluggable database pdbx using '/home/oracle/pdb4.xml' NOCOPY TEMPFILE REUSE;
2) COPY Method
Create pluggable database pdbx using '/home/oracle/pdb4.xml' COPY FILE_NAME_CONVERT('pdb4_dir','pdb5_dir');
3) AS CLONE or MOVE/COPY Method
Create pluggable database pdbx using '/home/oracle/pdb4.xml' MOVE;
select * from gV$session s  join gv$pdbs p
on  p.con_id=s.con_id  and p.inst_id=s.inst_id
Oracle Net
Listener
 listener is a process that runs onthe the server and listens for client connection requests
Database Service:
is logical representation of a database, which is the way that the base is presented to clients.
Oracle Net configuration files are
1) $ORACLE_HOME/network/admin/listener.ora
1) $ORACLE_HOME/network/admin/sqlnet.ora
[root@oracle] su oracle
[root@oracle]$ cd
[root@oracle root] . oraenv
ORACLE_SID= [oracle] ? orcl
[oracle@oracle]$sqlplus / as sysdba
[oracle@oracle]$ cat $ ORACLE_HOME/network/admin/listener.ora
LSNRCTL command is used to administer the listener.
[oracle@oracle]$lsnrctl start
[oracle@oracle] ps auxww | grep tnslsnr
Net Service Covers Firewall,Network Traffic Encryption and Listener Security.
Listener Registration Process
LREG
1)Name of the database services
2) Name of the database instance
3) Service handlers available for the instance
SQL> show parameter listener
Name, Type, Value,
forward_listener,string
listener_network,string
local_listener, string,   Listener_orcl
remote_listener string
SQL>cat $ORACLE_HOME/network/admin/tnsnames.ora
To check the list of all registered service
[oracle@oracle]$lsnrctl status
[oracle@oracle]$sqlplus hr/hr@pdb1_conn ( alias name defined in Listener)
------------------------------------------------------------------------
Data Access Control Covers
Privilege/Roles
Virtual private Database
User Creation
Creat User statement
Create user c##abc identified by pass123 Container=ALL default tablespace users Temporary Tablespace temp Account Unlock
User created in specified PDBs
alter session set container=pdb1;
show con_name
Create user c##abc identified by pass123
alter session set container=cdbs$Root;
show con_name
Select  p.pdb_id,

 p.pdb_name,
 u.username,
 u.common
from DBA_PDBs p , CDB_Users u
where  p.pdb_id=u.con_id
order by p.pdb_id;

GRANT create session to abcuser container=ALL
Creat ROLE  abcrole Container=ALL
------------------- create a role then assign grant priviliage to those Role as shown below---
Create Role HR_Clerk;
Grant create session to HR_Clerk
Grant select,update on Hr.employees To hr_clerk
Grant ALL on hr.jobs to Hr_clerk;
Grant hr_clerk to hr_mngr
Now Grant Roles to the users in the environment
tmp table space
Quota size on tablespace
select * from CDB_TEMP_FILES
Revoke select on hr.employee from hr_clerk
Drop Role VIP_SECURE;
Drop User TOM
Authetication of user by Operating System
--------------------------------------------
[oracle@oracle]$grep 'oracle' /etc/group
groupname:password:GroupID:user_list
oinstall :   x:      54321:           oracle

orapwORACLE_SID
[oracle@oracle]$ ls $ORACLE_HOME/dbs/orapworcl

SQL>show parameter REMOTE_LOGIN_PASSWORDFILE;
SQL>select username,sysdba from v$PWFILE_USERS;

profile of users
-------------------
vim $ORACLE_HOME/rdbms/admin/utlpwdmg.sql
utlpwdmg.sql script changes the Default profile with Alter Profile command
To create profile
----------------------
Function of profile:-
------------------------------
Using Profile we can control following
---------------------------------------

1)CPU Resources( limit on per session )
2)Network and Memory Resources(idle time of concurrent Sessions)
3)Disc IO Resources(limit on total number of reads)

A)Create user joey identified by oracle
B)Grant create session to joey;
C)Select username,user_id,profile from cdb_user where username='JOEY';
D)Alter profile abc limit idle_time 30;
SQL>create profile abc_profile limit
SQL>select * from CDB_Profiles
SQL>create profile abc  limit idle_time 60;
SQL>select * from dba_TS_QuoTas

DATA Confidentiality Covers following
Data Maksing
DBMS_CRYPTO
DB Storage Security
---------------------------
Transparent Data Encription
Data Encryption/ Oracle Secure Backup Encription in following ways
  1)Data files and OS files goes to OSB---Encripted-- TapeDevices
                   2)Redo log files
                   3)Archived Redo log files
                   4)Backup files
is  manages by key automatically.
Expdp Data is encrypted to dump file
Data is import/Exported using Parameter
                  1) ENCRYPTION
                  2) ENCRYPTION_MODE
                  3) ENCRYPTION_PASSWORD
                  4)  ENCRYPTION_ALGORITHM
STEPS
Create Software Keysotre
Create a wallet Directory
mkdir -p $ORACLE_BASE/admin/orcldb/wallet/tde
Set the key store Location & Type
WALLET_ROOT="/u01/app/oracle/admin/orcldb/wallet"

Create a password-base Software KeyStore
create keystore '/u01/app/oracle/admin/orcldb/wallet/tde'
--------------------------------------------------------
[oracle@oracle]$mkdir -p $ORACLE_BASE/admin/orcldb/wallet/tde
[oracle@oracle]$sqlplus / as sysdba
To Create a pfile from SPFILE
SQL> create pfile='home/oracle/backup/init786.ora' from spfile;
SQL>alter system set WALLET_ROOT="/u01/app/oracle/admin/orcldb/wallet" scope=spfile;
SQL>shutdown immediate
Database closed.
Database dismounted.
Oracle instance shut down.
SQL>startup
Total System Global Area
Fixed Size
Variable Size
Database Buffer
Reo Buffer
Database mounted.
Database Opened.
SQL>alter system set TDE_CONFIGURATION="KEYSTONE_CONFIGURATION=FILE" scope=spfile;
System altered.
SQL>shutdown immediate
Database closed.
Database dismounted.
SQL>startup
Total System Global Area
Fixed Size
Variable Size
Database Buffer
Reo Buffer
Database mounted.
Database Opened.
SQL>administer key management create keystore '/u01/app/oracle/admin/orcldb/wallet/tde'
keystore altered.
SQL> select status from v$encryption_wallet;
SQL>set markup csv on;
SQL>select WRL_TYPE,SRL_Parameter,status,con_id from v$encryption_wallet;
SQL>!ls -ltr /u01/app/oracle/admin/orcldb/wallet/tde
SQL>select Tablespace_name,encrypted from  dba_tablespaces;
Do regular copies of Keystore to some 2nd storages
-------------------------------------------------------------------------------------
TableSpace:-
A tablespace is an allocation of space in DB that can contail shema objects
When a table is created a segment is create to hold it data.
A samll file tablespace contains 1022 datafiles
each of which contains 4 million blocks
Collection of segments is called table space.
We can create 3 Type of table spaces
1) Permanent tablespaces
2) Temporary tablespaces
3) Undo tablespaces
SQL>select property_value from database_properties
    where property_name='Default_TBS_Type';
SmallFile.
Create tablespace orcl_cdb_users
default '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/orcl_users01.dbf' size 1M;
Tablespace created
SQL> alter session set contain=PDB1;
 session altered.
Create tablespace orcl_pdb_users
default '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/orcl_users01.dbf' size 1M;
create table t1( id int generated always as identity,
           column varchar2(32)
)tablespace orcl_pdb_users;
SQL>Begin
For counter IN 1..100 loop
    insert into t1(column1)
    values(sys_guid())
End loop;
End;
sql procedure successfully completed.
select tablespace_name,
 bytes /1024/1024 MB
 from
 dba_free_space
where tablespace_name='ORCL_PDB_USERS';
TableSpaceName,"MB"
ORCL_PDB_USERS, 1.2
We can increase the size of ORCL_PDB_USERS01.dbf file from 1MB to 10MB using following command
SQL>Alter database datafile '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/orcl_users01.dbf' resize 10m;
SQL>Create tablespace orcl_pdb_users
default '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/orcl_users01.dbf' size 1M
AUTOEXTEND ON NEXT 10M MAXSIZE 3G OR UNLIMITED
Create BigFILE tablespace orcl_pdb_users
default '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/orcl_users01.dbf' size 1G;
select tablespace_name,
 status, Bigfile
 from
 dba_free_space
Create tablespace orcl_pdb_encrypt
default '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/orcl_users01.dbf'
size 100M
Encryption using '3DES168' --,AES128,AES192
default storage (encrypt)
view related to tablespace are
1) cdb_tablespaces
2) dba_tablespaces
3) v$tablespace
data files information will in following view
Cdb_data_files
dba_data_files
V$datafiles
Temp file information
1)cdb_temp_files
2) dba_temp_files
3) v$tempfile
Alter TableSpace
Increase/Decrease File Size
Alter database datafile '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/orcl_users01.dbf' resize 10m;
Add Data Files to existing Table Space
Alter tablespace ORCL_PDB_USERS
 datafile '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/orcl_users02.dbf' resize 10m;
Alter tablespace ORCL_PDB_USERS
 datafile '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/orcl_users02.dbf' resize 10m;
 autoextend on
next 512K
Maxsize 2G
--To check the files associate to tablespace ORCL_PDB_USERS
select tablespace_name,file_name, bytes /1024 /1024 MB from dba_data_files

Change Satee of Table space
Alter tablespace ORCL_PDB_USERS Read Only;
Online/offline
Alter tablespace ORCL_PDB_USERS Read Write;
Alter tablespace ORCL_PDB_USERS offline Normal;
Move Data Files
Take TS offline
Move Files
 mkdir /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/pdb1_extra
 mv    /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/orcl_users02.dbf /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/pdb1_extra/orcl_pdb1_users02.dbf
Alter TableSpace Rename DataFiles
Take TS onlin
----------------------------------------------------------------------------------
Oracle Managed Files
Parameters                    Description
DB_CREATE_FILE_DEST          (define the location of the default file system directory for data files&temporary files)
DB_CREATE_ONLINE_LOG_DEST    Define the location for redo log files and control file creation
DB_RECOVERY_FILE_DEST        Gives the default location for the fast recovery area
SQL>alter system set db_create_file_dest='/u01/app/oracle/product/19.0.0/db_1/mydbfiles' scope=Both
SQL CREATE tablespace orcl_CDB_OK
-------------------------------------
Enable ARCHIVELOG mode
---------------------------------------
SQL>archive log list;
Database log mode                 No Archive Mode
Automatic archival                Disabled
Archive destination               USE_DB_RECOVERY_FILE_DEST
Oldest Online log sequence         114
Current log sequence               116
SQL>shutdown immediate
SQL>startup mount
SQL>alter database archivelog;
SQL>alter database open;
SQL>select log_mode from v$database;
Log_mode=ARCHIVELoG
SQL>select flashback_on from v$database
Flashback_on
----
NO
SQL>alter database flashback on;
SQL>show con_name;
con_name
----------
pdb
Show user
User is HrS
SQL>create table us_states(id number(8) not null, name varchar2(50) not null);
    table created
SQL>Insert into us_states vlaues(101,'SANDI');
commit;
wait for some time ///Run FLASHBACK Query to get uno data by specifing SCN
SQL> select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual
to_char(sysdate,'DD
--------------------
select * from hr.us_states as of timestamp to_timestamp('15-10-2021 06:15:00','dd-mm-yyyy hh24:mi:ss')
select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') ddate, dbms_flashback.get_system_change_number scn from dual;
DDATE                   SCN
----------              -----
14-10-2022 05:30:41      6664607

select scn_to timestamp(6664607) ddate, timestamp_to_scn(to_timestamp('15-10-2021 06:15:00','dd-mm-yyyy hh24:mi:ss')) scn  from dual;
UNDO management
-------------
alter session set container=pdb1;
show parameter undo_management;
select tablespace_name,content,status from dba_tablespaces where contents='UNDO'
to view all the datafile used by undoTBS1
SQL>select file_name from dba_data_files where tablespace_name=undotbs1' order by 1;
DBA_UNDO_EXTENTS
select * from V$UNDOSTAT
A-------------------------------------------------------------------------------------------------------------

(FAST Recovery Area) Backup_Recovery Parameter

db_recovery_file_dest_

db_recovery_file_dest_size (used to control the max of space to store backup file in side FRA)

db_recovery_file_dest-control (the location of FRA)

SQL>show parameter db_recovery_file_dest_size

name,type,value

db_recovery_file_dest,string,/u01/app/oracle/product/19.0.0/db_1/myfiles

db_recovery_file_dest_size,big integer,10G

To increase the size of FRA

----------------------------

SQL>Alter System set db_recovery_file_dest_size=15G SCOPE=BOTH 

SQL>show parameter db_recovery_file_dest_size

name,type,value

db_recovery_file_dest,string,/u01/app/oracle/product/19.0.0/db_1/myfiles

db_recovery_file_dest_size,big integer,15G

To monitor FRA you can query two Views

V$Recovery_File_Dest and V$Recovery_Area_Usage

Other thing We can do the following use

Multiplex control files, traced back ups and may be used to view the content of control files.

Control files can be back up to a trace file

Lets create a backup of control File,

to a trace file to see the information store in a control files

SQL>show con_name

CDB_$Root

SQL>alter database backup controlfile to trace;

Database altered

Step Now check the record in the alert log ,

     that point to the file name on the O.C for our backup

oracle@racle]tail /u01/app/oracle/diag/rdbms/orcldb/orcl/trace/alert_orcl.log

backup written to control as follows

/u01/app/oracle/diag/rdbms/orcldb/orcl/trace/orcl_ora_14147.trc

Current setting of our control files

--------------------------------------

show parameter control files

name,         type,            value

control files,string,/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control01.ctl,

                     /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control02.ctl

Creating 3rd copy of control file for your self 

------------------------------------------------

Alter system set control_file='/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control01.ctl',

                              '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control02.ctl',

                              '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control03.ctl' SCOPE=SPFILE;

System altered.

SQL>shutdown immediate.

database closed

database dismounted.

oracle instance shut down.

[oracle@oracle]cp 

Then copy an existing control file to the another location by your selection.

[oracle@oracle~]cp /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control02.ctl /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control03.ctl

[oracle@oracle~]ls /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control0*

SQL>startup 

Next step to multiplex files and redo log group

---------------------------------------------------

Instance/Disk failure is recoverdusing redo log information is used to roll data file forward

Redo log group are made up of redo log files

Redo log is duplicate to group at least two file per group

Join V$log and V$logfile to see all redo log member and their status

--------------------------------------------------------------------

select b.group#,a.status, b.status,b.member from V$log , V$logfile where a.group#=b.group# order by 1,2

Group#,status,status,member

Add addition member to each group

--------------------------------

SQL>Alter database 

add logfile member '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/another_disk/redo01.log'

to  group 1;

exit;

[oracle@oracle~]mkdir /u01/app/oracle/product/19.0.0/db_1/mydbfiles/another_disk

[oracle@oracle~]sqlplus / as sysdba

SQL>set markup csv on;

SQL>Alter database 

add logfile member '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/another_disk/redo01.log'

to  group 1

SQL>add logfile member '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/another_disk/redo02.log'

to  group 2

Next Step is to enable archiving of the redo logs

------------------------------------------------

Note Every single 

1)Insert,

2)delete

3)update statemetn is recorded in the redo logs files.

------------------------------------------------------

Redo log Switch

------------------------------------------------------

When Oracle finished writing to  a redo log grop that is group number 1,

It performs a redo log Switch.

After that redo log group 1 is archiving to redo log backup to some external storage for recovery purpose.

----------

Note:-We rely on the DB archive redo logs that contains/records the history of all committed DB Transactions

---

SQL>archive log list; 

----------

(FAST Recovery Area) Backup_Recovery Parameter

db_recovery_file_dest_

db_recovery_file_dest_size (used to control the max of space to store backup file in side FRA)

db_recovery_file_dest-control (the location of FRA)

SQL>show parameter db_recovery_file_dest_size

name,type,value

db_recovery_file_dest,string,/u01/app/oracle/product/19.0.0/db_1/myfiles

db_recovery_file_dest_size,big integer,10G

To increase the size of FRA

----------------------------  FRA------------------------------------------------------                

SQL>Alter System set db_recovery_file_dest_size=15G SCOPE=BOTH 

SQL>show parameter db_recovery_file_dest_size

name,type,value

db_recovery_file_dest,string,/u01/app/oracle/product/19.0.0/db_1/myfiles

db_recovery_file_dest_size,big integer,15G

To monitor FRA you can query two Views

V$Recovery_File_Dest and V$Recovery_Area_Usage

Other thing We can do the following use

Multiplex control files, traced back ups and may be used to view the content of control files.

Control files can be back up to a trace file

Lets create a backup of control File,

to a trace file to see the information store in a control files

SQL>show con_name

CDB_$Root

SQL>alter database backup controlfile to trace;

Database altered

Step Now check the record in the alert log ,

     that point to the file name on the O.C for our backup

oracle@racle]tail /u01/app/oracle/diag/rdbms/orcldb/orcl/trace/alert_orcl.log

backup written to control as follows

/u01/app/oracle/diag/rdbms/orcldb/orcl/trace/orcl_ora_14147.trc

Current setting of our control files

--------------------------------------

show parameter control files

name,         type,            value

control files,string,/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control01.ctl,

                     /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control02.ctl

Creating 3rd copy of control file for your self 

------------------------------------------------

Alter system set control_file='/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control01.ctl',

                              '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control02.ctl',

                              '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control03.ctl' SCOPE=SPFILE;

System altered.

SQL>shutdown immediate.

database closed

database dismounted.

oracle instance shut down.

[oracle@oracle]cp 

Then copy an existing control file to the another location by your selection.

[oracle@oracle~]cp /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control02.ctl /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control03.ctl

[oracle@oracle~]ls /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control0*

SQL>startup 

Next step to multiplex files and redo log group

---------------------------------------------------

Instance/Disk failure is recoverdusing redo log information is used to roll data file forward

Redo log group are made up of redo log files

Redo log is duplicate to group at least two file per group

Join V$log and V$logfile to see all redo log member and their status

--------------------------------------------------------------------

select b.group#,a.status, b.status,b.member from V$log , V$logfile where a.group#=b.group# order by 1,2

Group#,status,status,member

Add addition member to each group

--------------------------------

SQL>Alter database 

add logfile member '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/another_disk/redo01.log'

to  group 1;

exit;

[oracle@oracle~]mkdir /u01/app/oracle/product/19.0.0/db_1/mydbfiles/another_disk

[oracle@oracle~]sqlplus / as sysdba

SQL>set markup csv on;

SQL>Alter database 

add logfile member '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/another_disk/redo01.log'

to  group 1

SQL>add logfile member '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/another_disk/redo02.log'

to  group 2

Next Step is to enable archiving of the redo logs

-------------------------------------------------

Note Every single 

1)Insert,

2)delete

3)update statemetn is recorded in the redo logs files.

------------------------------------------------------

Redo log Switch

------------------------------------------------------

When Oracle finished writing to  a redo log grop that is group number 1,

It performs a redo log Switch.

After that redo log group 1 is archiving to redo log backup to some external storage for recovery purpose.

----------

Note:-We rely on the DB archive redo logs that contains/records the history of all committed DB Transactions

---

SQL>archive log list; 

----------

RMAN is the primary backup technology of oracle.

that manages 

1)Backup

2)Restore

3)Recovery process

4)RMAN can store backup on disk for Quick recovery.

5)RMAN can do incremental,full backup and whole complete database backup

6)RMAN support Full backup(copy of all Data Blocks),incremental backup(only copy data block of DB which is changed compared to previous Backup)

7)RMAN have built in configuration options.

RMAN>

Backup incremental level 0 database;

Backup incremental level 1 database

Backup incremental level 1 CUMULATIVE database

DB 

Data Files  

Control Files 

Archived Log goes into the --->RMAN-->Backup set/Backup Pieces

Image copies are exact byte for the byte copies of files and recorded in RMAN respository.

RMAN creates image copies only on disk.

The file protected by RMAN includes to reconstruct the database.

1)data files

2) control files

3)Server parameter files

4)Archived redo log files

Logical backup includes

exporting database object such as  

1) table 

2)table space 

which become supplyment to physical backups for database.

[root@oracl] su oracle

[oracle@oracl]$ cd

[oracle@oracl]$ rman target=/

Recovery Manager: Release ----

Connected to target db:abcDB

RMAN>show all; --to see the available configuration option of RMAN

configure backup retention policy to redundancy 1

RMAN>show retention policy;

RMAN configuration parameters for db with unique_name abcDB are

configure backup retention policy to redundancy 1; #default

RMAN>configure retention policy to recovery window of 7 days

new RMAN configuration parameters:

RMAN>show retention policy;

 RMAN>configure retention policy clear

---------------------------------------------

To take full backup of the database

---------------------------------------------

[root@oracl] su oracle

[oracle@oracl]$ cd

[oracle@oracl]$ rman target=/

Recovery Manager: Release ----

Connected to target db:abcDB

RMAN>backup database plus archivelog;

starting backup at date-------

using target database control file instead of recovery catalog

allocated channel:ora_disk_1

RMAN>backup database root;

RMAN>backup pluggable database pdb;

[oracle@oracl]$ lsnrctl status

-------------

Directly connect to pdb database through RMAN and do backup

----------------

[oracle@oracl]$rman traget=sys / or_2022@localhost/pdb

RMAN> backup database;

or

RMAN> backup database tag 'pdb_test_Bckup'

-----

Incremental backups

------

RMAN> backup incremental level 0                database tag 'cdb_incm_level0' plus archivelog;

RMAN> backup incremental level 1                database tag 'cdb_incm_level1' plus archivelog;

RMAN> backup incremental level 1  cumulative     database tag 'cdb_incm_level1' plus archivelog;

RMAN> list backup

RMAN> list backup summary;

RMAN> list backup of database pdb2

RMAN> list backup of datafile 10

RMAN> list backup of datafile 'u01/app/oracle/product/19.0.0/db_1/yourdbfiles/orcDB/pdb2/system01.dbf'(show only the image copy of datafile)

RMAN> list backup by file;

RMAN> list archivelog all;

RMAN> list backup of controlfile

---------------------------------

RMAN Views

---------------------------------

V$RMAN_Backup_job_details

V$RMAN_status

V$backup_set

V$pdbs(contain all PDEs in CDB)

Understanding Basic of Pandas

 Basic of Pandas data frame

import pandas

l = [["a", 12, 12], [None, 12.3, 33.], ["b", 12.3, 123], ["a", 1, 1]]

df = pandas.DataFrame(l, columns=["a", "b", "c"])
print(df)

Loading the data
df=pd.read_csv("D:\\Pandas\\data.csv")

Printing its column
print(df.columns)

Query the columns
  result_df = df.query("colname=='valueincolum'").groupby(['columnname']).agg({'columname': 'sum'})

  Example #sum_df = df.groupby(['year','time']).agg({'cricketscore': 'sum', 'Totalscore': 'sum'})

Combining the two dataframes

df3=pd.concat([df, result_df])

 Remaining the columns of a dataframe

 df.rename(columns={"Transport_Cost(PKR)":"TransferCostPKR","PerUnitPrice(PKR)":"UnitPrice_in_PKR"},inplace=True)
df.info()

 

Query the data set  using Pandas
import pandas as pd
df=pd.read_csv("data.csv")
df=df[["car_model_name","car_type_name","car_Speed_Miles"]]
df1=df[(df.car_model_name=='COONDAA') & (df.car_type_name.str.contains("CKIAA"))]
df1=df1.groupby(["car_model_name","car_type_name"]).agg({'car_Speed_Miles':'sum'})
print(df1)

Making Joins on two data set using Pandas
import pandas as pd
df1=pd.read_csv("1.csv")
df2=pd.read_csv("2.csv")
df3=pd.merge(df1,df2,on='common_column_name',how='inner')
print(df3)

Converting Object to Date and getting Month from the date in Pandas

 import pandas as pd
df=pd.read_csv("D:\\Pandas\\Practice\data.csv")
df["Date"]   =pd.to_datetime(df["Date"])
if df["Date"].dt.month==1:
    print("Jan")

Concatenating and Traverse a list  in Pandas

import pandas as pd
df=pd.read_csv('datalist.csv')
l=df['first_name']
names=''
start=0
end=start+4
for i in range(1,4):#1,2,3
    if i>1:
       start=end
       end= start+4
    
    for j in range(start,end):
        names=names+"'"+str(l[j])+"'"+","
        print(j)
print(names)

Taking the name one by one in  a list  in Pandas

sample name list:first_name_samples,Tahir,Art,Khalid,Lion,Simon,John,Lota,Sage,Kris,Minna,Table 

 import pandas as pd
store=[]
df=pd.read_csv('datalist.csv')
l=df['first_name']
names=''
start=0
p=0
end=start+4
for i in range(1,4):#1,2,3
     
    if i>1:
       start=end
       end= start+4
    
    for j in range(start,end):
        names=names+"'"+str(l[j])+"'"+","
    store.append(names)
    names=''       
print(store[2])

Reading Files name from the Folder

import os
y=[]
with os.scandir() as i:
    for entry in i:
        if entry.is_file():
            n=entry.name.split('.')
            y.append(n[0])
            print(n[0])
            print(len(y)) 

Applying SQLQueries on data frames using pandassql Package

import os
import pandas
import pandasql  
df=pandas.read_csv("D:\\Pandas\\data.csv")
query = "SELECT *  FROM df "
queryresult=pandasql.sqldf(query)
print(queryresult) 

 

Using List to Prepare a IN Clause String to Query  in SQL Script

import pandas as pd

msisdn_list=[]

batch1=''

query = """

SELECT  num

FROM tableA a,

             tableB b,

WHERE a.abc=b.abc

and num  in ({})

"""

 df = pd.read_csv('yourdata.csv')

df['num'] = df['num'].astype(str)

df['num']= df['num'].map(lambda m: m.split('.')[0])

num_list=df['num'].unique().tolist()

#num_list=str(num_list)[1:-1]

final_num_list=num_list

 

for j in range(0,999):

    batch1= batch1+"'"+str(final_num_list[j])+"'"+","

batch1=batch1[:-1]

print(batch1)

query= query.format(batch1)

Result= pd.read_sql(query, con)

Result.to_csv('./yourresult.csv', index=False, encoding='utf-8-sig')

 Adding File Name as column in data frame

import os
import pandas as pd
fname=[]
for f in os.listdir("D:\\New folder"):
    if f.endswith('csv'):
    print(f)
    fname.append(f)
df = pd.DataFrame()
for i in range(1,len(fname)):
    data=pd.read_csv("D:\\New folder\\"+fname[i]+" ", dtype=str)
    data['FileName']=fname[i]
    df = df.append(data)
print(df)
df.to_csv('.\\abc.csv',index=False)