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; 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,”)…
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=’sysname’ then ‘nvarchar2(128)’when t.name=’nvarchar’ then ‘nvarchar2(‘+cast(ac.max_length as varchar)+’)’when t.name=’varchar’ then ‘varchar2(‘+cast(ac.max_length as varchar)+’)’when t.name=’char’ then ‘char(‘+cast(ac.max_length as varchar)+’)’when t.name=’int’ then ‘number(10)’when t.name=’bigint’ then ‘number(19)’when t.name in (‘float’,’real’) then ‘number’when t.name=’smallint’ then ‘number(5)’when t.name=’tinyint’ then ‘number(3)’when…
A database in ARCHIVELOG mode copies redo log entries in the achive log files. Archive log number and size is a metric of a database transactions load in time. Use the following query to monitor the Archive log production per…
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…
Use the following statements to find partitioned tables with non aligned indexes. Unaligned indexes forbid partition switch.
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: