Few Interesting SQL Queries

Few Interesting SQL Queries

Here I have listed few interesting and useful SQL queries. Have fun! If you know more, you can share also.

1] Check Database / instance & machine you are using.

SQL> select name from v$database;

SQL> select instance_name from v$instance;

SQL> select * from global_name; 

2] Retrieve a random number using sql statement.

SQL> select dbms_random.random from dual;

3] Copy a table from one instance to another instance.

SQL> copy from apps/apps@vip replace xxtest1 or create xxtest1 using select * from xxtest1;

4] Check if your oracle applications instance is setup for Multi-Org ? 

SQL> select multi_org_flag from fnd_product_groups;

5] Check what patches have been already loaded.

SQL> select * from AD_APPLIED_PATCHES;

6] Check the database name.

SQL> select value from v$system_parameter where name = ‘db_name’;

7] Check the data Dictionary (includes all views and tables of the database).

SQL> select * from dictionary;

8] Check the definition data from a specific table (in this case, all tables with string “XXX”)

SQL> select * from ALL_ALL_TABLES where upper(table_name) like ‘%XXX%’;

9] Check the tables from actual user.

SQL> select * from user_tables;

10] Check all the objects of the connected user.

SQL> select * from user_catalog;

11] Check the Oracle products installed and version number.

SQL> select * from product_component_version;

12] Check the roles and roles privileges.

SQL> select * from role_sys_privs;

13] Check the integrity rules.

SQL> select constraint_name, column_name from sys.all_cons_columns;

14] Check all the tablespaces.

SQL> select * from V$TABLESPACE;

15] Oracle SQL query to know the database size.

SQL> select sum(BYTES)/1024/1024 MB from DBA_EXTENTS;