SQL query to find top 50 tables by size

select top50.owner, top50.table_name, meg, a.num_rows
from dba_tables a,
   (Select * from (
SELECT
   owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg
FROM
(SELECT segment_name table_name, owner, bytes
 FROM dba_segments
 WHERE segment_type like 'TABLE%'
 UNION ALL
 SELECT i.table_name, i.owner, s.bytes
 FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND   s.owner = i.owner
 AND   s.segment_type like 'INDEX%'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBSEGMENT'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.index_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBINDEX')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10  /* Ignore small tables */
ORDER BY SUM(bytes) desc
) where rownum < 51) top50
where top50.owner =a.owner
and top50.table_name = a.table_name
order by meg desc, num_rows desc;


Note: Change 50 to any other number of tables you want to find

Loading xml file data to oracle table using python

Sample xml file (test.xml):

<?xml version="1.0"?>

<data>

    <customer name="Ravi" >

        <email>lravikumarvsp@gmail.com</email>

        <phone>9999999999</phone>

    </customer>

    <customer name="kumar" >

        <email>kumar@gmail.com</email>

    </customer>

    <customer name="lanke" >

        <email>lanke@gmail.com</email>

        <phone>8888888888</phone>

    </customer>

    <customer name="ravikumar" >

        <phone>7777777777</phone>

        <address>

            <street>kukatpally</street>

        </address>

    </customer>

</data>

Python script for parsing xml file data to data frame:

import pandas as pd

import xml.etree.cElementTree as et

#Reading Data from the xml file

parsedXML = et.parse( "test.xml" )                                                                         

dfcols = ['name','email','phone','street']                                                                                

df = pd.DataFrame(columns=dfcols)

def getvalueofnode( node ):

                return node.text if node is not None else None

for node in parsedXML.getroot():                                                                              

                name = node.attrib.get('name')

                email = node.find('email')

                phone = node.find('phone')

                street = node.find('address/street')

                df = df.append( pd.Series([name, getvalueofnode(email), getvalueofnode(phone), getvalueofnode(street)], index=dfcols) ,ignore_index=True)

print (df)                                                                                                                                  

Run the program as below:

Now create a table with column names in the data frame.

CREATE TABLE TEST 

   (                          "NAME" VARCHAR2(200 BYTE),

                               "EMAIL" VARCHAR2(200 BYTE),

                               "PHONE" VARCHAR2(200 BYTE),

                               "STREET" VARCHAR2(200 BYTE)

   )

Python script for loading xml file to oracle table.

import pandas as pd

import cx_Oracle

import xml.etree.cElementTree as et

#Reading Data from the xml file

parsedXML = et.parse( "test.xml" )                                                                         

dfcols = ['name','email','phone','street']                                

df = pd.DataFrame(columns=dfcols)

def getvalueofnode( node ):

                               return node.text if node is not None else None

for node in parsedXML.getroot():                                                                               

                               name = node.attrib.get('name')

                               email = node.find('email')

                               phone = node.find('phone')

                               street = node.find('address/street')

                               df = df.append( pd.Series([name, getvalueofnode(email), getvalueofnode(phone), getvalueofnode(street)], index=dfcols) ,ignore_index=True)

print (df)                                                                                                                             

#Loading Data into the Database Table

host1 = '172.16.11.32'

port1 = '1521'

sid1 = 'srcdb'

schema1 = 'system'

pwd1 = 'Admin123'

sdsn = cx_Oracle.makedsn(host1, port1, service_name=sid1)    

connect=cx_Oracle.connect(schema1, pwd1, sdsn)                                                        

cursor=connect.cursor()

dataframe_list=df.values.tolist()                                                                                                              # convert the dataframe to list

sql='INSERT INTO TEST values (:1,:2,:3,:4)'

for index,elem in enumerate(dataframe_list):                                                   

                               cursor.execute(sql,dataframe_list[index]) 

                               connect.commit()                                                             

Run the script to load the data to oracle table now

Check the table for data

Purge Oracle Analytics Cloud cache using curl command

Clearing cache for all databases:

Syntax:

curl -i -X DELETE -u joe@example.com:mypassword1 -H "X-ID-TENANT-NAME:myidentitydomain" https://mybicloudservice-myidentitydomain.analytics.us1.oraclecloud.com/bimodeler/api/v1/dbcache

Status : Failure -Test failed: ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

Status: Failure -Test failed: ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

Check the LOGINS status with the below SQL command.

Connect to the database as an SYS user through command prompt and execute the below commands

Grant restricted session privilege to the required user as below.