Learn oracle-environment configuration from zero (i)

I. Preparation before the start

1. Launch Oracle

Start the Listening service: Oracleorahome90tnslistener

Start Instance service: Oracleserviceorac

2.Oracle System User

Oracle three major system users

By default: System/welcome

Super User: Sys/welcome permissions maximum

Average User: Scott/tiger

3. How to Sign in Oracle

The Sqlplus operation under Windows window is running->sqlplusw.exe

DOS Sqlplus operation-> Sqlplus.exe

Log on to Oracle but do not connect to the database run->sqlplusw/nolog

Connect username/Password @ host connection string connect to specified user

4.Oracle Common operation

show user 查看当前用户　　 select * from tab where tabtype='TABLE'　 查看当前用户下 的表
desc dept　　　　　　　　　　　　 察看表dept的结构
quit/exit　　　　　　　　　　　　 退出
clear screen　　　　　　　　　 清除屏幕
set linesize 200　　　　　 设置一行显示200个字符
set pagesize 20　　　　　　 设置每页显示20行
spool 文件名 (spool c:abc.txt) 作日志文件命令　　　　　　　　　spool off
alter session set nls_date_format = 'yyyy-mm-dd'; 改日期的默认格式
conn /as sysdba　　　　 select * from v$version;查看当前Oracle的版本
conn system/welcome select * from v$tablespace; 查看表空间

5. Relationship of table space user table Three

One user can use one or more tablespaces, and one table space can be used by multiple users

The user and tablespace do not have a subordinate relationship.

Tablespace is a logical concept for managing data storage, which is only related to data files, and data files are physical.

A table space can contain multiple data files, and a data file can only be subordinate to one table space

Second, the general operation

1.DDL statements (data definition Languages) data Define Language

The statement at the beginning of the Create ALTER drop truncate TRUNCATE TABLE < name >

Feature:<1> set up and modify data Objects

<2> establish and modify directly into the library, directly effective

Creating a tablespace: Create tablespace Product datafile ' D:\test\aa. DBF ' Size 100M

Remove tablespace: Drop tablespace Product

Creating User: Create user Zhangsan identified by "Welcome" default Tablespace Product

Modify User: Alter user Zhangsan identified by "Hello"

Permissions granted to a user: Grant connect to Zhangsan;

Revoke user connection permissions: Revoke connect from Zhangsan;

To grant users permission to create objects in a table space

grant unlimited tablespace to zhangsan;
grant resource　 to zhangsan;

System privileges: Unlimited tablespace indicates that a user can create an object in any tablespace, and that this permission can only be granted to a user and not to a role

Resource role: After giving the user resouce permissions, the user has the ability to create tables on all table spaces

Grant user Zhangsan the right to add, delete, check and change the EMP form

grant　 select　 on scott.empa to y2;
　　　　　　　　　 delete
　　　　　　　　　 update
　　　　　　　　　 insert
　　　　　　　　　 all　 （=select,delete,update,insert)

With GRANT option grants user permissions, users who accept permissions can grant this permission to other users.

(1). Build the table and specify the table space

　　示例： Create table tab1
　　　　　　　　　 (
　　　　　　　　　　　　 no number(4),
　　　　　　　　　　　　 name varchar2(10)
　　　　　　　　　 )tablespace Product;