Oracle 常用语法函数收集

 

[Oracle初始口令]   

  　　internal  : 【oracle】   

  　　sys          : 【change_on_install】    

      sysman     : 【oem_temp】

  　　system   : 【manager】   

  　　scott      : 【tiger】  

---

--查询”xiaoma“ 用户下面所有的表（除系统表外）

SELECT  *  FROM   ALL_TABLES  WHERE  TABLE_NAME NOT LIKE ‘%$%’AND OWNER = 'XIAOMA';  

---

--启动监听，Oracle服务

lsnrctl start

net start OracleServiceOrce

oradim -STARTUP -SID myoracle -STARTTYPE srvc,inst  

oradim -SHUTDOWN -SID myoracle -SHUTTYPE srvc,inst

--修改Oracle监听

D:\oracle\product\10.1.0\Db_1\NETWORK\ADMIN\listener.ora

要显示监听器的运行状态：$lsnrctl status;
要停止监听器：$lsnrctl stop。

          

---

----------------用户密码更改

1. sqlplus /nolog  
2. conn /as sysdba
3. alter user scott account unlock;
4. select username,password from dba_users;
5. alter user scott identified by  或：alter user scott identified by values

---

----SQLPlus 清屏

• Oracle：clear screen; host cls
• Mysql ：clear scr
• window ：cls

---设置输出格式

• set linesize 800
• set pagesize 200

          ---进入SQLPLus

1. 运行中：sqlplusw /nolog 进入SQLPLUS
2. conn /as sysdba
3. select instance_name from v$instance;

---

-----------------------创建用户，设置权限--------------------------

--步骤

1. create user DB_USER identified by DB_PASSWORD；   //用户名：DB_USER ；密码：DB_PASSWORD   
2. grant create session to DB_USER  ；    //授权连接权限
3. grant resource to DB_USER；               //授权访问权限
4. grant create table to DB_USER;            //授权创建表的权限
5. select * from user_tab_privs;                 //本用户读取其他用户对象的权限:
6. select * from user_sys_privs;                 //本用户所拥有的系统权限:

--其他授权

• grant dba to DB_USER;                        //授予DBA权限
• grant unlimited tablespace to DB_USER;     //授予不限制的表空间
• grant select any table to DB_USER;              //授予查询任何表
• grant select any dictionary to DB_USER;     //授予 查询 任何字典

---

---

创建数据库实例

在Windows中创建数据库实例的命令为Oradim.exe，是一个可执行文件，可以在操作系统符号下直接运行。直接输入oradim显示此命令的帮助。

c:\>Oradim

下面对Oradim命令的参数进行一个说明

-------------------------------

-NEW 表示新建一个实例

-EDIT 表示修改一个实例

-DELETE 表示删除一个实例

-SID sid 指定要启动的实例名称

-SRVC service 指定要启动的服务名称

-INTPWD password 以Internal方式连接数据库时的口令字

-MAXUSERS count 该实例可以连接的最大用户数

-USRPWD password 指定内部用户的口令，如是作为Windows管理登录，不用此参数

-PFILE pfile 该实例所使用的参数文件名及路径

-STARTTYPE srvc|inst|srvc,inst 启动选项（srvc:只启动服务，inst:启动实例，服务必须先启动，srvc,inst:服务和实例同时启动）

-SHUTTYPE srvc|linst|srvc,inst 关闭选项（srvc:只关闭服务，实例必须已关闭，inst:只关闭实例，srvc,inst:服务和实例同时关闭）

-STARTMODE a|m 创建实例所使用的模式（a:自动，即windows启动时自动启动 m:手动）

-SHUTMODE a|i|m 关闭实例时所使用的模式（a:abort异常方式,i:immediate立即方式,n:normal正常方式）

具体的命令操作演示

-----------------------

例：创建一个数据库实例

c:\>oradim -NEW -SID myoracle -STARTMODE m -PFILE "d:\fangys\initmyoracle.ora"

或

c:\>oradim -NEW -SRVC OracleServicemyoracle -STARTMODE m -PFILE "d:\fangys\initmyoracle.ora"

