Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97

    Question Unanswered: SELECT across multiple schemas

    Hi everyone,

    I'm pretty new to the world of PL/SQL, but I have been charged with a task and I was hoping to get some guidance as to the best way to go about it.

    The skinny is we have like 100 identical schemas, one for each of our customers, and I need to select VALUE from TABLE in all of them, and add it up.

    Is there an easy, painless way to do that, or is this going to hurt?

    Thank you for the help


    Atsuko
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >is this going to hurt?
    It is going to hurt.
    The FROM clause must contain every OWNER.TABLE to be included in the SUM()
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97
    That does sound painful!! @_@
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  4. #4
    Join Date
    Sep 2012
    Posts
    18

    once do this

    assalam u alaikum !

    grant all tables rights to public

    make a query joing all tables and make it as a script

    it will be painfull to you just one time..then it will be a piece of cake...

    conn schema/pass
    >> grant select on table to public;
    or
    con sys as sysdba

    select "grant select on " name " to public " from user_tables where username = '[USERNAME]';
    (this query will grant all tables to public in ur difiened username)
    example :
    select column 1, column2 from schema.table,schema2.table,schema3.table ;

    best of luck

    MUHAMMAD ARSALAN

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'm not sure I'd want to GRANT SELECT on all of my tables to PUBLIC.

  6. #6
    Join Date
    Sep 2012
    Posts
    18
    assalam u alaikum


    well sir !
    you can also grant select on the table to any specific or new dummy created schema to perform your operation



    i am still not sure what actually is your goal

    i just told you to avoid the same task to do multiple times thats it


    MUHAMMAD ARSALAN

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I know. But the fact that you CAN do something doesn't mean that you SHOULD do it. Just like you provided one option, I felt free to object to it; I don't see anything wrong with either opinion, we are just discussing the issue.

    After all, nobody is forcing Atsuko to do anything people suggested here, I suppose.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I would not do this in PL/SQL, myself. I would write a script or program to cycle through each schema and accumulate the sum in a variable to be output at the end. This would save you a lot of trouble when you get new customers, lose old customers, or get different requirements.

    Apart from that, you have learned a valuable lesson from this experience. Saving individual customers in different schemas may sound like a good idea, but it really is not.

  9. #9
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Here is some sample code for doing this in PL/SQL, in this example I have 2 schemas with the same table TESTVALUES. I read the ALL_TABLES view and loop through these one at a time:

    Code:
    SET SERVEROUTPUT ON
    
    DECLARE 
    CURSOR c_tables IS SELECT owner, table_name FROM all_tables WHERE table_name = 'TESTVALUES';
    l_rec c_tables%ROWTYPE;
    l_total INTEGER := 0;
    l_count INTEGER;
    BEGIN
    	OPEN c_tables;
    	FETCH c_tables INTO l_rec;
    	WHILE c_tables%FOUND LOOP
    		EXECUTE IMMEDIATE 'SELECT count(1) FROM '||l_rec.owner||'.'||l_rec.table_name INTO l_count;
    		l_total := l_total + l_count;
    		DBMS_OUTPUT.PUT_LINE('Schema: ' || l_rec.owner || ' - ' || l_count);
    		FETCH c_tables INTO l_rec;
    	END LOOP;
    	DBMS_OUTPUT.PUT_LINE('Grand total: ' || l_total);
    END;
    /
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Why COUNT(1) and not COUNT(*), if I may ask?

  11. #11
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Yes, this is something historical. In Oracle 7.3 it used to expand this out using up more memory. It parsed the statement a little slower. This has now been changed and it is as fast using COUNT(1) as COUNT(*).
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  12. #12
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Oracle 7.3 was released in 1996 so ... there aren't many people who still use it. Therefore, wouldn't it be better to use COUNT(*) all the time, nowadays?

  13. #13
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Yes both are equally as good!!
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •