Some Oracle SQL should known by Developer - 1

========================================================================

	---- To get Sample n rows from the table
	Select * from table where rownum<=n ;
	---- Oracle SQL query over the view that shows database state:
	select * from v$instance;
	---- Oracle SQL query that shows if database is opened
	select status from v$instance;
	---- Oracle SQL query over the view that show Oracle database general parameters
	select * from v$system_parameter;
	---- Oracle SQL query to know Oracle version
	select value from v$system_parameter where name = 'compatible';
	---- Oracle SQL query to show the database name.
	select value from v$system_parameter where name = 'db_name';
	---- Oracle SQL query that show the opened connections group by the program that opens the connection.
	-- need admin access
	select program Aplicacion, count(program) Numero_Sesiones
	from v$session
	group by program
	order by Numero_Sesiones desc ;
	---- Oracle SQL query that shows definition data from a specific table
	-- (in this case, all tables with string "XXX")
	select * from ALL_ALL_TABLES where upper(table_name) like '%XXX%' ;
	---- Oracle SQL query to know tables from actual user
	select * from user_tables ;
	---- Oracle SQL query to know all the objects of the connected user
	select * from user_catalog ;
	---- Oracle SQL query to know all the tablespaces:
	select * from V$TABLESPACE ;
	---- Oracle SQL query to know the database size
	select sum(BYTES)/1024/1024 MB from DBA_EXTENTS ;
	---- Oracle SQL query to calculate the size of the database data files
	select sum(bytes)/1024/1024 MB from dba_data_files ;
	---- Oracle SQL query to know the memory used by a column in a table
	select sum(vsize('COLUMNNAME'))/1024/1024 MB from 'TABLENAME' ;
	---- Oracle SQL query to calculate memory used by a user
	SELECT owner, SUM(BYTES)/1024/1024 MB FROM DBA_EXTENTS
	group by owner ;
	---- Oracle SQL query to calculate size from the different segments
	-- (tables, indexes, undo, rollback, cluster, ...)
	SELECT SEGMENT_TYPE, SUM(BYTES)/1024/1024 MB FROM DBA_EXTENTS
	group by SEGMENT_TYPE ;
	---- Show Row Counts For All Tables That Have ANALYZE On
	select owner table_name, num_rows from dba_tables where num_rows > 0 ;
	---- Select All Users Active In The System
	select sid, serial#,user#, Username, machine, program, server, status, command, type
	from v$session order by username ;
	---- Show What A Current User Is Doing
	select sid, serial#, status, server from v$session where username = 'BROWNBH';
	---- Create Count For All Tables
	select 'Select count(*) from ' ||owner|| '.' ||table_name|| ';'
	from dba_all_tables order by owner, table_name ;
	---- Sum Space By Owner
	select owner, sum(blocks) Totalblocks, sum(bytes)TotalBytes
	from DBA_SEGMENTS group by owner ;
	---- Sum Space by Tablespace
	select tablespace_name, sum(blocks) Totalblocks, sum(bytes)TotalBytes
	from DBA_SEGMENTS group by tablespace_name ;
	---- Display All The Oracle Tables In The Database
	select owner, table_name, table_type, tablespace_name
	from dba_all_tables
	where owner not in ('SYSTEM','DBSNMP','ORDSYS','OUTLN','SYS')
	order by owner, table_name, tablespace_name;
	---- Display All The Oracle Indexes In The Database
	Select owner, index_name, table_type, tablespace_name
	From dba_indexes
	where owner not in ('SYSTEM','DBSNMP','ORDSYS','OUTLN','SYS')
	order by owner, index_name, tablespace_name;

view raw BasicAdminSqlOracle.sql hosted with ❤ by GitHub

Like the Facebook Page & join Group
https://www.facebook.com/DataStage4you
https://www.facebook.com/groups/DataStage4you