Find the log switches with following query: SELECT TRUNC (first_time) "Date", TO_CHAR (first_time, 'Dy') "Day",<br>COUNT (1) "Total",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) "h0",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) "h1",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0))…
To create AWR reports based on the automated metrics gathered:Connect to the correct Pluggable: select * from v$pdbs; Find the dbid:select * from v$database;<br> If you want to create manually a AWR snapshot use the following: select sys.dbms_workload_repository.create_snapshot('ALL') from dual;…
with cols as (select s.name,st.name table_name,ac.name column_name,t.name column_type,ac.max_length,ac.precision,ac.scale,casewhen t.name in (‘datetime’,’datetime2′,’date’) then ‘DATE’end oracle_typefrom sys.all_columns ac join sys.types t on t.system_type_id=ac.system_type_idjoin sys.tables st on st.object_id=ac.object_idjoin sys.schemas s on s.schema_id=st.schema_idwhere st.name!=’sysdiagrams’),grp as(select upper(table_name) table_name, col= upper(STUFF ((SELECT ‘,’ + column_name+’ ‘+isnull(oracle_type,”)…
Fast Recovery Area is used for various components such as duplicates of control files and redo logs, backups, archive logs and flashback logs. If a database is in ARCHIVELOG mode and there is no space left in FRA, the database…
DataPump is an excellent tool to transfer data between databases. Since Autonomous Databases don’t provide access to their OS there is a difficulty to use datapump to import or export data. This is bypassed by using a storage bucket. To…
To access the Autonomous Database using CloudShell you need to set the following parameters: export ADBID=ocid1….. –This is the OCID of the Autonomous database. export WNAME=wallet.zip —This is the wallet file. export WPWD=password — This is the password of the…
Use the following statement to see all columns covered by indexes on a table:
Use the following query to find the size of a table including its indexes and LOBs.