Skip to content

Commit 7404f44

Browse files
davidwfrancodavidwfranco
committed
Merge branch 'master' of https://github.com/davidwfranco/sql 
2 parents e101249 + f77b4da commit 7404f44

11 files changed

+332
-44
lines changed

‎README.md

Lines changed: 36 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -1,20 +1,42 @@
1-
# sql
2-
*Sql scripts for the day to day stuff and some specific operations*
1+
# **sql**
2+
_Sql scripts for the day to day stuff and some specific operations_
33

4-
## getInstStatus.sql
5-
Get the status of the database, it shows the instance name(s), status, if logins are allowed and their hostnames.
4+
## _awrMiscOps.sql_
5+
Get awr informations, like the retention time, the snap interval, the dbid's present on your awr and other stuff
66

7-
## selectivity.sql
8-
Get statistics of the distribution of the values on a column or group of columns to help on the decision making of create/alter/drop indexes
7+
## _awrTimeBreakdown.sql_
8+
Get the wait events and present them on the timeline
99

10-
## getDesyncSequences.sql
11-
PL/SQL block created to find sequences of an database that have their curr value lower than the column where they are used for
10+
## _awrTopEvents.sql_
11+
Get the wait events for the whole period
1212

13-
## awrMiscOps.sql
14-
Get awr informations, like the retention time, the snap interval, the dbid's present on your awr and other stuff
13+
## cleanSchema.sql
14+
Drop all objects from a given schema. Usefull for process where you want to reimport all objects of a schema or just clean it without having to bother about the user privileges or password or tablespace appointment etc...
15+
```
16+
SQL> @cleanSchema.sql <schema_name>
17+
```
1518

16-
## awrTimeBreakdown.sql
17-
Get the wait events and present them on the timeline
19+
## _createUser.sql_
20+
Just a simple plsql to create a user and grant all the basic permission
1821

19-
## awrTopEvents.sql
20-
Get the wait events for the whole period
22+
<small>
23+
OBS: this one is still in building phase, with basically no error treatment or flexibility to unpredicted stuff
24+
</small>
25+
26+
## _findObject.sql_
27+
Find all objects wich the name constains the string passed to the script, it returns the owner, object name, object type, creation date and object status
28+
```
29+
SQL> @findObject.sql <string>
30+
```
31+
32+
## _getAsmDGInfo.sql_
33+
## _getDesyncSequences.sql_
34+
35+
## _getGrants.sql_
36+
## _getInstStatus.sql_
37+
Get the status of the database, it shows the instance name(s), status, if logins are allowed and their hostnames.
38+
39+
## _getKillSessCmd.sql_
40+
## _getUserInfo.sql_
41+
## _selectivity.sql_
42+
Get statistics of the distribution of the values on a column or group of columns to help on the decision making of create/alter/drop indexes

‎cleanSchema.sql

