Oracle database’s dynamic performance views, better known as v$ views, have loads of useful information related to the internals of Oracle database. But you have to admit, selecting from them especially using SQL*Plus produces an unreadable stream of texts on the screen that make them hard to use, unless using an IDE that puts the output into a nice looking table. Even then, you do have to scroll left and right to get what you want!
This made me wonder if there was an easy way to show the columns as rows, and Yes, according to the “column to rows” blog post in AskTOM there is an easy workaround for this solution. I checked it out and it works perfectly for what I need it to do, but I noticed after running the original solution that the RNUM column in the generated output returns the value 1 for all the rows. I checked and I think the l_rnum that’s being incremented by 1 on line 44 of the original solution should be moved to the inner loop after line 42. This little adjustment takes care of that issue, but other than that the function works brilliantly.
Definitely checkout the original question and answer, but I’m pasting the updated solution below for myself so I don’t have to search for this over and over! Come to think of it, it might be a good idea to add this little function to the list of scripts I run after each new database install to make my DBA life a bit easier!
CREATE OR replace TYPE myscalartype AS object (
rnum NUMBER,
cname VARCHAR2(30),
val VARCHAR2(4000) )
/
CREATE OR replace TYPE mytabletype
AS TABLE OF MYSCALARTYPE
/
CREATE OR replace FUNCTION Cols_as_rows(p_query IN VARCHAR2)
RETURN MYTABLETYPE
-- this function is designed to be installed ONCE per database, and
-- it is nice to have ROLES active for the dynamic sql, hence the
-- AUTHID CURRENT_USER
authid current_user
-- this function is a pipelined function -- meaning, it'll send
-- rows back to the client before getting the last row itself
-- in 8i, we cannot do this
pipelined
AS
l_thecursor INTEGER DEFAULT dbms_sql.open_cursor;
l_columnvalue VARCHAR2(4000);
l_status INTEGER;
l_colcnt NUMBER DEFAULT 0;
l_desctbl dbms_sql.desc_tab;
l_rnum NUMBER := 1;
BEGIN
-- parse, describe and define the query. Note, unlike print_table
-- i am not altering the session in this routine. the
-- caller would use TO_CHAR() on dates to format and if they
-- want, they would set cursor_sharing. This routine would
-- be called rather infrequently, I did not see the need
-- to set cursor sharing therefore.
dbms_sql.Parse(l_thecursor, p_query, dbms_sql.native);
dbms_sql.Describe_columns(l_thecursor, l_colcnt, l_desctbl);
FOR i IN 1 .. l_colcnt LOOP
dbms_sql.Define_column(l_thecursor, i, l_columnvalue, 4000);
END LOOP;
-- Now, execute the query and fetch the rows. Iterate over
-- the columns and "pipe" each column out as a separate row
-- in the loop. increment the row counter after each
-- dbms_sql row
l_status := dbms_sql.EXECUTE(l_thecursor);
WHILE ( dbms_sql.Fetch_rows(l_thecursor) > 0 ) LOOP
FOR i IN 1 .. l_colcnt LOOP
dbms_sql.Column_value(l_thecursor, i, l_columnvalue);
pipe ROW (Myscalartype( l_rnum, L_desctbl(i).col_name, l_columnvalue
));
l_rnum := l_rnum + 1;
END LOOP;
END LOOP;
-- clean up and return...
dbms_sql.Close_cursor(l_thecursor);
RETURN;
END cols_as_rows;
/
Now that the function and types are created, let’s take a look at the beautified output of some of the important dynamic performance views, v$instance, v$database, and v$instance_log_group, that DBAs use frequently. I honestly think this output is much more readable:
SQL> select * from TABLE(cols_as_rows('select * from v$instance') );
RNUM CNAME VAL
---------- ------------------------------ ---------------------------------------
1 INST_ID 1
2 INSTANCE_NUMBER 1
3 INSTANCE_NAME SBX21C
4 HOST_NAME jester.localdomain
5 VERSION 21.0.0.0.0
6 VERSION_LEGACY 21.0.0.0.0
7 VERSION_FULL 21.7.0.0.0
8 STARTUP_TIME 27-AUG-22
9 STATUS OPEN
10 PARALLEL NO
11 THREAD# 1
12 ARCHIVER STOPPED
13 LOG_SWITCH_WAIT
14 LOGINS ALLOWED
15 SHUTDOWN_PENDING NO
16 DATABASE_STATUS ACTIVE
17 INSTANCE_ROLE PRIMARY_INSTANCE
18 ACTIVE_STATE NORMAL
19 BLOCKED NO
20 CON_ID 0
21 INSTANCE_MODE REGULAR
22 EDITION EE
23 FAMILY
24 DATABASE_TYPE SINGLE
24 rows selected.
SQL> select * from TABLE(cols_as_rows('select * from v$database') );
RNUM CNAME VAL
---------- ------------------------------ ---------------------------------------
1 DBID 501335790
2 NAME SBX21C
3 CREATED 27-AUG-22
4 RESETLOGS_CHANGE# 1
5 RESETLOGS_TIME 27-AUG-22
6 PRIOR_RESETLOGS_CHANGE# 0
7 PRIOR_RESETLOGS_TIME
8 LOG_MODE NOARCHIVELOG
9 CHECKPOINT_CHANGE# 2483585
10 ARCHIVE_CHANGE# 1256845
11 CONTROLFILE_TYPE CURRENT
12 CONTROLFILE_CREATED 27-AUG-22
13 CONTROLFILE_SEQUENCE# 611
14 CONTROLFILE_CHANGE# 2586020
15 CONTROLFILE_TIME 27-AUG-22
16 OPEN_RESETLOGS NOT ALLOWED
17 VERSION_TIME 27-AUG-22
18 OPEN_MODE READ WRITE
19 PROTECTION_MODE MAXIMUM PERFORMANCE
20 PROTECTION_LEVEL UNPROTECTED
21 REMOTE_ARCHIVE ENABLED
22 ACTIVATION# 501328366
23 SWITCHOVER# 501328366
24 DATABASE_ROLE PRIMARY
25 ARCHIVELOG_CHANGE# 0
26 ARCHIVELOG_COMPRESSION DISABLED
27 SWITCHOVER_STATUS NOT ALLOWED
28 DATAGUARD_BROKER DISABLED
29 GUARD_STATUS NONE
30 SUPPLEMENTAL_LOG_DATA_MIN NO
31 SUPPLEMENTAL_LOG_DATA_PK NO
32 SUPPLEMENTAL_LOG_DATA_UI NO
33 FORCE_LOGGING NO
34 PLATFORM_ID 13
35 PLATFORM_NAME Linux x86 64-bit
36 RECOVERY_TARGET_INCARNATION# 1
37 LAST_OPEN_INCARNATION# 1
38 CURRENT_SCN 2586037
39 FLASHBACK_ON NO
40 SUPPLEMENTAL_LOG_DATA_FK NO
41 SUPPLEMENTAL_LOG_DATA_ALL NO
42 DB_UNIQUE_NAME SBX21C
43 STANDBY_BECAME_PRIMARY_SCN 0
44 FS_FAILOVER_MODE DISABLED
45 FS_FAILOVER_STATUS DISABLED
46 FS_FAILOVER_CURRENT_TARGET
47 FS_FAILOVER_THRESHOLD 0
48 FS_FAILOVER_OBSERVER_PRESENT
49 FS_FAILOVER_OBSERVER_HOST
50 CONTROLFILE_CONVERTED NO
51 PRIMARY_DB_UNIQUE_NAME
52 SUPPLEMENTAL_LOG_DATA_PL NO
53 MIN_REQUIRED_CAPTURE_CHANGE#
54 CDB YES
55 CON_ID 0
56 PENDING_ROLE_CHANGE_TASKS NOT APPLICABLE
57 CON_DBID 501335790
58 FORCE_FULL_DB_CACHING NO
59 SUPPLEMENTAL_LOG_DATA_SR NO
60 GOLDENGATE_BLOCKING_MODE DISABLED
60 rows selected.
SQL> select * from TABLE(cols_as_rows('select * from V$INSTANCE_LOG_GROUP') );
RNUM CNAME VAL
---------- ------------------------------ ---------------------------------------
1 THREAD# 1
2 STATUS OPEN
3 ENABLED PUBLIC
4 GROUPS 3
5 INSTANCE SBX21C
6 OPEN_TIME 27-AUG-22
7 CURRENT_GROUP# 1
8 SEQUENCE# 10
9 CHECKPOINT_CHANGE# 2483585
10 CHECKPOINT_TIME 27-AUG-22
11 ENABLE_CHANGE# 1
12 ENABLE_TIME 27-AUG-22
13 DISABLE_CHANGE# 0
14 DISABLE_TIME
15 CON_ID 0
15 rows selected.
