Home » SQL & PL/SQL » SQL & PL/SQL » merged again: 2000+ selects, fill rates
merged again: 2000+ selects, fill rates [message #304008] Mon, 03 March 2008 12:35 Go to next message
brandond
Messages: 20
Registered: March 2008
Junior Member
I have an oracle database with 16 schemas, and I need to know the maximum fill rate on every row in every table, in all schemas. I tried this, but that only gives me the tables available to system, and not all of the schems tables.

spool c:\test.sql
select 'select max(length('||column_name||')) from '||table_name||';' from user_tab_cols;
@c:\test.sql

So I then tried

spool c:\test.sql
select 'select max(length('||column_name||')) from '||table_name||';' from <schema>.user_tab_cols;
@c:\test.sql

but that gives me an error, telling me that, that table does not exist.

Any thoughts on what I could possibly do for this?

Thanks.
Re: fill rates on all fields in all schemas [message #304009 is a reply to message #304008] Mon, 03 March 2008 12:45 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Please read & follow posting guidelines.

>but that gives me an error, telling me that, that table does not exist.

This can occur when you do not have privs to access the table.
Re: fill rates on all fields in all schemas [message #304010 is a reply to message #304008] Mon, 03 March 2008 12:46 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
USER_TAB_COLUMNS cannot be used in that way. You have to use ALL_TAB_COLUMNS where OWNER={something} or better yet, DBA_TAB_COLUMNS.

ALL_TAB_COLUMNS will only give you tables you have privileges on.
2000+ selects, need to format into a readable table [message #304017 is a reply to message #304008] Mon, 03 March 2008 14:13 Go to previous messageGo to next message
brandond
Messages: 20
Registered: March 2008
Junior Member
After running this on a table,


select 'select max(length('||column_name||')) from '||table_name||';' from user_tab_cols;


which generated roughly 2400 select statements. I am needing a way to run this entire .sql file, and have the output formatted so that I can compare it against 15 other schemas. If possible, I would like to format it in such a manner that I could pull the information into an access table so that I can analyze the data.

thanks
Re: 2000+ selects, need to format into a readable table [message #304020 is a reply to message #304017] Mon, 03 March 2008 14:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What format access understand?
CSV?
In this case just use spool.

Regards
Michel
Re: 2000+ selects, need to format into a readable table [message #304023 is a reply to message #304020] Mon, 03 March 2008 14:38 Go to previous messageGo to next message
brandond
Messages: 20
Registered: March 2008
Junior Member
how can i create spool to be comma delimated?

can i do just

spool c:\test.csv?

thanks
Re: 2000+ selects, need to format into a readable table [message #304024 is a reply to message #304023] Mon, 03 March 2008 14:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes and "set colsep ';'" (or any character you want to delimit your fields).
Also "set head off" to avoid headings or "set headsep off" if you just don't want the "-" line.

Regards
Michel
Re: 2000+ selects, need to format into a readable table [message #304028 is a reply to message #304024] Mon, 03 March 2008 15:19 Go to previous messageGo to next message
brandond
Messages: 20
Registered: March 2008
Junior Member
michel

thanks for your help.

i ran this in sql+

set recsepchar ";"
spool c:\test.txt
@c:\test.sql

and the spool file doesn't contain ; seperators for what is being spooled. Am I doing something wrong in setting the recsepchar?

thanks
Re: 2000+ selects, need to format into a readable table [message #304032 is a reply to message #304028] Mon, 03 March 2008 15:42 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is the RECSEPCHAR? Where did you find it in Michel's guidelines?

Unformatted:
SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NY
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
Formatted:
SQL> set heading off
SQL> set headsep off
SQL> set colsep ";"             --> COLSEP, not RECSEPCHAR !!!
SQL> select * from dept;

        10;ACCOUNTING    ;NY
        20;RESEARCH      ;DALLAS
        30;SALES         ;CHICAGO
        40;OPERATIONS    ;BOSTON

SQL>
Re: 2000+ selects, need to format into a readable table [message #304034 is a reply to message #304032] Mon, 03 March 2008 15:52 Go to previous messageGo to next message
brandond
Messages: 20
Registered: March 2008
Junior Member
the reason i tried recsepchar is becuase i am running multiple select statements, not just 1.

Each select statement I am using is only pulling 1 value from the database.

so my output looks like such

MAX(LENGTH (INSP_TYPE)) ----------------------
20


MAX(LENGTH(LAST_MOD_DATE)) --------------------------
9

there is 2500 select statements being runn simultaneously by using @c:\test.sql

do you know if it is possible divide those up by comma delimeted.

thanks
Re: 2000+ selects, need to format into a readable table [message #304058 is a reply to message #304017] Mon, 03 March 2008 22:29 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
To print a line of dashes after each wrapped column value, enter the commands:


SET RECSEP WRAPPED
SET RECSEPCHAR "-"

Finally, enter the following query:


SELECT LAST_NAME, JOB_TITLE, CITY
FROM EMP_DETAILS_VIEW
WHERE SALARY>12000;

Now restrict the width of the column JOB_TITLE and tell SQL*Plus to wrap whole words to additional lines when necessary:


COLUMN JOB_TITLE FORMAT A20 WORD_WRAPPED

Run the query:


/

LAST_NAME                 JOB_TITLE            CITY
------------------------- -------------------- ------------------------------
King                      President            Seattle
Kochhar                   Administration Vice  Seattle
                          President
--------------------------------------------------------------------------------
De Haan                   Administration Vice  Seattle
                          President
--------------------------------------------------------------------------------
Russell                   Sales Manager        Oxford
Partners                  Sales Manager        Oxford
Hartstein                 Marketing Manager    Toronto


6 rows selected.

If you set RECSEP to EACH, SQL*Plus prints a line of characters after every row (after every department, for the above example).

Before continuing, set RECSEP to OFF to suppress the printing of record separators:


SET RECSEP OFF


regards,

[Updated on: Mon, 03 March 2008 22:30]

Report message to a moderator

Re: 2000+ selects, need to format into a readable table [message #304069 is a reply to message #304017] Mon, 03 March 2008 23:29 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
do you know if it is possible divide those up by comma delimeted.

I would add the column alias including commas.

Are you sure, two tables do not have the same column name? In my opinion it would be useful to show the table name too.

Just curious, how are you satisfied with its performance.
Re: 2000+ selects, need to format into a readable table [message #304107 is a reply to message #304017] Tue, 04 March 2008 01:19 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
dear brandond,

may i have your feedback,please?

regards,

[Updated on: Tue, 04 March 2008 01:19]

Report message to a moderator

Re: 2000+selects and fill rates (merged) [message #304252 is a reply to message #304008] Tue, 04 March 2008 12:14 Go to previous messageGo to next message
brandond
Messages: 20
Registered: March 2008
Junior Member
maybe i'm just not explaing this right.

I have a txt file with this in it

select max(length(CAUSE_EST_RECURRENCE)) from CPT_EVENTS;
select max(length(CAUSE_FOLLOWUP)) from CPT_EVENTS;
select max(length(CREATE_WO_IND)) from CPT_EVENTS;
select max(length(CM_REFERENCE)) from CPT_EVENTS;
select max(length(CAUSE_ENTERED_BY)) from CPT_EVENTS;
select max(length(CAUSE_ENTERED_ON)) from CPT_EVENTS;
select max(length(EVA_ID)) from CPT_EVENT_ACTIONS;
select max(length(EVENT_ID)) from CPT_EVENT_ACTIONS;
select max(length(ACTION_ID)) from CPT_EVENT_ACTIONS;
select max(length(ACTION_DATE)) from CPT_EVENT_ACTIONS;
select max(length(DESCRIPTION)) from CPT_EVENT_ACTIONS;
select max(length(RECORD_VERSION)) from CPT_EVENT_ACTIONS;


That is a small portion of what I have, there is another 2400+ of those in the text file. Here is the code I put in to run it.

SQL> spool c:\test.txt
SQL> set colsep ";"
SQL> @c:\table\file.txt

Here is what I get, in no way is it formatted in any way, that is useful to me (the selects above and the below output don't match, just copied portions of my input and spool file). I understand what your doing above with the select * option, but that is not what I am doing.

MAX(LENGTH(INSP_TYPE))                                                          
----------------------                                                          
                    20                                                          


MAX(LENGTH(LAST_MOD_DATE))                                                      
--------------------------                                                      
                         9                                                      


MAX(LENGTH(LAST_MOD_EMP_NO))                                                    
----------------------------                                                    
                           8                                                    


MAX(LENGTH(SEQUENCE_NO))                                                        
------------------------                                                        
                       1                                                        


MAX(LENGTH(INSP_TYPE))                                                          
----------------------                                                          
                    20                                                          

[Updated on: Tue, 04 March 2008 12:15]

Report message to a moderator

Re: 2000+selects and fill rates (merged) [message #304255 is a reply to message #304252] Tue, 04 March 2008 12:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you should say which format you want if you want us to help to get it.

Regards
Michel
Re: 2000+selects and fill rates (merged) [message #304260 is a reply to message #304252] Tue, 04 March 2008 12:29 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
I am needing a way to run this entire .sql file, and have the output formatted so that I can compare it against 15 other schemas. If possible, I would like to format it in such a manner that I could pull the information into an access table so that I can analyze the data.

If I were you the way I will do it is will be something like this
select table_name, max(col1), max(col2), max(col3) ... from <table_name>;
.....
....

Ofcourse you have add the following statements depends on what you want to suppress like heading, echo, feedback. Check in the oracle sql reference manual for further details
 set head off echo off feedback off 

So by executing the above query you can get the values in one comma delimited file (if you have mentioned the colsep to ','). Rerun the same query in different database / schema and compare it.

Quote:
select max(length(CAUSE_EST_RECURRENCE)) from CPT_EVENTS;
select max(length(CAUSE_FOLLOWUP)) from CPT_EVENTS;
select max(length(CREATE_WO_IND)) from CPT_EVENTS;
select max(length(CM_REFERENCE)) from CPT_EVENTS;
select max(length(CAUSE_ENTERED_BY)) from CPT_EVENTS;
select max(length(CAUSE_ENTERED_ON)) from CPT_EVENTS;

Why do you want to scan the same table n number of times. Instead try the above mentioned approach.

Hope that helps

Regards

Raj

[Updated on: Tue, 04 March 2008 12:30]

Report message to a moderator

Re: 2000+selects and fill rates (merged) [message #304264 is a reply to message #304260] Tue, 04 March 2008 12:39 Go to previous messageGo to next message
brandond
Messages: 20
Registered: March 2008
Junior Member
Raj

Thanks for the help. The file I am pulling in from doesn't just contain those lines over and over, it contains pieces from all the other tables. I got my input file by running this

select 'select max(length('||column_name||')) from '||table_name||';' from user_tab_cols;


Each schema has it's own input file, because each schema's tables don't match exactly. I just don't know how to get a decent formatted file with the code I showed you.

The example you gave with doing the select table_name, max(col1), max(col2),

would be harder for me to produce by doing something similiar to the code I showed you above. when i set the pieces that said to off, then I am missing the row headers, which I need for my output.

thanks
Re: 2000+selects and fill rates (merged) [message #304268 is a reply to message #304264] Tue, 04 March 2008 12:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select 'select '''||table_name||''', '''||column_name||
       ''', max(length('||column_name||')) from '||table_name||';' 
from user_tab_cols;

will give at each line: "table_name;column_name;max_length" so it is easy to handle this in what application you want that understands CSV file.

Regards
Michel

[Updated on: Tue, 04 March 2008 12:53]

Report message to a moderator

Re: 2000+selects and fill rates (merged) [message #304278 is a reply to message #304268] Tue, 04 March 2008 13:13 Go to previous messageGo to next message
brandond
Messages: 20
Registered: March 2008
Junior Member
Michel

thanks so much for the help.

I ran it as such

set head off echo off feedback off 
set colsep ";"
@c:\test.sql



and I got this output


EAM_INSPECTION_TYPES            ;INSP_TYPE                                      
                    20           



Is there a way to get read of the eam_insp.. part? and then to get a ; after the insp_type. I am not sure why it isn't already happening with doing what I did.

I really appreciate all the help given, this has been a headache of a request.

Thanks
Re: 2000+selects and fill rates (merged) [message #304280 is a reply to message #304278] Tue, 04 March 2008 13:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Set your linesize to a greater value.
If you don't want the table name, remove it from the query I gave.

Regards
Michel
Re: 2000+selects and fill rates (merged) [message #304291 is a reply to message #304008] Tue, 04 March 2008 14:34 Go to previous messageGo to next message
brandond
Messages: 20
Registered: March 2008
Junior Member
michel and others

thanks much for all your help. i finally got the query working the way i needed it to.

sql wildcard help [message #304537 is a reply to message #304008] Wed, 05 March 2008 10:11 Go to previous messageGo to next message
brandond
Messages: 20
Registered: March 2008
Junior Member
I want to use a wildcard in sql to help limit the table names I pull from the user_tab_cols table. Here is my query,

select 'select '''||column_name||
       ''', max(length('||column_name||')) from '||table_name||';' 
from user_tab_cols;


and most of the tables it pulls that are views are named something like

aaa_vw_aaaa
or aaa_vwe_aaa

So if possible I would like to limit out the ones that have the vw or vwe in it.

I am unsure how to format my wildcard, as if I put in ***_vw_*
it isn't working how I need it.

Thanks for the help
Re: sql wildcard help [message #304544 is a reply to message #304537] Wed, 05 March 2008 10:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
LIKE Condition

You should really read SQL Reference and not try to guess the syntax.

Regards
Michel
Re: sql wildcard help [message #304551 is a reply to message #304537] Wed, 05 March 2008 10:56 Go to previous messageGo to next message
brandond
Messages: 20
Registered: March 2008
Junior Member
michel

thanks for the help again, i read over the sql link you sent me, but am still unsure how to format it so that i can have any character in the first part of my string, and then the vw or vwe and then anyting after that.

Do I need to put my wildcards into a ( ) or ?

Thanks
Re: sql wildcard help [message #304552 is a reply to message #304537] Wed, 05 March 2008 11:07 Go to previous message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Do I need to put my wildcards into a ( ) or ?
Just do like the examples shown in the URL Michel provided.
Previous Topic: ALTER USER with COMPLEX PASSWORD
Next Topic: ORA-01848 error on view query
Goto Forum:
  


Current Time: Sun Dec 04 00:44:13 CST 2016

Total time taken to generate the page: 0.10435 seconds