в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:03:57Z

1 версия

Новая страница

== LONG controls how much of a LONG or CLOB sqlplus displays by default==



<source lang="sql">
SQL>
SQL> Set long 5000
SQL>
SQL></source>

== Set long==



<source lang="sql">
SQL> set long
SQL>
SQL>
SQL> select text
2 from all_views
3 where view_name = "USER_TABLES";
set pagesize 1000
TEXT
--------------------------------------------------------------------------------
select o.name, decode(bitand(t.property, 2151678048), 0, ts.name, null),
decode(bitand(t.property, 1024), 0, null, co.name),
decode((bitand(t.property, 512)+bitand(t.flags, 536870912)),
0, null, co.name),
decode(bitand(t.trigflag, 1073741824), 1073741824, "UNUSABLE", "VALID"),
decode(bitand(t.property, 32+64), 0, mod(t.pctfree$, 100), 64, 0, null),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(t.property, 32+64), 0, t.pctused$, 64, 0, null)),
decode(bitand(t.property, 32), 0, t.initrans, null),
decode(bitand(t.property, 32), 0, t.maxtrans, null),
s.iniexts * ts.blocksize,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extsize * ts.blocksize),
s.minexts, s.maxexts,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extpct),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1, decode(s.lists, 0, 1, s.lists))),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1, decode(s.groups, 0, 1, s.groups))),
decode(bitand(t.property, 32+64), 0,
decode(bitand(t.flags, 32), 0, "YES", "NO"), null),
decode(bitand(t.flags,1), 0, "Y", 1, "N", "?"),
t.rowcnt,
decode(bitand(t.property, 64), 0, t.blkcnt, null),
decode(bitand(t.property, 64), 0, t.empcnt, null),
decode(bitand(t.property, 64), 0, t.avgspc, null),
t.chncnt, t.avgrln, t.avgspc_flb,
decode(bitand(t.property, 64), 0, t.flbcnt, null),
lpad(decode(t.degree, 32767, "DEFAULT", nvl(t.degree,1)),10),
lpad(decode(t.instances, 32767, "DEFAULT", nvl(t.instances,1)),10),
lpad(decode(bitand(t.flags, 8), 8, "Y", "N"),5),
decode(bitand(t.flags, 6), 0, "ENABLED", "DISABLED"),
t.samplesize, t.analyzetime,
decode(bitand(t.property, 32), 32, "YES", "NO"),
decode(bitand(t.property, 64), 64, "IOT",
decode(bitand(t.property, 512), 512, "IOT_OVERFLOW",
decode(bitand(t.flags, 536870912), 536870912, "IOT_MAPPING", null
))),
decode(bitand(o.flags, 2), 0, "N", 2, "Y", "N"),
decode(bitand(o.flags, 16), 0, "N", 16, "Y", "N"),
decode(bitand(t.property, 8192), 8192, "YES",
decode(bitand(t.property, 1), 0, "NO", "YES")),
decode(bitand(o.flags, 2), 2, "DEFAULT",
decode(s.cachehint, 0, "DEFAULT", 1, "KEEP", 2, "RECYCLE", NULL)),
decode(bitand(t.flags, 131072), 131072, "ENABLED", "DISABLED"),
decode(bitand(t.flags, 512), 0, "NO", "YES"),
decode(bitand(t.flags, 256), 0, "NO", "YES"),
decode(bitand(o.flags, 2), 0, NULL,
decode(bitand(t.property, 8388608), 8388608,
"SYS$SESSION", "SYS$TRANSACTION")),
decode(bitand(t.flags, 1024), 1024, "ENABLED", "DISABLED"),
decode(bitand(o.flags, 2), 2, "NO",
decode(bitand(t.property, 2147483648), 2147483648, "NO",
decode(ksppcv.ksppstvl, "TRUE", "YES", "NO"))),
decode(bitand(t.property, 1024), 0, null, cu.name),
decode(bitand(t.flags, 8388608), 8388608, "ENABLED", "DISABLED"),
decode(bitand(t.property, 32), 32, null,
decode(bitand(s.spare1, 2048), 2048, "ENABLED", "DISABLED")),
decode(bitand(o.flags, 128), 128, "YES", "NO")
from sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o,
sys.obj$ cx, sys.user$ cu, x$ksppcv ksppcv, x$ksppi ksppi
where o.owner# = userenv("SCHEMAID")
and o.obj# = t.obj#
and bitand(t.property, 1) = 0
and bitand(o.flags, 128) = 0
and t.bobj# = co.obj# (+)
and t.ts# = ts.ts#
and t.file# = s.file# (+)
and t.block# = s.block# (+)
and t.ts# = s.ts# (+)
and t.dataobj# = cx.obj# (+)
and cx.owner# = cu.user# (+)
and ksppi.indx = ksppcv.indx
and ksppi.ksppinm = "_dml_monitoring_enabled"

