oracle数据库js 在浏览器中执行可以生成创建表空间的语句

页面执行js：

/* 创建数据库命令生成 */

/* 由于使用了模板字符串，以下代码需要在支持 ES6 的浏览器中运行 */

/* 设置 */
var settings = {
/* 数据文件大小 */
dataFileSize: "1000M",
/* 用户名 */
user: "mt_ls",
/* 密码 */
password: "mt_ls",
/* 数据库实例路径 */
databasePath: "D:/app/Administrator/oradata/lsxorcl",
/* 导入文件名 */
empFile: "zzzd_test2.dmp",
}

/* 设置自动补全 */
settings = {
...settings,
/* 表空间名 */
getTableSpace: function () { return this.user + "_data" },
/* 数据文件夹名 */
getDataDirectoryName: function () { return this.user + "_data_dir" },
/* 数据文件夹路径 */
getDataDirectoryPath: function () { return this.databasePath + "\\exp" },
/* 导入文件路径 */
getEmpFile: function () { return "f:" + '\\' + this.empFile }
}

/* 创建数据库语句 */
var createStatement = `
-- 创建表空间
create tablespace ${settings.getTableSpace()} datafile '${settings.databasePath}\\data.dbf' size ${settings.dataFileSize};

-- 创建用户并绑定默认表空间
create user ${settings.user} identified by ${settings.password}
default tablespace ${settings.getTableSpace()};

-- 用户授权
grant connect,resource,
create any view,
create database link,
select any table,
create table,
update any table,
insert any table,
delete any table,
alter any table,
debug any procedure,
dba
to ${settings.user};

-- 用户角色
ALTER USER ${settings.user} DEFAULT ROLE "CONNECT";
ALTER USER ${settings.user} DEFAULT ROLE "RESOURCE";
ALTER USER ${settings.user} DEFAULT ROLE "DBA";

-- 数据文件夹授权
create directory ${settings.getDataDirectoryName()} as '${settings.getDataDirectoryPath()}';
grant CREATE ANY DIRECTORY to ${settings.user};
`

/* 引入命令（在 CommandLine 中运行，而非 SqlPlus） */
var importCommand = `
imp ${settings.user}/${settings.password}@localhost:1521/orcl file=${settings.getEmpFile()} full=y ignore=y
`

/* 删除语句 */
var deleteStatement = `
-- 删除用户
DROP user ${settings.user} cascade;
-- 删除表空间
DROP TABLESPACE ${settings.getTableSpace()} INCLUDING CONTENTS AND DATAFILES;
-- 删除数据文件夹
DROP DIRECTORY ${settings.getDataDirectoryName()};
`

function generateStatements (createStatement, importCommand, deleteStatement) {
/* 准备拼接 */
var Statements = [createStatement, importCommand, deleteStatement];
/* 空行分隔 */
var Statement = Statements.join("\n--------------------\n");
/* 复制到剪贴板 */
copy(Statement);

return Statement;
}

/* 提示 */
console.log("-- 创建数据库相关语句已复制到剪贴板中\n", generateStatements(createStatement, importCommand, deleteStatement));

执行步骤:

1、打开浏览器，F12

将上面的代码复制到 console 中按回车键会生成创建oracle 表空间的语句

 1 -- 创建表空间
 2 create tablespace mt_ls_data datafile 'D:/app/Administrator/oradata/lsxorcl\data.dbf' size 1000M;
 3 
 4 -- 创建用户并绑定默认表空间
 5 create user mt_ls identified by mt_ls  
 6 default tablespace mt_ls_data;  
 7 
 8 -- 用户授权
 9 grant connect,resource,
10 create any view,
11 create database link,
12 select any table,
13 create table,
14 update any table,
15 insert any table,
16 delete any table,
17 alter any table,
18 debug any procedure,
19 dba  
20 to mt_ls;
21 
22 -- 用户角色
23 ALTER USER mt_ls DEFAULT ROLE "CONNECT"; 
24 ALTER USER mt_ls DEFAULT ROLE "RESOURCE"; 
25 ALTER USER mt_ls DEFAULT ROLE "DBA"; 
26 
27 -- 数据文件夹授权
28 create directory mt_ls_data_dir as 'D:/app/Administrator/oradata/lsxorcl\exp';  
29 grant CREATE ANY DIRECTORY to mt_ls;
30 
31 --------------------
32 
33 imp mt_ls/mt_ls@localhost:1521/orcl file=f:\zzzd_test2.dmp full=y ignore=y
34 
35 --------------------
36 
37 -- 删除用户
38 DROP user mt_ls cascade;
39 -- 删除表空间
40 DROP TABLESPACE mt_ls_data INCLUDING CONTENTS AND DATAFILES;
41 -- 删除数据文件夹
42 DROP DIRECTORY mt_ls_data_dir;

