oracle+sql脚本大全,Oracle 常用pl/sql脚本

骑猪看日落 2023-01-18 11:21 26阅读 0赞

\--设置语言

ALTER SESSION SET nls\_language = 'american'

\--查找职责或请求组

select frv.RESPONSIBILITY\_NAME,

frg.request\_group\_name,

frg.request\_group\_code,

fcpv.user\_concurrent\_program\_name,

\--favg.APPLICATION\_NAME req\_app\_name,

favc.APPLICATION\_NAME con\_app\_name

from FND\_REQUEST\_GROUPS frg,

FND\_REQUEST\_GROUP\_UNITS frgu,

FND\_CONCURRENT\_PROGRAMS\_VL fcpv,

\--FND\_APPLICATION\_VL favg,

FND\_APPLICATION\_VL favc,

FND\_RESPONSIBILITY\_VL frv

where frg.REQUEST\_GROUP\_ID = frgu.REQUEST\_GROUP\_ID

and frg.application\_id = frgu.application\_id

and fcpv.CONCURRENT\_PROGRAM\_ID = frgu.request\_unit\_id

\--and fcpv.CONCURRENT\_PROGRAM\_ID = 48023

\--and frg.application\_id = favg.application\_id

and fcpv.application\_id = favc.application\_id

\--and fcpv.concurrent\_program\_name like 'Create Consumption Advice%'

AND fcpv.user\_concurrent\_program\_name like 'Create Consumption Advice%'

\--AND favc.APPLICATION\_NAME like 'Create Consumption Advice%'

and fcpv.enabled\_flag = 'Y'

and frv.REQUEST\_GROUP\_id(+) = frg.REQUEST\_GROUP\_ID

\--查找消息内容

select message\_text, message\_number, type, fnd\_log\_severity, category, severity

from fnd\_new\_messages m, fnd\_application a

where 1=1

and m.application\_id = a.application\_id

AND NAME\_ARG = m.message\_name

and LANG\_ARG = m.language\_code

and APPIN = a.application\_short\_name

\--在Oracle中查看所有的表:

select \* from tab/dba\_tables/dba\_objects/cat;

\--看用户建立的表   :

select table\_name from user\_tables;  //当前用户的表

select table\_name from all\_tables;  //所有用户的表

select table\_name from dba\_tables;  //包括系统表

\--automatically set your operating unit

dbms\_application\_info.set\_client\_info('&Org\_id');

\--查看所有package和package body

SELECT  DISTINCT NAME  FROM   ALL\_SOURCE

WHERE   TYPE   =   'PACKAGE'   AND

OWNER   =   'APPS'  AND

NAME LIKE 'XX%'

\--  ROWNUM <10

ORDER   BY   NAME;

\--用户授权:

GRANT ALTER ANY INDEX TO "user\_id "

GRANT "dba " TO "user\_id ";

ALTER USER "user\_id " DEFAULT ROLE ALL

\--创建用户:

CREATE USER "user\_id " PROFILE "DEFAULT " IDENTIFIED BY " DEFAULT TABLESPACE "USERS " TEMPORARY TABLESPACE "TEMP " ACCOUNT UNLOCK;

GRANT "CONNECT " TO "user\_id ";

\--用户密码设定:

ALTER USER "CMSDB " IDENTIFIED BY "pass\_word "

\--表空间创建:

CREATE TABLESPACE "table\_space " LOGGING DATAFILE 'C:\\ORACLE\\ORADATA\\dbs\\table\_space.ora' SIZE 5M

\------------------------------------------------------------------------

\--1、查看当前所有对象

SQL > select \* from tab;

\--2、建一个和a表结构一样的空表

SQL > create table b as select \* from a where 1=2;

SQL > create table b(b1,b2,b3) as select a1,a2,a3 from a where 1=2;

\--3、察看数据库的大小，和空间使用情况

SQL > col tablespace format a20

SQL > select b.file\_id　　文件ID,

b.tablespace\_name　　表空间,

b.file\_name　　　　　物理文件名,

b.bytes　　　　　　　总字节数,

