Random SQL Scratchpad

Random SQL scratchpad

DISCLAIMER - the following are presented for informational purposes only and are not supported in any way. Do not run these queries on a production system without fully testing them first! Do not run these queries without fully understanding what they do! Please do not contact Client Support about these queries - though you are welcome to comment on this article if you have questions. Thank you.

Queries to get an overview of data in activity_accumulator:

create global temporary table aatemp (timestamp varchar2(30));
insert into aatemp select to_char(timestamp,'YYYY-MM-DD') from activity_accumulator;
select count(timestamp),timestamp from aatemp group by timestamp;
drop table aatemp;

Much better way, using a view:

create view aatemp (timestamp) as select to_char(timestamp,'YYYY-MM-DD') from activity_accumulator;
select count(timestamp),timestamp from aatemp group by timestamp;
drop view aatemp;

Query to get hour-by-hour hits from the AA:

select to_char(timestamp,'HH24') as hour,
count(to_char(timestamp,'HH24')) as hits
from activity_accumulator where timestamp
between '02-AUG-2007' AND '03-AUG-2007'
group by to_char(timestamp,'HH24')
order by hour;

Simple PL / SQL loop, which I used to delete users and courses:

BEGIN
FOR MYID IN (SELECT PK1 FROM USERS WHERE PK1 > 14)
LOOP
USERS_RM(MYID.PK1);
COMMIT;
END LOOP;
END;
/

Same as above, but for T-SQL (MS SQL Server):

USE BB_BB60
GO
BEGIN
DECLARE @v_pk1 NUMERIC;
DECLARE C1 CURSOR FOR
SELECT PK1 FROM USERS WHERE PK1 > 14 and PK1 < 20;
OPEN C1
FETCH NEXT from C1 INTO  @v_pk1;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC users_rm @p1=@v_pk1;
FETCH  NEXT from C1 INTO  @v_pk1;
END
CLOSE C1
DEALLOCATE C1
END;
GO

Loop to run a statement on multiple VIs:

DECLARE vSQL VARCHAR2(200);
v_val VARCHAR2(20);
BEGIN
FOR MYID IN (select bbuid from bbadmin.bb_instance)
LOOP
vSQL := 'select registry_value from ' \|\| myid.bbuid \|\| '.system_registry where registry_key=''release_number'' ';
execute immediate vSQL into v_val;
dbms_output.put_line(myid.bbuid \|\| ' - ' \|\| v_val);
END LOOP;
END;
/

Stuff to show discussion boards with more than 200 posts (Bb 7.0 and previous?):

create view ctemp as select count(pk1) as thecount,forummain_pk1
from msg_main group by forummain_pk1 order by thecount;
select course_main.pk1 as crspk1,course_id,forum_main.name
from course_main,conference_main,forum_main
where forum_main.confmain_pk1=conference_main.pk1
AND conference_main.crsmain_pk1 = course_main.pk1
AND forum_main.pk1 IN (select forummain_pk1 from ctemp where thecount > 200)
order by course_id;
drop view ctemp;

Stuff to show courses with high numbers of gradebook main entries and enrollments:

create view gbtemp as select count(pk1) as thecount,crsmain_pk1 from gradebook_main group by crsmain_pk1;
create view cutemp as select count(pk1) as thecount,crsmain_pk1 from course_users group by crsmain_pk1;

SELECT course_main.pk1 as cm_pk1,course_id,gbtemp.thecount AS gbmain_count,cutemp.thecount AS enroll_count
FROM course_main,gbtemp,cutemp
WHERE course_main.pk1 = gbtemp.crsmain_pk1
AND course_main.pk1 = cutemp.crsmain_pk1
AND gbtemp.thecount > 10
AND cutemp.thecount > 20
ORDER BY course_id;

drop view gbtemp;
drop view cutemp;

Lots of quota-related queries

Query to list out courses by disk usage with the disk_usage registry key:

SELECT     course_main.course_id, course_registry.registry_key, course_registry.registry_value
FROM         course_main, course_registry WHERE course_main.pk1 = course_registry.crsmain_pk1
AND        (course_registry.registry_key = 'disk_usage') AND (course_registry.registry_value > 0)
ORDER BY registry_value DESC

Same query as above but show in kb:

select course_id,registry_value,registry_value/1024 as disk_usage_kb from course_main
left outer join course_registry
on course_main.pk1 = course_registry.crsmain_pk1
where registry_key='disk_usage'
order by disk_usage_kb desc

The following will show courses which have a quota override value set, along with the values of the override and the current disk usage:

select course_id,registry_value/1024 as disk_usage_kb,abs_limit/1024 as abs_kb,soft_limit/1024 as soft_kb
from course_main left outer join course_registry
on course_main.pk1 = course_registry.crsmain_pk1
where registry_key='disk_usage' and crsmain_pk1 in (select crsmain_pk1 from course_registry where registry_key='quota_override' and registry_value='Y')
order by disk_usage_kb desc

The following will show courses that do not have a quota override set, yet are in violation of the default quotas (absolute limit). Note - this ignores organizations.

select cm.course_id,cr.registry_value,cr.registry_value/1024 as disk_usage_kb,sr.registry_value/1024 as sys_abs_kb
from system_registry sr,course_main cm
left outer join course_registry cr
on cm.pk1 = cr.crsmain_pk1
where cr.registry_key='disk_usage'
and sr.registry_key='Fquota_abs_limit'
and  cr.registry_value > cast(sr.registry_value as int)
and cm.service_level='F'
and crsmain_pk1 not in (select crsmain_pk1 from course_registry where registry_key='quota_override' and registry_value='Y')
order by disk_usage_kb desc

If you want to see this for the soft limit instead of absolute limit replace 'Fquota_abs_limit' above with 'Fquota_soft_limit' .

Rebuilding Indexes

PL/SQL loop to rebuild indexes: Log in as bb_bb60 and execute this:

DECLARE
STMT VARCHAR2(100);
BEGIN
FOR cur in (select index_name from user_indexes where index_type='NORMAL' and index_name not like 'BIN$%' )
LOOP
STMT := 'alter index '\|\|cur.index_name\|\|' rebuild online';
execute immediate STMT;
END LOOP;
END;
/

Page Operations

Browse Space

Random SQL Scratchpad

Labels: