-
Notifications
You must be signed in to change notification settings - Fork 0
/
articulo_05_querys_finales.sql
60 lines (42 loc) · 1.8 KB
/
articulo_05_querys_finales.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
CREATE TABLESPACE TBS_TABLE_DELETE
DATAFILE 'C:\APP\XMY9080\ORADATA\ORCL\TBS_TABLE_DELETE.DBF'
size 10m
REUSE;
DROP TABLESPACE TBS_TABLE_DELETE INCLUDING CONTENTS AND DATAFILES;
CREATE TABLE hr.TABLE_DELETE(consecutive_id number, id number, name char(20), last_name char(20)) tablespace TBS_TABLE_DELETE;
CREATE TABLESPACE TBS_TABLE_TRUNCATE
DATAFILE 'C:\APP\XMY9080\ORADATA\ORCL\TBS_TABLE_TRUNCATE.DBF'
size 10m
REUSE;
DROP TABLESPACE TBS_TABLE_TRUNCATE INCLUDING CONTENTS AND DATAFILES;
CREATE TABLE hr.TABLE_TRUNCATE(consecutive_id number, id number, name char(20), last_name char(20)) tablespace TBS_TABLE_TRUNCATE;
SELECT TABLESPACE_NAME, FILE_ID, BLOCK_ID, BYTES/1024/1024 BYTES_MB, BLOCKS, RELATIVE_FNO
FROM dba_free_space
WHERE TABLESPACE_NAME IN ('TBS_TABLE_DELETE','TBS_TABLE_TRUNCATE');
SELECT TABLESPACE_NAME, BYTES/1024/1024 BYTES_MB
FROM dba_free_space
WHERE TABLESPACE_NAME IN ('TBS_TABLE_DELETE','TBS_TABLE_TRUNCATE');
SELECT TABLESPACE_NAME, BYTES/1024/1024 BYTES_MB FROM dba_data_files
WHERE TABLESPACE_NAME IN ('TBS_TABLE_DELETE','TBS_TABLE_TRUNCATE');
DECLARE
Uppercase_letter char(10);
Lowercase_letter char(10);
Random_number number;
Final_length number:=100000000;
Consecutive_number number:=1;
i number:=1;
BEGIN
FOR i IN 1 .. Final_length LOOP
Lowercase_letter:=dbms_random.string('l',10);
Uppercase_letter:=dbms_random.string('u',10);
Random_number:=round(DBMS_RANDOM.value(1,300));
INSERT INTO hr.TABLE_DELETE VALUES(i, Random_number, Lowercase_letter, Uppercase_letter);
INSERT INTO hr.TABLE_TRUNCATE VALUES(i, Random_number, Lowercase_letter, Uppercase_letter);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END;
SELECT TABLESPACE_NAME, BYTES/1024/1024 BYTES_MB
FROM dba_free_space
WHERE TABLESPACE_NAME IN ('TBS_TABLE_DELETE','TBS_TABLE_TRUNCATE');