SQL> set long 10000
SQL> /
--
TEXT
--------------------------------------------------------------------------------
select o.name, decode(bitand(t.property, 2151678048), 0, ts.name, null),
decode(bitand(t.property, 1024), 0, null, co.name),
decode((bitand(t.property, 512)+bitand(t.flags, 536870912)),
0, null, co.name),
decode(bitand(t.trigflag, 1073741824), 1073741824, "UNUSABLE", "VALID"),
decode(bitand(t.property, 32+64), 0, mod(t.pctfree$, 100), 64, 0, null),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(t.property, 32+64), 0, t.pctused$, 64, 0, null)),
decode(bitand(t.property, 32), 0, t.initrans, null),
decode(bitand(t.property, 32), 0, t.maxtrans, null),
s.iniexts * ts.blocksize,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extsize * ts.blocksize),
s.minexts, s.maxexts,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extpct),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1, decode(s.lists, 0, 1, s.lists))),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1, decode(s.groups, 0, 1, s.groups))),
decode(bitand(t.property, 32+64), 0,
decode(bitand(t.flags, 32), 0, "YES", "NO"), null),
decode(bitand(t.flags,1), 0, "Y", 1, "N", "?"),
t.rowcnt,
decode(bitand(t.property, 64), 0, t.blkcnt, null),
decode(bitand(t.property, 64), 0, t.empcnt, null),
decode(bitand(t.property, 64), 0, t.avgspc, null),
t.chncnt, t.avgrln, t.avgspc_flb,
decode(bitand(t.property, 64), 0, t.flbcnt, null),
lpad(decode(t.degree, 32767, "DEFAULT", nvl(t.degree,1)),10),
lpad(decode(t.instances, 32767, "DEFAULT", nvl(t.instances,1)),10),
lpad(decode(bitand(t.flags, 8), 8, "Y", "N"),5),
decode(bitand(t.flags, 6), 0, "ENABLED", "DISABLED"),
t.samplesize, t.analyzetime,
decode(bitand(t.property, 32), 32, "YES", "NO"),
decode(bitand(t.property, 64), 64, "IOT",
decode(bitand(t.property, 512), 512, "IOT_OVERFLOW",
decode(bitand(t.flags, 536870912), 536870912, "IOT_MAPPING", null
))),
decode(bitand(o.flags, 2), 0, "N", 2, "Y", "N"),
decode(bitand(o.flags, 16), 0, "N", 16, "Y", "N"),
decode(bitand(t.property, 8192), 8192, "YES",
decode(bitand(t.property, 1), 0, "NO", "YES")),
decode(bitand(o.flags, 2), 2, "DEFAULT",
decode(s.cachehint, 0, "DEFAULT", 1, "KEEP", 2, "RECYCLE", NULL)),
decode(bitand(t.flags, 131072), 131072, "ENABLED", "DISABLED"),
decode(bitand(t.flags, 512), 0, "NO", "YES"),
decode(bitand(t.flags, 256), 0, "NO", "YES"),
decode(bitand(o.flags, 2), 0, NULL,
decode(bitand(t.property, 8388608), 8388608,
"SYS$SESSION", "SYS$TRANSACTION")),
decode(bitand(t.flags, 1024), 1024, "ENABLED", "DISABLED"),
decode(bitand(o.flags, 2), 2, "NO",
decode(bitand(t.property, 2147483648), 2147483648, "NO",
decode(ksppcv.ksppstvl, "TRUE", "YES", "NO"))),
decode(bitand(t.property, 1024), 0, null, cu.name),
decode(bitand(t.flags, 8388608), 8388608, "ENABLED", "DISABLED"),
decode(bitand(t.property, 32), 32, null,
decode(bitand(s.spare1, 2048), 2048, "ENABLED", "DISABLED")),
decode(bitand(o.flags, 128), 128, "YES", "NO")
from sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o,
sys.obj$ cx, sys.user$ cu, x$ksppcv ksppcv, x$ksppi ksppi
where o.owner# = userenv("SCHEMAID")
and o.obj# = t.obj#
and bitand(t.property, 1) = 0
and bitand(o.flags, 128) = 0
and t.bobj# = co.obj# (+)
and t.ts# = ts.ts#
and t.file# = s.file# (+)
and t.block# = s.block# (+)
and t.ts# = s.ts# (+)
and t.dataobj# = cx.obj# (+)
and cx.owner# = cu.user# (+)
and ksppi.indx = ksppcv.indx
and ksppi.ksppinm = "_dml_monitoring_enabled"

