How to delete/drop an user schema in Oracle

alter session set "_oracle_script"=true;

drop user <USER_SCHEMA> cascade;

Example:
drop user myUserSchema cascade;

How to restart Oracle

Shutdown

$ sqlplus '/ as sysdba'
SQL> shutdown

Options:
shutdown - waits for sessions end
shutdown immediate - rollbacks and ends sessions (waits for rollback finish)
shutdown abort - kills everything - usually database has to perform some recovery on startup.

Startup

$ sqlplus '/ as sysdba'
SQL> startup

References:
https://dba.stackexchange.com/questions/42774/how-to-quickly-startup-shutdown-oracle-11
https://docs.oracle.com/cd/E17781_01/server.112/e18804/startup.htm#ADMQS124
https://www.cyberciti.biz/faq/how-do-i-start-oracle-service-in-unix/

How to limit the number of records returned in a SQL query

There is no a SQL standard to limit the number of records returned in a query, ie, each vendor implements this functionality in its own way.

Here's a summary on how to do this for some vendors:

DB2 -- select * from table fetch first 10 rows only 
Informix, InterBase/Firebird, SKIP  -- select first 10 * from table 
Microsoft SQL Server and Access -- select top 10 * from table 
MySQL, PostgreSQL, SQLite, HSQL/H2 -- select * from table limit 10 
Oracle -- select * from table where rownum <= 10

Oracle Note:

If the query has "group by" or "order by" rownum must be supplied:

Ex:

SELECT a, b, c
FROM
( SELECT a, b, c, ROWNUM rn
FROM
( SELECT a, b, c FROM RECORD 
WHERE AVAIL='Y"
ORDER BY DATE DESC
)
WHERE ROWNUM <= 25
)
WHERE rn >= 21;

References:

http://stackoverflow.com/questions/595123/is-there-an-ansi-sql-alternative-to-the-mysql-limit-keyword

http://www.macoratti.net/d250102.htm

http://www.dougboude.com/blog/1/2006/06/Equivalent-of-SQL-TOP-X-in-Oracle.cfm

http://www.dbforums.com/oracle/1214188-limit-clause-oracle-sql.html

How to show only one header in sqlplus

When you have to use sqlplus and want to avoid showing table header each n rows returned, you can use the command bellow to show only one header:


set pagesize 0   -- only one header row

Another useful commands:
set colsep ,     -- separate columns with a comma
set trimspool on -- remove trailing blanks
set headsep off  -- this may or may not be useful...depends on your headings.
set linesize X   -- X should be the sum of the column widths

sqlplus reference: http://psoug.org/reference/sqlplus.html
How to create a csv file from sqlplus output: http://stackoverflow.com/questions/643137/how-do-i-spool-to-a-csv-formatted-file-using-sqlplus

Auto generated ID's for MySql and Oracle using Hibernate

Here you have auto generated ID's examples using Hibernate with Oracle and MySql databases.

Oracle

...

@Entity
@Table(name = "grupo")
public class Grupo implements GenericModel {


@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "GRUPO_ID_SEQ")
@SequenceGenerator(name = "GRUPO_ID_SEQ", sequenceName = "GRUPO_ID_SEQ", allocationSize = 1)
@Column(nullable=false)
private Long id_grupo;

...

Sequence GRUPO_ID_SEQ must exist in Oracle database.


Considering that MySql does not have sequences, if you try to use the code above with MySql you will get a runtime exception:


Caused by: org.hibernate.MappingException: Dialect does not support sequences
at org.hibernate.dialect.Dialect.getSequenceNextValString(Dialect.java:619)
at org.hibernate.id.SequenceGenerator.configure(SequenceGenerator.java:88)
at org.hibernate.id.SequenceHiLoGenerator.configure(SequenceHiLoGenerator.java:66)
at org.hibernate.id.IdentifierGeneratorFactory.create(IdentifierGeneratorFactory.java:127)

MySQL


...
@Entity
@Table(name = "grupo")
public class Grupo implements GenericModel {


@Id
@GeneratedValue
@Column(nullable=false)
private Long id_grupo;

...

The field id_grupo must be marked as auto increment in MySql.

I PASSED: 1Z0-894 Oracle Certified Expert Java Platform, Enterprise Edition 6 JavaServer Pages and Servlet Developer

I did the test on 09/12 and got through!

I was taking 70% in Mocks and hoped to pass with a score about 75% or 80%, but the test was harder than I expected and I passed with 68%.

As I had posted October, 1st, I based my studies on EPractize Labs Study Guide and Mock Exam, because I had already read Katy Sierra's book Head First J2EE1.4 sometimes on other occasions. Also I read in a Servlet 3.0 spec.

I really do not recommend EPractize Labs. The Study Guide is very superficial and Mock has many errors. In addition, the Mock is not consistent with the real exam. The real exam does not cover almost anything about custom tags and it covers much of the Servlet 3.0 spec, but Mock is upside down.

I believe Piotr's web page (http://piotrnowicki.com/2011/03/jee-6-scwcd-mock-questions/) saved me. It has 67 questions focused on the Servlet 3.0 specification. Enthuware Mock also helped a lot (http://enthuware.com/index.php/mock-exams/oracle-certified-expert/oce-jsp-servlet-mock-questions).

This Mock is cheaper and the quality of it is infinitely greater. The proof is that in addition to being 100% Test Pass Garantee, they return the money if you find three errors. Many exam questions were very similar to those on Mock.

My tip for anyone to prepare for this test is:

* Study the book Head First Servlets & JSP, Second Edition (JEE5): http://shop.oreilly.com/product/9780596516680.do?green=25526262438&cmp=af-mybuy-9780596516680.IP 
* Study the JSR-315 Spec Servlets 3.0: http://jcp.org/aboutJava/communityprocess/mrel/jsr315/index.html 
* Answering the questions of Piotr
* Make Enthuware Mock Exams

Following these steps with time, for sure you will do better than me :-)

Good studies!

1Z0-894 Oracle Certified Expert Java Platform, Enterprise Edition 6 JavaServer Pages and Servlet Developer

I've just started my preparation to Java for Web development certification, former SCWCD - Sun Certified Web Component Developer. 


In the newest version of this certification, and already in the Oracle world, its certification
code is 1Z0-894, and the name is Oracle Certified Expert, Java Platform, Enterprise Edition 6 Servlet and JavaServer Pages Developer.


You'll find more details about this test on the Oracle site: http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=41&p_exam_id=1Z0_894


EPractize Labs's website also has interesting information, including a simulation of how long you will need to prepare for the test: http://www.epractizelabs.com/blog2/?p=106


Because this test is relatively new, and I had already read the book Head First Servlets & JSP 1.4 JEE twice, I decided to prepare only with the Study Guide and Mock Exam EPractize Labs:
http://www.epractizelabs.com/certification/sun/oce-jsp-servlet-exam-6.html


This material is $ 40.5 dollars, but the EPractize Labs guarantees money back if you get the test failed.


This Study Guide has some Ctlr-C/Ctrl-V errors, several typos and content is very superficial, but I intend to fill the gap in Internet surveys.


I have also created a project in Eclipse to get going in practice what I learn in the Study Guide.


My goal is to make the test in the first week of December/2011, I hope it is enough time!


If you have tips or questions about this certification, share commenting on this post.


--
PS: The result: http://dotdebug.blogspot.com/2011/12/pass-1z0-894-oracle-certified-expert.html