单独创建表空间的sql 1 :

 1 --表空间  
 2 CREATE TABLESPACE MT_DATA
 3 DATAFILE 'C:/soft/oracle/dbf/szDta.dbf' size 800M  
 4          EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;   
 5 
 6        
 7   
 8 --2.建用户  
 9 create user mt identified by mt123  
10 default tablespace MT_DATA
11    
12 --3.赋权  
13 grant connect,resource to mt;  
14 grant create any sequence to mt;  
15 grant create any table to mt;  
16 grant delete any table to mt;  
17 grant insert any table to mt;  
18 grant select any table to mt;  
19 grant unlimited tablespace to mt;  
20 grant execute any procedure to mt;  
21 grant update any table to mt;  
22 grant create any view to mt;

单独创建表空间的sql 1 :

-- 创建表空间
create tablespace mt_ls_data datafile 'D:/app/Administrator/oradata/lsxorcl\data.dbf' size 1000M;

-- 创建用户并绑定默认表空间
create user mt_ls identified by mt_ls  
default tablespace mt_ls_data;  

-- 用户授权
grant connect,resource,
create any view,
create database link,
select any table,
create table,
update any table,
insert any table,
delete any table,
alter any table,
debug any procedure,
dba  
to mt_ls;

-- 用户角色
ALTER USER mt_ls DEFAULT ROLE "CONNECT"; 
ALTER USER mt_ls DEFAULT ROLE "RESOURCE"; 
ALTER USER mt_ls DEFAULT ROLE "DBA"; 

-- 数据文件夹授权
create directory mt_ls_data_dir as 'D:/app/Administrator/oradata/lsxorcl\exp';  
grant CREATE ANY DIRECTORY to mt_ls;

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

imp mt_ls/mt_ls@localhost:1521/orcl file=f:\zzzd_test2.dmp full=y ignore=y

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

-- 删除用户
DROP user mt_ls cascade;
-- 删除表空间
DROP TABLESPACE mt_ls_data INCLUDING CONTENTS AND DATAFILES;
-- 删除数据文件夹
DROP DIRECTORY mt_ls_data_dir;





-- 创建表空间
create tablespace ZHYLDB_DATA datafile 'F:/oracle/product/10.2.0/oradata/zhyldbData/zhyldbData.dbf' size 1000M;


create tablespace SZ_DATA datafile 'F:/oracle/product/10.2.0/oradata/szData/szData.dbf' size 1000M;

套娃sql ：

一、

 1 SELECT
 2         A.termcode,
 3         u.name
 4         FROM
 5         mt_pmba_user_profile u,
 6         (
 7         SELECT
 8         x.*,
 9         p.agent
10         FROM
11         MT_PMBA_AUTHORIZATION p,
12         ( SELECT t.*, m.authorizationid FROM MT_RESULTTAB t, mt_pmba_verify_record m WHERE t.guid = m.verifyrecord ) x
13         WHERE
14         p.guid = x.authorizationid
15         ) A
16         WHERE
17         u.guid = A.agent

二、

 1 SELECT
 2     f.name,
 3 --     r.ID,
 4 --     r.DEVICENUMBER,
 5     r.Remark,
 6     r.districtname,
 7     r.INSPECTIONWAY,
 8     r.areaname,
 9     r.FAULTWAY,
