Oracle Common Commands

start and Close commands

Open Server

net start Oracleservicebinbo

Open Listener

Lsnrctl start

Start the database

Startup

Close the database

Shutdown

Turn off monitoring

Lsnrctl stop

Create a database

Dbca

Create a listener

Netca

CREATE TABLE Space

Create tablespace tablespace-name datafile '/path/to/tablespace/fiel ' size 2g autoextend on;

Alter tablespace tablespace-name add datafile '/path/to/tablespace/fiel2 ' size 2g autoextend on;

Create user

Create user USERNAME identified by PASSWORD default Tablespace tablespace-name;

Create a Directory

Create directory DIRNAME as '/path/to/dir ';

Authorized

Grant read,write on directory DIRNAME to USERNAME;

View instance Name

Select instance_name from V$instance;

View Table Spaces

Select Tablespace_name from Dba_tablespaces;

To view the user's default table space

Select Username,default_tablespace from Dba_users

where username= ' USERNAME ';

View Users and Status

Select Username,account_status from Dba_users;

View all table names

Select table_name from All_tables;

View Database

SELECT * from V$database;

View database files

SELECT * from V$dbfile;

View current number of connections

Select COUNT (*) from v$bgprocess;

View and modify the maximum number of connections and the maximum session (requires SYSDBA)

View Processes

Show parameter processes

View Session

Show parameter Sessions

Modify Processes

Alter system set PROCESSES=300 Scope=spfile;

Modify Sessions

Alter system set sessions=335 Scope=spfile;

viewing connection Session messages

Select Sid,serial#,username,program,machine,status from V$session;

View the number of SQL connections used

SELECT B.machine, B.program, COUNT (*)

From V$process A, v$session b

WHERE a.addr = b.paddr and b.username are not NULL

GROUP by B.machine, B.program

ORDER by COUNT (*) DESC;

To view connected users

Select Osuser,a.username,cpu_time/executions/1000000| | ' S ', Sql_fulltext,machine

From V$session A,v$sqlarea b

where a.sql_address = b.address

ORDER BY cpu_time/executions Desc;

Not to be continued

Oracle Common Commands