1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56
| # Conexion a la base de datos por odbc
library("RODBC")
con <- odbcConnect("bico", uid="qq372719", pwd="xxxxxxx",believeNRows=FALSE )
# Querys para sacar la info que necesitamos
dbName <- sqlQuery(con, "SELECT instance_name, host_name from v$instance",errors=FALSE)
nbrbdlinks <- sqlQuery(con, "SELECT count(1) as NBR from dba_db_links where owner <>'PUBLIC'",errors=FALSE)
nbrPbdlinks <- sqlQuery(con, "SELECT count(1) as NBR from dba_db_links where owner='PUBLIC'",errors=FALSE)
dest <- sqlQuery(con, "SELECT DB_LINK, substr(host,1,10) HOST from dba_db_links where owner <>'PUBLIC'",errors=FALSE)
destP <- sqlQuery(con, "SELECT substr(host,1,10) HOST from dba_db_links where owner = 'PUBLIC'",errors=FALSE)
linkName <- sqlQuery(con, "select db_link from dba_db_links where owner <>'PUBLIC'", errors=FALSE)
PlinkName <- sqlQuery(con, "select db_link from dba_db_links where owner='PUBLIC'", errors=FALSE)
# Empieza el dibujo aqui
# Info general
plot(c(1, 200), c(1, 200), type= "n", xlab = "", ylab = "")
title (main = paste0("Mapa de los dblinks del entorno: ", dbName$INSTANCE_NAME, "_",
dbName$HOST_NAME),sub="Luis Diaz - Emergencies & improvments")
# Repartition de dblinks por tipo (publico o no)
text(35,203,cex=0.8,"dbLinks no públicos")
rect(40, 180, 60, 200, density = 0, lty=2,border = "green")
text(45,190,paste0(nbrbdlinks))
text(175,203,cex=0.8,"dbLinks públicos")
rect(180, 180, 200, 200, density = 0,border = "yellow")
text(185,190,paste0(nbrPbdlinks))
## Pintar los destinos de los dblinks NO PUBLICOS
for(row in 1:nrow(linkName)){
query <- paste0("select count(1) from dual@", linkName$DB_LINK[row])
test <- sqlQuery(con, query)
if (test[1]=='1'){
segments(45,180, (((row-1)*200/nbrbdlinks$NBR)+5), 80, col= 'green')
}else{
segments(45,180, (((row-1)*200/nbrbdlinks$NBR)+5), 80, col= 'red')
}
text ((((row-1)*200/nbrbdlinks$NBR)+5),45, cex=0.5, paste(dest$DB_LINK[row], '_', dest$HOST[row]),srt=90)
}
## Pintar los destinos de los dblinks PUBLICOS
for(rowP in 1:nrow(PlinkName)){
queryP <- paste0("select count(1) from dual@", PlinkName$DB_LINK[rowP])
testP <- sqlQuery(con, queryP)
if (testP[1]=='1'){
segments(185,180, (((rowP-1)*200/nbrbdlinks$NBR)+180), 130, col= 'green')
}else{
segments(185,180, (((rowP-1)*200/nbrbdlinks$NBR)+180), 130, col= 'red')
}
text ((((rowP-1)*200/nbrbdlinks$NBR)+180),125, cex=0.5, paste(dest$DB_LINK[rowP], '_', dest$HOST[rowP]))
} |
Partager