第75/76/77/78次：2016-01-30双休日

botang · 发表于 2016-1-31 21:39:00

2016-01-30.sql ：

select * from dba_advisor_templates;
select * from dba_views v where v.view_name='DBA_ADVISOR_TEMPLATES';
select * from wri$_adv_tasks where owner_name='SH';
select * from v$temp_extent_pool;
select * from v$temp_extent_map;
select tf.file_name , bytes/1024/1024, tf.autoextensible from dba_temp_files tf ;
alter database tempfile '+NEWDATA/orcl/tempfile/temp2.285.902486201' autoextend on;
select * from v$sql where sql_text like '%sales%';
select * from v_$session s where s.SQL_ID='6ut95d6thg2nu';
alter tablespace temp shrink space;
alter system kill session '23,292' immediate ;
---
select * from dba_temp_files;
create temporary tablespace temp1 tempfile size 10M tablespace group tempgroup;
create temporary tablespace temp2 tempfile size 10M tablespace group tempgroup;
select * from database_properties;
alter database default temporary tablespace tempgroup;
select * from tab$ where obj# in (
select object_id from dba_objects t where t.object_name like 'GTEMP%');
select * from v$tablespace;

复制代码

2016-01-30b.sql：

select * from dba_advisor_templates;
select * from dba_views v where v.view_name='DBA_ADVISOR_TEMPLATES';
select * from wri$_adv_tasks where owner_name='SH';
select * from v$temp_extent_pool;
select * from v$temp_extent_map;
select tf.file_name , bytes/1024/1024, tf.autoextensible from dba_temp_files tf ;
alter database tempfile '+NEWDATA/orcl/tempfile/temp2.285.902486201' autoextend on;
select * from v$sql where sql_text like '%sales%';
select * from v_$session s where s.SQL_ID='6ut95d6thg2nu';
alter tablespace temp shrink space;
alter system kill session '23,292' immediate ;
---
select * from dba_temp_files;
create temporary tablespace temp1 tempfile size 10M tablespace group tempgroup;
create temporary tablespace temp2 tempfile size 10M tablespace group tempgroup;
select * from database_properties;
alter database default temporary tablespace tempgroup;
select * from tab$ where obj# in (
select object_id from dba_objects t where t.object_name like 'GTEMP%');
select * from v$tablespace;
------
select * from dba_indexes i where i.blevel = 3;
---
CREATE TABLE "HR"."IOT1" ( "A" NUMBER, "B" VARCHAR2(20), "C" VARCHAR2(20),
CONSTRAINT "IOT1_PK" PRIMARY KEY ("A") VALIDATE )
ORGANIZATION INDEX
MAPPING TABLE
PCTTHRESHOLD 10
INCLUDING "B" OVERFLOW TABLESPACE "TBS_BASIC";
select * from dba_tables t where t.table_name='IOT1';
select * from dba_segments s where s.segment_name='IOT1_PK';
select * from dba_indexes i where i.owner='HR' and i.index_name='ICLUSTER1';
select * from dba_objects o where o.object_name='IOT1';
select * from dba_tables t where t.table_name like '%75157%';
select * from hr.SYS_IOT_MAP_75157;
select * from dba_clusters c where c.owner <> 'SYS';
select * from dba_tables t where t.owner='HR' and t.table_name like 'ORDER%';
select * from dba_segments s where s.owner='HR' and s.segment_name like 'ORDER%';
select * from dba_segments s where s.owner='HR' and s.segment_name='CLUSTER1';
----
select * from dba_indexes i where i.table_name='TBIG';
---
select * from dba_data_files where tablespace_name='USERS';
---
select * from dba_Segments s where s.segment_name='IPART1';
-----
create table sh.PROD_MASTER
(
PROD_ID number,
CUST_ID number,
TIME_ID date ,
CHANNEL_ID char(1) ,
PROMO_ID number ,
QUANTITY_SOLD number,
AMOUNT_SOLD number(8,2)
);
drop table sh.PROD_MASTER;
create directory dir1 as '/home/oracle/dir1';
create directory dir2 as '/home/oracle/dir2';
grant read,write on directory dir1 to sh;
grant all on directory dir2 to sh;
select * from dba_external_locations l where l.table_name='PROD_MASTER';
select t.table_name , t.degree
from dba_tables t where t.owner='SH' and t.table_name in ('SALES','CUSTOMERS');
SQL> select CUST_LAST_NAME, sum(AMOUNT_SOLD)
2 from sales s, customers c
3 where s.CUST_ID=c.CUST_ID
4 group by CUST_LAST_NAME ;
select * from v$io_calibration_status;

复制代码

2016-01-31.sql：

CREATE TABLE sales3 ( prod_id NUMBER(6) NOT NULL,
cust_id NUMBER NOT NULL,
time_id DATE NOT NULL,
channel_id char(1) NOT NULL,
promo_id NUMBER (6) NOT NULL,
quantity_sold NUMBER(3) NOT NULL,
amount_sold NUMBER(10,2) NOT NULL )
PARTITION BY RANGE (time_id)
SUBPARTITION BY RANGE (cust_id)
SUBPARTITION TEMPLATE
( SUBPARTITION sp1 VALUES LESS THAN (50000),
SUBPARTITION sp2 VALUES LESS THAN (100000),
SUBPARTITION sp3 VALUES LESS THAN (150000),
SUBPARTITION sp4 VALUES LESS THAN (MAXVALUE) )
(
PARTITION p1 VALUES LESS THAN (TO_DATE('1-4-2007','DD-MM-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2007','DD-MM-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('1-8-2007','DD-MM-YYYY')),
PARTITION p4 VALUES LESS THAN (TO_DATE('1-1-2008','DD-MM-YYYY'))
);
select * from user_segments s where s.segment_name='SALES3';
---
---
select * from dba_tablespaces;
---
create table tbasic_s ( a varchar2(20) encrypt no salt ,
b blob )
lob(b) store as (tablespace example
disable storage in row
chunk 16384 storage (initial 2M ) nocache );
select s.segment_name, bytes/1024/1024
from user_segments s where s.segment_name in ('SYS_LOB0000075344C00002
,
'SYS_LOB0000075364C00002
);
select * from tbasic;
declare
v_f bfile;
v_b blob;
begin
insert into tsecure2 values ('a.dat',empty_blob()) return b into v_b;
v_f := bfilename('DIR1','a.dat');
dbms_lob.fileopen(v_f,dbms_lob.file_readonly);
dbms_lob.loadfromfile(dest_lob => v_b,
src_lob => v_f,
amount => dbms_lob.getlength(v_f) );
dbms_lob.fileclose(v_f);
commit;
end;
select * from tbasic;
select * from user_lobs;
select * from tsecure;
----
create table tsecure5 ( a varchar2(20),
b blob )
lob(b) store as securefile (tablespace example
disable storage in row
nocache compress deduplicate );
---
create table tsecure_s2 ( a varchar2(20),
b blob encrypt using '3des168' )
lob(b) store as securefile (tablespace example
disable storage in row
nocache compress high deduplicate );
select * from user_encrypted_columns;
create table tsecure2 ( a varchar2(20),
b blob )
lob(b) store as securefile (tablespace example
disable storage in row
nocache compress high keep_duplicates );
alter table tsecure2 modify lob(b) (nocompress);

复制代码