-- Oracle Unicode & Hexcode Replace Characters
-- ( Non-Printable ASCII Characters )
-- Define Asci Characters
select ASCIISTR('String or Column') from &&TABLE_NAME ;
-- Replace Asci Charcters From Unicode
select REPLACE(COLUMN_NAME,UNISTR('\xxxx'),'Character which you want to replace') from &&TABLE_NAME;
-- Arrange Column for Search
CREATE OR REPLACE FUNCTION #SCHEMA_NAME#.#FUNCTION_NAME# (sutun VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS
r_sutun varchar2(4000);
BEGIN
select TRANSLATE((nls_upper(TRIM(sutun), 'NLS_SORT = XTURKISH')),'İŞÇĞÜÖ/(),."-:;''][&{}+','ISCGUO') into r_sutun from dual;
RETURN r_sutun;
EXCEPTION
WHEN OTHERS THEN
RETURN '';
END;
/
-- Oracle Give Permissions to Schema Objects to USER or ROLE
select '-- SEQUENCES ----------------------------------------------------------------' as SCRIPT from dual
UNION ALL
select 'grant execute on '||OWNER||'.'||OBJECT_NAME||' to &&USERNAME;' AS SCRIPT from dba_objects where OWNER='&&SCHEMA_NAME' and (OBJECT_TYPE='FUNCTION' OR OBJECT_TYPE='PROCEDURE' OR OBJECT_TYPE='PACKAGE')
UNION ALL
select '-- FUNCTIONS - PROCEDURES - PACKAGES ----------------------------------------' as SCRIPT from dual
UNION ALL
select 'grant select on '||OWNER||'.'||OBJECT_NAME||' to &&USERNAME;' AS SCRIPT from dba_objects where OWNER='&&SCHEMA_NAME' and (OBJECT_TYPE='SEQUENCE')
UNION ALL
select '-- VIEWS --------------------------------------------------------------------' as SCRIPT from dual
UNION ALL
select 'grant select on '||OWNER||'.'||VIEW_NAME||' to &&USERNAME;' AS SCRIPT from DBA_VIEWS where OWNER='&&SCHEMA_NAME'
UNION ALL
select '-- TABLES --------------------------------------------------------------------' as SCRIPT from dual
UNION ALL
select 'grant select on '||OWNER||'.'||TABLE_NAME||' to &&USERNAME;' AS SCRIPT from dba_tables where OWNER='&&SCHEMA_NAME';
-- Oracle List Table In A Schema Order By Number Of Rows Count
select TABLE_NAME,NUM_ROWS,BLOCKS,SAMPLE_SIZE,LAST_ANALYZED,PCT_FREE,PCT_USED from all_tables where OWNER = '&&SCHEMA_NAME' order by NUM_ROWS desc;
-- List Invalid Objects
select OWNER,OBJECT_TYPE,OBJECT_NAME from dba_objects where status = 'INVALID' order by OWNER;
-- Compile Invalid Objects
select 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;' from dba_objects where status = 'INVALID' and object_type in ('PACKAGE','FUNCTION','PROCEDURE');
-- Move Oracle User With Same Password
select dbms_metadata.get_ddl('USER', username) || '/' usercreate from dba_users where username='&&USERNAME';
-------- Compile All Unusable Indexes --------
-- Indexes
SELECT 'alter index '||owner||'.'||index_name||' rebuild tablespace '||tablespace_name ||';' sql_to_rebuild_index FROM dba_indexes WHERE status = 'UNUSABLE';
-- Index partitions
SELECT 'alter index '||index_owner||'.'||index_name ||' rebuild partition '||PARTITION_NAME||' TABLESPACE '||tablespace_name ||';' sql_to_rebuild_index FROM dba_ind_partitions WHERE status = 'UNUSABLE';
-- Index subpartitions
SELECT 'alter index '||index_owner||'.'||index_name ||' rebuild subpartition '||SUBPARTITION_NAME||' TABLESPACE '||tablespace_name ||';' sql_to_rebuild_index FROM dba_ind_subpartitions WHERE status = 'UNUSABLE';
---------------------------------------------
-------- Oracle Move Schema Tables, Indexes and Lob Segments to New Tablespace --------
-- Give Permission
ALTER USER &USERNAME QUOTA UNLIMITED ON &NEWTABLESPACE;
-- Move Tables
SELECT 'ALTER TABLE '||owner||'.'|| table_name|| ' MOVE TABLESPACE &&NEWTABLESPACE COMPRESS NOLOGGING PARALLEL 8;' FROM ALL_TABLES WHERE OWNER = '&&SCHEMA_NAME';
-- Move Indexes
SELECT 'ALTER INDEX ' || owner|| '.' || index_name|| ' REBUILD TABLESPACE &&NEWTABLESPACE COMPRESS NOLOGGING PARALLEL 8;' FROM ALL_INDEXES WHERE OWNER = '&&SCHEMA_NAME';
-- Move Lobs
SELECT 'ALTER TABLE '||owner||'.'|| table_name||' MOVE LOB ('||COLUMN_NAME|| ') STORE AS SECUREFILE (TABLESPACE &&NEWTABLESPACE) COMPRESS NOLOGGING PARALLEL 8;' FROM ALL_LOBS WHERE OWNER = '&&SCHEMA_NAME';
-- Move partitions
SELECT 'ALTER TABLE '|| TABLE_OWNER|| '.' ||table_name||' MOVE PARTITION '||partition_name||' TABLESPACE &&NEWTABLESPACE COMPRESS NOLOGGING PARALLEL 8;' FROM dba_tab_partitions WHERE TABLESPACE_NAME = '&&OLDTABLESPCE';
-- Move lob partition
alter table &TABLE_NAME move partition &PARTITION_NAME lob (&LOB_COLUMN_NAME) store as (tablespace &NEWTABLESPACE);
select 'alter table ' ||TABLE_OWNER||'.'|| table_name || ' move partition "' || partition_name|| '" tablespace &&NEW_TABLESPACE COMPRESS NOLOGGING PARALLEL 8;' "PARTITION_MOVE_SCRIPT" from ALL_TAB_PARTITIONS where table_name='&&TABLE_NAME';
---------------------------------------------
Etiketler :
Oracle