Common SQL commands for Oracle Management (coming soon ......)

Common Oracle management SQL commands

Database Version	SQL> select * from V $ version;Banner----------------------------------------------------------------Oracle9i Enterprise Edition Release 9.2.0.1.0-ProductionPL/SQL release 9.2.0.1.0-ProductionCore 9.2.0.1.0 ProductionTNS for 32-bit windows: Version 9.2.0.1.0-ProductionNlsrtl version 9.2.0.1.0-Production
Current User	SQL> show user;UserIs "sys"
Empty table management SQL
Create a tablespace	SQL> Create tablespace test datafile 'd:/test01.dbf' size 10 m extent management local; the tablespace has been created.
View data file size	SQL> select name, create_bytes/1024/1024 | 'M' file_size from V $ datafile; Name file_size Bytes ----------------------------------------------------------------------------------- D:/Oracle/oradata/ora92/system01.dbf 250md:/Oracle/oradata/ora92/undotbs01.dbf 50md:/Oracle/oradata/ora92/indx01.dbf 25 m
Delete a tablespace	SQL> drop tablespace test; the tablespace is discarded.
Delete tablespaces and data files	SQL> drop tablespace test including contents and datafiles; The tablespace is discarded.
Change Data File Size	SQL> alter database datafile 'd:/Oracle/oradata/ora92/sp01.dbf' resize 130 m; the database has been changed.
View All data files	SQL> select name from V $ datafile; Name Bytes -------------------------------------------------------------------------------------- D:/Oracle/oradata/ora92/system01.dbf D:/Oracle/oradata/ora92/undotbs01.dbf D:/Oracle/oradata/ora92/indx01.dbf D:/Oracle/oradata/ora92/tools01.dbf D:/Oracle/oradata/ora92/users01.dbf D:/Oracle/oradata/ora92/sp01.dbf
View All tablespaces	SQL> select name from V $ tablespace; Name ------------------------------ System Undotbs1 Temp Indx Tools Users SP
Tablespace read-only	SQL> alter tablespace SP read only; the tablespace has been changed.
Tablespaces can be read and written.	SQL> alter tablespace SP read write; The tablespace has been changed.
View the tablespace read/write status	SQL> select status from dba_tablespaces where tablespace_name = 'SP '; Status --------- Read Only
Tablespace online	SQL> alter tablespace SP online; The tablespace has been changed.
Tablespace offline	SQL> alter tablespace SP offline; The tablespace has been changed.
Online table space status query	SQL> select status, tablespace_name from dba_tablespaces; Status tablespace_name --------------------------------------- Online System Online undotbs1 Online temp Online indx Online tools Online users Online sp
Move data files	SQL> alter tablespace test rename datafile 'd:/Oracle/oradata/ora92/test01.dbf 'to 'd:/test01.dbf'; The tablespace has been changed.
Add data files to the tablespace	SQL> alter tablespace test add datafile 'd:/Oracle/oradata/ora92/test02.dbf 'size 10 m; the tablespace has been changed.
Create temporary tablespace	SQL> create temporary tablespace test tempfile 'd:/test01.dbf 'size 10 m extent management local; the tablespace has been created.
Create an undo tablespace	SQL> Create undo tablespace undo1 datafile 'd:/undo1.dbf' size 10 m; the tablespace has been created.
Configuration File Management SQL
Create a spfile file using the pfile File	SQL> Create spfile from pfile;
Instance Management
Start Database	The SQL> startuporacle routine has been started. Total system global area 85006980 bytesfixed size 453252 bytesvariable size 58720256 bytesdatabase buffers 25165824 bytesredo buffers 667648 bytes database loaded. The database has been opened.
Start the database to nomount	SQL> startup nomount; the Oracle routine has been started. Total system global area 85006980 bytesfixed size 453252 bytesvariable size 58720256 bytesdatabase buffers 25165824 bytesredo buffers 667648 bytes
Start the database from nomount to mount	SQL> alter database Mount; the database has been changed.
Start the database from Mount to open	SQL> alter database open; the database has been changed.
Start the database with the specified parameter file	SQL> startup pfile = D:/Oracle/ora92/initdb01.oraoracle routine has been started. Total system global area 85006980 bytesfixed size 453252 bytesvariable size 58720256 bytesdatabase buffers 25165824 bytesredo buffers 667648 bytes database loaded. The database has been opened.
Close Database	SQL> shutdown immediate; the database has been closed. The database has been detached. The Oracle routine has been disabled.
Table Management
Create a table	SQL> Create Table table_a (field_a char) tablespace users; the table has been created.
Add Field	SQL> alter table table_a add field_ B number (2); the table has been changed.
Delete Field	SQL> alter table table_a drop column field_ B;
Data Management
User Management
Add users	SQL> create user a identified by a; the user has created
Lock user	SQL> alter user a account lock; the user has changed.
Unlock a user	SQL> alter user a account unlock; the user has changed.
Change Password	SQL> alter user a identified by B; the user has changed.
User authorization	SQL> grant resource to a; authorization successful.
Remove authorization	SQL> revoke resource from a; UNDO successful.
Delete a user	SQL> drop user a; user discarded

Query the locked table: Select O. object_name,
L. session_id,
S. Serial #,
S. program,
S. username,
S. Command,
S. machine,
S. lockwait
From v $ locked_object L, all_objects o, V $ session s
Where o. object_id = L. object_id
And S. Sid = L. session_id;