10     r.ibkbosstate,
11     r.paperstatus,
12     r.putimg,
13     r.faultdesc,
14     r.PROBLEMWAY,
15     r.MAINTAINPERSON --         r.CREATEDATE
16 --     r.solution
17 FROM
18     sys_user f,
19     (
20         SELECT--         a.ID,
21 --         a.DEVICENUMBER,
22         w.Remark,
23         w.districtname,
24         w.INSPECTIONWAY,
25         w.areaname,
26         w.FAULTWAY,
27         w.ibkbosstate,
28         w.PROBLEMWAY,
29         w.paperstatus,
30         w.putimg,
31         a.faultdesc,
32 --         a.solution,
33 --         w.CREATEDATE,
34         w.MAINTAINPERSON 
35     FROM
36         mt_opsworkorder a,
37         (
38         SELECT
39             b.EQUIPMENTID equipmentidone,
40             b.Remark,
41             q.equipmentid equipmentidtwo,
42 --             b.CREATEDATE,
43             q.districtname,
44             b.INSPECTIONWAY,
45             q.putimg,
46             b.PROBLEMWAY,
47             b.ibkbosstate,
48             b.paperstatus,
49             q.areaname,
50             b.FAULTWAY,
51             b.MAINTAINPERSON 
52         FROM
53             mt_inspection b,
54             (
55             SELECT
56                 j.districtname,
57                 e.equipmentid,
58                 e.areaname,
59                 e.putimg 
60             FROM
61                 mt_district j,
62                 mt_terminal e 
63             WHERE
64                 e.addresstid = j.districtid 
65             ) q 
66         WHERE
67             b.EQUIPMENTID = q.equipmentid 
68         ) w 
69     WHERE
70         a.DEVICENUMBER = w.equipmentidone 
71     ) r 
72 WHERE
73     f.ID = r.MAINTAINPERSON 
74 GROUP BY
75     f.name,
76 --     r.ID,
77 --     r.DEVICENUMBER,
78     r.districtname,
79     r.INSPECTIONWAY,
80     r.areaname,
81     r.MAINTAINPERSON,
82     r.FAULTWAY,
83     r.PROBLEMWAY,
84     r.faultdesc,
85     r.ibkbosstate,
86     r.paperstatus,
87     r.putimg,
88     r.Remark --     r.CREATEDATE,
89 --     r.solution

三、

 1 SELECT
 2     a.PROFILE,
 3     z.guid,
 4     z.termcode,
 5     z.name 
 6 FROM
 7     MT_PMBA_LOCAL_AUTH a
 8     INNER JOIN (
 9     SELECT
10         A.termcode,
11         u.name,
12         u.guid 
13     FROM
14         mt_pmba_user_profile u
15         INNER JOIN (
16         SELECT
17             x.authorizationid,
18             p.agent,
19             x.termcode 
20         FROM
21             MT_PMBA_AUTHORIZATION p
22             INNER JOIN (
23             SELECT
24                 t.guid,
25                 t.termcode,
26                 m.authorizationid 
27             FROM
28                 MT_RESULTTAB t
29                 INNER JOIN mt_pmba_verify_record m ON t.guid = m.verifyrecord 
30             ) x ON p.guid = x.authorizationid 
31         ) A ON u.guid = A.agent 
32     ) z ON a.PROFILE = z.guid

四、

 1 SELECT
 2     n.sectionname AS sectionname,
 3     NVL( z.onlineCount, 0 ) AS onlineCount,
 4     n.num AS numberofMachines,
 5     NVL( ROUND( z.onlineCount / n.num, 2 ), 0 ) AS utilizationRate 
 6 FROM
 7     (
 8     SELECT
 9         m.name AS sectionname,
10         a.num,
11         m.id 
12     FROM
13         (
14         SELECT
15             count( sectionid ) AS num,
16             sectionid 
17         FROM
18             mt_terminal t 
19         WHERE
20             t.medinsid = ( SELECT guid FROM mt_medstructure m WHERE m.id = 'H6090' ) 
21             AND t.termialtype = 'desktop' 
22             AND t.USAGESTATUS != 'test' 
23         GROUP BY
24             sectionid 
25         ) a
26         LEFT JOIN mt_medstructure m ON a.sectionid = m.guid 
27     ORDER BY
28         a.sectionid DESC 
29     ) n
30     LEFT JOIN (
31     SELECT
32         count( termcode ) AS onlineCount,
33         sectioncode 
34     FROM
35         (
36         SELECT
37             t.termcode,
38             count( sectioncode ),
39             t.sectioncode 
40         FROM
41             mt_resulttab t 
42         WHERE
43             t.medinscode = 'H6090' 
44             AND TO_CHAR( TO_DATE( operdate, 'YYYY-MM-DD HH24:MI:SS' ), 'YYYY-MM-DD' ) = TO_CHAR( SYSDATE, 'YYYY-MM-DD' ) 
45         GROUP BY
46             termcode,
47             t.sectioncode 
48         ) m 
49     GROUP BY
50     m.sectioncode 
51     ) z ON n.id = z.sectioncode

