一、creating a database
1、以系统管理员用户登录。
$ sqlplus / as sysdba
2、启动数据库导nomount状态。
SQL> startup nomount;
3、执行创建数据库语句。
SQL> create database invrep controlfile reuse
maxlogfiles 16
maxlogmembers 4
maxdatafiles 1024
maxinstances 1
maxloghistory 680
character set "UTF8"
logfile group 1
('/ora01/oradata/INVREP/redo01a.log',
'/ora01/oradata/INVREP/redo01b.log') size 200m reuse,
group 2
('/ora01/oradata/INVREP/redo02a.log',
'/ora01/oradata/INVREP/redo02b.log' ) size 200m reuse,
group 3
('/ora01/oradata/INVREP/redo03a.log',
'/ora01/oradata/INVREP/redo03b.log' ) size 200m reuse
datafile
'/ora01/oradata/INVREP/system01.dbf'
size 500m
reuse
undo tablespace undotbs1 datafile
'/ora01/oradata/INVREP/undotbs01.dbf'
size 800m
reuse
sysaux datafile
'/ora01/oradata/INVREP/sysaux01.dbf'
size 200m
reuse
default temporary tablespace temp tempfile
'/ora01/oradata/INVREP/temp01.dbf'
size 800m
reuse;
4、执行这两个语句,生成数据字典。
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
5、执行这个过程,创建通用同义词,供别的用户访问系统表。
SQL> connect system/manager
SQL> @?/sqlplus/admin/pupbld.sql
二、dropping a database
1、select name from v$database;
2、shutdown immediate;
3、startup mount exclusive restrict;
4、drop database;
三、查看连接信息
SQL>select name from v$database;
SQL> show user;
四、creating tablespace
create tablespace inv_data
datafile '/ora02/RMDB11/invdata01.dbf'
size 100m
extent management local
uniform size 256k
segment space management auto;
五、Dropping a Tablespace
alter tablespace inv_data offline;
drop tablespace inv_data including contents and datafiles;
六、调整表空间大小
1、select name, bytes from v$datafile;
2.1、alter database datafile '/ora01/oradata/INVREP/reg_data01.dbf' resize 1g;
2.2、alter tablespace reg_data add datafile '/ora01/oradata/INVREP/reg_data02.dbf' size 100m;
2.3、alter database datafile '/ora01/oradata/INVREP/reg_data02.dbf' autoextend on maxsize 1000m;
3、select name, bytes from v$tempfile;
3.1、alter database tempfile '/ora01/oradata/INVREP/temp01.dbf' resize 500m;
3.2、alter tablespace temp add tempfile '/ora01/oradata/INVREP/temp02.dbf' size 5000m;
七、限制每个用户访问的数据资源数目。
1、create profile user_profile_limit
limit
sessions_per_user 20
cpu_per_session 240000
logical_reads_per_session 1000000
connect_time 480
idle_time 120;
2、 alter user heera profile user_profile_limit;
3、select name, value from v$parameter where name='resource_limit';
八、创建权限组。(role)
create role jr_dba;
grant select any table to jr_dba;
grant create any table to jr_dba;
grant create any view to jr_dba;
grant create synonym to jr_dba;
grant create database link to jr_dba;
grant jr_dba to lellison;
grant jr_dba to cphillips;
select grantee, granted_role from dba_role_privs order by 1;
select * from user_role_privs;
revoke create database link from jr_dba;
revoke jr_dba from lellison;
九、创建用户
create user heera identified by chaya
default tablespace users
temporary tablespace temp;
grant create session to heera;
grant create table to heera;
alter user heera quota unlimited on users;
十、用户管理
drop user heera cascade;
alter user heera account lock;
select username, lock_date from dba_users;
alter user heera account unlock;
十一、修改用户密码
alter user heera identified by foobar;
1、以系统管理员用户登录。
$ sqlplus / as sysdba
2、启动数据库导nomount状态。
SQL> startup nomount;
3、执行创建数据库语句。
SQL> create database invrep controlfile reuse
maxlogfiles 16
maxlogmembers 4
maxdatafiles 1024
maxinstances 1
maxloghistory 680
character set "UTF8"
logfile group 1
('/ora01/oradata/INVREP/redo01a.log',
'/ora01/oradata/INVREP/redo01b.log') size 200m reuse,
group 2
('/ora01/oradata/INVREP/redo02a.log',
'/ora01/oradata/INVREP/redo02b.log' ) size 200m reuse,
group 3
('/ora01/oradata/INVREP/redo03a.log',
'/ora01/oradata/INVREP/redo03b.log' ) size 200m reuse
datafile
'/ora01/oradata/INVREP/system01.dbf'
size 500m
reuse
undo tablespace undotbs1 datafile
'/ora01/oradata/INVREP/undotbs01.dbf'
size 800m
reuse
sysaux datafile
'/ora01/oradata/INVREP/sysaux01.dbf'
size 200m
reuse
default temporary tablespace temp tempfile
'/ora01/oradata/INVREP/temp01.dbf'
size 800m
reuse;
4、执行这两个语句,生成数据字典。
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
5、执行这个过程,创建通用同义词,供别的用户访问系统表。
SQL> connect system/manager
SQL> @?/sqlplus/admin/pupbld.sql
二、dropping a database
1、select name from v$database;
2、shutdown immediate;
3、startup mount exclusive restrict;
4、drop database;
三、查看连接信息
SQL>select name from v$database;
SQL> show user;
四、creating tablespace
create tablespace inv_data
datafile '/ora02/RMDB11/invdata01.dbf'
size 100m
extent management local
uniform size 256k
segment space management auto;
五、Dropping a Tablespace
alter tablespace inv_data offline;
drop tablespace inv_data including contents and datafiles;
六、调整表空间大小
1、select name, bytes from v$datafile;
2.1、alter database datafile '/ora01/oradata/INVREP/reg_data01.dbf' resize 1g;
2.2、alter tablespace reg_data add datafile '/ora01/oradata/INVREP/reg_data02.dbf' size 100m;
2.3、alter database datafile '/ora01/oradata/INVREP/reg_data02.dbf' autoextend on maxsize 1000m;
3、select name, bytes from v$tempfile;
3.1、alter database tempfile '/ora01/oradata/INVREP/temp01.dbf' resize 500m;
3.2、alter tablespace temp add tempfile '/ora01/oradata/INVREP/temp02.dbf' size 5000m;
七、限制每个用户访问的数据资源数目。
1、create profile user_profile_limit
limit
sessions_per_user 20
cpu_per_session 240000
logical_reads_per_session 1000000
connect_time 480
idle_time 120;
2、 alter user heera profile user_profile_limit;
3、select name, value from v$parameter where name='resource_limit';
八、创建权限组。(role)
create role jr_dba;
grant select any table to jr_dba;
grant create any table to jr_dba;
grant create any view to jr_dba;
grant create synonym to jr_dba;
grant create database link to jr_dba;
grant jr_dba to lellison;
grant jr_dba to cphillips;
select grantee, granted_role from dba_role_privs order by 1;
select * from user_role_privs;
revoke create database link from jr_dba;
revoke jr_dba from lellison;
九、创建用户
create user heera identified by chaya
default tablespace users
temporary tablespace temp;
grant create session to heera;
grant create table to heera;
alter user heera quota unlimited on users;
十、用户管理
drop user heera cascade;
alter user heera account lock;
select username, lock_date from dba_users;
alter user heera account unlock;
十一、修改用户密码
alter user heera identified by foobar;