Oracle查询当前用户下的所有表及sqlplus 设置列宽

如果oracle服务器中装有多个数据库实例，则在用户名处输入：用户名/密码@数据库名称。如果数据库服务器不在本机上，还需要加上数据库服务器的地址：用户名/密码@IP地址/数据库名称。

[oracle@node130 ~]$ sqlplus scott/123456@192.168.8.145/prod

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 13 19:49:57 2017

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

类型为number的列宽是用99999或999,99的格式来设置的。

99999表示列宽设为五位数，999,99表示列宽设为6位数，因为逗号也算一位。

SQL> select * from dept;

    DEPTNO DNAME   LOC
---------- -------------- -------------
10 ACCOUNTING   NEW YORK
20 RESEARCH   DALLAS
30 SALES   CHICAGO
40 OPERATIONS   BOSTON

SQL> col deptno format 9999;
SQL> select * from dept;

DEPTNO DNAME       LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES       CHICAGO
    40 OPERATIONS     BOSTON

设置varchar2类型列的宽度这里a30表示占30个字符的宽度。

SQL> col dname format a30;
SQL> select * from dept;

DEPTNO DNAME         LOC
------ ------------------------------ -------------
    10 ACCOUNTING        NEW YORK
    20 RESEARCH         DALLAS
    30 SALES         CHICAGO
    40 OPERATIONS        BOSTON

查看表结构 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(14)
LOC          VARCHAR2(13)

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查询当前用户下的所有表及sqlplus 设置 列宽

Oracle查询当前用户下的所有表及sqlplus 设置列宽