Assuming there's some order involved: If only the last 5000 records are interesting, just reverse the ordering. If you only want the top and bottom records, then something like this:
select object_name, object_type, object_id, r#, rr# from ( select o.* , row_number()over(order by object_id) r# , row_number()over(order by object_id DESC) rr# from all_objects o where owner like 'SYS%' ) where rr# <= 5 or r# <= 5 order by r#;OBJECT_NAME OBJECT_TYPE OBJECT_ID R# RR#------------------------------ ------------------------------ ---------- ---------- ----------C_OBJ# CLUSTER 2 1 52440I_OBJ# INDEX 3 2 52439TAB$ TABLE 4 3 52438CLU$ TABLE 5 4 52437C_TS# CLUSTER 6 5 52436WRH$_CELL_OPEN_ALERTS_PK INDEX PARTITION 1628511 52436 5WRH$_CELL_OPEN_ALERTS_PK INDEX PARTITION 1628512 52437 4WRH$_IM_SEG_STAT TABLE PARTITION 1628514 52438 3WRH$_IM_SEG_STAT_PK INDEX PARTITION 1628515 52439 2WRH$_IM_SEG_STAT_PK INDEX PARTITION 1628516 52440 110 rows selected.
However, few people can really grasp more than a dozen or so records.
Similarly, it's easy to get 'bite size' examples records from the entire ordering. Say you want to look at records from seven (roughly) equal-sized chunks. So then:
select object_name, object_type, object_id, r#, rr#, mod# from ( select o.* , row_number()over(order by object_id)-1 AS r# , TRUNC(MOD(row_number()over(order by object_id) ,count(*)over() / 7 -- divide into seven "chunks" ) ) AS mod# , row_number()over(order by object_id DESC)-1 AS rr# from all_objects o where owner like 'SYS%' ) where rr# < 5 or r# < 5 or mod# < 5 order by r#SQL> /OBJECT_NAME OBJECT_TYPE OBJECT_ID R# RR# MOD#------------------------------ ------------------------------ ---------- ---------- ---------- ----------C_OBJ# CLUSTER 2 0 52439 1I_OBJ# INDEX 3 1 52438 2TAB$ TABLE 4 2 52437 3CLU$ TABLE 5 3 52436 4C_TS# CLUSTER 6 4 52435 5XS_PRINCIPAL_INT PACKAGE 11352 7491 44948 0XS_PRINCIPAL_INT PACKAGE BODY 11353 7492 44947 1XS_PRINCIPAL PACKAGE BODY 11354 7493 44946 2DS_VARRAY_4_CLOB TYPE 11358 7494 44945 3COLDICTREC TYPE 11360 7495 44944 4sqlj/translator/ClassPathEntry JAVA CLASS 22653 14982 37457 0sqlj/translator/ClassPath JAVA CLASS 22654 14983 37456 1sqlj/translator/ClassFile JAVA CLASS 22655 14984 37455 2/91e1f59f_ClassResolverImpl JAVA CLASS 22656 14985 37454 3sqlj/translator/Main JAVA CLASS 22657 14986 37453 4/d6c9df50_ServiceLocator JAVA CLASS 30147 22474 29965 0/6612359c_GenericURLContext JAVA CLASS 30148 22475 29964 1/f27212bf_GenericURLDirContext JAVA CLASS 30149 22476 29963 2/b8950380_ldapURLContext JAVA CLASS 30150 22477 29962 3/2342c2e_ldapURLContextFactory JAVA CLASS 30151 22478 29961 4/88d7b011_TexturePaintContextI JAVA CLASS 37642 29965 22474 0java/awt/TexturePaintContext JAVA CLASS 37643 29966 22473 1java/awt/TexturePaint JAVA CLASS 37644 29967 22472 2/1777c77d_OGLPaintsTexture JAVA CLASS 37645 29968 22471 3sun/java2d/opengl/OGLPaints JAVA CLASS 37646 29969 22470 4/59fd3565_SerProfileToClassErr JAVA CLASS 45135 37457 14982 0/48429d3_SerProfileToClassErro JAVA CLASS 45136 37458 14981 1/3dbc884b_SerProfileToClassErr JAVA CLASS 45137 37459 14980 2sqlj/mesg/SyntaxErrorsText JAVA CLASS 45138 37460 14979 3sqlj/mesg/SyntaxErrorsText_ar JAVA CLASS 45139 37461 14978 4WRI$_OPTSTAT_SYNOPSIS$ TABLE SUBPARTITION 555078 44948 7491 0WRI$_OPTSTAT_SYNOPSIS$ TABLE SUBPARTITION 555079 44949 7490 1WRI$_OPTSTAT_SYNOPSIS$ TABLE SUBPARTITION 555080 44950 7489 2WRI$_OPTSTAT_SYNOPSIS$ TABLE SUBPARTITION 555081 44951 7488 3WRI$_OPTSTAT_SYNOPSIS$ TABLE SUBPARTITION 555082 44952 7487 4WRH$_CELL_OPEN_ALERTS_PK INDEX PARTITION 1628511 52435 4 7487WRH$_CELL_OPEN_ALERTS_PK INDEX PARTITION 1628512 52436 3 7488WRH$_IM_SEG_STAT TABLE PARTITION 1628514 52437 2 7489WRH$_IM_SEG_STAT_PK INDEX PARTITION 1628515 52438 1 7490WRH$_IM_SEG_STAT_PK INDEX PARTITION 1628516 52439 0 040 rows selected.
Lets you look around a bit. (Notice that there are 8 sets of 5 records. Seven "first" records, and then the final 5 of the results).
If we add in Oracle ACE Aketi Jyuuzou's "Tabibitosan" technique for grouping (which should be in everyone's repertoire), then the GRP# make the stratifications visible:
column grp# format 999,999column r# format 999,999column rr# format 999,999column mod# format 999,999SELECT object_name, object_type, object_id , DENSE_RANK() OVER (ORDER BY tabibito#) GRP# , r#, rr#, mod# FROM (select object_name, object_type, object_id, r#, rr#, mod# , r# - row_number() OVER (order by r#) AS tabibito# from ( select o.* , row_number()over(order by object_id)-1 AS r# , TRUNC(MOD(row_number()over(order by object_id) ,count(*)over() / 7 -- divide into seven "chunks" ) ) AS mod# , row_number()over(order by object_id DESC)-1 AS rr# from all_objects o where owner like 'SYS%' ) where rr# < 5 or r# < 5 or mod# < 5) order by r#;OBJECT_NAME OBJECT_TYPE OBJECT_ID GRP# R# RR# MOD#------------------------------ ------------------------------ ---------- -------- -------- -------- --------C_OBJ# CLUSTER 2 1 0 52,439 1I_OBJ# INDEX 3 1 1 52,438 2TAB$ TABLE 4 1 2 52,437 3CLU$ TABLE 5 1 3 52,436 4C_TS# CLUSTER 6 1 4 52,435 5XS_PRINCIPAL_INT PACKAGE 11352 2 7,491 44,948 0XS_PRINCIPAL_INT PACKAGE BODY 11353 2 7,492 44,947 1XS_PRINCIPAL PACKAGE BODY 11354 2 7,493 44,946 2DS_VARRAY_4_CLOB TYPE 11358 2 7,494 44,945 3COLDICTREC TYPE 11360 2 7,495 44,944 4sqlj/translator/ClassPathEntry JAVA CLASS 22653 3 14,982 37,457 0sqlj/translator/ClassPath JAVA CLASS 22654 3 14,983 37,456 1sqlj/translator/ClassFile JAVA CLASS 22655 3 14,984 37,455 2/91e1f59f_ClassResolverImpl JAVA CLASS 22656 3 14,985 37,454 3sqlj/translator/Main JAVA CLASS 22657 3 14,986 37,453 4/d6c9df50_ServiceLocator JAVA CLASS 30147 4 22,474 29,965 0/6612359c_GenericURLContext JAVA CLASS 30148 4 22,475 29,964 1/f27212bf_GenericURLDirContext JAVA CLASS 30149 4 22,476 29,963 2/b8950380_ldapURLContext JAVA CLASS 30150 4 22,477 29,962 3/2342c2e_ldapURLContextFactory JAVA CLASS 30151 4 22,478 29,961 4/88d7b011_TexturePaintContextI JAVA CLASS 37642 5 29,965 22,474 0java/awt/TexturePaintContext JAVA CLASS 37643 5 29,966 22,473 1java/awt/TexturePaint JAVA CLASS 37644 5 29,967 22,472 2/1777c77d_OGLPaintsTexture JAVA CLASS 37645 5 29,968 22,471 3sun/java2d/opengl/OGLPaints JAVA CLASS 37646 5 29,969 22,470 4/59fd3565_SerProfileToClassErr JAVA CLASS 45135 6 37,457 14,982 0/48429d3_SerProfileToClassErro JAVA CLASS 45136 6 37,458 14,981 1/3dbc884b_SerProfileToClassErr JAVA CLASS 45137 6 37,459 14,980 2sqlj/mesg/SyntaxErrorsText JAVA CLASS 45138 6 37,460 14,979 3sqlj/mesg/SyntaxErrorsText_ar JAVA CLASS 45139 6 37,461 14,978 4WRI$_OPTSTAT_SYNOPSIS$ TABLE SUBPARTITION 555078 7 44,948 7,491 0WRI$_OPTSTAT_SYNOPSIS$ TABLE SUBPARTITION 555079 7 44,949 7,490 1WRI$_OPTSTAT_SYNOPSIS$ TABLE SUBPARTITION 555080 7 44,950 7,489 2WRI$_OPTSTAT_SYNOPSIS$ TABLE SUBPARTITION 555081 7 44,951 7,488 3WRI$_OPTSTAT_SYNOPSIS$ TABLE SUBPARTITION 555082 7 44,952 7,487 4WRH$_CELL_OPEN_ALERTS_PK INDEX PARTITION 1628511 8 52,435 4 7,487WRH$_CELL_OPEN_ALERTS_PK INDEX PARTITION 1628512 8 52,436 3 7,488WRH$_IM_SEG_STAT TABLE PARTITION 1628514 8 52,437 2 7,489WRH$_IM_SEG_STAT_PK INDEX PARTITION 1628515 8 52,438 1 7,490WRH$_IM_SEG_STAT_PK INDEX PARTITION 1628516 8 52,439 0 040 rows selected.
FYI: "Tabibito" is Japanese for "traveler"; "san" is an honorific.
SQL developer is a terrible tool
It's nowhere even close compared to toad.
Just try to get current user creation script, not possible easily even with dba module.
How can the product team be so dumb and unaware.
Please learn from toad and try to incorporate its features.
I dont mind if you want to bleat about your gripes with SQL Developer.
Because I know also there's 4,000,000+ downloads of SQL Developer, and the vast majority of people that have done so, have positive things to say about it ... one of those things being its price compared with TOAD.
Of course, you could always adopt a more sensible stance and *liaise* with the product management for SQL Developer, and provide *objective* feedback, and work *with* them to get desired improvements to the product.
But hey.... do whatever floats your boat. If bile and invective are your thing...fine.
Even with rants, I know the SQL Dev product team will keep working to make a great product greater, because that's the kind of team they are.
I switched to a TOAD-less existence over 2 years ago as an Oracle developer / data designer:
http://it.toolbox.com/blogs/data-ruminations/drinking-your-own-champagne-skip-the-toad-juice-59408
I haven't looked back -- except when someone asks. Overall, SQL*Developer isn't as slick as TOAD, but each release gets better. I'ven't felt any need to go back to the TOAD juice.
Usually a google search of ThatJeffSmith.com answers any questions I have about SQL*Developer.
Team,
This morning was reading this blog post from Jeffsmith, Excellent !
https://www.thatjeffsmith.com/archive/2012/03/javadoc-for-the-database-a-la-dbdoc-via-sql-developer/Is that still possible to generated DBDOC for other schema that are not listed in connections panel?
Say, we have a Database that supports 10 to 15 applications, spread across different schema. we have a DBA account, which we can switch into different schema and do manipulations, but we don't know the password for individual schema.
Is it still possible from SQL Developer (version 17.4) to generate DB DOC for all the schema from a single DBA account?
Team,
You could perhaps use the Database Export facility to export DDL using the "one file per object, and then iterate through that with the command line facility.
Able to understand this - "You could perhaps use the Database Export facility to export DDL using the "one file per object"
"and then iterate through that with the command line facility" - what does that mean? could you please elaborate ?
On the pldoc site
http://pldoc.sourceforge.net/maven-site/
You can run the command line utility for multiple files , eg
pldoc -d sampleOutput -doctitle TheBigApp -overview overview.html sample*.sql
So use SQL Developer Tools => Database Export to export your DDL for all schemas desired, and then use the command line utility against that.