五、

  1 SELECT
  2     a1.onlineCountsum,
  3     a1.hospptname,
  4     a1.dateaddedsum,
  5     a1.sectionname,
  6     a1.onlineCount,
  7     a1.numberofMachines,
  8     a1.utilizationRate,
  9     a1.numberofsum,
 10     a1.dateadded,
 11     NVL( a2.hrpotcheck, 0 ) AS hrpotcheck,
 12     NVL( a2.yeshrpotcheck, 0 ) AS yeshrpotcheck,
 13     NVL( a2.nohrpotcheck, 0 ) AS nohrpotcheck 
 14 FROM
 15     (
 16     SELECT
 17         n.sectionname AS sectionname,
 18         NVL( z.onlineCount, 0 ) AS onlineCount,
 19         n.num AS numberofMachines,
 20         NVL( ROUND( z.onlineCount / n.num, 2 ), 0 ) AS utilizationRate,
 21         SUM( n.NUM ) OVER () AS numberofsum,
 22         (
 23         n.num - NVL( z.onlineCount, 0 )) AS dateadded,
 24         SUM(
 25         NVL( z.onlineCount, 0 )) OVER () AS onlineCountsum,
 26         SUM( ( n.num - NVL( z.onlineCount, 0 )) ) OVER () AS dateaddedsum,
 27         hospptname AS hospptname,
 28         ID AS sectionid 
 29     FROM
 30         (
 31         SELECT
 32             M.NAME AS sectionname,
 33             A.sectionid,
 34             A.num,
 35             M.ID,
 36             ( SELECT T.NAME FROM MT_MEDSTRUCTURE T WHERE T.guid = M.parentid ) AS hospptname 
 37         FROM
 38             ( SELECT COUNT( termcode ) AS num, sectionid FROM mt_terminal T WHERE T.USAGESTATUS != 'test' GROUP BY sectionid ) A
 39             LEFT JOIN mt_medstructure M ON A.sectionid = M.guid 
 40         ORDER BY
 41             A.sectionid DESC 
 42         ) n
 43         LEFT JOIN (
 44         SELECT
 45             COUNT( termcode ) AS onlineCount,
 46             sectioncode 
 47         FROM
 48             ( SELECT T.termcode, COUNT( sectioncode ), T.sectioncode FROM mt_resulttab T GROUP BY termcode, T.sectioncode ) M 
 49         GROUP BY
 50             M.sectioncode 
 51         ) z ON n.ID = z.sectioncode 
 52     ) a1
 53     LEFT JOIN (
 54     SELECT
 55         t1.totalpeople AS hrpotcheck,
 56         ( t1.totalpeople - NVL( p1.passpepple, 0 ) ) AS yeshrpotcheck,
 57         NVL( p1.passpepple, 0 ) AS nohrpotcheck,
 58         sectioncode 
 59     FROM
 60         (
 61         SELECT
 62             NVL( M.ID, 0 ) AS sectioncode,
 63             NVL( n.totalpeople, 0 ) AS totalpeople,
 64             M.sectionname AS sectionname,
 65             M.num AS num 
 66         FROM
 67             ( SELECT COUNT( DISTINCT patid ) AS totalpeople, sectioncode FROM mt_resulttab T GROUP BY T.sectioncode ) n
 68             RIGHT JOIN (
 69             SELECT
 70                 M.NAME AS sectionname,
 71                 A.num,
 72                 M.ID 
 73             FROM
 74                 ( SELECT COUNT( sectionid ) AS num, sectionid FROM mt_terminal T WHERE T.USAGESTATUS != 'test' GROUP BY sectionid ) A
 75                 LEFT JOIN mt_medstructure M ON A.sectionid = M.guid 
 76             ORDER BY
 77                 A.sectionid DESC 
 78             ) M ON n.sectioncode = M.ID 
 79         ) t1
 80         LEFT JOIN (
 81         SELECT
 82             M.ID,
 83             NVL( n.totalpeople, 0 ) AS passpepple,
 84             M.sectionname AS sectionname 
 85         FROM
 86             ( SELECT COUNT( DISTINCT patid ) AS totalpeople, sectioncode FROM mt_resulttab T WHERE T.scode = '1' GROUP BY T.sectioncode ) n
 87             RIGHT JOIN (
 88             SELECT
 89                 M.NAME AS sectionname,
 90                 A.num,
 91                 M.ID 
 92             FROM
 93                 ( SELECT COUNT( sectionid ) AS num, sectionid FROM mt_terminal T WHERE T.USAGESTATUS != 'test' GROUP BY sectionid ) A
 94                 LEFT JOIN mt_medstructure M ON A.sectionid = M.guid 
 95             ORDER BY
 96                 A.sectionid DESC 
 97             ) M ON n.sectioncode = M.ID 
 98         ) p1 ON t1.sectioncode = p1.ID 
 99     ) a2 ON a1.sectionid = a2.sectioncode
100     
101

以上为实例sql 嵌套

posted @ 2021-09-28 15:56 易言。阅读(112) 评论(0) 收藏举报

刷新页面返回顶部

易言。

oracle数据库js 在浏览器中执行可以生成创建表空间的语句

公告