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