Handy ORACLE commands

Some useful commands(source: http://www.vttoth.com/oracle.htm , http://www.orafaq.com/wiki/Oracle_database_FAQ, and the internet ;-)

Oracle version:	select * from v$version;
To start a session as sysdba:	sqlplus sys@tnsname as sysdba;
To start a sysdba session:	sqlplus /as sysdba
To generate an AWR report:	SQL>@$ORACLE_HOME/rdbms/admin/awrrpt.sql
Show current database:	SELECT * FROM global_name;
Show current database:	SELECT name FROM v$database;
Show spfile location:	show parameter spfile;
Check database block size:	select value from v$parameter where name = 'db_block_size';
Database size:	select sum(bytes)/1024/1024 "Meg" from v$datafile;
Redo log size:	select l.bytes/1024/1024 "Meg",g.member from v$log l,v$logfile g where l.group# = g.group#;
Check whether or not the database is in archivelog mode:	select log_mode from v$database;
Temp file size total:	select nvl(sum(bytes),0)/1024/1024 "Meg" from dba_temp_files;
List datafiles:	select name from v$datafile;
List control files:	select name from v$controlfile;
List redo log files:	select member from v$logfile;
Tablespaces to which the datafiles belong:	select file_name, tablespace_name from dba_data_files;
Tablespace status, and it's block size:	select tablespace_name, block_size, status from dba_tablespaces;
Table info:	select owner, table_name, tablespace_name, blocks,NUM_ROWS from all_tables where table_name='your_table_name';
To list all tables in current schema:	SELECT table_name FROM user_tables;
or, all tables current user has access to:	SELECT table_name FROM all_tables;
To list all schemas:	SELECT username FROM all_users ORDER BY username;
To turn pause on:	SET PAUSE ON;
To list top n rows of a table in order:	SELECT * FROM (SELECT * FROM t ORDER BY c) WHERE ROWNUM <= n;
Use database:	CONNECT schema/password@tnsname;
Show who I am:	SHOW USER;
Describe table:	DESC tablename;
Set display rows:	SET PAGESIZE 66;
Read field constraints:	SELECT constraint_name,search_condition FROM user_constraints WHERE table_name='tablename';
Who owns a table and what tablespace it is in:	select owner, table_name, tablespace_name from dba_tables where table_name='CUSTOMERS';
Copy table from foreign host to here:	COPY FROM user@tnsname CREATE tablename USING SELECT * FROM tablename;
Start SQLPLUS without login:	SQLPLUS /NOLOG
Change a user's password:	ALTER USER user IDENTIFIED BY password;
Unlock an account	ALTER USER user ACCOUNT UNLOCK;
ASM ALLOCATION UNIT SIZE	select name, allocation_unit_size from v$asm_diskgroup;
ASM Disk Group Size	SELECT name, type, total_mb, free_mb,usable_file_mb FROM V$ASM_DISKGROUP;