DBA Operation SQL Queries

-- 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';
---------------------------------------------