USE
GO
DBCC SHRINKFILE (<logical name>, size)
GO
—- Exemplo 1
USE MYBD
GO
DBCC SHRINKFILE (MYBD_Data, 65426)
GO
—- Exemplo 2
DBCC SHRINKFILE ( databasename_Log, 1)
obs:
Para saber o logical name, connecte na base e execute o comando sp_helpfile.
use MYDB
go
sp_helpfile
------------------>TAMANHO TABLESPACES set lines 132 set pages 105 set pause off set echo off set feedb on column "TOTAL ALLOC (MB)" format 9,999,990.00 column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00 column "USED (MB)" format 9,999,990.00 column "FREE (MB)" format 9,999,990.00 column "% USED" format 990.00 select a.tablespace_name, a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)", a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)", nvl(b.tot_used,0)/(1024*1024) "USED (MB)", (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED" from (select tablespace_name, sum(bytes) physical_bytes, sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc from dba_data_files group by tablespace_name ) a, (select tablespace_name, sum(bytes) tot_used from dba_segments group by tablespace_name ) b where a.tablespace_name = b.tablespace_name (+) and a.tablespace_name not in (select distinct tablespace_name from dba_temp_files) and a.tablespace_name not like 'UNDO%' order by 1;
————————–
—– Partial List of Index Statistics for the AdventureWorks Database
SELECT object_id AS ObjectID,
index_id AS IndexID,
avg_fragmentation_in_percent AS PercentFragment,
fragment_count AS TotalFrags,
avg_fragment_size_in_pages AS PagesPerFrag,
page_count AS NumPages
FROM sys.dm_db_index_physical_stats(DB_ID(‘AdventureWorks’),
NULL, NULL, NULL , ‘DETAILED’)
WHERE avg_fragmentation_in_percent > 0
ORDER BY ObjectID, IndexID
—————————
—- List of Index Statistics for the Sales.StoreContact Table in the AdventureWorks Database
SELECT b.name AS IndexName,
a.avg_fragmentation_in_percent AS PercentFragment,
a.fragment_count AS TotalFrags,
a.avg_fragment_size_in_pages AS PagesPerFrag,
a.page_count AS NumPages
FROM sys.dm_db_index_physical_stats(DB_ID(‘AdventureWorks’),
OBJECT_ID(‘Sales.StoreContact’), NULL, NULL , ‘DETAILED’) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id
AND a.index_id = b.index_id
WHERE a.avg_fragmentation_in_percent > 0
ORDER BY IndexName
——–
—– In the following SELECT statement, I return statistics on a specific index in the StoreContact table:
DECLARE @IndexName VARCHAR(100)
SET @IndexName = ‘PK_StoreContact_CustomerID_ContactID’
DECLARE @IndexID SMALLINT
SET @IndexID =
(SELECT index_id FROM sys.indexes
WHERE name = @IndexName)
SELECT @IndexName AS IndexName,
avg_fragmentation_in_percent AS PercentFragment,
fragment_count AS TotalFrags,
avg_fragment_size_in_pages AS PagesPerFrag,
page_count AS NumPages
FROM sys.dm_db_index_physical_stats(DB_ID(‘AdventureWorks’),
OBJECT_ID(‘Sales.StoreContact’),
@IndexID, NULL , ‘DETAILED’)
WHERE avg_fragmentation_in_percent > 0
—————
—- To reorganize an index, run an ALTER INDEX statement and include the keyword REORGANIZE, as shown in the following example:
ALTER INDEX PK_StoreContact_CustomerID_ContactID
ON AdventureWorks.Sales.StoreContact
REORGANIZE
——- Rebuilding Indexes
— rebuild a index
ALTER INDEX IX_StoreContact_ContactID
ON AdventureWorks.Sales.StoreContact
REBUILD
— rebuild all index of a table
ALTER INDEX ALL
ON [Sales].[DefragSalesOrderDetail] Rebuild
go
—-
ALTER INDEX IX_StoreContact_ContactTypeID
ON AdventureWorks.Sales.StoreContact
REBUILD WITH (
FILLFACTOR = 70,
ONLINE = ON
)
—-
CREATE UNIQUE NONCLUSTERED INDEX AK_StoreContact_rowguid
ON AdventureWorks.Sales.StoreContact (rowguid ASC)
WITH (
DROP_EXISTING = ON,
FILLFACTOR = 70,
ONLINE = ON
) ON [PRIMARY]
———————–
———————–
EXECUTE master.dbo.IndexOptimize @Databases = ‘MyDatabase’
go
———————–
———————–
/* Originally created by Microsoft */
/* Error corrected by Pinal Dave (http://www.SQLAuthority.com) */
— Specify your Database Name
USE AdventureWorks;
GO
— Declare variables
SET NOCOUNT ON;
DECLARE @tablename VARCHAR(128);
DECLARE @execstr VARCHAR(255);
DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @frag decimal;
DECLARE @maxfrag decimal;
— Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0;
— Declare a cursor.
DECLARE tables CURSOR FOR
SELECT CAST(TABLE_SCHEMA AS VARCHAR(100))+’.’+CAST(TABLE_NAME AS VARCHAR(100)) AS Table_Name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’;
— Create the table.
CREATE TABLE #fraglist (ObjectName CHAR(255),ObjectId INT,IndexName CHAR(255),IndexId INT,Lvl INT,CountPages INT,CountRows INT,MinRecSize INT,MaxRecSize INT,AvgRecSize INT,ForRecCount INT,Extents INT,
ExtentSwitches INT,AvgFreeBytes INT,AvgPageDensity INT,ScanDensity decimal,BestCount INT,ActualCount INT,LogicalFrag decimal,ExtentFrag decimal);
— Open the cursor.
OPEN tables;
— Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @tablename;
WHILE @@FETCH_STATUS = 0
BEGIN;
— Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC (‘DBCC SHOWCONTIG (”’ + @tablename + ”’) WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS’);
FETCH NEXT
FROM tables
INTO @tablename;
END;
— Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;
— Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag FROM #fraglist WHERE LogicalFrag >= @maxfrag AND INDEXPROPERTY (ObjectId, IndexName, ‘IndexDepth’) > 0;
— Open the cursor.
OPEN indexes;
— Loop through the indexes.
FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag;
WHILE @@FETCH_STATUS = 0
BEGIN;
PRINT ‘Executing DBCC INDEXDEFRAG (0, ‘ + RTRIM(@tablename) + ‘,’ + RTRIM(@indexid) + ‘) – fragmentation currently ‘+ RTRIM(CONVERT(VARCHAR(15),@frag)) + ‘%’;
SELECT @execstr = ‘DBCC INDEXDEFRAG (0, ‘ + RTRIM(@objectid) + ‘,’ + RTRIM(@indexid) + ‘)’;
EXEC (@execstr);
FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag;
END;
— Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
— Delete the temporary table.
DROP TABLE #fraglist;
GO
===========================================
===> Partitions
—- To determine if a table is partitioned
SELECT *
FROM sys.tables AS t
JOIN sys.indexes AS i
ON t.[object_id] = i.[object_id]
AND i.[type] IN (0,1)
JOIN sys.partition_schemes ps
ON i.data_space_id = ps.data_space_id
WHERE t.name = ‘PartitionTable’;
GO
—- To determine the boundary values for a partitioned table
SELECT t.name AS TableName, i.name AS IndexName, p.partition_number, p.partition_id, i.data_space_id, f.function_id, f.type_desc, r.boundary_id, r.value AS BoundaryValue
FROM sys.tables AS t
JOIN sys.indexes AS i
ON t.object_id = i.object_id
JOIN sys.partitions AS p
ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.partition_schemes AS s
ON i.data_space_id = s.data_space_id
JOIN sys.partition_functions AS f
ON s.function_id = f.function_id
LEFT JOIN sys.partition_range_values AS r
ON f.function_id = r.function_id and r.boundary_id = p.partition_number
WHERE t.name = ‘PartitionTable’ AND i.type <= 1
ORDER BY p.partition_number;
—- To determine the partition column for a partitioned table
SELECT
t.[object_id] AS ObjectID
, t.name AS TableName
, ic.column_id AS PartitioningColumnID
, c.name AS PartitioningColumnName
FROM sys.tables AS t
JOIN sys.indexes AS i
ON t.[object_id] = i.[object_id]
AND i.[type] <= 1 — clustered index or a heap
JOIN sys.partition_schemes AS ps
ON ps.data_space_id = i.data_space_id
JOIN sys.index_columns AS ic
ON ic.[object_id] = i.[object_id]
AND ic.index_id = i.index_id
AND ic.partition_ordinal >= 1 — because 0 = non-partitioning column
JOIN sys.columns AS c
ON t.[object_id] = c.[object_id]
AND ic.column_id = c.column_id
WHERE t.name = ‘PartitionTable’ ;
GO
— Ex:
ALTER PARTITION SCHEME ps_quinzenal NEXT USED FGDATA1;
ALTER PARTITION FUNCTION pf_quinzenal() SPLIT RANGE (‘2017-03-15 00:00:00’);
go
ALTER PARTITION SCHEME ps_quinzenal NEXT USED FGDATA2;
ALTER PARTITION FUNCTION pf_quinzenal() SPLIT RANGE (‘2017-04-01 00:00:00’);
go
https://technet.microsoft.com/pt-br/library/ms188730(v=sql.110).aspx
========================================= FIM PARTITIONS
—– Informações sobre coletas de estáticas de um schema
select
‘ – OWNER -> ‘||OWNER,
‘ – TABLE_NAME -> ‘||TABLE_NAME,
‘ – LAST_ANALYZED -> ‘||to_char(LAST_ANALYZED,’YYYY/MM/DD HH24:MI:SS’)
FROM SYS.DBA_TAB_STATISTICS where owner = ‘ORCL’;
—- gather tabelas particionadas
EXEC DBMS_STATS.GATHER_TABLE_STATS (‘SCOTT’, ‘MENSAL’, ‘JAN_2016’, GRANULARITY => ‘PARTITION’);
EXEC DBMS_STATS.GATHER_TABLE_STATS (‘SCOTT’, ‘MENSAL’, ‘FEV_2016’, GRANULARITY => ‘PARTITION’);
—- coleta estatísticas de um determinado schema e/ou de toda a base
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => ‘SCOTT’);
exec dbms_stats.gather_database_stats;
—- Criando um Job para coletar estatísticas diariamente
begin
dbms_scheduler.create_job(job_name => ‘SCOTT.JOB_COLETA_STATS_DB_FPW’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘begin DBMS_STATS.GATHER_SCHEMA_STATS(ownname => ”SCOTT”); end;’,
start_date => SYSTIMESTAMP,
repeat_interval => ‘FREQ=DAILY; BYHOUR=03;BYMINUTE=57’,
enabled => TRUE,
auto_drop => FALSE,
comments => ‘Cria estatisticas para o database ORCL schema SCOTT todos os dias as 03:57 AM’);
end;
/
—– Criando um job para coletar a estatística de todo o database diariamente
begin
dbms_scheduler.create_job(job_name => ‘JOB_COLETA_STATS_DB’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘begin dbms_stats.gather_database_stats; end;’,
start_date => SYSTIMESTAMP,
repeat_interval => ‘FREQ=DAILY; BYHOUR=22;’,
enabled => TRUE,
auto_drop => FALSE,
comments => ‘Cria estatisticas para o database’);
end;
/
set long 30
select substr(tp.TABLE_OWNER,1,25) “Owner”, tp.TABLE_NAME, substr(tp.PARTITION_NAME,1,28) “Partition name”, tp.HIGH_VALUE, substr(tp.TABLESPACE_NAME,1,28) “Tablespace” from dba_tab_partitions tp where tp.PARTITION_NAME = (select max(x.partition_name)
from dba_tab_partitions x
where x.table_owner = tp.table_owner and x.table_name = tp.table_name)
and tp.table_owner <> ‘SYSTEM’
order by table_owner, table_name, partition_name;
alter session set nls_date_format=’dd/mm/yy hh24:mi:ss’ ;
set lines 450 pages 1000
col file_name format a70 ;
col member format a70 ;
obs:
Essas configurações podem ser adicionados no arquivo $ORACLE_HOME/sqlplus/glogin.sql , assim todas as vezes que houver uma query com campo tipo date, será exibido no format conforme acima (‘dd/mm/yy hh24:mi:ss’)
—- abaixo utilizado para o RMAN
export NLS_DATE_FORMAT=’dd/mm/yy hh24:mi:ss’
Obs:
Esse export pode ser adicionado no arquivo ~/.bash_profile (/home/oracle/.bash_profile)
—- outros
SQL> SELECT * FROM NLS_DATABASE_PARAMETERS ;
Abaixo segue exemplo de como trocar o endereço(single name) do SCAN:
Testando a resolução do novo single name utilizando o nslookup:
| 1
2 3 4 5 6 7 8 |
[root@teste01 ~]$ nslookup oracle-scantst2
Server: 192.168.250.203 Address: 192.168.250.203#53 Name: oracle-scantst2.oraclehome.com.br Address: 192.168.250.197 Name: oracle-scantst2.oraclehome.com.br Address: 192.168.250.198 |
Logado com o usuário GRID:
| 1
2 3 4 5 |
GRID-> srvctl config scan
SCAN name: oracle-scantst, Network: 1/192.168.250.0/255.255.255.0/bond0 SCAN VIP name: scan1, IP: /oracle-scantst/192.168.250.196 SCAN VIP name: scan2, IP: /oracle-scantst/192.168.250.195 GRID-> |
Parar o scan e scan listener:
| 1
2 3 4 5 6 7 |
GRID-> srvctl stop scan_listener
GRID-> srvctl stop scan GRID-> srvctl status scan SCAN VIP scan1 is enabled SCAN VIP scan1 is not running SCAN VIP scan2 is enabled SCAN VIP scan2 is not running |
Logado como ROOT alterar o endereço “single name“:
| 1 | ROOT-> srvctl modify scan -n oracle-scantst2 |
Logando novamente como GRID, verificar as alterações:
| 1
2 3 4 |
GRID-> srvctl config scan
SCAN name: oracle-scantst2, Network: 1/192.168.250.0/255.255.255.0/bond0 SCAN VIP name: scan1, IP: /oracle-scantst2/192.168.250.198 SCAN VIP name: scan2, IP: /oracle-scantst2/192.168.250.197 |
Efetuar o update da configuração do serviço (-u) e iniciar:
| 1
2 3 4 5 6 7 8 |
GRID-> srvctl modify scan_listener -u
GRID-> srvctl start scan_listener GRID-> srvctl status scan SCAN VIP scan1 is enabled SCAN VIP scan1 is running on node teste02 SCAN VIP scan2 is enabled SCAN VIP scan2 is running on node teste01 GRID-> |
fontes :
http://www.oraclehome.com.br/2015/03/16/alterando-o-endereco-do-scan-listener/
# crontab -l ############ crontab # Example of job definition: # .---------------- minute (0 - 59) # | .------------- hour (0 - 23) # | | .---------- day of month (1 - 31) # | | | .------- month (1 - 12) OR jan,feb,mar,apr ... # | | | | .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,f ri,sat # | | | | | # * * * * * user-name command to be executed
Roberto Magalhães - Dicas 