Skip to Main Content

SQLcl: MCP Server & SQL Prompt

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Wrong standart format

user635344Aug 27 2018 — edited Aug 30 2018

I am getting wrong format for sql likes:

select employee_id,first_name,last_name from employees;

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
        100 Steven         King
        101 Neena         Kochhar
        102 Lex              De Haan
        103 Alexander   Hunold
        104 Bruce          Ernst
        105 David          Austin
        106 Valli             Pataballa
        107 Diana          Lorentz
        108 Nancy         Greenberg

Second varchar2 column  is not adjusted to column header (Last Name).

Any ideas?

SQL> desc employees

Name           Null?    Type

-------------- -------- ------------

EMPLOYEE_ID NOT NULL NUMBER(6)

FIRST_NAME          VARCHAR2(20)

LAST_NAME NOT NULL VARCHAR2(25)

EMAIL  NOT NULL VARCHAR2(25)

PHONE_NUMBER          VARCHAR2(20)

HIRE_DATE NOT NULL DATE

JOB_ID NOT NULL VARCHAR2(10)

SALARY          NUMBER(8,2)

COMMISSION_PCT          NUMBER(2,2)

MANAGER_ID          NUMBER(6)

DEPARTMENT_ID          NUMBER(4)

All sets are standard

Thank you

Comments

Maran Viswarayar

GOOGLE!!!!!!!

Salman Qureshi

Hello,

Before moving forward, you would need to note following to clear your concepts

1. If you have a request to create a tablespace, you don't need to check existing free space in the database because somehow, you have to create a new tablespace.

2. You can get a query to check free space in the database, but not for all databases in your environment, You would need to run that query in each database individually to find out free space in it.

See following scripts from my blog, these would give you highlight of allocated and free space in your database and some more information as well

Salman - Oracle DBA: Tablespace Growth History and Forecast

http://salmandba.blogspot.sg/2015/01/database-growth-history-and-forecast.html

Salman

salmandba.blogspot.com

trajon

Hi, you need to check your freespace on level tablspaces:


select  a.tablespace_name,

       round(a.bytes_alloc / 1024 / 1024) megs_alloc,

       round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,

       round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,

       round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,

       100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,

       round(maxbytes/1048576) Max,

      c.status, c.contents

from  ( select  f.tablespace_name,

               sum(f.bytes) bytes_alloc,

               sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes

        from dba_data_files f

        group by tablespace_name) a,

      ( select  f.tablespace_name,

               sum(f.bytes)  bytes_free

        from dba_free_space f

        group by tablespace_name) b,

      dba_tablespaces c

where a.tablespace_name = b.tablespace_name(+)

and a.tablespace_name = c.tablespace_name

union all

select h.tablespace_name,

       round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,

       round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576) megs_free,

       round(sum(nvl(p.bytes_used, 0))/ 1048576) megs_used,

       round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,

       100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) pct_used,

       round(sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes) / 1048576)) max,

      c.status, c.contents

from   sys.v_$TEMP_SPACE_HEADER h,

       sys.v_$Temp_extent_pool p,

       dba_temp_files f,

      dba_tablespaces c

where  p.file_id(+) = h.file_id

and    p.tablespace_name(+) = h.tablespace_name

and    f.file_id = h.file_id

and    f.tablespace_name = h.tablespace_name

and f.tablespace_name = c.tablespace_name

group by h.tablespace_name, c.status, c.contents

ORDER BY 1

good luck...

kt1

HI

Are using ASM??, I use the following at database level.

SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,

fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,

(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,

fs.free_space_mb FREE_SPACE_MB,

ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE

FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,

      ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB

      FROM dba_data_files

      GROUP BY tablespace_name) df,

     (SELECT tablespace_name, SUM(bytes) FREE_SPACE,

       ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB

       FROM dba_free_space

       GROUP BY tablespace_name) fs

WHERE df.tablespace_name = fs.tablespace_name(+)

ORDER BY fs.tablespace_name;

1 - 4

Post Details

Added on Aug 27 2018
6 comments
965 views