Skip to main content

Posts

How to get DDL of user and grants

Use DBMS_METADATA to extract the complete DDL (metadata) of an Oracle user, including system, object and role grants. This is handy when cloning a user or migrating it to another database. 1. User DDL set long 999999 select dbms_metadata.get_ddl('USER','<username>') from dual; 2. System privilege grants select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','<username>') from dual; 3. Object grants select dbms_metadata.get_granted_ddl('OBJECT_GRANT','<username>') from dual; 4. Role grants select dbms_metadata.get_granted_ddl('ROLE_GRANT','<username>') from dual; Tip: run set long 999999 and set pagesize 0 first so the output isn't truncated. If a grant type has no rows, you'll get ORA-31608; that just means the user has none of that type.
Recent posts

How to perform GRID/RDBMS patching of DB systems deployed in OCI using dbcli command line utility

This procedure covers OS, Grid and RDBMS home patching for Oracle Cloud Infrastructure (OCI) DB systems using the dbcli utility. Run every step as root . Advantages of dbcli patching Simple command line, with no manual patch download or copy to the nodes. Patches are downloaded automatically if the service gateway can reach Oracle Object Storage and the YUM repos. Applied in a rolling fashion, so no downtime is needed. Disadvantages You can't choose the patch version; the latest available in OCI is applied. Rollback isn't possible from the console, and dbcli support is limited (see the note at the end). Steps 1. Backup Make sure a recent full backup exists before patching. 2. Blackout Create a blackout for both nodes (console or emctl ) to avoid unnecessary alerts. 3. Check patch availability # dbcli describe-component 4. Pre-checks Grid home: # dbcli update-server -p # dbcli describe-job -i <jobId> RDBMS home: # dbcli list-dbhomes...

How to get SQL ID STAT information in Oracle Database 12c

 How to get SQL ID STAT information in Oracle Database 12c Please use the following Query to get the information of SQL ID statistics in oracle database. It will provide you the information about SQL ID and their Plan hash value information along with execution details. Don't forget to remove comments and format the SQL before running. WITH sql_id_list AS ( SELECT /*+ MATERIALIZE */ regexp_substr(:SQL_ID, '[^,]+', 1, LEVEL) SQL_ID   FROM dual CONNECT BY LEVEL <= length(:SQL_ID) - length(REPLACE(:SQL_ID, ',', '')) + 1 ), sqlstat AS ( SELECT TRUNC(last_active_Time,:TRUNC_DATE_FORMAT) last_active_Time       --,s.instance_number inst_num       ,s.sql_id       ,s.plan_hash_value       ,MAX(s.child_number) max_child_number       ,command_type       ,parsing_schema_name schema       --,module       ,ROUND((SUM(s.elapsed_time)/1000000)/60,2) elapsed_time_minutes ...

How to get SQL Standard Deviation information of SQL ID in Oracle 12c

 How to get SQL Standard Deviation information of SQL ID in Oracle Database. Please user the below SQL to get the information of SQL Standard Deviation information. Don't forget to remove comment lines before running and formatting the sql. WITH sqlstat AS ( SELECT TRUNC(h.begin_interval_time,:TRUNC_DATE_FORMAT) snap_time  -- Change to MI, HH , or DD for a individual sql statement to checking performance over time       ,s.instance_number inst_num       --,MIN(h.begin_interval_time) min_start_date       --,MAX(h.begin_interval_time) max_end_date       --,MAX(s.snap_id) max_snap_id       ,s.sql_id       ,s.plan_hash_value       ,parsing_schema_name schema       ,module       ,ROUND((SUM(s.elapsed_time_delta)/1000000)/60,2) elapsed_time_minutes       ,ROUND(SUM(s.elapsed_time_delta)/1000000,2) elapsed_time_seconds     ...

How to Move objects from one tablespace to another tablespace online

How to Move objects from one tablespace to another tablespace online using procedure. set serveroutput on set lines 300 DECLARE V_CURRENT_TABLESPACE VARCHAR2(50); V_DEST_TABLESPACE VARCHAR2(50); V_SQL VARCHAR2(1000); V_OVERFLOW NUMBER := 0; BEGIN V_CURRENT_TABLESPACE := '&current_tablespace'; V_DEST_TABLESPACE := '&destination_tablespace'; FOR LIST_OWNER IN (SELECT DISTINCT OWNER FROM DBA_SEGMENTS WHERE TABLESPACE_NAME=V_CURRENT_TABLESPACE and OWNER='&my_schema') LOOP DBMS_OUTPUT.PUT_LINE('MOVING TABLES:'); DBMS_OUTPUT.PUT_LINE('------------------'); FOR LIST_TABLES IN (SELECT TABLE_NAME, IOT_NAME FROM DBA_TABLES WHERE OWNER=LIST_OWNER.OWNER AND TABLESPACE_NAME=V_CURRENT_TABLESPACE) LOOP IF  LIST_TABLES.IOT_NAME IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE('MOVING INDEX ORGANIZED TABLE ' || LIST_TABLES.IOT_NAME || '...'); V_SQL := 'ALTER TABLE "' || LIST_OWNER.OWNER  ...