Script for describing users database (no request, this is it)
Date: Fri, 24 Sep 1993 09:59:26 GMT
Message-ID: <1993Sep24.095926.1287_at_news.cs.tut.fi>
Hello!
This is a small utility script, which describes the database of one user. It's made for Oracle V6 Data Dictionary.
I have found this script useful when I have different version of particular database or application installed. Using this script and diff I can see what has to be converted to get the database up to date. It can also be used to examine objects of one user.
As usually, this script is delivered AS IS with no quarantees. Also, there are no limitations using this script. You can even kill it ;-) The script is poorly commented, but who needs comments because code is obvious (actually I did not want to write comments again in English and the most of you would have done nothing with Finnish comments).
Hope somebody finds this useful...
Jukka
- cut here --- cut here --- cut here --- cut here --- cut here --- rem ================================================================= rem Describing the database of one user. Objects of logging user rem and Public Synonyms pointing to users objects are listed. rem rem jht_at_cs.tut.fi 23.9.1993 rem =================================================================
DEFINE f_spool='descdb.lst';
SET PAGESIZE 0;
SET HEADING OFF;
SET VERIFY OFF;
SET TERMOUT OFF;
SET FEEDBACK OFF;
rem =================================================================rem Temporary table for building the report.
rem =================================================================
COLUMN table_name NOPRINT NEW_VALUE table_name;
SELECT 'TMP_' || USERENV('SESSIONID') "table_name" FROM SYSTEM.DUAL;
DROP TABLE &table_name;
CREATE TABLE &table_name (
row_no NUMBER,
text CHAR(132) );
rem =================================================================rem Building the report with PL/SQL
rem =================================================================
DECLARE
prev_object_type CHAR(13); s_object_type CHAR(13); s_object_name CHAR(30); s_column_name CHAR(30); s_data_type CHAR(9); s_data_length NUMBER; s_data_precision NUMBER; s_data_scale NUMBER; s_nullable CHAR(8); s_table_owner CHAR(30); s_unique CHAR(30); s_table_name CHAR(30); s_db_link CHAR(30); s_min_value NUMBER; s_max_value NUMBER; s_increment_by NUMBER; s_cycle CHAR(3); s_order CHAR(3);
s_synonym_name CHAR(30);
s_row_no NUMBER := 1; s_text CHAR(132); n_column NUMBER; n_row NUMBER; CURSOR fetch_object IS SELECT object_type, object_name
FROM user_objects
WHERE object_name != '&table_name'
ORDER BY object_type, object_name;
CURSOR fetch_public_synonym IS
SELECT synonym_name, table_owner, table_name, db_link
FROM all_synonyms
WHERE owner = 'PUBLIC'
AND table_owner = USER
ORDER BY synonym_name;
CURSOR fetch_table_col IS
SELECT column_name, data_type, data_length, data_precision, data_scale, RPAD(DECODE(nullable,'N','Not NULL','Y',' ',nullable),8)FROM user_tab_columns
WHERE table_name = s_object_name
ORDER BY column_id;
CURSOR fetch_index IS
SELECT table_name, DECODE(uniqueness,'NONUNIQUE',NULL,uniqueness || ' ')FROM user_indexes
WHERE index_name = s_object_name;
CURSOR fetch_index_col IS
SELECT column_name
FROM user_ind_columns
WHERE index_name = s_object_name
AND table_name = s_table_name
ORDER BY column_position;
CURSOR fetch_sequence IS
SELECT min_value, max_value, increment_by, DECODE(cycle_flag,'N','No','Y','Yes',cycle_flag), DECODE(order_flag,'N','No','Y','Yes',order_flag)FROM user_sequences
WHERE sequence_name = s_object_name;
CURSOR fetch_synonym IS
SELECT table_owner, table_name, db_link
FROM user_synonyms
WHERE synonym_name = s_object_name;
BEGIN
OPEN fetch_object;
LOOP
FETCH fetch_object INTO s_object_type, s_object_name; EXIT WHEN fetch_object%NOTFOUND; IF s_row_no > 1 THEN IF s_object_type IN ('TABLE','VIEW') OR prev_object_type != s_object_type THEN INSERT INTO &table_name VALUES (s_row_no, NULL); s_row_no := s_row_no + 1; END IF; END IF; IF s_object_type IN ('TABLE','VIEW') THEN INSERT INTO &table_name VALUES ( s_row_no, INITCAP(s_object_type) || ' ' || LOWER(s_object_name)); s_row_no := s_row_no + 1; OPEN fetch_table_col; LOOP FETCH fetch_table_col INTO s_column_name, s_data_type, s_data_length, s_data_precision, s_data_scale, s_nullable; EXIT WHEN fetch_table_col%NOTFOUND; s_text := ' ' || RPAD(LOWER(s_column_name),30); s_text := s_text || ' ' || s_nullable; s_text := s_text || ' ' || INITCAP(s_data_type); IF s_data_type = 'CHAR' THEN s_text := s_text || '(' || TO_CHAR(s_data_length) || ')'; ELSIF s_data_type = 'NUMBER' THEN IF s_data_precision > 0 THEN s_text := s_text || '(' || TO_CHAR(s_data_precision); IF s_data_scale > 0 THEN s_text := s_text || ',' || TO_CHAR(s_data_scale); END IF; s_text := s_text || ')'; END IF; END IF; INSERT INTO &table_name VALUES (s_row_no, s_text); s_row_no := s_row_no + 1; END LOOP; CLOSE fetch_table_col; ELSIF s_object_type = 'INDEX' THEN s_text := 'Index ' || LOWER(s_object_name); OPEN fetch_index; FETCH fetch_index INTO s_table_name, s_unique; IF fetch_index%FOUND THEN s_text := s_text || ': ' || INITCAP(s_unique) || 'on ' || LOWER(s_table_name); n_column := 0; OPEN fetch_index_col; LOOP FETCH fetch_index_col INTO s_column_name; EXIT WHEN fetch_index_col%NOTFOUND; n_column := n_column + 1; IF n_column = 1 THEN s_text := s_text || ' (' || LOWER(s_column_name); ELSE s_text := s_text || ', ' || LOWER(s_column_name); END IF; END LOOP; CLOSE fetch_index_col; IF n_column > 0 THEN s_text := s_text || ')'; END IF; END IF; CLOSE fetch_index; INSERT INTO &table_name VALUES (s_row_no, s_text); s_row_no := s_row_no + 1; ELSIF s_object_type = 'SEQUENCE' THEN s_text := 'Sequence ' || LOWER(s_object_name); OPEN fetch_sequence; FETCH fetch_sequence INTO s_min_value, s_max_value, s_increment_by, s_cycle, s_order; IF fetch_sequence%FOUND THEN s_text := s_text || ': ' || TO_CHAR(s_min_value) || '-' || TO_CHAR(s_max_value) || '++' || TO_CHAR(s_increment_by) || ' Cycle: ' || s_cycle || ' Order: ' || s_order; END IF; CLOSE fetch_sequence; INSERT INTO &table_name VALUES (s_row_no, s_text); s_row_no := s_row_no + 1; ELSIF s_object_type = 'SYNONYM' THEN s_text := 'Synonym ' || LOWER(s_object_name); OPEN fetch_synonym; FETCH fetch_synonym INTO s_table_owner, s_table_name, s_db_link; IF fetch_synonym%FOUND THEN s_text := s_text || ' for ' || LOWER(s_table_owner) || '.' || LOWER(s_table_name); IF s_db_link IS NOT NULL THEN s_text := s_text || '_at_' || LOWER(s_db_link); END IF; END IF; CLOSE fetch_synonym; INSERT INTO &table_name VALUES (s_row_no, s_text); s_row_no := s_row_no + 1; ELSE INSERT INTO &table_name VALUES ( s_row_no, INITCAP(s_object_type) || ' ' || LOWER(s_object_name)); s_row_no := s_row_no + 1; END IF; prev_object_type := s_object_type;END LOOP;
CLOSE fetch_object;
n_row := 0;
OPEN fetch_public_synonym;
LOOP
FETCH fetch_public_synonym INTO s_synonym_name, s_table_owner, s_table_name, s_db_link; EXIT WHEN fetch_public_synonym%NOTFOUND; s_text := 'Public Synonym ' || LOWER(s_synonym_name) || ' for ' || LOWER(s_table_name); IF s_db_link IS NOT NULL THEN s_text := s_text || '_at_' || LOWER(s_db_link); END IF; IF n_row = 0 THEN INSERT INTO &table_name VALUES (s_row_no, NULL); s_row_no := s_row_no + 1; n_row := 1; END IF; INSERT INTO &table_name VALUES (s_row_no, s_text); s_row_no := s_row_no + 1;
END LOOP;
CLOSE fetch_public_synonym;
COMMIT;
END;
/
rem =================================================================rem Running the report itself.
rem =================================================================
COLUMN text FORMAT a80;
SPOOL &f_spool;
SELECT text
FROM &table_name
ORDER BY row_no;
SPOOL OFF;
rem =================================================================rem Cleaning up.
rem =================================================================
DROP TABLE &table_name;
QUIT;
--- cut here --- cut here --- cut here --- cut here --- cut here ---
-- // // Jukka Harkki Net: jht_at_cs.tut.fi Addr: Joinex Oy // Tel: +358 31 222 4680 Pyh{j{rvenkatu 5B // Fax: +358 31 222 4685 33200 TAMPERE, FINLANDReceived on Fri Sep 24 1993 - 11:59:26 CEST