Hello Gurus,
I am using TOAD in my company to browse different schemas. I am just wondering is there a way to parse different schemas and see the data in those schemas.
Ex: SCHEMA A has 10 tables
Schema B has 10 tables, …SCHEMA N has 10 tables
I would like to see the data of 1 common table (eg. Table X is present in all schemas) in all these schemas. Will that be possible in TOAD? Is there a SQL to write or a PL/SQL ?
Thanks for your help.
Newbie in TOAD
Well one way to view all the tables is using the Tab view in the schema browser
(not Tree view). When you’re on the tables tab there is a funnel icon. Clicking
that you can you can change the Schema Name to something like “IN” and list the
schemas you want to see ‘schema1’,‘schema2’,…
Then right click on the column header (below the filter icon) and select Schema.
This will add a schema column next to the table name.
Then you can sort the tables by clicking the table column to group the same ones
together or above the tabs type in the table name into the filter.
If you want to see data from all tables together there is always SQL to do that
select ‘schema1’ schema_name, col1, col2, col3, col4
from schema1.some_table
union all
select ‘schema2’ schema_name, col1, col2, col3, col4
from schema2.some_table
union all
select ‘schema3’ schema_name, col1, col2, col3, col4
from schema3.some_table
union all
select ‘schema4’ schema_name, col1, col2, col3, col4
from schema4.some_table
You can also create some views if you want too. Then under your Views tab you
would have a view for each group of tables showing all the data.
create or replace view some_table_v (schame_name, col1, col2, col3, col4)
as (
select ‘schema1’ schema_name, col1, col2, col3, col4
from schema1.some_table
union all
select ‘schema2’ schema_name, col1, col2, col3, col4
from schema2.some_table
union all
select ‘schema3’ schema_name, col1, col2, col3, col4
from schema3.some_table
union all
select ‘schema4’ schema_name, col1, col2, col3, col4
from schema4.some_table
)
Ed
[TeamT]
Do you mean you want to browse the data for a table with the same name in all 3
schemas? If so the following SELECT might do it as long as the table structure
is exactly the same in all three tables.
Select * from
( select ‘A’ schemaname, a.* from a.tabx
Union all
Select ‘B’, b.* from b.tabx
Union all
Select ‘C’, c.* from c.tabx)
Order by schemaname
;
Where A, B, C are the schema names an TABX is the table name
Thank you very much for the clear explanation.
The solution that you provided below is helpful if I know the schemas that I want to select before hand. But I would like to see the schemas dynamically which satisfy the criteria in the table or view that you have shown below.
There are some 500 schemas altogether in the database and of these schemas there are 400 schemas in which there is a table x with some 15 columns in all these 400 schemas So how can I dynamically select those schemas which satisfy the condition like salary(Sal > 10000) in accounts table present in some 400 schemas of all together 500 schemas.
I am looking for o/p something like
Schema-Name, Table-Name, department, salary, …
Thanks in advance for you help.
But I would like to see the schemas dynamically which satisfy
the criteria in the table or view that you have shown below.
If I was tasked with that, I’d probably build some code to dynamically
create the query I want. After all… writing up your query for one table
would be pretty easy, coding that into a procedure to build that statement
dynamically for the full 400 estimated schemas would initially be a bit of work,
but nowhere near the amount of work to manually update your code an additional
399 times.
As this was a question about whether Toad could that… I didn’t
mention anything before. However, since everyone seems to be responding with sql
suggestions, this would be mine:
Build a procedure that will accept as inputs: a target table, a comma delimited
list of columns and a where clause. It will then gather a list of all available
schemas that exact table name exists in, then produce a select statement
gathering the information specified.
One caveat: I’ve never built a select statement with 399 unions before.
There may be a limit as to just how large your query may be and I don’t
know if you’ll hit that. As a result, if you do end up hitting some kind
of limit, you may very well have to break your query down to logical sections
that do not go over that limit. That could be done in your procedure once you
know what that limit is (if it exists).
Roger S.
–as an example here is a listing of table_names which belong to owner = ‘SYS’
SQL> select OWNER,TABLE_NAME from ALL_TAB_COLUMNS where COLUMN_NAME=‘TABLE_NAME’
AND OWNER=‘SYS’;
SYS ALL_ALL_TABLES
SYS ALL_CAPTURE_PREPARED_TABLES
SYS ALL_CATALOG
SYS ALL_COL_COMMENTS
SYS ALL_COL_PRIVS
SYS ALL_COL_PRIVS_MADE
SYS ALL_COL_PRIVS_RECD
SYS ALL_CONSTRAINTS
SYS ALL_CONS_COLUMNS
SYS ALL_CONS_OBJ_COLUMNS
SYS ALL_ENCRYPTED_COLUMNS
SYS ALL_EVALUATION_CONTEXT_TABLES
SYS ALL_EXTERNAL_LOCATIONS
SYS ALL_EXTERNAL_TABLES
SYS ALL_FILE_GROUP_TABLES
SYS ALL_INDEXES
SYS ALL_IND_COLUMNS
SYS ALL_IND_EXPRESSIONS
SYS ALL_IND_STATISTICS
SYS ALL_INTERNAL_TRIGGERS
SYS ALL_LOBS
SYS ALL_LOB_PARTITIONS
SYS ALL_LOB_SUBPARTITIONS
SYS ALL_LOB_TEMPLATES
SYS ALL_LOG_GROUPS
SYS ALL_LOG_GROUP_COLUMNS
SYS ALL_NESTED_TABLES
SYS ALL_NESTED_TABLE_COLS
SYS ALL_OBJECT_TABLES
SYS ALL_OBJ_COLATTRS
SYS ALL_PARTIAL_DROP_TABS
SYS ALL_PART_COL_STATISTICS
SYS ALL_PART_HISTOGRAMS
SYS ALL_PART_INDEXES
SYS ALL_PART_LOBS
SYS ALL_PART_TABLES
SYS ALL_PENDING_CONV_TABLES
SYS ALL_REFRESH_DEPENDENCIES
SYS ALL_REFS
SYS ALL_SNAPSHOTS
SYS ALL_STREAMS_NEWLY_SUPPORTED
SYS ALL_STREAMS_TABLE_RULES
SYS ALL_STREAMS_UNSUPPORTED
SYS ALL_SUBPARTITION_TEMPLATES
SYS ALL_SUBPART_COL_STATISTICS
SYS ALL_SUBPART_HISTOGRAMS
SYS ALL_SYNONYMS
SYS ALL_TABLES
SYS ALL_TAB_COLS
SYS ALL_TAB_COLUMNS
SYS ALL_TAB_COL_STATISTICS
SYS ALL_TAB_COMMENTS
SYS ALL_TAB_HISTOGRAMS
SYS ALL_TAB_MODIFICATIONS
SYS ALL_TAB_PARTITIONS
SYS ALL_TAB_PRIVS
SYS ALL_TAB_PRIVS_MADE
SYS ALL_TAB_PRIVS_RECD
SYS ALL_TAB_STATISTICS
SYS ALL_TAB_STATS_HISTORY
SYS ALL_TAB_SUBPARTITIONS
SYS ALL_TRIGGERS
SYS ALL_TRIGGER_COLS
SYS ALL_UNUSED_COL_TABS
SYS ALL_UPDATABLE_COLUMNS
SYS ALL_XML_INDEXES
SYS ALL_XML_TABLES
SYS ALL_XML_TAB_COLS
SYS COLUMN_PRIVILEGES
SYS DBA_ADVISOR_SQLW_COLVOL
SYS DBA_ADVISOR_SQLW_TABLES
SYS DBA_ADVISOR_SQLW_TABVOL
SYS DBA_ALL_TABLES
SYS DBA_CACHEABLE_TABLES
SYS DBA_CACHEABLE_TABLES_BASE
SYS DBA_CAPTURE_PREPARED_TABLES
SYS DBA_CATALOG
SYS DBA_CHANGE_NOTIFICATION_REGS
SYS DBA_CLU_COLUMNS
SYS DBA_COL_COMMENTS
SYS DBA_COL_PRIVS
SYS DBA_CONSTRAINTS
SYS DBA_CONS_COLUMNS
SYS DBA_CONS_OBJ_COLUMNS
SYS DBA_ENCRYPTED_COLUMNS
SYS DBA_EVALUATION_CONTEXT_TABLES
SYS DBA_EXTERNAL_LOCATIONS
SYS DBA_EXTERNAL_TABLES
SYS DBA_FILE_GROUP_TABLES
SYS DBA_HIST_SNAP_ERROR
SYS DBA_INDEXES
SYS DBA_IND_COLUMNS
SYS DBA_IND_EXPRESSIONS
SYS DBA_IND_STATISTICS
SYS DBA_INTERNAL_TRIGGERS
SYS DBA_LOBS
SYS DBA_LOB_PARTITIONS
SYS DBA_LOB_SUBPARTITIONS
SYS DBA_LOB_TEMPLATES
SYS DBA_LOGSTDBY_NOT_UNIQUE
SYS DBA_LOGSTDBY_UNSUPPORTED
SYS DBA_LOG_GROUPS
SYS DBA_LOG_GROUP_COLUMNS
SYS DBA_NESTED_TABLES
SYS DBA_NESTED_TABLE_COLS
SYS DBA_OBJECT_TABLES
SYS DBA_OBJ_COLATTRS
SYS DBA_PARTIAL_DROP_TABS
SYS DBA_PART_COL_STATISTICS
SYS DBA_PART_HISTOGRAMS
SYS DBA_PART_INDEXES
SYS DBA_PART_LOBS
SYS DBA_PART_TABLES
SYS DBA_PENDING_CONV_TABLES
SYS DBA_REFS
SYS DBA_SNAPSHOTS
SYS DBA_STREAMS_ADD_COLUMN
SYS DBA_STREAMS_DELETE_COLUMN
SYS DBA_STREAMS_NEWLY_SUPPORTED
SYS DBA_STREAMS_RENAME_COLUMN
SYS DBA_STREAMS_TABLE_RULES
SYS DBA_STREAMS_TRANSFORMATIONS
SYS DBA_STREAMS_UNSUPPORTED
SYS DBA_SUBPARTITION_TEMPLATES
SYS DBA_SUBPART_COL_STATISTICS
SYS DBA_SUBPART_HISTOGRAMS
SYS DBA_SYNONYMS
SYS DBA_TABLES
SYS DBA_TAB_COLS
SYS DBA_TAB_COLUMNS
SYS DBA_TAB_COL_STATISTICS
SYS DBA_TAB_COMMENTS
SYS DBA_TAB_HISTOGRAMS
SYS DBA_TAB_MODIFICATIONS
SYS DBA_TAB_PARTITIONS
SYS DBA_TAB_PRIVS
SYS DBA_TAB_STATISTICS
SYS DBA_TAB_STATS_HISTORY
SYS DBA_TAB_SUBPARTITIONS
SYS DBA_TRIGGERS
SYS DBA_TRIGGER_COLS
SYS DBA_UNUSED_COL_TABS
SYS DBA_UPDATABLE_COLUMNS
SYS DBA_XML_INDEXES
SYS DBA_XML_TABLES
SYS DBA_XML_TAB_COLS
SYS DICTIONARY
SYS DICT_COLUMNS
SYS EXU10SNAP
SYS EXU10SNAPI
SYS EXU10SNAPU
SYS EXU81SNAP
SYS EXU81SNAPC
SYS EXU81SNAPI
SYS EXU81SNAPU
SYS EXU8SNAP
SYS EXU8SNAPC
SYS EXU8SNAPI
SYS EXU8SNAPU
SYS EXU9SNAP
SYS EXU9SNAPC
SYS EXU9SNAPI
SYS EXU9SNAPU
SYS FGR$TABLE_INFO
SYS FLASHBACK_TRANSACTION_QUERY
SYS GV$INDEXED_FIXED_COLUMN
SYS GV_$LOGMNR_CONTENTS
SYS IMP_LOB_INFO
SYS KU$_FIND_HIDDEN_CONS_VIEW
SYS KU$FIND_SGC_VIEW
SYS LOADER_COL_FLAGS
SYS LOADER_COL_TYPE
SYS LOADER_CONSTRAINT_INFO
SYS LOADER_FULL_ATTR_NAME
SYS LOADER_INTCOL_INFO
SYS LOADER_LOB_FLAGS
SYS LOADER_NESTED_VARRAYS
SYS LOADER_OID_INFO
SYS LOADER_REF_INFO
SYS LOADER_TRIGGER_INFO
SYS LOGMNRG_SEED$
SYS LOGMNRT_SEED$
SYS LOGSTDBY_UNSUPPORTED_TABLES
SYS ROLE_TAB_PRIVS
SYS SM$INTEGRITY_CONS
SYS STREAMS$INTERNAL_TRANSFORM
SYS TABLE_PRIVILEGES
SYS USER_ADVISOR_SQLW_COLVOL
SYS USER_ADVISOR_SQLW_TABLES
SYS USER_ADVISOR_SQLW_TABVOL
SYS USER_ALL_TABLES
SYS USER_CATALOG
SYS USER_CHANGE_NOTIFICATION_REGS
SYS USER_CLU_COLUMNS
SYS USER_COL_COMMENTS
SYS USER_COL_PRIVS
SYS USER_COL_PRIVS_MADE
SYS USER_COL_PRIVS_RECD
SYS USER_CONSTRAINTS
SYS USER_CONS_COLUMNS
SYS USER_CONS_OBJ_COLUMNS
SYS USER_ENCRYPTED_COLUMNS
SYS USER_EVALUATION_CONTEXT_TABLES
SYS USER_EXTERNAL_LOCATIONS
SYS USER_EXTERNAL_TABLES
SYS USER_FILE_GROUP_TABLES
SYS USER_INDEXES
SYS USER_IND_COLUMNS
SYS USER_IND_EXPRESSIONS
SYS USER_IND_STATISTICS
SYS USER_INTERNAL_TRIGGERS
SYS USER_LOBS
SYS USER_LOB_PARTITIONS
SYS USER_LOB_SUBPARTITIONS
SYS USER_LOB_TEMPLATES
SYS USER_LOG_GROUPS
SYS USER_LOG_GROUP_COLUMNS
SYS USER_NESTED_TABLES
SYS USER_NESTED_TABLE_COLS
SYS USER_OBJECT_TABLES
SYS USER_OBJ_COLATTRS
SYS USER_PARTIAL_DROP_TABS
SYS USER_PART_COL_STATISTICS
SYS USER_PART_HISTOGRAMS
SYS USER_PART_INDEXES
SYS USER_PART_LOBS
SYS USER_PART_TABLES
SYS USER_PENDING_CONV_TABLES
SYS USER_REFS
SYS USER_SNAPSHOTS
SYS USER_SUBPARTITION_TEMPLATES
SYS USER_SUBPART_COL_STATISTICS
SYS USER_SUBPART_HISTOGRAMS
SYS USER_SYNONYMS
SYS USER_TABLES
SYS USER_TAB_COLS
SYS USER_TAB_COLUMNS
SYS USER_TAB_COL_STATISTICS
SYS USER_TAB_COMMENTS
SYS USER_TAB_HISTOGRAMS
SYS USER_TAB_MODIFICATIONS
SYS USER_TAB_PARTITIONS
SYS USER_TAB_PRIVS
SYS USER_TAB_PRIVS_MADE
SYS USER_TAB_PRIVS_RECD
SYS USER_TAB_STATISTICS
SYS USER_TAB_STATS_HISTORY
SYS USER_TAB_SUBPARTITIONS
SYS USER_TRIGGERS
SYS USER_TRIGGER_COLS
SYS USER_UNUSED_COL_TABS
SYS USER_UPDATABLE_COLUMNS
SYS USER_XML_COLUMN_NAMES
SYS USER_XML_INDEXES
SYS USER_XML_TABLES
SYS USER_XML_TAB_COLS
SYS V$OBJECT_USAGE
SYS V$INDEXED_FIXED_COLUMN
SYS V$LOGMNR_CONTENTS
SYS WM_COMPRESS_BATCH_SIZES
SYS WRI$_ADV_SQLW_TABLES
SYS WRI$_ADV_SQLW_TABVOL
SYS WRM$_SNAP_ERROR
SYS _ALL_FILE_GROUP_TABLES
SYS _ALL_REPL_NESTED_TABLE_NAMES
SYS _DBA_REPL_NESTED_TABLE_NAMES
SYS _DBA_STREAMS_NEWLY_SUPTED_10_1
SYS _DBA_STREAMS_NEWLY_SUPTED_10_2
SYS _DBA_STREAMS_TRANSFORMATIONS
SYS _DBA_STREAMS_UNSUPPORTED_10_1
SYS _DBA_STREAMS_UNSUPPORTED_10_2
SYS _DBA_STREAMS_UNSUPPORTED_9_2
SYS _USER_REPL_NESTED_TABLE_NAMES
273 rows selected.
@C:\Oracle\AICTRAIN\rdbms\admin\utlfile.sql
–use the oracle directory to create a directory Create directory. 9i and higher
CREATE OR REPLACE DIRECTORY DATAHOME_MYDATA AS ‘/Oracle/’;
GRANT WRITE ON DIRECTORY DATAHOME_MYDATA TO user WITH GRANT OPTION;
–IN A PROCEDURE
CREATE OR REPLACE PROCEDURE GET_TABLES_FOR_SCHEMANAME(SCHEMANAME_PARAM IN
VARCHAR2)
IS
OutFile utl_file.file_type;
ex BOOLEAN := TRUE;
flen NUMBER := 0;
bsize BINARY_INTEGER := 0;
CURSOR cur_schemaname IS
select OWNER,TABLE_NAME from ALL_TAB_COLUMNS where COLUMN_NAME=‘TABLE_NAME’ AND
OWNER=SCHEMANAME_PARAM
BEGIN
–i have used out.sql as the filename but you may want to consider
parameterising this as an input variable to your procedure…
utl_file.fgetattr(‘DATAHOME_MYDATA’, ‘out.sql’, ex, flen, bsize);
IF ex THEN
dbms_output.put_line(‘File Exists’);
ELSE
dbms_output.put_line(‘File Does Not Exist’);
END IF;
– open a file to write in the DATAHOME directory which Oracle has write access
to
OutFile := utl_file.fopen(‘DATAHOME_MYDATA’, ‘out.sql’, ‘w’);
utl_file.put_line(OutFile, "SELECT FU FROM BAR WHERE TABLE_NAME=’); --tweak this
utl_file.fflush(OutFile);
–spin thru all of the table_name entries and tack onto the where clause
FOR rec_schemaname IN cur_schemaname LOOP
DBMS_OUTPUT.PUT_LINE(‘OWNER =’||rec_schemaname.OWNER);
DBMS_OUTPUT.PUT_LINE(‘TABLE_NAME=’||rec_schemaname.TABLE_NAME);
utl_file.put_line(OutFile, TABLE_NAME);
utl_file.fflush(Outfile);
if(cur_schemaname%NOTFOUND) break; --short circuit
utl_file.put_line(OutFile,’’, OR TABLE_NAME=’’);
utl_file.fflush(OutFile);
END LOOP;
utl_file.put_line(OutFile,’’)’); --thats enclosing tick another tick for
delimiting string data followed by right-paren followed by enclosing tickmark
utl_file.fflush(OutFile);
utl_file.fclose(OutFile);
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - ‘||SQLCODE||’ -ERROR-
'||SQLERRM);
END;
–as roger mentioned each join tacked onto the where can place a burdensome load
on the CPU so you may want using an alternate strategy than appending on every
tablename
(HTH)
Martin–