Lines changed: 133 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,133 @@
1+
-- Drop all objects of a given schema
2+
-- Thanks to Lucas Bernardini for the script wireframe
3+
4+
PROMPT INFO: BEGIN cleanSchema.sql
5+
6+
var v_owner varchar2(30);
7+
8+
exec :v_owner := '&1';
9+
10+
set serveroutput on size unlimited verify off
11+
12+
PROMPT INFO: Object count BEFORE cleaning the schema
13+
14+
select object_type, count(*)
15+
from dba_objects
16+
where owner = upper(:v_owner)
17+
group by object_type
18+
;
19+
20+
declare
21+
cmd varchar2(200);
22+
23+
cursor tables is
24+
select t.owner, t.table_name
25+
from dba_tables t
26+
where t.owner = upper(:v_owner)
27+
and t.table_name not in (select object_name
28+
from dba_objects
29+
where owner = t.owner
30+
and object_type = 'MATERIALIZED VIEW');
31+
32+
cursor miscObjects is
33+
select object_type, owner, object_name
34+
from dba_objects
35+
where object_type not in ('TABLE', 'INDEX', 'TRIGGER', 'LOB', 'PACKAGE BODY', 'JOB', 'SCHEDULE', 'DATABASE LINK')
36+
and object_type not like '%LINK%'
37+
and object_type not like '%PARTITION%'
38+
and owner = upper(:v_owner)
39+
order by 1;
40+
41+
cursor pkgs is
42+
select object_type, owner, object_name
43+
from dba_objects
44+
where object_type = 'PACKAGE BODY'
45+
and owner = upper(:v_owner)
46+
order by 1;
47+
48+
cursor jobs is
49+
select owner, object_name
50+
from dba_objects
51+
where object_type = 'JOB'
52+
and owner = upper(:v_owner)
53+
order by 1;
54+
55+
cursor schedules is
56+
select owner, object_name
57+
from dba_objects
58+
where object_type = 'SCHEDULE'
59+
and owner = upper(:v_owner)
60+
order by 1;
61+
begin
62+
dbms_output.put_line('INFO: Dropping objects from schema ' || :v_owner);
63+
dbms_output.put_line('INFO: Dropping tables...');
64+
for tbl in tables loop
65+
begin
66+
execute immediate 'drop table '||tbl.owner||'.'||tbl.table_name||' cascade constraints purge';
67+
--dbms_output.put_line('drop table '||tbl.owner||'.'||tbl.table_name||' cascade constraints purge');
68+
exception
69+
when others then
70+
dbms_output.put_line('ERROR: Drop failed on Table: '||tbl.owner||'.'||tbl.table_name||' --> '|| sqlerrm);
71+
end;
72+
end loop;
73+
dbms_output.put_line('INFO: Done.');
74+
75+
dbms_output.put_line('INFO: Dropping other objects...');
76+
for obj in miscObjects loop
77+
begin
78+
execute immediate 'drop '||obj.object_type||' '||obj.owner||'.'||obj.object_name;
79+
--dbms_output.put_line('drop '||obj.object_type||' '||obj.owner||'.'||obj.object_name);
80+
exception
81+
when others then
82+
dbms_output.put_line('ERROR: Drop failed on '||obj.object_type||': '||obj.owner||'.'||obj.object_name||' --> '|| sqlerrm);
83+
end;
84+
end loop;
85+
dbms_output.put_line('INFO: Done.');
86+
87+
dbms_output.put_line('INFO: Dropping Packages...');
88+
for pkg in pkgs loop
89+
begin
90+
execute immediate 'drop '||pkg.object_type||' '||pkg.owner||'.'||pkg.object_name;
91+
--dbms_output.put_line('drop '||pkg.object_type||' '||pkg.owner||'.'||pkg.object_name);
92+
exception
93+
when others then
94+
dbms_output.put_line('ERROR: Drop failed on '||pkg.object_type||': '||pkg.owner||'.'||pkg.object_name||' --> '|| sqlerrm);
95+
end;
96+
end loop;
97+
dbms_output.put_line('INFO: Done.');
98+
99+
dbms_output.put_line('INFO: Dropping jobs...');
100+
for job in jobs loop
101+
begin
102+
dbms_scheduler.drop_job(job.owner||'.'||job.object_name);
103+
--dbms_output.put_line('dbms_scheduler.drop_job('||job.owner||'.'||job.object_name||')');
104+
exception
105+
when others then
106+
dbms_output.put_line('ERROR: Drop failed on JOB: '||job.owner||'.'||job.object_name||' --> '|| sqlerrm);
107+
end;
108+
end loop;
109+
dbms_output.put_line('INFO: Done.');
110+
111+
dbms_output.put_line('INFO: Dropping schedules...');
112+
for schedule in schedules loop
113+
begin
114+
dbms_scheduler.drop_schedule(schedule.owner||'.'||schedule.object_name);
115+
--dbms_output.put_line('dbms_scheduler.drop_schedule('||schedule.owner||'.'||schedule.object_name||')');
116+
exception
117+
when others then
118+
dbms_output.put_line('ERROR: Drop failed on SCHEDULE: '||schedule.owner||'.'||schedule.object_name||' --> '|| sqlerrm);
119+
end;
120+
end loop;
121+
dbms_output.put_line('INFO: Done.');
122+
end;
123+
/
124+
125+
PROMPT INFO: Object count AFTER cleaning the schema
126+
127+
select object_type, count(*)
128+
from dba_objects
129+
where owner = upper(:v_owner)
130+
group by object_type
131+
;
132+
133+
PROMPT INFO: END cleanSchema.sql

