数据库管理常用sql语句

 一、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;