Now you don't have to look in a reference manual to find descriptions about v$ and other dictionary tables and their columns -- you can write a SQL query. The sample script shown below will prompt you for the name of the table.
SET LINESIZE 300 SET PAGESIZE 0 COLUMN TABLE_NAME FORMAT A40 WRAP COLUMN COLUMN_NAME FORMAT A40 WRAP COLUMN COMMENTS FORMAT A80 WRAP
This is to get information about tables. Try it by entering DBA_TABLES.
SELECT * FROM DICTIONARY WHERE TABLE_NAME= UPPER('&TABLE_NAME');
This is to get information about columns. Try it by entering DBA_TABLES.
SELECT * FROM DICT_COLUMNS WHERE TABLE_NAME= UPPER('&TABLE_NAME');
Edward M. writes: Here is what I get when I run this query.
Enter value for table_name: dba_tables old 2: WHERE TABLE_NAME= UPPER('&TABLE_NAME') new 2: WHERE TABLE_NAME= UPPER('dba_tables') buffer overflow. Use SET command to reduce ARRAYSIZE or increase MAXDATA.What I can I do in this case?
The author responds: Please use the following before executing the query:
SET ARRAYSIZE 1The Following is an explanation from the SQL*PLUS manual about why we are reducing ARRAYSIZE TO 1:
"SET ARRAYSIZE sets the number of rows--called a batch--that SQL*Plus will fetch from the database at one time. Valid values are 1 to 5000. A large value increases the efficiency of queries and subqueries that fetch
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free Oracle tips and scripts.
- Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, relational model, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
- Best Web Links: Oracle tips, tutorials, and scripts from around the Web.
This was first published in September 2002