def DropPDB(self, pdb_name):
self.PDB_NAME = pdb_name
close_sql = """alter pluggable database {pdb} close""".format(pdb=self.PDB_NAME)
drop_sql = """drop pluggable database {pdb} including datafiles""".format(pdb=self.PDB_NAME)
try:
with ConnectDB.get_connect() as db_cursor:
db_cursor.execute(close_sql)
db_cursor.execute(drop_sql)
except cx_Oracle.DatabaseError as e:
return e
def DropUser(self, pdb_name, user_name):
self.PDB_NAME = pdb_name
self.USERNAME = user_name
switch_sql="alter session set container={pdb}".format(pdb=self.PDB_NAME)
drop_sql="drop user {user} cascade".format(user=self.USERNAME)
try:
with ConnectDB.get_connect() as db_cursor:
db_cursor.execute(switch_sql)
db_cursor.execute(drop_sql)
except cx_Oracle.DatabaseError as e:
return e
def CheckPDB(self, pdb_name):
self.PDB_NAME = pdb_name
try:
with ConnectDB.get_connect() as db_cursor:
db_cursor.execute("select count(1) from dba_pdbs where pdb_name= upper(:pdb)", pdb=pdb_name)
if db_cursor.fetchall()[0][0]:
return True
else:
return False
except cx_Oracle.DatabaseError as e:
return False
def ShowPDB(self):
select_sql = "select NAME, OPEN_MODE from V$PDBS order by CON_ID"
try:
with ConnectDB.get_connect() as db_cursor:
db_cursor.execute(select_sql)
db_records = db_cursor.fetchall()
print('数据库列表'.center(40, '#'))
for name, mode in db_records:
print("数据库名:", name, " 打开模式:", mode)
print(''.center(45, '#'))
except cx_Oracle.DatabaseError as e:
return e
def CheckProfile(self, pdb_name):
self.PDB_NAME = pdb_name
select_sql="select count(1) from cdb_profiles a join dba_pdbs b on a.CON_ID=b.CON_ID where PROFILE='DEFAULT1' and PDB_NAME=upper('{pdb}')".format(pdb=self.PDB_NAME)
try:
with ConnectDB.get_connect() as db_cursor:
db_cursor.execute(select_sql)
if db_cursor.fetchall()[0][0]:
return True
else:
return False
except cx_Oracle.DatabaseError as e:
return False
def CreateProfile(self, pdb_name):
self.PDB_NAME = pdb_name
switch_sql = "alter session set container={pdb}".format(
pdb=self.PDB_NAME)
create_sql = "create PROFILE default1 LIMIT FAILED_LOGIN_ATTEMPTS unlimited PASSWORD_LIFE_TIME unlimited PASSWORD_REUSE_TIME unlimited PASSWORD_REUSE_MAX unlimited PASSWORD_VERIFY_FUNCTION default PASSWORD_GRACE_TIME unlimited"
try:
with ConnectDB.get_connect() as db_cursor:
db_cursor.execute(switch_sql)
db_cursor.execute(create_sql)
return True
except cx_Oracle.DatabaseError as e:
return e
def CheckTBS(self, pdb_name, user_name):
self.PDB_NAME = pdb_name
self.USERNAME = user_name
select_sql="select count(1) from cdb_tablespaces a join dba_pdbs b on a.CON_ID=b.CON_ID where TABLESPACE_NAME like upper('%{username}%') and PDB_NAME=upper('{pdb}')".format(username=self.USERNAME,pdb=self.PDB_NAME)
try:
with ConnectDB.get_connect() as db_cursor:
db_cursor.execute(select_sql)
if db_cursor.fetchall()[0][0]:
return True
else:
return False
except cx_Oracle.DatabaseError as e:
return False
def ListUser(self, pdb_name):
self.PDB_NAME = pdb_name
select_sql = "select USERNAME,created,profile from cdb_users a join dba_pdbs b on a.CON_ID=b.CON_ID where PDB_NAME=upper('{PDB_NAME}') order by created desc fetch first 10 rows only".format(
PDB_NAME=self.PDB_NAME)
try:
with ConnectDB.get_connect() as db_cursor:
db_cursor.execute(select_sql)
db_records = db_cursor.fetchall()
print('用户列表'.center(40, '#'))
for user, ctd, profile in db_records:
print('用户:', user, ",创建时间:,", ctd, ",概要文件:", profile)
print(''.center(44, '#'))
except cx_Oracle.DatabaseError as e:
return e
def ShowUser(self, pdb_name, user_name):
self.PDB_NAME = pdb_name
self.USERNAME = user_name
select_sql = """select PDB_NAME,USERNAME,DEFAULT_TABLESPACE from cdb_users a join dba_pdbs b on a.CON_ID=b.CON_ID where PDB_NAME=upper('{PDB_NAME}') and ( '{USER_NAME}' is null or username like upper('%{USER_NAME}%')) order by created""".format(
PDB_NAME=self.PDB_NAME, USER_NAME=self.USERNAME)
try:
with ConnectDB.get_connect() as db_cursor:
db_cursor.execute(select_sql)
db_records = db_cursor.fetchall()
print('用户信息'.center(40, '#'))
for pdb, user, tbs in db_records:
print("数据库名: ", pdb_name, ",用户名:", user,
",默认密码:ChangeMe,默认表空间:", tbs)
print(''.center(44, '#'))
except cx_Oracle.DatabaseError as e:
return e
def CreatePDB(self, pdb_name):
self.PDB_NAME = pdb_name
create_sql = """CREATE PLUGGABLE DATABASE {pdb} ADMIN USER admin IDENTIFIED BY {passwd} ROLES=(CONNECT,DBA)
DEFAULT TABLESPACE {pdb} DATAFILE '{datafilepath}/{dbname}/{pdb}/{pdb}.dbf'
SIZE 100M AUTOEXTEND ON PATH_PREFIX = '{datafilepath}/{pdb}/'
FILE_NAME_CONVERT = ('{datafilepath}/{dbname}/pdbseed', '{datafilepath}/{dbname}/{pdb}')""".format(
pdb=self.PDB_NAME,
passwd='"ChangeMe"',
datafilepath=settings.realfilepath,
dbname=settings.db_name)
open_sql = "alter pluggable database {pdb} open".format(
pdb=self.PDB_NAME)
try:
with ConnectDB.get_connect() as db_cursor:
db_cursor.execute(create_sql)
db_cursor.execute(open_sql)
return True
except cx_Oracle.DatabaseError as e:
return e
def CreateUser(self, pdb_name, user_name):
self.PDB_NAME = pdb_name
self.USERNAME = user_name
self.tbs = "tbs_{user}".format(user=self.USERNAME)
self.pwd = "ChangeMe"
switch_sql = "alter session set container={pdb}".format(
pdb=self.PDB_NAME)
create_sql = "create user {username} default tablespace {tablespace} identified by {passwd} PROFILE default".format(
username=self.USERNAME, tablespace=self.tbs, passwd=self.pwd)
grant_sql = "grant dba to {username}".format(username=self.USERNAME)
try:
with ConnectDB.get_connect() as db_cursor:
db_cursor.execute(switch_sql)
db_cursor.execute(settings.enabled_ddl_parallel)
db_cursor.execute(create_sql)
db_cursor.execute(grant_sql)
return True
except cx_Oracle.DatabaseError as e:
return e
def CreateTBS(self, pdb_name, user_name):
self.PDB_NAME = pdb_name
self.USERNAME = user_name
tbs = "tbs_{user}".format(user=self.USERNAME)
switch_sql = "alter session set container={pdb}".format(
pdb=self.PDB_NAME)
create_sql = "create tablespace {tablespace_name} datafile '{datafilepath}/{dbname}/{pdb}/{tablespace_name}.dbf' SIZE 100M AUTOEXTEND ON NEXT 1G MAXSIZE 30G".format(
tablespace_name=tbs,
pdb=self.PDB_NAME,
datafilepath=settings.realfilepath,
dbname=settings.db_name)
try:
with ConnectDB.get_connect() as db_cursor:
db_cursor.execute(switch_sql)
db_cursor.execute(settings.enabled_ddl_parallel)
db_cursor.execute(create_sql)
return tbs
except cx_Oracle.DatabaseError as e:
return e