Oracle Basic Command Notes

Recently contacted Oracle again for work reasons, so re-acquainted with Oracle's basic commands.

--Change Password
1. Run-type "cmd" carriage return
2. Type "Sqlplus/nolog" carriage return
3. Type "Conn/as sysdba" carriage return
4. Type "ALTER user Scott identified by Tiger;"

--Normal User login
Conn Scott/[email PROTECTED]:1521/ORCL

--Account Unlocked
Conn Sys/sys as sysdba;--logged in as a DBA
Alter user Scott account unlock;--and then unlock

--Account Authorization
Grant Create Session,create table,create view,create trigger, create Sequence,create procedure,unlimited tablespace, Create database link to Scott;

---clear table data
TRUNCATE TABLE name/delete from table name

---delete a table
DROP table Name

--Add another table space
ALTER tablespace USERS
Add DataFile ' D:\oracle\product\10.2.0\oradata\zjforcl\USERS02. DBF '
SIZE 32767M;

--Increase the present table space
ALTER DATABASE
DataFile ' D:\oracle\product\10.2.0\oradata\zjforcl\USERS02. DBF '
RESIZE 32767M;

--Table Space query usage
Select TBS "tablespace name", SUM (totalm) "Total size M", SUM (USEDM) "used space M", SUM (REMAINEDM) "Remaining space M", SUM (USEDM)/sum (Totalm) *100 "percent used" , SUM (REMAINEDM)/sum (Totalm) *100 "percent remaining"
From (SELECT b.file_id ID, b.tablespace_name TBS, b.file_name name, b.bytes/1024/1024 Totalm, B.bytes-sum (NVL (a.bytes,0 ))/1024/1024 usedm, sum (NVL (a.bytes,0)/1024/1024) remainedm, sum (NVL (a.bytes,0)/(b.bytes) *100), (a sum (NVL ( a.bytes,0)/(b.bytes) *100)) from Dba_free_space a,dba_data_files b WHERE a.file_id = b.file_id GROUP by b.tablespace_name , B.file_name,b.file_id,b.bytes ORDER by B.tablespace_name) GROUP by TBS

Oracle Basic Command Notes