Category ORACLE

My first Oracle cloud application using oracle cloud trail account

I have tested Oracle cloud application using oracle cloud trail account.Steps are given below 1.go to¬†https://cloud.oracle.com/, login with your OTN account and request a trial period.need credit card for verification, but it will not get charged. 2.Download Oracle SQL Developer from below link http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html 3.To start SQL Developer, run sqldeveloper.exe. 4.Log in to APEX. Once […]

Some Tips on Oracle Table space and Users

–List all users/schema SELECT DISTINCT USERNAME FROM DBA_USERS; SELECT * FROM DBA_USERS; –List ONLY your tables SELECT * FROM CAT; SELECT * FROM TAB; –List the Oracle Tablespaces SELECT * from v$TABLESPACE; –See your TABLESPACE SELECT * FROM ALL_TABLES WHERE TABLE_NAME = ‘BL_SESSION_EVENT_HIST’; SELECT TABLE_NAME, TABLESPACE_NAME, STATUS FROM USER_TABLES; –find table space of all tables […]

Configure MS SQL SERVER,MY SQL,MS ACCESS to run from Oracle SQL Developer

We can execute MS SQL SERVER,MY SQL,MS ACCESS from Oracle SQL Developer.Steps are given below 1.download Oracle SQL Developer from below link http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html 2.Oracle connection We can connect oracle by Host Name,Port and SID or by TNS details.                     3.MY SQL Server connection a.need to download […]

Random numbers using oracle query

create 10 random numbers between 1 and 99 using below query SELECT R FROM (SELECT R FROM (SELECT ROWNUM R FROM ALL_OBJECTS WHERE ROWNUM < 100) ORDER BY DBMS_RANDOM.VALUE) WHERE ROWNUM <= 10;

How to stop and run Oracle MVIEWS

Run the select query to run all jobs select * from user_jobs; Need to get the JOB job column and execute: begin dbms_job.broken(50,TRUE);–change the correct job number in place of 50 commit; end; This will stop MVIEW to auto refresh. to refresh the mviews again we have to use below code begin dbms_job.run(50);–change the correct […]

How to select the first row of each group in Oracle?

I have a table like this: ID | VAL | DATE ———————- 1 | 10 | 25/12/2013 1 | 2000 | 25/12/2012 2 | 5 | 25/12/2012 2 | 3 | 25/12/2013 I want to make a SELECT that will return just the first row for each ID, ordering by Date. Sample output: ID | […]

Row number in Oracle…..

—Before 8i SELECT ROWNUM FROM ALL_OBJECTS WHERE ROWNUM <= &N; –SQL 8i SELECT ROWNUM FROM (SELECT 1 FROM DUAL GROUP BY CUBE(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)) WHERE ROWNUM <= &N; –SQL 9i SELECT * FROM (SELECT ROWNUM FROM DUAL CONNECT BY LEVEL <= &N); –SQL 10g SELECT ROWNUM FROM DUAL […]