Follow us: Connect on YouTube Connect on YouTube Connect on YouTube


Tuesday, 11 April 2017

SQL Table Drop

It is used to remove the table permanently from database along with data


Drop table <Table_Name>;


        DROP TABLE Student;
        DROP TABLE dept;
        DROP TABLE emp;
Share this article :


  1. Hi Dayakar,

    A spot on observation on what probably is “the” underlying details of the SQL Table Drop. Too many people don’t even think about wherever there will be actual demand and more importantly what happens if this demand comes later (or maybe a lot later) than they expect

    Number of times processes repeatedly query the dictionary table is called recursive hints. It is due to the data dictionary cache is too small. By increasing the SHARED_POOL_SIZE parameter we can optimize the size of data dictionary cache.

    I need help of forming a SQL query for the below situation.

    There's a table (T_BATCH_RULE) having 2 VARCHAR2(255 char) columns [BATCH_NAME, RULE_NAME]

    Assume the following data

    B1 R1
    B1 R2
    B1 R3
    B2 R3

    Since R3 is associated to both Batch B1 and B2, I want all the rules associated with B1 to be listed as part of B2 as well.

    My output should look something like below

    B1 R1
    B1 R2
    B1 R3
    B2 R1
    B2 R2
    B2 R3

    Please help me with the SQL query (No PL/SQL please).

    Thanks a lot. This was a perfect step-by-step guide. Don’t think it could have been done better.


  2. Halo,

    Fully agree on #topic. We’re seeing a lot of projects tackle big complex problems but few seem to have taken into consideration and in particular reasons to adopt.

    Currently I'm trying to make a test using create job as a way to have multiple process on database starting in a loop.
    Basically I'm getting different behaviors when testing on different database machines. I'll define the machine names are A and B.
    If I started running the anonymous block below in the machine A for a loop to create 3 multiple jobs, is working fine, i.e, the create job using
    enabled parameter, created the job and automatically start:
    L_job_name VARCHAR2(100);
    L_comments VARCHAR2(240);
    L_pls_block VARCHAR2(32000);
    L_thread NUMBER := 1; -- count of jobs
    L_max_threads NUMBER := 3; -- max number of jobs to be started
    L_lot_id NUMBER := 1234; -- add any number just to represent a lot

    while L_thread <= L_max_threads loop
    L_job_name := 'job_' || L_lot_id || L_thread;
    L_comments := 'Job test ' || L_lot_id || ' and thread ' || L_thread;
    L_pls_block := 'BEGIN logger.log(''job running of thread' || L_thread || '''); END;';
    dbms_scheduler.create_job(job_name => L_job_name,
    job_type => 'PLSQL_BLOCK',
    job_action => L_pls_block,
    start_date => SYSTIMESTAMP,
    comments => L_comments,
    enabled => true);


    L_thread := L_thread + 1;
    end loop;

    but if I start the same code in the machine B, nothing is happening and I need to uncomment the line about DBMS_SCHEDULER.RUN_JOB(L_job_name, TRUE);

    I did another test in the same machine B using enabled FALSE when creating the job and during the call from DBMS_SCHEDULER.RUN_JOB changed to false in the user_credentials but only one job runned, i.e, the result was logged from just one job, instead of the 3 jobs that had most be created.

    Do you have an idea about what I need to look? Is there something related about database setup, credentials that I need to take a look?

    It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
    Keep up the good work!