(b.bytes-sum(nvl(a.bytes,0)))　　　已使用,

sum(nvl(a.bytes,0))　　　　　　　　剩余,

sum(nvl(a.bytes,0))/(b.bytes)\*100　剩余百分比

from dba\_free\_space a,dba\_data\_files b

where a.file\_id=b.file\_id

group by b.tablespace\_name,b.file\_name,b.file\_id,b.bytes

order by b.tablespace\_name

dba\_free\_space --表空间剩余空间状况

dba\_data\_files --数据文件空间占用情况

\--4、查看现有回滚段及其状态

SQL > col segment format a30

SQL > SELECT SEGMENT\_NAME,OWNER,TABLESPACE\_NAME,SEGMENT\_ID,FILE\_ID,STATUS FROM DBA\_ROLLBACK\_SEGS;

\--5、查看数据文件放置的路径

SQL > col file\_name format a50

SQL > select tablespace\_name,file\_id,bytes/1024/1024,file\_name from dba\_data\_files order by file\_id;

\--6、显示当前连接用户

SQL > show user

\--7、把SQL\*Plus当计算器

SQL > select 100\*20 from dual;

\--8、连接字符串

SQL > select 列1 | |列2 from 表1;

SQL > select concat(列1,列2) from 表1;

\--9、查询当前日期

SQL > select to\_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual;

\--10、用户间复制数据

SQL > copy from user1 to user2 create table2 using select \* from table1;

\--11、视图中不能使用order by，但可用group by代替来达到排序目的

SQL > create view a as select b1,b2 from b group by b1,b2;

\--12、通过授权的方式来创建用户

SQL > grant connect,resource to test identified by test;

SQL > conn test/test

\--13、查出当前用户所有表名。

select unique tname from col;

\-----------------------------------------------------------------------

/\* 向一个表格添加字段 \*/

alter table alist\_table add address varchar2(100);

/\* 修改字段 属性 字段为空 \*/

alter table alist\_table modify address varchar2(80);

/\* 修改字段名字 \*/

create table alist\_table\_copy as select ID,NAME,PHONE,EMAIL,

QQ as QQ2, /\*qq 改为qq2\*/

ADDRESS from alist\_table;

drop table alist\_table;

rename alist\_table\_copy to alist\_table

/\* 修改表名 \*/

\--空值处理

\--有时要求列值不能为空

create table dept (deptno number(2) not null, dname char(14), loc char(13));

\--在基表中增加一列

alter table dept

add (headcnt number(3));

\--修改已有列属性

alter table dept

modify dname char(20);

/\*注：只有当某列所有值都为空时，才能减小其列值宽度。

只有当某列所有值都为空时，才能改变其列值类型。

只有当某列所有值都为不空时，才能定义该列为not null。\*/

\--例：

alter table dept modify (loc char(12));

alter table dept modify loc char(12);

alter table dept modify (dname char(13),loc char(12));

\--查找未断连接

select process,osuser,username,machine,logon\_time ,sql\_text

from v$session a,v$sqltext b where a.sql\_address=b.address;

\-----------------------------------------------------------------

\--1.以USER\_开始的数据字典视图包含当前用户所拥有的信息, 查询当前用户所拥有的表信息:

select \* from user\_tables;

/\*2.以ALL\_开始的数据字典视图包含ORACLE用户所拥有的信息,

查询用户拥有或有权访问的所有表信息: \*/

select \* from all\_tables;

\--3.以DBA\_开始的视图一般只有ORACLE数据库管理员可以访问:

select \* from dba\_tables;

\--4.查询ORACLE用户：

conn sys/change\_on\_install

select \* from dba\_users;

conn system/manager;

select \* from all\_users;

\--5.创建数据库用户：

CREATE USER user\_name IDENTIFIED BY password;

GRANT CONNECT TO user\_name;

GRANT RESOURCE TO user\_name;

\--授权的格式: grant (权限) on tablename to username;

\--删除用户(或表):

drop user(table) username(tablename) (cascade);

\--6.向建好的用户导入数据表

