4. Create oracle username tablespaces, tables, insert data, and user management tables in windows and Linux

Last Update:2014-10-15 Source: Internet

Author: User

Tags dname

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

4. Create oracle username tablespaces, tables, insert data, and user management tables in windows and Linux

Log on to the super administrator and run the following command:

Create databases, tablespaces, users, and insert data in Windows

-- 01 create a tablespace

-- Note that the tablespace path is adjusted according to the actual installation environment.

Create tablespace ts_myscott

LOGGING

DATAFILE 'f:/app/to-to/oradata/orcl/ts_myscott.dbf' SIZE 10 M

Extent management local;

Create tablespace ts_myscott2

LOGGING

DATAFILE 'f:/app/to-to/oradata/orcl/ts_myscott2.dbf 'SIZE 20 M

Extent management local;

Alter database datafile 'f:/app/to-to/oradata/orcl // ts_myscott.dbf' autoextend on next 10 m maxsize unlimited;

Alter database datafile 'f:/app/to-to/oradata/orcl/ts_myscott2.dbf 'autoextend on next 20 m maxsize unlimited;

Commit;

-- 02 create a solution (create a user)

CREATE USER MYSCOTT PROFILE DEFAULT

IDENTIFIED BY MYSCOTT DEFAULT TABLESPACE USERS

Account unlock;

-- Resource and logon Permissions

Grant resource to myscott;

GRANT create session to myscott;

-- 03 create a table

-- Create a department table and assign values

Create table myscott. DEPT (

Deptno number (2) primary key,

DNAME VARCHAR2 (14) not null,

LOC VARCHAR2 (13)

) TABLESPACE ts_myscott;

Insert into myscott. dept VALUES (10, 'accounting', 'New YORK ');

Insert into myscott. dept VALUES (20, 'Research ', 'Dallas ');

Insert into myscott. dept VALUES (30, 'sales', 'Chicago ');

Insert into myscott. dept VALUES (40, 'operations', 'boston ');

Commit;

-- Create an employee table and assign values

Create table myscott. EMP (

Empno number (4) constraint emp_empno_pk primary key,

ENAME VARCHAR2 (10) constraint emp_ename_notnull not null,

JOB VARCHAR2 (9 ),

Mgr number (4 ),

Hiredate date,

Sal number (7,2) constraint emp_sal_check check (SAL> 0 ),

Comm number (7, 2 ),

Deptno number (2) constraint emp_deptno_fk references MYSCOTT. dept (deptno)

) TABLESPACE ts_myscott;

-- Create an index on the new tablespace

Create index myscott. IX_CAtbAuditOperInfo_OT on myscott. EMP (ENAME) TABLESPACE ts_myscott2;

Insert into myscott. emp values (7369, 'Smith ', 'cler', 7902, '17-August-80', 800, NULL, 20 );

Insert into myscott. emp values (7499, 'allen', 'salesman', 7698, '20-January 1, February-81 ', 1600,300, 30 );

Insert into myscott. emp values (7521, 'ward ', 'salesman', 7698, '22-January 1, February-81 ', 1250,500, 30 );

Insert into myscott. emp values (7566, 'Jones ', 'manager', 7839, '02-January 1, April-81', 2975, NULL, 20 );

Insert into myscott. emp values (7654, 'martin ', 'salesman', 7698, '28-January 1, September-81', 1250,140 0, 30 );

Insert into myscott. emp values (7698, 'bucke', 'manager', 7839, '01-August 81 ', 2850, NULL, 30 );

Insert into myscott. emp values (7782, 'clark', 'manager', 7839, '09-July 81-81 ', 2450, NULL, 10 );

Insert into myscott. emp values (7788, 'Scott ', 'analyst', 7566, '19-January 1, April-87', 3000, NULL, 20 );

Insert into myscott. emp values (7839, 'King', 'President ', NULL, '17-January 1, November-81', 5000, NULL, 10 );

Insert into myscott. emp values (7844, 'turner ', 'salesman', 7698, '08-January 1, September-81', 1500, 0, 30 );

Insert into myscott. emp values (7876, 'adams', 'lock', 7788, '23-August-87 ', 1100, NULL, 20 );

Insert into myscott. emp values (7900, 'James ', 'cler', 7698, '03-July 81', 950, NULL, 30 );

Insert into myscott. emp values (7902, 'Ford ', 'analyst', 7566, '03-January 1, December-81', 3000, NULL, 20 );

Insert into myscott. emp values (7934, 'miller ', 'cler', 7782, '23-August-82', 1300, NULL, 10 );

Commit;

-- Create a wage level table and assign values

Create table myscott. SALGRADE (

Grade number,

Losal number,

HISAL NUMBER

) TABLESPACE ts_myscott;

Insert into myscott. salgrade values (1,700,120 0 );

Insert into myscott. salgrade values (2, 1201,140 0 );

Insert into myscott. salgrade values (3, 1401,200 0 );

Insert into myscott. salgrade values (4, 2001,300 0 );

Insert into myscott. salgrade values (5, 3001,999 9 );

Commit;

-- Create a bonus table

Create table myscott. BONUS (

ENAME VARCHAR2 (10 ),

JOB VARCHAR2 (9 ),

Sal number,

COMM NUMBER

) TABLESPACE ts_myscott;

------ Stop -----

-- 04 create a new user scheme to access the database through MYSCOTTUSER1. Permission configuration demonstration

Create user "MYSCOTTUSER1" PROFILE "DEFAULT" identified by "123456" default tablespace "USERS" account unlock;

GRANT "CONNECT" TO "MYSCOTTUSER1 ";

Grant select any table to "MYSCOTTUSER1 ";

Grant delete on myscott. dept to "MYSCOTTUSER1 ";

Grant insert on myscott. dept to "MYSCOTTUSER1 ";

Grant update on myscott. dept to "MYSCOTTUSER1 ";

Grant delete on myscott. emp to "MYSCOTTUSER1 ";

Grant insert on myscott. emp to "MYSCOTTUSER1 ";

Grant update on myscott. emp to "MYSCOTTUSER1 ";

Commit;

Create databases, tablespaces, users, and insert data in Linux

-- 01 create a tablespace

-- Note that the tablespace path is adjusted according to the actual installation environment.

Create tablespace ts_myscott

LOGGING

DATAFILE '/home/oracle_11/app/oradata/orcl/ts_myscott.dbf' SIZE 10 M

Extent management local;

Create tablespace ts_myscott2

LOGGING

DATAFILE '/home/oracle_11/app/oradata/orcl/ts_myscott2.dbf' SIZE 20 M

Extent management local;

Alter database datafile '/home/oracle_11/app/oradata/orcl/ts_myscott.dbf' autoextend on next 10 m maxsize unlimited;

Alter database datafile '/home/oracle_11/app/oradata/orcl/ts_myscott2.dbf' autoextend on next 20 m maxsize unlimited;