Sql tricks

select 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO INFA_REPO_RO_ROLE;' from dba_objects where OWNER='INFA_REPO' and object_type !='INDEX';

select 'GRANT SELECT ON '||OWNER||'.'||TABLE_NAME||' TO HCA_CAS_QA;' from dba_tables where OWNER='CA_SASQA';


BEGIN
    FOR t IN (SELECT * FROM user_tables where OWNER='HCA_READONLY')
    LOOP
        EXECUTE IMMEDIATE 'GRANT SELECT ON ' || t.table_name || ' TO b';  
    END LOOP;
END;


select 'grant '||PRIVILEGE||' on '||OWNER||'.'||TABLE_NAME||' to '||GRANTEE||';' from dba_tab_privs where table_name='STG_WEREGP';


select 'alter database rename file '''||MEMBER||''' to ''+REDO'';' from v$logfile;

select 'Alter SEQUENCE  '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME||' NOCACHE ORDER;' from all_sequences where SEQUENCE_OWNER='UVODS';


select 'alter user '||USERNAME||' temporary tablespace temp1;' from dba_users where USERNAME='HMA_PROD';

select 'Alter index  '||owner||'.'||INDEX_NAME||' REBUILD;' from dba_indexes where status='UNUSABLE';

select 'Alter table  '||owner||'.'||TABLE_NAME||' NOPARALLEL;' from dba_tables where degree!=1;

*******************************************

select 'Alter table  '||owner||'.'||TABLE_NAME||' NOPARALLEL;' from dba_tables where ltrim(degree) != '1';

select 'Alter index  '||owner||'.'||INDEX_NAME||' NOPARALLEL;' from dba_indexes where ltrim(degree) != '1' and index_type !='LOB';




Find mviews with logging

select b.mview_name,a.logging from dba_tables a inner join dba_mviews b on a.table_name=b.mview_name where a.owner='HMA_DM_SERVICE' and a.logging='YES'

Changing mviews from logging to nologging

select 'ALTER MATERIALIZED VIEW '||a.OWNER||'.'||b.mview_name||' nologging;' from dba_tables a inner join dba_mviews b on a.table_name=b.mview_name where a.owner='HMA_DM_SERVICE' and a.logging='YES';


Insert 10000 records in to a table

create table testing ( id number);

insert into testing(id)
select a.S1+level
   from dual,(select nvl(max(id),0) S1 from testing) a
connect by level <= 100000;

commit;

select 'Alter table  '||owner||'.'||TABLE_NAME||' MOVE LOB ('||COLUMN_NAME||') store as ( tablespace kma_edq_data);' from dba_LOBS where owner like 'KMAEDQ%'


SELECT 'ALTER TABLE HDD_DM_USER.' || OBJECT_NAME ||' MOVE TABLESPACE '||' HDD_DM_DATA; '
FROM ALL_OBJECTS
WHERE OWNER = 'HDD_DM_USER'
AND OBJECT_TYPE = 'TABLE' ;

select 'alter index '||owner||'.'||index_name||' rebuild tablespace HDD_DM_INDEX;' from all_indexes where owner='HDD_DM_USER';

select 'alter table '||owner||'.'||table_name||' MOVE LOB('||COLUMN_NAME||') STORE AS (tablespace HDD_DM_DATA);' from dba_lobs where owner='HDD_DM_USER';

Unlock table stats of all tables

select 'exec dbms_stats.unlock_table_stats (''HMA_STAGE'', '''||table_name||''');' from dba_tables where owner='HMA_STAGE';