例：修改一个数据实例

c:\>oradim -EDIT -SID myoracle -STARTMODE a

或

c:\>oradim -EDIT -SRVC OracleServicemyoracle -STARTMODE a

例：删除一个实例

c:\>oradim -DELETE -SID myoracle

或

c:\>oradim -DELETE -SRVC Oracleservicemyoracle

例：启动服务与实例

c:\>oradim -STARTUP -SID myoracle -STARTTYPE srvc,inst

只启动服务

c:\>oradim -STARTUP -SID myoracle -STARTTYPE srvc

启动实例：

c:\>oradim -STARTUP -SID myoracle -STARTTYPE inst

例：关闭服务与实例

c:\>oradim -SHUTDOWN -SID myoracle

c:\>oradim -SHUTDOWN -SID myoracle -SHUTTYPE srvc,inst

启动实例并创建数据库

------------------------

c:\>oradim -NEW -SID myoracle -INTPWD syspass -STARTMODE a -PFILE d:\fangys\initmyoracle.ora

c:\>set ORACLE_SID=myoracle

c:\>sqlplus sys/syspass as sysdba

sql>startup -pfile=d:\fangys\initmyoracle.ora nomount

sql>CREATE DATABASE myoracle

logfile group...

...

创建数据字典

------------------

sql>@d:\oracle\product\10.1.0\db_1\rdbms\admin\catalog.sql;

sql>@d:\oracle\product\10.1.0\db_1\rdbms\admin\catproc.sql;

sql>@d:\oracle\product\10.1.0\db_1\rdbms\admin\catrep.sql;

sql>@d:\oracle\product\10.1.0\db_1\javavm\install\initjvm.sql;

sql>@d:\oracle\product\10.1.0\db_1\sqlplus\admin\ppbld.sql;

---

数据库启动参数

1.NOMOUNT:只启动实例，不装载数据库

sql>startup nomount

2.MOUNT:启动实例，并装载数据，但不打开数据库

sql>startup mount

3.READ ONLY:将数据库置为只读

sql>startup mount

sql>alter database open read only

4.READ WRITE:以可读写方式打开数据，这是默认方式

sql>startup mount

sql>alter database open read write

5.OPEN:打开数据库，这是默认方式

sql>startup mount

sql>alter database open;

6.RESTRICT:以受限方式打开，只有具有RESTRICTED SESSION权限的用户才可以连接数据库

sql>startup restrict

7.PFILE:启时指定参数文件

sql>startup pfile=/u02/user/mypfile.ora

8.FORCE:以强制方式启动数据库

sql>startup pfile=/u02/user/mypfile.ora open restrict force

---

数据库的关闭

　　与启动过程相对应，关闭包括三个过程

１)．关闭数据库

２)．卸载数据库

３)．关闭数据库实例

１．NORMAL关闭方式

　　NORMAL关闭方式是正常关闭方式，是默认关闭方式。在这种关机方式下，数据库服务器必须等待所有客户端从Oracle中正常退出时，才可以关闭数据库。

SQL>SHUTDOWN NORMAL

２．IMMEDIATE关闭方式

　　使用这种方式关闭数据库时，系统将连接到数据库的所有用户尚未提交的事务全部回退，中断连接，然后关闭数据库。

SQL>SHUTDOWN IMMEDIATE

３．ABORT关闭方式

　　ABORT关闭方式为异常方式，使用这种方式关闭数据库时，系统立即将数据库实例关闭，对事务膛数据完整性不作检查。是最快的关机方式。

sql>shutdown abort;

４．TRANSACTIONAL关闭方式

　　在这种关机方式中，数据库必须等待所有的客户运行的事务结束，提交或回退。

sql>shutdown transactional

---

ORACLE 权限管理说明

-----------------

角色相关的视图.

