Oracle 常用语法函数收集-优快云博客

本文链接：https://blog.youkuaiyun.com/iteye_11024/article/details/81962955

[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。

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

sqlplus /nolog
conn /as sysdba
alter user scott account unlock;
select username,password from dba_users;
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

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

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

--步骤

create user DB_USER identified by DB_PASSWORD； //用户名：DB_USER ；密码：DB_PASSWORD
grant create session to DB_USER  ；    //授权连接权限
grant resource to DB_USER；               //授权访问权限
grant create table to DB_USER;   //授权创建表的权限
select * from user_tab_privs; //本用户读取其他用户对象的权限:
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:     某个角色被赋予的相关表的权限

权限查询

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

Select * from session_privs;   //查询当前用户所拥有的权限
Select * from user_sys_privs; //查询某个用户被赋予的系统权限.
select * from DBA_SYS_PRIVS where grantee='XXX' //(需要当前用户拥有DBA角色)
Select * from SESSION_ROLES order by ROLE //查询当前用户被授予的角色
Select * from USER_ROLE_PRIVS
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;

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