一个语句创建Oracle所有表的序列
-- 动态创建序列 2 declare 3 cursor c_job is 4 select TABLE_NAME from user_tables; 5 6 c_row c_job%rowtype; 7 v_sql varchar2(200); 8 begin 9 for c_row in c_job loop 10 begin 11 v_sql := 'create sequence SEQ_' || c_row.TABLE_NAME; 12 dbms_output.put_line(v_sql); 13 execute immediate v_sql; 14 end; 15 end loop; 16 17 end;
/**
查找表序号索引字段名称
**/
select * from user_sequences
/**
表空间是成功建立的，可以通过以下语句来查询表空间的名称及大小：
**/
SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size
FROM dba_tablespaces t, dba_data_files d
WHERE t.tablespace_name = d.tablespace_name
GROUP BY t.tablespace_name;
/*
Navicat Premium Data Transfer
Source Server : 10.2.146.30
Source Server Type : Oracle
Source Server Version : 110200
Source Host : 10.2.146.30:1521
Source Schema : SCOTT
Target Server Type : Oracle
Target Server Version : 110200
File Encoding : 65001
Date: 19/08/2020 10:44:53
*/
-- ----------------------------
-- Table structure for SERV_EMAIL_EXCEPTION
-- ----------------------------
CREATE TABLE "SCOTT"."SERV_EMAIL_EXCEPTION" (
"ID" NUMBER ,
"CUST_NAME" VARCHAR2(50 BYTE) ,
"CUST_EMAIL" VARCHAR2(200 BYTE) ,
"SEND_TIME" DATE ,
"SEND_TYPE" NUMBER ,
"SEND_TITLE" VARCHAR2(200 BYTE) ,
"SEND_CONTEXT" VARCHAR2 (500 BYTE),
"CREATE_TIME" DATE DEFAULT sysdate ,
"REMARK" VARCHAR2(200 BYTE) ,
"EX_TYPE" VARCHAR2(30 BYTE) ,
"FILE_PATH" VARCHAR2(200 BYTE) ,
"CC_USER" VARCHAR2(1000 BYTE) ,
"EX_MESSAGE" VARCHAR2(1000 BYTE) ,
"EX_EXCEPTION" VARCHAR2(1000 BYTE)
)
TABLESPACE "SYSTEM"
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
INITIAL 4194304
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT
;
COMMENT ON COLUMN "SCOTT"."SERV_EMAIL_EXCEPTION"."CUST_NAME" IS '客户名称';
COMMENT ON COLUMN "SCOTT"."SERV_EMAIL_EXCEPTION"."CUST_EMAIL" IS '客户email';
COMMENT ON COLUMN "SCOTT"."SERV_EMAIL_EXCEPTION"."SEND_TIME" IS '发送时间';
COMMENT ON COLUMN "SCOTT"."SERV_EMAIL_EXCEPTION"."SEND_TYPE" IS '0-未发送，6-发送成功 7-发送失败';
COMMENT ON COLUMN "SCOTT"."SERV_EMAIL_EXCEPTION"."SEND_TITLE" IS '邮件标题';
COMMENT ON COLUMN "SCOTT"."SERV_EMAIL_EXCEPTION"."SEND_CONTEXT" IS '邮件正文';
COMMENT ON COLUMN "SCOTT"."SERV_EMAIL_EXCEPTION"."CREATE_TIME" IS '批次插入时间';
COMMENT ON COLUMN "SCOTT"."SERV_EMAIL_EXCEPTION"."REMARK" IS '备注';
COMMENT ON COLUMN "SCOTT"."SERV_EMAIL_EXCEPTION"."EX_TYPE" IS '异常类型 1-解析异常2-未发邮件3-登陆异常/手工导入异常';
COMMENT ON COLUMN "SCOTT"."SERV_EMAIL_EXCEPTION"."FILE_PATH" IS '直销电子对账单保存word文件路径';
COMMENT ON COLUMN "SCOTT"."SERV_EMAIL_EXCEPTION"."CC_USER" IS '抄送email';
COMMENT ON COLUMN "SCOTT"."SERV_EMAIL_EXCEPTION"."EX_MESSAGE" IS '异常message';
COMMENT ON COLUMN "SCOTT"."SERV_EMAIL_EXCEPTION"."EX_EXCEPTION" IS '异常';
-- ----------------------------
-- Indexes structure for table SERV_EMAIL_EXCEPTION
-- ----------------------------
CREATE INDEX "SCOTT"."INDEX_EMAIL_DETAIL"
ON "SCOTT"."SERV_EMAIL_EXCEPTION" ("ID" ASC)
LOGGING
TABLESPACE "SYSTEM"
VISIBLE
PCTFREE 10
INITRANS 2
STORAGE (
INITIAL 327680
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
);
CREATE INDEX "SCOTT"."INDEX_EMAIL_DETAIL2"
ON "SCOTT"."SERV_EMAIL_EXCEPTION" ("SEND_TYPE" ASC, "ID" ASC)
LOGGING
TABLESPACE "SYSTEM"
VISIBLE
PCTFREE 10
INITRANS 2
STORAGE (
INITIAL 393216
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
);