‎createRandPass.sql

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,21 @@
1+
set lines 190 pages 50000 long 10000
2+
set serveroutput on size unlimited
3+
4+
DECLARE
5+
vPasswd varchar2(50);
6+
BEGIN
7+
vPasswd := DBMS_RANDOM.STRING('a', 1);
8+
FOR i IN 1 .. 11
9+
LOOP
10+
CASE ROUND(DBMS_RANDOM.VALUE(1,5))
11+
WHEN 1 THEN
12+
vPasswd := vPasswd || ROUND(DBMS_RANDOM.VALUE(0, 10));
13+
WHEN 2 THEN
14+
vPasswd := vPasswd || '#';
15+
ELSE
16+
vPasswd := vPasswd || DBMS_RANDOM.STRING('a', 1);
17+
END CASE;
18+
END LOOP;
19+
DBMS_OUTPUT.PUT_LINE(vPasswd);
20+
END;
21+
/

‎createUser.sql

Lines changed: 40 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -1,37 +1,58 @@
1-
set lines 180 pages 50000 feedback on autoprint on serveroutput on size unlimited
1+
set serveroutput on size unlimited
2+
set feedback on
23

34
DECLARE
4-
TYPE NamesList IS TABLE OF VARCHAR2(35);
5-
usernames NamesList := NamesList('ADMA88719');
5+
TYPE NamesList IS TABLE OF VARCHAR2(30); -- nested table type
6+
usernames NamesList := NamesList(
7+
'USER_847448','USER_928866','USER_993233','USER_886618','USER_A34104'
8+
);
69
tempTbsName VARCHAR2(50);
710
v_count NUMBER;
11+
vPasswd VARCHAR2(20);
812
BEGIN
9-
select distinct tablespace_name
10-
INTO tempTbsName
11-
from dba_temp_files where rownum = 1;
12-
13-
FOR i IN usernames.FIRST .. usernames.LAST
13+
SELECT DISTINCT tablespace_name
14+
INTO tempTbsName
15+
FROM dba_temp_files WHERE ROWNUM = 1;
16+
FOR i IN usernames.FIRST .. usernames.LAST
1417
LOOP
1518
v_count := 0;
19+
vPasswd := DBMS_RANDOM.STRING('a', 1);
20+
21+
FOR i IN 1 .. 11
22+
LOOP
23+
CASE ROUND(DBMS_RANDOM.VALUE(1,5))
24+
WHEN 1 THEN
25+
vPasswd := vPasswd || ROUND(DBMS_RANDOM.VALUE(0, 10));
26+
WHEN 2 THEN
27+
vPasswd := vPasswd || '#';
28+
ELSE
29+
vPasswd := vPasswd || DBMS_RANDOM.STRING('a', 1);
30+
END CASE;
31+
END LOOP;
1632

1733
SELECT COUNT(*)
18-
INTO v_count
19-
FROM DBA_USERS
20-
WHERE USERNAME = usernames(i);
34+
INTO v_count
35+
FROM DBA_USERS
36+
WHERE USERNAME = usernames(i);
2137

2238
IF v_count > 0 THEN
23-
EXECUTE IMMEDIATE 'DROP USER ' || usernames(i) || ' CASCADE';
39+
EXECUTE IMMEDIATE 'DROP USER "' || usernames(i) || '" CASCADE';
2440
END IF;
2541