一. 概述

   与权限,角色相关的视图大概有下面这些:

   DBA_SYS_PRIVS: 查询某个用户所拥有的系统权限

   USER_SYS_PRIVS:  当前用户所拥有的系统权限

   SESSION_PRIVS:    当前用户所拥有的全部权限

   ROLE_SYS_PRIVS:    某个角色所拥有的系统权限

   注意: 要以SYS用户登陆查询这个视图,否则返回空.

   ROLE_ROLE_PRIVS:    当前角色被赋予的角色

   SESSION_ROLES:      当前用户被激活的角色

   USER_ROLE_PRIVS:   当前用户被授予的角色

   另外还有针对表的访问权限的视图:

   TABLE_PRIVILEGES

   ALL_TAB_PRIVS   

   ROLE_TAB_PRIVS:     某个角色被赋予的相关表的权限

---

权限查询

--------------

1. Select  *  from  session_privs;    //查询当前用户所拥有的权限
2. Select  *  from  user_sys_privs;  //查询某个用户被赋予的系统权限.      
3. select * from DBA_SYS_PRIVS where grantee='XXX'  //(需要当前用户拥有DBA角色)
4. Select  *  from SESSION_ROLES order by ROLE          //查询当前用户被授予的角色
5.  Select * from USER_ROLE_PRIVS     
6. Select Privilege from ROLE_SYS_PRIVS where ROLE=&Role ； //查询某一角色被赋予的系统权限

        输入 role='CONNECT'
        输出:
           PRIVILEGE
           --------------------
            ALTER SESSION
            CREATE CLUSTER
            CREATE DATABASE LINK
            CREATE SEQUENCE
            CREATE SESSION
            CREATE SYNONYM
            CREATE TABLE
            CREATE VIEW

          说明: 这个查询会返回当前用户所被授予的全部角色, 其中包括嵌套授权的
                   角色. 例如将DBA角色授予了一个用户,DBA角色已经被授予的角色(例如

exp_full_database 和 imp_full_database)也会被查询出来    

---

查询当前角色被授予的角色

      Select GRANTED_ROLE from ROLE_ROLE_PRIVS where ROLE=&ROLE

      输入 role= 'DBA'

      输出:

           GRANTED_ROLE

           ----------------------

            DELETE_CATALOG_ROLE

            EXECUTE_CATALOG_ROLE

            EXP_FULL_DATABASE

            IMP_FULL_DATABASE

            PLUSTRACE

            SELECT_CATALOG_ROLE

       说明: PLUSTRACE这个角色是用于执行SQL AUTO TRACE的, 通过执行 $ORACLE_HOME/sqlplus/admin/plustrce.sql可以生成这个角色.

---

 Oracle10g 在PLSQL Developer上建立表空间脚本

             --创建表空间
             create tablespace MOF_TEMP
             datafile 'D:\oracle\product\10.2.0\oradata\orcl\MOF_TEMP.def' size 500M
             autoextend on next 100M maxsize unlimited logging
             extent management local autoallocate
             segment space management auto;

1. DATAFILE: 表空间数据文件存放路径
2.  SIZE: 起初设置为200M
3. UNIFORM: 指定区尺寸为128k,如不指定，区尺寸默认为64k
4. 空间名称MOF_TEMP与 数据文件名称 MOF_TEMP.dbf 不要求相同,可随意命名
5. AUTOEXTEND ON/OFF 表示启动/停止自动扩展表空间
6. alter database datafile ' D:\oracle\product\10.2.0\oradata\orcl\MOF_TEMP.dbf ' resize 500m; //手动修改数据文件大小为500M

 

        删除表空间
        DROP TABLESPACE MOF_TEMP INCLUDING CONTENTS AND DATAFILES;  
        

---

        

         从某用户导入到另外一个用户   Oracle 导入dmp文件

        alter table bb_temp_da14 rename column tj_count to tjcount

 

         创建用户 ： create user linshi identified by 123456;

                            grant dba to linshi

    

        SQL> host imp system/manager@orce fromuser=tutorial touser=xiaoma file=F:\export.dmp     

        SQL> host exp 用户名/密码@sid full=y owner=用户名

                  exp 用户名/密码@sid owner = 用户名 file = 路径（如F:\exp.dmp）

 

导    入 ：host imp linshi/123456@oracle file=e:\yanshi1021.dmp full=y ignore=y

execute immediate