SQL></source>

== set long 50000==



<source lang="sql">
SQL>
SQL>
SQL> set long 50000
SQL>
SQL> select text
2 from all_views
3 where view_name = "ALL_TAB_COLUMNS"
4 /

TEXT
--------------------------------------------------------------------------------
select /*+ rule */ OWNER, TABLE_NAME,
COLUMN_NAME, DATA_TYPE, DATA_TYPE_MOD, DATA_TYPE_OWNER,
DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID,
DEFAULT_LENGTH, DATA_DEFAULT, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE,
DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE,
CHARACTER_SET_NAME, CHAR_COL_DECL_LENGTH,
GLOBAL_STATS, USER_STATS, AVG_COL_LEN, CHAR_LENGTH, CHAR_USED,
V80_FMT_IMAGE, DATA_UPGRADED, HISTOGRAM
from ALL_TAB_COLS
where HIDDEN_COLUMN = "NO"

SQL></source>

== set long 999==



<source lang="sql">
SQL>
SQL> set long 999
SQL>
SQL> select view_name, text
2 from user_views
3 where rownum < 50;
VIEW_NAME TEXT
------------------------------ ------------------------------------------
APPLICATION_USERS select "UNAME","PW","ROLE_TO_GRANT" from a
pplication_users_tbl
AUTHOR_PUBLICATION SELECT author.id,
author.name,
publication.title,
publication.written_date
FROM author,
publication
WHERE author.id = publication.id
AVG_EVALUATIONS select course
, avg(evaluation) as avg_eval
from registrations
group by course
COURSE_DAYS select e.empno
, e.ename
, sum(c.duration) as days
VIEW_NAME TEXT
------------------------------ ------------------------------------------
from registrations r
join courses c on (c.code = r.
course)
join employees e on (e.empno = r.
attendee)
group by e.empno
, e.ename
CRS_COURSE_SCHEDULE select o.course as course_code, c.descript
ion, o.begindate
from course_schedule o
join
courses c
on (o.course = c.code)
CRS_OFFERINGS select o.course as course_code, c.descript
ion, o.begindate
from offerings o
join
VIEW_NAME TEXT
------------------------------ ------------------------------------------
courses c
on (o.course = c.code)
DEPT20_V select "EMPNO","ENAME","INIT","JOB","MGR",
"BDATE","MSAL","COMM","DEPTNO" from employ
ees where deptno=20
DEPT_OR select deptno, dname, loc,
cast ( multiset (
select empno, ename, job, m
gr, hiredate, sal, comm
from emp
where emp.deptno = dept.de
ptno )
as emp_tab_type )
from dept
EMP_V select empno , ename, job,mgr,
audit_trail_pkg.record( empno, "sal
VIEW_NAME TEXT
------------------------------ ------------------------------------------
", sal ) sal,
audit_trail_pkg.record( empno, "com
m", comm ) comm,
audit_trail_pkg.record( empno, "hir
edate", hiredate ) hiredate,
deptno
from emp
EMP_VIEW select ename, empno from emp
where ename = sys_context( "userenv", "cli
ent_info")
HRC_ORG_SITE SELECT h.hrc_code,hrc_descr,
o.org_id,org_short_name,org_long_
name,
os.site_no,site_descr
FROM org_site_tab os,org_tab o,hrc_tab h
,site_tab s
WHERE os.org_id =o.org_id
VIEW_NAME TEXT
------------------------------ ------------------------------------------
AND o.hrc_code =h.hrc_code
AND os.site_no =s.site_no
SALES_BY_ATLAS_V SELECT "PRODUCT_NAME","SALESPERSON","ORDER
_DATE","QUANTITY" FROM product_order WHE
RE salesperson = "CA"
SALES_PER_PERSON_V SELECT pers.first_name || " " || pers.last
_name SALESPERSON,
purc.product_name,
purc.order_date,
purc.quantity
FROM person pers,
product_order purc
WHERE pers.person_code = purc.salesperson
(+)
USER_AVAIL_TRACE_FILES select "USERNAME","FILENAME","DT" from ava
il_trace_files where username = user
VIEW_NAME TEXT
------------------------------ ------------------------------------------

14 rows selected.
SQL></source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/SQL PLUS Session Environment/Long - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия