MSSQL query boundaries conditions
SELECT Max(Id) nt
FROM (SELECT Id,(Ntile(160) over( ORDER BY Id)) nt FROM demo)
test
GROUP BY nt
ORDER BY nt
option (maxDOP 1024)
Thursday, March 7, 2024
Monday, February 26, 2024
Oracle Sql developer error: could not install some modules
Oracle SQL Developer Error :
Solutions :
Go to command prompt
C:\Users\akpandey\AppData\Roaming\SQL Developer\system22.2.1.234.1810\system_cache\config\Modules
- delete all files here
- restart the SQLDeveloper .
This solution work in my case all connection all there .
Wednesday, February 14, 2024
DMS mapping JSON ( one schema (DB1) to other schema (DB2)) - AWS
DMS mapping task JSON - AWS
{
"rules": [
{
"rule-type": "transformation",
"rule-id": "828748134",
"rule-name": "828748134",
"rule-target": "schema",
"object-locator": {
"schema-name": "dbo"
},
"parallel-load": null,
"rule-action": "rename",
"value": "pra_mns_pss",
"old-value": null
},
{
"rule-type": "transformation",
"rule-id": "828155163",
"rule-name": "828155163",
"rule-target": "table",
"object-locator": {
"schema-name": "dbo",
"table-name": "demo2"
},
"parallel-load": null,
"rule-action": "rename",
"value": "demo2",
"old-value": null
},
{
"rule-type": "selection",
"rule-id": "828120150",
"rule-name": "828120150",
"object-locator": {
"schema-name": "dbo",
"table-name": "demo2"
},
"rule-action": "include",
"filters": [],
"parallel-load": null,
"isAutoSegmentationChecked": false
},
{
"rule-type": "selection",
"rule-id": "820918688",
"rule-name": "820918688",
"object-locator": {
"schema-name": "dbo",
"table-name": "demo1"
},
"rule-action": "include",
"filters": [],
"parallel-load": null,
"isAutoSegmentationChecked": false
},
{
"rule-type": "transformation",
"rule-id": "1",
"rule-name": "1",
"rule-target": "table",
"object-locator": {
"schema-name": "dbo",
"table-name": "demo1"
},
"rule-action": "rename",
"value": "demo1",
"old-value": null
}
]
}
Monday, February 5, 2024
How to check all node are serving in RAC - 4 node cluster
Query to check , all node are serving connection in RAC
Query-1
select inst_id,count(*)
from
gv$session
where
username is not null
group by
inst_id;
Query-2
select
inst_id,
count(*)
from
gv$session
where
status='ACTIVE' and username is not null
group by
inst_id;
Thursday, February 1, 2024
How to Terminates or kill a session in AWS Oracle RDS
Terminates or kill a session in AWS Oracle RDS
BEGIN
rdsadmin.rdsadmin_util.kill(
sid => sid,
serial => serial_number,
method => 'IMMEDIATE');
END;
/
BEGIN
rdsadmin.rdsadmin_util.kill(
sid => 1231,
serial => 54321,
method => 'IMMEDIATE');
END;
/
Friday, January 19, 2024
AWS DMS Task Parameters to Improve Performance
DMS Tasks Parameters to Improve Performance
{
"Logging": {
"EnableLogging": true,
"EnableLogContext": false,
"LogComponents": [
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "TRANSFORMATION"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "SOURCE_UNLOAD"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "IO"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "TARGET_LOAD"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "PERFORMANCE"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "SOURCE_CAPTURE"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "SORTER"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "REST_SERVER"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "VALIDATOR_EXT"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "TARGET_APPLY"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "TASK_MANAGER"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "TABLES_MANAGER"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "METADATA_MANAGER"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "FILE_FACTORY"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "COMMON"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "ADDONS"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "DATA_STRUCTURE"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "COMMUNICATION"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "FILE_TRANSFER"
}
],
"CloudWatchLogGroup": "dms-tasks-dms-repl-demo-hr-inst-prd-1",
"CloudWatchLogStream": "dms-task-IJSGJUCFA37KTMQLEH46GTN3SYH4I35DRTOGBWI"
},
"StreamBufferSettings": {
"StreamBufferCount": 12,
"CtrlStreamBufferSizeInMB": 8,
"StreamBufferSizeInMB": 128
},
"ErrorBehavior": {
"FailOnNoTablesCaptured": true,
"ApplyErrorUpdatePolicy": "LOG_ERROR",
"FailOnTransactionConsistencyBreached": false,
"RecoverableErrorThrottlingMax": 1800,
"DataErrorEscalationPolicy": "SUSPEND_TABLE",
"ApplyErrorEscalationCount": 0,
"RecoverableErrorStopRetryAfterThrottlingMax": true,
"RecoverableErrorThrottling": true,
"ApplyErrorFailOnTruncationDdl": false,
"DataTruncationErrorPolicy": "LOG_ERROR",
"ApplyErrorInsertPolicy": "LOG_ERROR",
"EventErrorPolicy": "IGNORE",
"ApplyErrorEscalationPolicy": "LOG_ERROR",
"RecoverableErrorCount": 5,
"DataErrorEscalationCount": 0,
"TableErrorEscalationPolicy": "STOP_TASK",
"RecoverableErrorInterval": 5,
"ApplyErrorDeletePolicy": "IGNORE_RECORD",
"TableErrorEscalationCount": 0,
"FullLoadIgnoreConflicts": true,
"DataErrorPolicy": "LOG_ERROR",
"TableErrorPolicy": "SUSPEND_TABLE"
},
"TTSettings": {
"TTS3Settings": null,
"TTRecordSettings": null,
"EnableTT": false
},
"FullLoadSettings": {
"CommitRate": 50000,
"StopTaskCachedChangesApplied": false,
"StopTaskCachedChangesNotApplied": false,
"MaxFullLoadSubTasks": 49,
"TransactionConsistencyTimeout": 600,
"CreatePkAfterFullLoad": false,
"TargetTablePrepMode": "DO_NOTHING"
},
"TargetMetadata": {
"ParallelApplyBufferSize": 0,
"ParallelApplyQueuesPerThread": 0,
"ParallelApplyThreads": 0,
"TargetSchema": "HR",
"InlineLobMaxSize": 0,
"ParallelLoadQueuesPerThread": 0,
"SupportLobs": true,
"LobChunkSize": 0,
"TaskRecoveryTableEnabled": false,
"ParallelLoadThreads": 0,
"LobMaxSize": 64,
"BatchApplyEnabled": false,
"FullLobMode": false,
"LimitedSizeLobMode": true,
"LoadMaxFileSize": 0,
"ParallelLoadBufferSize": 0
},
"BeforeImageSettings": null,
"ControlTablesSettings": {
"historyTimeslotInMinutes": 5,
"HistoryTimeslotInMinutes": 5,
"StatusTableEnabled": false,
"SuspendedTablesTableEnabled": false,
"HistoryTableEnabled": false,
"ControlSchema": "",
"FullLoadExceptionTableEnabled": false
},
"LoopbackPreventionSettings": null,
"CharacterSetSettings": null,
"FailTaskWhenCleanTaskResourceFailed": false,
"ChangeProcessingTuning": {
"StatementCacheSize": 50,
"CommitTimeout": 1,
"BatchApplyPreserveTransaction": true,
"BatchApplyTimeoutMin": 1,
"BatchSplitSize": 0,
"BatchApplyTimeoutMax": 30,
"MinTransactionSize": 1000,
"MemoryKeepTime": 120,
"BatchApplyMemoryLimit": 500,
"MemoryLimitTotal": 2048
},
"ChangeProcessingDdlHandlingPolicy": {
"HandleSourceTableDropped": true,
"HandleSourceTableTruncated": true,
"HandleSourceTableAltered": true
},
"PostProcessingRules": null
}
AWS DMS Mapping Table (Oracle) : Loading Table data using boundaries
Loading Table data using boundaries
Query to get table boundaries
SELECT /*+ parallel(256) */ '["' ||Max(ID)||'"],'
FROM (SELECT ID, Ntile(5) over( ORDER BY ID) nt FROM HR.DEMO)
GROUP BY nt
ORDER BY nt;
Mapping JSON
{
"rules": [
{
"rule-type": "transformation",
"rule-id": "113859112",
"rule-name": "113859112",
"rule-target": "table",
"object-locator": {
"schema-name": "HR",
"table-name": "DEMO"
},
"rule-action": "convert-uppercase",
"value": null,
"old-value": null
},
{
"rule-type": "selection",
"rule-id": "101",
"rule-name": "101",
"object-locator": {
"schema-name": "HR",
"table-name": "DEMO"
},
"rule-action": "include"
},
{
"rule-type": "table-settings",
"rule-id": "103",
"rule-name": "103",
"object-locator": {
"schema-name": "HR",
"table-name": "DEMO"
},
"parallel-load": {
"type": "ranges",
"columns": [
"ID"
],
"boundaries": [
[
"24016177"
],
[
"41650518"
],
[
"56874846"
],
[
"71834843"
],
[
"86760524"
]
]
},
"rule-action": "include",
"filters": []
}
]
}
Subscribe to:
Comments (Atom)