IMP SYSTEM/MANAGER FROMUSER = FUSER\_NAME TOUSER = USER\_NAME FILE = C:\\EXPDAT.DMP COMMIT = Y

\--7.索引

create index \[index\_name\] on \[table\_name\]( "column\_name ")

\--输出

dbms\_output.put\_line('lv\_object\_version\_number : '||lv\_object\_version\_number);

\--oracle中的userenv及sys\_context的参数2007-12-21 16:451、 USERENV(OPTION)

返回当前的会话信息.

OPTION='ISDBA'若当前是DBA角色,则为TRUE,否则FALSE.

OPTION='LANGUAGE'返回数据库的字符集.

OPTION='SESSIONID'为当前会话标识符.

OPTION='ENTRYID'返回可审计的会话标识符.

OPTION='LANG'返回会话语言名称的ISO简记.

OPTION='INSTANCE'返回当前的实例.

OPTION='terminal'返回当前计算机名

SELECT USERENV('LANGUAGE') FROM DUAL;

2、sys\_context

select

SYS\_CONTEXT('USERENV','TERMINAL') terminal,

SYS\_CONTEXT('USERENV','LANGUAGE') language,

SYS\_CONTEXT('USERENV','SESSIONID') sessionid,

SYS\_CONTEXT('USERENV','INSTANCE') instance,

SYS\_CONTEXT('USERENV','ENTRYID') entryid,

SYS\_CONTEXT('USERENV','ISDBA') isdba,

SYS\_CONTEXT('USERENV','NLS\_TERRITORY') nls\_territory,

SYS\_CONTEXT('USERENV','NLS\_CURRENCY') nls\_currency,

SYS\_CONTEXT('USERENV','NLS\_CALENDAR') nls\_calendar,

SYS\_CONTEXT('USERENV','NLS\_DATE\_FORMAT') nls\_date\_format,

SYS\_CONTEXT('USERENV','NLS\_DATE\_LANGUAGE') nls\_date\_language,

SYS\_CONTEXT('USERENV','NLS\_SORT') nls\_sort,

SYS\_CONTEXT('USERENV','CURRENT\_USER') current\_user,

SYS\_CONTEXT('USERENV','CURRENT\_USERID') current\_userid,

SYS\_CONTEXT('USERENV','SESSION\_USER') session\_user,

SYS\_CONTEXT('USERENV','SESSION\_USERID') session\_userid,

SYS\_CONTEXT('USERENV','PROXY\_USER') proxy\_user,

SYS\_CONTEXT('USERENV','PROXY\_USERID') proxy\_userid,

SYS\_CONTEXT('USERENV','DB\_DOMAIN') db\_domain,

SYS\_CONTEXT('USERENV','DB\_NAME') db\_name,

SYS\_CONTEXT('USERENV','HOST') host,

SYS\_CONTEXT('USERENV','OS\_USER') os\_user,

SYS\_CONTEXT('USERENV','EXTERNAL\_NAME') external\_name,

SYS\_CONTEXT('USERENV','IP\_ADDRESS') ip\_address,

SYS\_CONTEXT('USERENV','NETWORK\_PROTOCOL') network\_protocol,

SYS\_CONTEXT('USERENV','BG\_JOB\_ID') bg\_job\_id,

SYS\_CONTEXT('USERENV','FG\_JOB\_ID') fg\_job\_id,

SYS\_CONTEXT('USERENV','AUTHENTICATION\_TYPE') authentication\_type,

SYS\_CONTEXT('USERENV','AUTHENTICATION\_DATA') authentication\_data

from dual

oracle+sql脚本大全,Oracle 常用pl/sql脚本

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Oracle - PLSQL导入sql脚本

相关 oracle+sql脚本大全,Oracle 常用pl/sql脚本

相关 Oracle 性能相关常用脚本(SQL)

相关 [Oracle] DBA 常用脚本二

相关 [Oracle] DBA 常用脚本一

相关 Oracle管理及常用基础脚本

相关 oracle pl/sql脚本常用技巧

相关 XSS(跨站脚本攻击) - 常用代码大全

相关 MySql常用脚本

相关 plsql批量执行脚本