DBA hat eine Reihe von Aufgaben, die hauptsächlich darauf abzielen, die Datenbank-Performance und die Datenkonsistenz zu unterstützen. Der Administrator kann den CHECKDB-Befehl verwenden, um die Datenkonsistenz einfach zu überprüfen, falls sie jedoch ein ungültiges Objekt in einem Datenbankschema finden müssen, können einige Schwierigkeiten auftreten.
Einleitung
Unter ungültig verstehen wir normalerweise verschiedene Arten von Objekten wie Synonyme, Funktionen, Pakete, Prozeduren, Ansichten usw., die einen Verweis auf nicht vorhandene Objekte oder auf Objekte, die auf irgendeine Weise geändert (z. B. umbenannt) wurden, haben. Seien Sie beispielsweise vorsichtig mit Synonymen, die auf Objekten erstellt wurden, denn wenn Sie ein Objekt löschen, erhält sein Synonym den Status ungültig, und wenn Sie ein Objekt neu erstellen, muss sein Synonym ebenfalls neu kompiliert werden. Wenn Sie versuchen, ein ungültiges Objekt zu verwenden, wird ein Fehler ausgegeben.
Sie werden oft ungültige Objekte entdecken, wenn Sie Vorbereitungsskripts ausführen, Daten exportieren oder importieren, aktualisieren oder Patches anwenden. Aber eine wirklich gute Möglichkeit, mit ihnen zu arbeiten, besteht darin, regelmäßige Überprüfungen auf das Vorhandensein nicht übereinstimmender Objekte durchzuführen, bevor und nachdem Sie Änderungen oder Upgrades vornehmen.
Die Sache ist, dass ungültige Objekte ziemlich mehrdeutig sind. Einige von ihnen sind ziemlich harmlos, sodass Sie sie mit einer einfachen Aktion oder einer Neukompilierung beheben können, die normalerweise automatisch von einer Datenbank durchgeführt wird, wenn auf das Objekt zugegriffen wird. Beispielsweise gibt es keinen Grund, sich über ungültige materialisierte Ansichten Gedanken zu machen, da sie gültig werden, sobald Sie Daten zu den zugrunde liegenden Tabellen hinzufügen.
Alternativ weisen andere Objekte auf schwerwiegende latente Probleme hin und können nicht erfolgreich neu kompiliert werden. Dies gilt besonders dann, wenn die Änderung am referenzierten Objekt zu einem Fehler im aufrufenden Objekt führt und dieses somit nicht neu kompiliert werden kann. In beiden Fällen ist es wichtig, den Grund herauszufinden und diese Objekte in Ihrer Datenbank zu identifizieren, bevor weitere Probleme auftreten können. Je nach verwendeter Datenbank können Sie aus den unten bereitgestellten Skripts auswählen, um ungültige Objekte zu finden.
Oracle
Da Oracle eine komplexe und miteinander verbundene Datenbank ist, ist es oft der Fall, dass einige Objekte, die aufeinander angewiesen sind, ‘ungültig’ werden. Im Allgemeinen werden sie bei Bedarf automatisch neu kompiliert, besonders wenn es um komplexe Abhängigkeiten geht. Die Lösung besteht darin, diese Objekte zu finden und verschiedene Methoden anzuwenden, um sie neu zu kompilieren. Lassen Sie uns Ihnen zeigen, wie Sie mit der folgenden Abfrage eine Liste ungültiger Objekte in Oracle erhalten:
SELECT owner, object_type, object_name
FROM all_objects
WHERE status = 'INVALID'
Die Informationen, die Sie erhalten, helfen Ihnen bei der Entscheidung, welchen Schritt Sie als Nächstes unternehmen sollten, um die Objekte neu zu kompilieren.
Wenn Sie Schwierigkeiten haben mit der manuellen Suche und Behebung von ungültigen Objekten, kann Ihnen dbForge Studio for Oracle helfen, es umfangreiche Funktionen bietet, mit denen Sie die Anzahl der Fehler reduzieren und die Objekte umbenennen können, ohne die Abhängigkeiten zwischen ihnen aufzuheben. Erfahren Sie hier mehr darüber, wie Sie ungültige Objekte mit diesem Tool am besten neu kompilieren.
SQL Server
SQL Server erlaubt es nicht, nicht übereinstimmende Objekte direkt zu finden. In den meisten Fällen müssen Sie ein Skript ausführen, um sicherzustellen, dass das Objekt ungültig ist. Dies ist jedoch äußerst unpraktisch.
Aus diesem Grund lassen Sie uns ein Skript erstellen, das nach ungültigen Objekten sucht:
SELECT
obj_name = QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' + QUOTENAME(o.name)
, obj_type = o.type_desc
, d.referenced_database_name
, d.referenced_schema_name
, d.referenced_entity_name
FROM sys.sql_expression_dependencies d
JOIN sys.objects o ON d.referencing_id = o.[object_id]
WHERE d.is_ambiguous = 0
AND d.referenced_id IS NULL
AND d.referenced_server_name IS NULL -- ignore objects from Linked server
AND CASE d.referenced_class -- if does not exist
WHEN 1 -- object
THEN OBJECT_ID(
ISNULL(QUOTENAME(d.referenced_database_name), DB_NAME()) + '.' +
ISNULL(QUOTENAME(d.referenced_schema_name), SCHEMA_NAME()) + '.' +
QUOTENAME(d.referenced_entity_name))
WHEN 6 – or user datatype
THEN TYPE_ID(
ISNULL(d.referenced_schema_name, SCHEMA_NAME()) + '.' + d.referenced_entity_name)
WHEN 10 -- or XML schema
THEN (
SELECT 1 FROM sys.xml_schema_collections x
WHERE x.name = d.referenced_entity_name
AND x.[schema_id] = ISNULL(SCHEMA_ID(d.referenced_schema_name), SCHEMA_ID())
)
END IS NULL
Das Skript ist für die primäre Analyse nützlich. Allerdings gibt es darin einige Lücken. Das Hauptproblem besteht darin, dass das Skript keine Objekte mit ungültigen Spalten oder Parametern anzeigt.
CREATE VIEW dbo.vw_View
AS SELECT ID = 1
GO
CREATE PROCEDURE dbo.usp_Procedure
AS BEGIN
SELECT ID FROM dbo.vw_View
END
GO
ALTER VIEW dbo.vw_View
AS SELECT New_ID = 1
GO
Beim Ausführen des Speichervorgangs bekommen wir die Fehlermeldung:
Msg 207, Level 16, State 1, Procedure usp_Procedure, Line 6
Invalid column name 'ID'.
Außerdem funktioniert das Skript nicht auf SQL Server 2005. Daher können wir das bereitgestellte Skript nicht als primäres verwenden.
SQL Server bietet jedoch die Systemprozedur sp_refreshsqlmodule an. Diese Prozedur aktualisiert Metadaten für die angegebene nicht schemagebundene gespeicherte Prozedur, benutzerdefinierte Funktion, Ansicht, DML-Trigger, DDL-Trigger auf Datenbankebene oder DDL-Trigger auf Serverebene in der aktuellen Datenbank. Persistente Metadaten für diese Objekte, wie z. B. Datentypen von Parametern, können aufgrund von Änderungen an den zugrunde liegenden Objekten veraltet sein.
Daher gibt die sp_refreshsqlmodule-Prozedur einen Fehler aus, wenn ein Objekt ungültige Spalten oder Eigenschaften enthält. Die Prozedur kann innerhalb eines Cursors für jedes Objekt aufgerufen werden. Wenn es keine ungültigen Objekte gibt, wird die Prozedur ohne Fehler abgeschlossen.
Wichtig ist es, nicht zu vergessen, dass Skriptobjekte möglicherweise keine Abhängigkeiten haben oder anfänglich keine ungültigen Objekte enthalten können. Es ist nicht sinnvoll, solche Objekte zu überprüfen. SQL Server kümmert sich darum.
Das folgende Skript kann für die Suche nach ungültigen Objekten verwendet werden:
SET NOCOUNT ON;
IF OBJECT_ID('tempdb.dbo.#objects') IS NOT NULL
DROP TABLE #objects
CREATE TABLE #objects (
obj_id INT PRIMARY KEYa
, obj_name NVARCHAR(1000)
, err_message NVARCHAR(3000) NOT NULL
, obj_type CHAR(2) NOT NULL
)
INSERT INTO #objects (obj_id, obj_name, err_message, obj_type)
SELECT
t.referencing_id
, obj_name = QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' + QUOTENAME(o.name)
, 'Invalid object name ''' + t.obj_name + ''''
, o.[type]
FROM (
SELECT
d.referencing_id
, obj_name = MAX(COALESCE(d.referenced_database_name + '.', '')
+ COALESCE(d.referenced_schema_name + '.', '')
+ d.referenced_entity_name)
FROM sys.sql_expression_dependencies d
WHERE d.is_ambiguous = 0
AND d.referenced_id IS NULL
AND d.referenced_server_name IS NULL -- ignore objects from Linked server
AND CASE d.referenced_class -- if does not exist
WHEN 1 -- object
THEN OBJECT_ID(
ISNULL(QUOTENAME(d.referenced_database_name), DB_NAME()) + '.' +
ISNULL(QUOTENAME(d.referenced_schema_name), SCHEMA_NAME()) + '.' +
QUOTENAME(d.referenced_entity_name))
WHEN 6 -- or user datatype
THEN TYPE_ID(
ISNULL(d.referenced_schema_name, SCHEMA_NAME()) + '.' + d.referenced_entity_name)
WHEN 10 -- or XML schema
THEN (
SELECT 1 FROM sys.xml_schema_collections x
WHERE x.name = d.referenced_entity_name
AND x.[schema_id] = ISNULL(SCHEMA_ID(d.referenced_schema_name), SCHEMA_ID())
)
END IS NULL
GROUP BY d.referencing_id
) t
JOIN sys.objects o ON t.referencing_id = o.[object_id]
WHERE LEN(t.obj_name) > 4 -- hide valid aliases
DECLARE
@obj_id INT
, @obj_name NVARCHAR(1000)
, @obj_type CHAR(2)
DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
SELECT
sm.[object_id]
, QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' + QUOTENAME(o.name)
, o.[type]
FROM sys.sql_modules sm
JOIN sys.objects o ON sm.[object_id] = o.[object_id]
LEFT JOIN (
SELECT s.referenced_id
FROM sys.sql_expression_dependencies s
JOIN sys.objects o ON o.object_id = s.referencing_id
WHERE s.is_ambiguous = 0
AND s.referenced_server_name IS NULL
AND o.[type] IN ('C', 'D', 'U')
GROUP BY s.referenced_id
) sed ON sed.referenced_id = sm.[object_id]
WHERE sm.is_schema_bound = 0 -- objects without SCHEMABINDING
AND sm.[object_id] NOT IN (SELECT o2.obj_id FROM #objects o2)
AND OBJECTPROPERTY(sm.[object_id], 'IsEncrypted') = 0
AND (
o.[type] IN ('IF', 'TF', 'V', 'TR') --OR o.[type] = 'P' /* Microsoft Connect #656863 */
OR (
o.[type] = 'FN'
AND
-- ignore scalar functions, which are used in DEFAULT/CHECK constraints and COMPUTED columns
sed.referenced_id IS NULL
)
)
OPEN cur
FETCH NEXT FROM cur INTO @obj_id, @obj_name, @obj_type
WHILE @@FETCH_STATUS = 0 BEGIN
BEGIN TRY
BEGIN TRANSACTION
EXEC sys.sp_refreshsqlmodule @name = @obj_name, @namespace = N'OBJECT'
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF XACT_STATE() != 0
ROLLBACK TRANSACTION
INSERT INTO #objects (obj_id, obj_name, err_message, obj_type)
SELECT @obj_id, @obj_name, ERROR_MESSAGE(), @obj_type
END CATCH
FETCH NEXT FROM cur INTO @obj_id, @obj_name, @obj_type
END
CLOSE cur
DEALLOCATE cur
SELECT obj_name, err_message, obj_type
FROM #objects
Dasselbe Skript für SQL Server 2005:
SET NOCOUNT ON;
IF OBJECT_ID('tempdb.dbo.#objects') IS NOT NULL
DROP TABLE #objects
CREATE TABLE #objects (
obj_name NVARCHAR(1000)
, err_message NVARCHAR(3000) NOT NULL
, obj_type CHAR(2) NOT NULL
)
DECLARE
@obj_name NVARCHAR(1000)
, @obj_type CHAR(2)
DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
SELECT
QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' + QUOTENAME(o.name)
, o.[type]
FROM sys.sql_modules sm
JOIN sys.objects o ON sm.[object_id] = o.[object_id]
LEFT JOIN (
SELECT s.referenced_major_id
FROM sys.sql_dependencies s
JOIN sys.objects o ON o.object_id = s.[object_id]
WHERE o.[type] IN ('C', 'D', 'U')
GROUP BY s.referenced_major_id
) sed ON sed.referenced_major_id = sm.[object_id]
WHERE sm.is_schema_bound = 0
AND OBJECTPROPERTY(sm.[object_id], 'IsEncrypted') = 0
AND (
o.[type] IN ('IF', 'TF', 'V', 'TR')
OR (
o.[type] = 'FN'
AND
sed.referenced_major_id IS NULL
)
)
OPEN cur
FETCH NEXT FROM cur INTO @obj_name, @obj_type
WHILE @@FETCH_STATUS = 0 BEGIN
BEGIN TRY
BEGIN TRANSACTION
EXEC sys.sp_refreshsqlmodule @name = @obj_name, @namespace = N'OBJECT'
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF XACT_STATE() != 0
ROLLBACK TRANSACTION
INSERT INTO #objects (obj_name, err_message, obj_type)
SELECT @obj_name, ERROR_MESSAGE(), @obj_type
END CATCH
FETCH NEXT FROM cur INTO @obj_name, @obj_type
END
CLOSE cur
DEALLOCATE cur
SELECT obj_name, err_message, obj_type
FROM #objects
Die Ergebnisse der Skriptausführung lauten wie folgt (für eine Testdatenbank):
obj_name err_message obj_type
--------------------------------- ------------------------------------------------------------------------------- --------
[dbo].[vw_EmployeePersonalInfo] An insufficient number of arguments were supplied for ‘dbo.GetEmployee’ V
[dbo].[udf_GetPercent] Invalid column name ‘Code’. FN
[dbo].[trg_AIU_Sync] Invalid column name ‘DateOut’. P
[dbo].[trg_IOU_SalaryEmployee] Invalid object name ‘dbo.tbl_SalaryEmployee’. TR
[dbo].[trg_IU_ReturnDetail] The object ‘dbo.ReturnDetail’ does not exist or is invalid for this operation. TR
[dbo].[ReportProduct] Invalid object name ‘dbo.ProductDetail’. IF
SQL Server prüft beim Erstellen eines Synonyms nicht den Namen eines Objekts. So kann ein Synonym für ein nicht existierendes Objekt erstellt werden.
Um alle ungültigen Synonyme zu finden, können Sie das folgende Skript verwenden:
SELECT QUOTENAME(SCHEMA_NAME(s.[schema_id])) + '.' + QUOTENAME(s.name)
FROM sys.synonyms s
WHERE PARSENAME(s.base_object_name, 4) IS NULL -- ignore objects from Linked server
AND OBJECT_ID(s.base_object_name) IS NULL
Wenn Sie diese Prüfung zu einem aktuellen Skript hinzufügen müssen:
...
SELECT obj_name, err_message, obj_type
FROM #objects
UNION ALL
SELECT
QUOTENAME(SCHEMA_NAME(s.[schema_id])) + '.' + QUOTENAME(s.name) COLLATE DATABASE_DEFAULT
, 'Invalid object name ''' + s.base_object_name + '''' COLLATE DATABASE_DEFAULT
, s.[type] COLLATE DATABASE_DEFAULT
FROM sys.synonyms s
WHERE PARSENAME(s.base_object_name, 4) IS NULL
AND OBJECT_ID(s.base_object_name) IS NULL
Wie Sie sehen können, können Sie mit Metadaten die Standardfunktionalität von SSMS erweitern, um Ihre täglichen Datenbankaufgaben zu erledigen.
Wenn diese Aufgabe mühsame erscheint und viel Aufwand erfordert, können Sie den Prozess vereinfachen und viel Zeit mit SQL Complete sparen, einem Add-In zur Codevervollständigung für SSMS und VS. Diese leistungsstarke Funktionalität ermöglicht es Ihnen, nicht übereinstimmende Objekte in mehreren Datenbanken einfach zu erkennen und effektive Skripts zu ihrer Verwaltung zu generieren.
Schlussfolgerung
Bei der Arbeit mit einer Datenbank ist es üblich, eine Reihe ungültiger Objekte zu haben, die Ihre Arbeit behindern und Fehler verursachen. Das Wichtigste ist, sie rechtzeitig zu finden und zu validieren. In diesem Artikel haben wir Sie durch einige der wichtigsten Dinge geführt, die Sie über ungültige Objekte in Oracle- und SQL-Datenbanken wissen sollten, und Skripts bereitgestellt, die Sie bei der Identifizierung dieser Objekte unterstützen. Wir möchten auch hervorheben, dass es bei Schwierigkeiten automatisierte Möglichkeiten gibt, ungültige Objekte zu bearbeiten, zu identifizieren und zu reparieren, die von Devart und anderen Unternehmen bereitgestellt werden.