2642
DBMS_OUTPUT.PUT_LINE('--====>> CREATE USER - ' || usernames(i));
27-
EXECUTE IMMEDIATE 'CREATE USER ' || usernames(i) || ' IDENTIFIED BY C#arm4nder#001 ' ||
28-
'DEFAULT TABLESPACE USERS ' ||
29-
'TEMPORARY TABLESPACE ' || tempTbsName;
30-
EXECUTE IMMEDIATE 'GRANT DBA TO ' || usernames(i);
31-
EXECUTE IMMEDIATE 'GRANT UNLIMITED TABLESPACE TO ' || usernames(i);
32-
EXECUTE IMMEDIATE 'ALTER USER ' || usernames(i) || ' PROFILE ADMINISTRADOR';
43+
44+
EXECUTE IMMEDIATE 'CREATE USER "' || usernames(i) || '" IDENTIFIED BY ' || vPasswd || ' ' ||
45+
'DEFAULT TABLESPACE USERS ' ||
46+
'TEMPORARY TABLESPACE ' || tempTbsName;
47+
EXECUTE IMMEDIATE 'GRANT SELECT_CATALOG_ROLE TO "' || usernames(i) || '"';
48+
EXECUTE IMMEDIATE 'GRANT SELECT ANY DICTIONARY TO "' || usernames(i) || '"';
49+
EXECUTE IMMEDIATE 'GRANT CREATE SESSION TO "' || usernames(i) || '"';
50+
EXECUTE IMMEDIATE 'GRANT RESOURCE TO "' || usernames(i) || '"';
51+
EXECUTE IMMEDIATE 'ALTER USER "' || usernames(i) || '" DEFAULT ROLE ALL';
52+
53+
DBMS_OUTPUT.PUT_LINE('User --> ' || usernames(i) || ' / Pass --> ' || vPasswd);
3354
END LOOP;
3455
END;
3556
/
3657

37-
exit;
58+
--exit;

‎findObject.sql

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,20 @@
1+
-- Script to find a object, its owner and type based on the object name or part of it
2+
3+
SET LINES 170
4+
SET PAGES 9999
5+
COL OWNER FOR A25
6+
COL OBJECT_NAME FOR A30
7+
8+
var v_owner varchar2(30);
9+
10+
exec :v_owner := '&1';
11+
12+
SELECT DISTINCT OWNER,
13+
OBJECT_NAME,
14+
OBJECT_TYPE,
15+
TO_CHAR(CREATED, 'DD/MM/YYYY HH24:MI'),
16+
STATUS
17+
FROM DBA_OBJECTS
18+
WHERE OBJECT_NAME like upper('%' || :v_owner || '%')
19+
ORDER BY 1, 2, 3
20+
;

‎getAsmDGInfo.sql

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,13 @@
1+
-- Get total size and free space of asm diskgroups
2+
3+
SET LINES 180
4+
SET PAGES 9999
5+
SET LONG 100000
6+
set sqlbl on
7+
alter session set nls_numeric_characters=',.';
8+
9+
select name,
10+
round(free_mb/1024, 2) as free_gb,
11+
round(total_mb/1024, 2) as total_gb,
12+
round(free_mb/total_mb*100, 2) as percentage
13+
from v$asm_diskgroup;

‎getCompilationErrors.sql

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,30 @@
1+
set lines 230 pages 5000 long 10000
2+
col owner for a20
3+
col name for a30
4+
col "LIN/POS" for a8
5+
col text for a168
6+
7+
select OWNER,
8+
NAME,
9+
LINE || '/' || POSITION as "LIN/POS",
10+
TEXT
11+
from dba_errors
12+
where owner = 'REPORTWEB'
13+
order by owner, name, SEQUENCE
14+
;
15+
16+
17+
/*
18+
SQL> desc dba_errors
19+
Name Null? Type
20+
----------------------------------------- -------- ----------------------------
21+
OWNER NOT NULL VARCHAR2(30)
22+
NAME NOT NULL VARCHAR2(30)
23+
TYPE VARCHAR2(12)
24+
SEQUENCE NOT NULL NUMBER
25+
LINE NOT NULL NUMBER
26+
POSITION NOT NULL NUMBER
27+
TEXT NOT NULL VARCHAR2(4000)
28+
ATTRIBUTE VARCHAR2(9)
29+
MESSAGE_NUMBER NUMBER
30+
*/

0 commit comments

Comments
 (0)