Oracle PL/SQL Tutorial/SQL PLUS Session Environment/Long — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 13:03, 26 мая 2010
Содержание
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>