Oracle queries all tables and sqlplus set column widths under the current user

Source: Internet
Author: User
Tags dname sqlplus

If the Oracle server is loaded with multiple DB instances, enter the username/password @ database name at the user name. If the database server is not on this computer, you also need to add the address of the database server: username/password @ip address/database name.

[Email protected] ~]$ sqlplus scott/[email Protected]/prod

Sql*plus:release 11.2.0.3.0 Production on Thu Apr 13 19:49:57 2017

Copyright (c) 1982, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the partitioning, OLAP, Data Mining and Real application testing options

Sql> select table_name from tabs;

table_name
------------------------------
DEPT
Emp
BONUS
Salgrade

Sql> Select table_name from User_tables;

table_name
------------------------------
DEPT
Emp
BONUS
Salgrade

Column widths of type number are set in 99999 or 999,99 format.

99999 means the column width is set to five digits, and999,99 indicates that the column width is set to 6 digits because the comma is counted as one.

Sql> select * FROM dept;

DEPTNO dname LOC
---------- -------------- -------------
Ten ACCOUNTING NEW YORK
DALLAS
SALES CHICAGO
OPERATIONS BOSTON

sql> Col deptno format 9999;
Sql> select * FROM dept;

DEPTNO dname LOC
------ -------------- -------------
Ten ACCOUNTING NEW YORK
DALLAS
SALES CHICAGO
OPERATIONS BOSTON

Sets the width of the varchar2 type column here A30 represents a width of 30 characters.

sql> Col dname format A30;
Sql> select * FROM dept;

DEPTNO dname LOC
------ ------------------------------ -------------
Ten ACCOUNTING NEW YORK
DALLAS
SALES CHICAGO
OPERATIONS BOSTON

View Table Structure Decribe tablename

Sql> Select table_name from User_tables;

table_name
------------------------------
DEPT
Emp
BONUS
Salgrade

Sql> describe dept;
 Name        null?    Type
 ----------------- ------------------------------------------------------------
 deptno         Not NULL number (2)
 DNAME          VARCHAR2
 LOC          VARCHAR2 (+)

Sql> describe EMP;
 Name        null?    Type
 ----------------- ------------------------------------------------------------
 EMPNO         not NULL number (4)
 ENAME          VARCHAR2 (10)
 JOB          VARCHAR2 (9)
 MGR           Number (4)
 HIREDATE          DATE
 SAL          number (7,2)
 COMM           number (7,2)
 deptno           Number (2)

sql> describe bonus;
Name Null? Type
----------------------------------------- -------- ----------------------------
Ename VARCHAR2 (10)
JOB VARCHAR2 (9)
SAL number
COMM number

Sql> describe Salgrade;
Name Null? Type
----------------------------------------- -------- ----------------------------
GRADE number
Losal number
Hisal number

Oracle queries all tables and sqlplus in the current user to set the column width

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.