Wednesday, December 22, 2021

How to create a native login in azure

 

create login DAV4635 with password='YourPassword';


create user DAV4635 from login DAV4635;


USE AEDIDACATED

EXEC sp_addrolemember N'db_owner', N'DAV4635'

GO

__


-- ADD AD USER



  CREATE USER [dexter.soreta@aei.com] 

FROM EXTERNAL PROVIDER 

WITH DEFAULT_SCHEMA = dbo;  





EXEC sp_addrolemember N'db_datareader', N'dexter.soreta@aei.com'


Posted by at No comments:

Monday, December 13, 2021

BARBATOS /u01

 ./wind_ora_25225_20211214011600673492885833.aud

./wind_ora_25236_20211214011601236794820192.aud

[oracle@barbatos adump]$ pwd

/u01/app/oracle/admin/wind/adump

[oracle@barbatos adump]$ find . -name "*.aud" -delete


Posted by at No comments:

Wednesday, November 3, 2021

Oracle how to kill sessions by user

 BEGIN

  FOR r IN (select sid,serial# from v$session where username='COMPASS')

  LOOP

      EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid  || ',' 

        || r.serial# || ''' immediate';

  END LOOP;

END;

Posted by at No comments:

SQL Server Permissions to view Performance Dashboard

Permissions

On SQL Server, requires VIEW SERVER STATE and ALTER TRACE permissions. On Azure SQL Database, requires the VIEW DATABASE STATE permission in the database.

Posted by at No comments:

Friday, October 15, 2021

SQL SERVER last accessed and last restart

 --current connections

SELECT @@ServerName AS server

 ,NAME AS dbname

 ,COUNT(STATUS) AS number_of_connections

 ,GETDATE() AS timestamp

FROM sys.databases sd

LEFT JOIN sysprocesses sp ON sd.database_id = sp.dbid

WHERE database_id NOT BETWEEN 1 AND 4

GROUP BY NAME;


--db last accessed

SELECT d.name,

last_user_seek = MAX(last_user_seek),

last_user_scan = MAX(last_user_scan),

last_user_lookup = MAX(last_user_lookup),

last_user_update = MAX(last_user_update)

FROM sys.dm_db_index_usage_stats AS i

JOIN sys.databases AS d ON i.database_id=d.database_id

GROUP BY d.name


--last restart

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

Posted by at No comments:

Friday, August 20, 2021

Long Running SQL Sql server

select Session_ID = session_id

,Command = command

,Start_Time = start_time

,Status = status

,Percent_Completed = percent_complete

,Time_In_Min = (estimated_completion_time/60000)

,Elapsed_Time_Minutes = (total_elapsed_time/60000)

,Wait_Type = wait_type

,Last_Wait_type = last_wait_type

,Blocking_Session = Blocking_Session_Id

,Reads = reads

,Writes = writes

,Logical_Reads = logical_reads

from sys.dm_exec_requests

where session_id > 50

Posted by at No comments:

Wednesday, August 18, 2021

The OLE DB provider "OraOLEDB.Oracle" for linked server reported an error. Access denied.

 part1i tns was wrong

ServerObjects>Linkservers>Providers>OraOLEDB.Oracle>allowinprocess
recreate link server
Posted by at No comments:

Thursday, June 24, 2021

SQL SERVER Fix Database in suspect mode

ALTER DATABASE  AdventureWorks  SET  EMERGENCY

DBCC CHECKDB ('AdventureWorks');

ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CHECKDB ('AdventureWorks', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE AdventureWorks SET MULTI_USER

Posted by at No comments:

Saturday, May 29, 2021

Oracle how to trace which server is locking the user

 -- Enable option


ALTER SYSTEM SET AUDIT_TRAIL='DB' SCOPE=SPFILE;

-- Restart database 

-- SHUTDOWN IMMEDIATE
-- STARTUP 
-- To Audit all failed login attempts

AUDIT SESSION WHENEVER NOT SUCCESSFUL;

-- A table with audit data

SELECT * FROM DBA_AUDIT_SESSION;

no rows selected
SELECT * FROM DBA_AUDIT_SESSION 
where username='ORA_FDM' 
and timestamp > systimestamp - numtodsinterval(15,'MINUTE')
order by timestamp desc;
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:26:37 PM	LOGON						15393318	1017			5/29/2021 6:26:37.563375 PM +08:00			0	20521
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:26:31 PM	LOGON						15393315	1017			5/29/2021 6:26:31.979817 PM +08:00			0	20515
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:26:26 PM	LOGON						15393313	1017			5/29/2021 6:26:26.983067 PM +08:00			0	20470
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:26:22 PM	LOGON						15393310	28000			5/29/2021 6:26:22.019047 PM +08:00			0	20461
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:26:17 PM	LOGON						15393307	28000			5/29/2021 6:26:17.180618 PM +08:00			0	20455
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:26:11 PM	LOGON						15393305	28000			5/29/2021 6:26:11.982666 PM +08:00			0	20450
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:26:06 PM	LOGON						15393304	28000			5/29/2021 6:26:06.983250 PM +08:00			0	20448
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:26:02 PM	LOGON						15393301	28000			5/29/2021 6:26:02.115095 PM +08:00			0	20435
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:25:56 PM	LOGON						15393298	28000			5/29/2021 6:25:56.981523 PM +08:00			0	20427
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:25:51 PM	LOGON						15393297	28000			5/29/2021 6:25:51.982202 PM +08:00			0	20418
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:25:47 PM	LOGON						15393295	28000			5/29/2021 6:25:47.253188 PM +08:00			0	20410
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:25:41 PM	LOGON						15393291	28000			5/29/2021 6:25:41.981482 PM +08:00			0	20375
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:25:36 PM	LOGON						15393290	28000			5/29/2021 6:25:36.981488 PM +08:00			0	20373
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:25:32 PM	LOGON						15393288	28000			5/29/2021 6:25:32.499384 PM +08:00			0	20369
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:25:26 PM	LOGON						15393284	28000			5/29/2021 6:25:26.981390 PM +08:00			0	20358
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:25:21 PM	LOGON						15393282	28000			5/29/2021 6:25:21.985979 PM +08:00			0	20352
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:25:17 PM	LOGON						15393280	28000			5/29/2021 6:25:17.207585 PM +08:00			0	20337
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:25:11 PM	LOGON						15393276	28000			5/29/2021 6:25:11.984178 PM +08:00			0	20327
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:25:07 PM	LOGON						15393275	28000			5/29/2021 6:25:07.193042 PM +08:00			0	20323
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:25:02 PM	LOGON						15393273	28000			5/29/2021 6:25:02.000784 PM +08:00			0	20313
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:24:57 PM	LOGON						15393269	28000			5/29/2021 6:24:57.164574 PM +08:00			0	20293
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:24:51 PM	LOGON						15393268	28000			5/29/2021 6:24:51.984326 PM +08:00			0	20266
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:24:46 PM	LOGON						15393267	28000			5/29/2021 6:24:46.981795 PM +08:00			0	20258
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:24:42 PM	LOGON						15393264	28000			5/29/2021 6:24:42.074883 PM +08:00			0	20244
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:24:37 PM	LOGON						15393261	28000			5/29/2021 6:24:37.327730 PM +08:00			0	20236
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:24:31 PM	LOGON						15393260	28000			5/29/2021 6:24:31.982127 PM +08:00			0	20234
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:24:27 PM	LOGON						15393257	28000			5/29/2021 6:24:27.189532 PM +08:00			0	20225
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:24:21 PM	LOGON						15393253	28000			5/29/2021 6:24:21.984026 PM +08:00			0	20216
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:24:16 PM	LOGON						15393252	28000			5/29/2021 6:24:16.982166 PM +08:00			0	20213
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:24:12 PM	LOGON						15393249	28000			5/29/2021 6:24:12.271429 PM +08:00			0	20206
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:24:06 PM	LOGON						15393246	28000			5/29/2021 6:24:06.988428 PM +08:00			0	20199
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:24:01 PM	LOGON						15393245	28000			5/29/2021 6:24:01.996356 PM +08:00			0	20197
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:23:57 PM	LOGON						15393242	28000			5/29/2021 6:23:57.259637 PM +08:00			0	20189
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:23:51 PM	LOGON						15393239	28000			5/29/2021 6:23:51.984701 PM +08:00			0	20183
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:23:46 PM	LOGON						15393238	28000			5/29/2021 6:23:46.985184 PM +08:00			0	20180
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:23:41 PM	LOGON						15393236	28000			5/29/2021 6:23:41.983121 PM +08:00			0	20175
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:23:37 PM	LOGON						15393232	28000			5/29/2021 6:23:37.193607 PM +08:00			0	20166
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:23:31 PM	LOGON						15393231	28000			5/29/2021 6:23:31.988192 PM +08:00			0	20164
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:23:26 PM	LOGON						15393228	28000			5/29/2021 6:23:26.992729 PM +08:00			0	20152
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:23:22 PM	LOGON						15393225	28000			5/29/2021 6:23:22.051200 PM +08:00			0	20144
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:23:16 PM	LOGON						15393221	28000			5/29/2021 6:23:16.985216 PM +08:00			0	20132
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:23:11 PM	LOGON						15393219	28000			5/29/2021 6:23:11.984785 PM +08:00			0	20127
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:23:07 PM	LOGON						15393217	28000			5/29/2021 6:23:07.451285 PM +08:00			0	20123
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:23:01 PM	LOGON						15393214	28000			5/29/2021 6:23:01.991758 PM +08:00			0	20095
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:22:56 PM	LOGON						15393212	28000			5/29/2021 6:22:56.988493 PM +08:00			0	20072
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:22:51 PM	LOGON						15393211	28000			5/29/2021 6:22:51.985211 PM +08:00			0	20062
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:22:47 PM	LOGON						15393207	28000			5/29/2021 6:22:47.066390 PM +08:00			0	20053
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:22:42 PM	LOGON						15393205	28000			5/29/2021 6:22:42.140178 PM +08:00			0	20047
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:22:37 PM	LOGON						15393204	28000			5/29/2021 6:22:37.002799 PM +08:00			0	20045
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:22:32 PM	LOGON						15393200	28000			5/29/2021 6:22:32.092210 PM +08:00			0	20033
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:22:26 PM	LOGON						15393197	28000			5/29/2021 6:22:26.985574 PM +08:00			0	20025
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:22:21 PM	LOGON						15393195	28000			5/29/2021 6:22:21.989274 PM +08:00			0	20020
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:22:17 PM	LOGON						15393193	28000			5/29/2021 6:22:17.215509 PM +08:00			0	20015
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:22:11 PM	LOGON						15393189	28000			5/29/2021 6:22:11.990409 PM +08:00			0	20006
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:22:06 PM	LOGON						15393188	28000			5/29/2021 6:22:06.989700 PM +08:00			0	20003
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:22:02 PM	LOGON						15393186	28000			5/29/2021 6:22:02.275328 PM +08:00			0	19999
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:21:56 PM	LOGON						15393182	28000			5/29/2021 6:21:56.990633 PM +08:00			0	19990
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:21:52 PM	LOGON						15393181	28000			5/29/2021 6:21:52.006688 PM +08:00			0	19988
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:21:47 PM	LOGON						15393179	28000			5/29/2021 6:21:47.033009 PM +08:00			0	19981
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:21:41 PM	LOGON						15393175	28000			5/29/2021 6:21:41.997601 PM +08:00			0	19973
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:21:36 PM	LOGON						15393174	28000			5/29/2021 6:21:36.991714 PM +08:00			0	19965
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:21:32 PM	LOGON						15393172	28000			5/29/2021 6:21:32.167757 PM +08:00			0	19954
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:21:26 PM	LOGON						15393168	28000			5/29/2021 6:21:26.992667 PM +08:00			0	19907
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:21:22 PM	LOGON						15393166	28000			5/29/2021 6:21:22.009025 PM +08:00			0	19900
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:21:16 PM	LOGON						15393165	28000			5/29/2021 6:21:16.993214 PM +08:00			0	19896
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:21:12 PM	LOGON						15393162	28000			5/29/2021 6:21:12.309120 PM +08:00			0	19889
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:21:06 PM	LOGON						15393159	28000			5/29/2021 6:21:06.989239 PM +08:00			0	19883
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:21:01 PM	LOGON						15393158	28000			5/29/2021 6:21:01.997852 PM +08:00			0	19873
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:20:57 PM	LOGON						15393155	28000			5/29/2021 6:20:57.300155 PM +08:00			0	19863
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:20:51 PM	LOGON						15393152	28000			5/29/2021 6:20:51.992115 PM +08:00			0	19849
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:20:46 PM	LOGON						15393151	28000			5/29/2021 6:20:46.991095 PM +08:00			0	19845
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:20:42 PM	LOGON						15393148	28000			5/29/2021 6:20:42.078786 PM +08:00			0	19836
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:20:36 PM	LOGON						15393145	28000			5/29/2021 6:20:36.991581 PM +08:00			0	19829
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:20:32 PM	LOGON						15393144	28000			5/29/2021 6:20:32.011801 PM +08:00			0	19825
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:20:27 PM	LOGON						15393141	28000			5/29/2021 6:20:27.322720 PM +08:00			0	19813
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:20:21 PM	LOGON						15393137	28000			5/29/2021 6:20:21.991840 PM +08:00			0	19803
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:20:16 PM	LOGON						15393136	28000			5/29/2021 6:20:16.990688 PM +08:00			0	19800
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:20:11 PM	LOGON						15393133	28000			5/29/2021 6:20:11.992502 PM +08:00			0	19793
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:20:07 PM	LOGON						15393130	28000			5/29/2021 6:20:07.301722 PM +08:00			0	19785
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:20:01 PM	LOGON						15393129	28000			5/29/2021 6:20:01.993234 PM +08:00			0	19782
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:19:56 PM	LOGON						15393127	28000			5/29/2021 6:19:56.994085 PM +08:00			0	19773
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:19:52 PM	LOGON						15393124	28000			5/29/2021 6:19:52.082794 PM +08:00			0	19744
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:19:46 PM	LOGON						15393122	28000			5/29/2021 6:19:46.992809 PM +08:00			0	19738
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:19:41 PM	LOGON						15393120	28000			5/29/2021 6:19:41.994378 PM +08:00			0	19729
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:19:37 PM	LOGON						15393118	28000			5/29/2021 6:19:37.213960 PM +08:00			0	19722
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:19:31 PM	LOGON						15393115	28000			5/29/2021 6:19:31.991632 PM +08:00			0	19715
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:19:27 PM	LOGON						15393113	28000			5/29/2021 6:19:27.006595 PM +08:00			0	19709
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:19:22 PM	LOGON						15393110	28000			5/29/2021 6:19:22.423662 PM +08:00			0	19701
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:19:16 PM	LOGON						15393107	28000			5/29/2021 6:19:16.994051 PM +08:00			0	19692
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:19:12 PM	LOGON						15393105	28000			5/29/2021 6:19:12.328618 PM +08:00			0	19686
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:19:06 PM	LOGON						15393104	28000			5/29/2021 6:19:06.995516 PM +08:00			0	19684
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:19:02 PM	LOGON						15393102	28000			5/29/2021 6:19:02.544340 PM +08:00			0	19679
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:18:57 PM	LOGON						15393099	28000			5/29/2021 6:18:57.603199 PM +08:00			0	19670
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:18:52 PM	LOGON						15393097	28000			5/29/2021 6:18:52.717592 PM +08:00			0	19663
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:18:46 PM	LOGON						15393096	28000			5/29/2021 6:18:46.998611 PM +08:00			0	19658
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:18:42 PM	LOGON						15393093	28000			5/29/2021 6:18:42.269956 PM +08:00			0	19651
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:18:38 PM	LOGON						15393092	28000			5/29/2021 6:18:38.123664 PM +08:00			0	19647
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:18:32 PM	LOGON						15393089	28000			5/29/2021 6:18:32.290260 PM +08:00			0	19640
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:18:27 PM	LOGON						15393087	28000			5/29/2021 6:18:27.215952 PM +08:00			0	19634
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:18:22 PM	LOGON						15393084	28000			5/29/2021 6:18:22.401511 PM +08:00			0	19626
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:18:18 PM	LOGON						15393083	28000			5/29/2021 6:18:18.107270 PM +08:00			0	19623
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:18:12 PM	LOGON						15393079	28000			5/29/2021 6:18:12.576818 PM +08:00			0	19614
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:18:06 PM	LOGON						15393078	28000			5/29/2021 6:18:06.995317 PM +08:00			0	19612
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:18:02 PM	LOGON						15393076	28000			5/29/2021 6:18:02.125599 PM +08:00			0	19585
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:17:56 PM	LOGON						15393072	28000			5/29/2021 6:17:56.995233 PM +08:00			0	19576
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:17:51 PM	LOGON						15393071	28000			5/29/2021 6:17:51.995748 PM +08:00			0	19573
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:17:46 PM	LOGON						15393070	28000			5/29/2021 6:17:46.995411 PM +08:00			0	19568
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:17:42 PM	LOGON						15393067	28000			5/29/2021 6:17:42.010759 PM +08:00			0	19562
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:17:37 PM	LOGON						15393064	28000			5/29/2021 6:17:37.403078 PM +08:00			0	19552
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:17:31 PM	LOGON						15393063	28000			5/29/2021 6:17:31.999148 PM +08:00			0	19549
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:17:27 PM	LOGON						15393060	28000			5/29/2021 6:17:27.150866 PM +08:00			0	19543
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:17:22 PM	LOGON						15393056	28000			5/29/2021 6:17:22.000149 PM +08:00			0	19531
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:17:16 PM	LOGON						15393055	28000			5/29/2021 6:17:16.995372 PM +08:00			0	19528
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:17:12 PM	LOGON						15393052	28000			5/29/2021 6:17:12.486414 PM +08:00			0	19521
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:17:06 PM	LOGON						15393049	28000			5/29/2021 6:17:06.997292 PM +08:00			0	19515
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:17:02 PM	LOGON						15393048	28000			5/29/2021 6:17:02.020273 PM +08:00			0	19513
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:16:56 PM	LOGON						15393045	28000			5/29/2021 6:16:56.997268 PM +08:00			0	19506
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:16:52 PM	LOGON						15393042	28000			5/29/2021 6:16:52.098569 PM +08:00			0	19499
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:16:46 PM	LOGON						15393041	28000			5/29/2021 6:16:46.997666 PM +08:00			0	19495
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:16:41 PM	LOGON						15393039	28000			5/29/2021 6:16:41.999503 PM +08:00			0	19490
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:16:37 PM	LOGON						15393037	28000			5/29/2021 6:16:37.399034 PM +08:00			0	19483
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:16:32 PM	LOGON						15393034	28000			5/29/2021 6:16:32.002846 PM +08:00			0	19477
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:16:26 PM	LOGON						15393032	28000			5/29/2021 6:16:26.999013 PM +08:00			0	19436
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:16:22 PM	LOGON						15393029	28000			5/29/2021 6:16:22.202633 PM +08:00			0	19428
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:16:17 PM	LOGON						15393026	28000			5/29/2021 6:16:17.000624 PM +08:00			0	19421
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:16:12 PM	LOGON						15393024	28000			5/29/2021 6:16:12.007664 PM +08:00			0	19415
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:16:07 PM	LOGON						15393022	28000			5/29/2021 6:16:07.016758 PM +08:00			0	19411
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:16:01 PM	LOGON						15393019	28000			5/29/2021 6:16:01.998696 PM +08:00			0	19398
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:15:56 PM	LOGON						15393017	28000			5/29/2021 6:15:56.998946 PM +08:00			0	19393
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:15:52 PM	LOGON						15393015	28000			5/29/2021 6:15:52.043043 PM +08:00			0	19382
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:15:47 PM	LOGON						15393012	28000			5/29/2021 6:15:47.219810 PM +08:00			0	19376
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:15:41 PM	LOGON						15393010	28000			5/29/2021 6:15:41.999883 PM +08:00			0	19364
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:15:36 PM	LOGON						15393009	28000			5/29/2021 6:15:36.998933 PM +08:00			0	19362
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:15:32 PM	LOGON						15393007	28000			5/29/2021 6:15:32.133990 PM +08:00			0	19353
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:15:27 PM	LOGON						15393003	28000			5/29/2021 6:15:27.007065 PM +08:00			0	19334
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:15:22 PM	LOGON						15393001	28000			5/29/2021 6:15:22.000258 PM +08:00			0	19327
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:15:17 PM	LOGON						15392999	28000			5/29/2021 6:15:17.308432 PM +08:00			0	19305
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:15:12 PM	LOGON						15392995	28000			5/29/2021 6:15:12.000398 PM +08:00			0	19291
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:15:07 PM	LOGON						15392994	28000			5/29/2021 6:15:07.001840 PM +08:00			0	19289
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:15:02 PM	LOGON						15392992	28000			5/29/2021 6:15:02.095014 PM +08:00			0	19272
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:14:57 PM	LOGON						15392988	28000			5/29/2021 6:14:57.616255 PM +08:00			0	19238
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:14:52 PM	LOGON						15392987	28000			5/29/2021 6:14:52.001516 PM +08:00			0	19213
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:14:47 PM	LOGON						15392986	28000			5/29/2021 6:14:47.000372 PM +08:00			0	19211
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:14:42 PM	LOGON						15392983	28000			5/29/2021 6:14:42.079173 PM +08:00			0	19201
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:14:37 PM	LOGON						15392980	28000			5/29/2021 6:14:37.154805 PM +08:00			0	19188
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:14:32 PM	LOGON						15392979	28000			5/29/2021 6:14:32.003295 PM +08:00			0	19186
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:14:27 PM	LOGON						15392976	28000			5/29/2021 6:14:27.664045 PM +08:00			0	19172
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:14:22 PM	LOGON						15392972	28000			5/29/2021 6:14:22.002467 PM +08:00			0	19156
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:14:17 PM	LOGON						15392971	28000			5/29/2021 6:14:17.001858 PM +08:00			0	19154
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:14:12 PM	LOGON						15392968	28000			5/29/2021 6:14:12.301640 PM +08:00			0	19147
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:14:07 PM	LOGON						15392965	28000			5/29/2021 6:14:07.001695 PM +08:00			0	19124
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:14:02 PM	LOGON						15392964	28000			5/29/2021 6:14:02.002035 PM +08:00			0	19122
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:13:57 PM	LOGON						15392961	28000			5/29/2021 6:13:57.373986 PM +08:00			0	19109
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:13:52 PM	LOGON						15392958	28000			5/29/2021 6:13:52.002861 PM +08:00			0	19102
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:13:47 PM	LOGON						15392957	28000			5/29/2021 6:13:47.009467 PM +08:00			0	19095
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:13:42 PM	LOGON						15392955	28000			5/29/2021 6:13:42.003836 PM +08:00			0	19082
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:13:37 PM	LOGON						15392951	28000			5/29/2021 6:13:37.182559 PM +08:00			0	19070
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:13:32 PM	LOGON						15392950	28000			5/29/2021 6:13:32.003288 PM +08:00			0	19067
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:13:27 PM	LOGON						15392948	28000			5/29/2021 6:13:27.002043 PM +08:00			0	19063
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:13:22 PM	LOGON						15392944	28000			5/29/2021 6:13:22.178274 PM +08:00			0	19050
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:13:17 PM	LOGON						15392942	28000			5/29/2021 6:13:17.005193 PM +08:00			0	19045
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:13:12 PM	LOGON						15392940	28000			5/29/2021 6:13:12.004756 PM +08:00			0	19041
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:13:07 PM	LOGON						15392938	28000			5/29/2021 6:13:07.505652 PM +08:00			0	19034
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:13:02 PM	LOGON						15392935	28000			5/29/2021 6:13:02.212829 PM +08:00			0	19000
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:12:57 PM	LOGON						15392933	28000			5/29/2021 6:12:57.005553 PM +08:00			0	18989
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:12:52 PM	LOGON						15392931	28000			5/29/2021 6:12:52.346589 PM +08:00			0	18977
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:12:47 PM	LOGON						15392928	28000			5/29/2021 6:12:47.004929 PM +08:00			0	18965
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:12:42 PM	LOGON						15392926	28000			5/29/2021 6:12:42.005340 PM +08:00			0	18960
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:12:37 PM	LOGON						15392924	28000			5/29/2021 6:12:37.045460 PM +08:00			0	18956
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:12:32 PM	LOGON						15392922	28000			5/29/2021 6:12:32.143539 PM +08:00			0	18951
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:12:27 PM	LOGON						15392919	28000			5/29/2021 6:12:27.005503 PM +08:00			0	18940
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:12:22 PM	LOGON						15392917	28000			5/29/2021 6:12:22.004443 PM +08:00			0	18928
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:12:17 PM	LOGON						15392915	28000			5/29/2021 6:12:17.657324 PM +08:00			0	18922
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:12:12 PM	LOGON						15392911	28000			5/29/2021 6:12:12.005194 PM +08:00			0	18914
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:12:07 PM	LOGON						15392910	28000			5/29/2021 6:12:07.005123 PM +08:00			0	18908
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:12:02 PM	LOGON						15392908	28000			5/29/2021 6:12:02.270179 PM +08:00			0	18902
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:11:57 PM	LOGON						15392904	28000			5/29/2021 6:11:57.007035 PM +08:00			0	18893
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:11:52 PM	LOGON						15392903	28000			5/29/2021 6:11:52.006533 PM +08:00			0	18883
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:11:47 PM	LOGON						15392901	28000			5/29/2021 6:11:47.209576 PM +08:00			0	18878
BALRON$	ORA_FDM	BALRON	unknown	5/29/2021 6:11:42 PM	LOGON						15392897	28000			5/29/2021 6:11:42.005666 PM +08:00			0	18870

1017 means wrong password... 28000 is locked.  

Since I am in no mood to wait for the developers to correct this
I temporarily will set my profile to not lock the users;

SQL> alter profile default limit failed_login_attempts unlimited;

Profile altered.

SQL> alter user ora_fdm account unlock;

User altered.

Posted by at No comments:

Wednesday, May 26, 2021

How to change domain of Oracle Database in Linux

 old: ecp.priv

new: aei.com

DB: epchrs        LINUX SERVER: sandrock

First login as root in the server.


[root@sandrock etc]# hostnamectl set-hostname sandrock.aei.com

[root@sandrock etc]# cat hostname

sandrock.aei.com

[root@sandrock etc]# vi hosts

[root@sandrock etc]# cat hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

10.131.128.39 serpentcustom.ecp.priv serpentcustom

[root@sandrock etc]# vi hosts  -- here add the new hosts

[root@sandrock etc]# hostname

sandrock.aei.com

You have new mail in /var/spool/mail/root

[root@sandrock etc]# service network restart

Restarting network (via systemctl):                        [  OK  ]

[root@sandrock etc]# hostname

sandrock.aei.com


Next 
update the 
$TNSADMIN/listener.ora
$TNSADMIN/tnsnames.ora


[oracle@sandrock admin]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Wed May 26 19:11:18 2021
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
EPCHRS.ECP.PRIV

SQL> show parameters db_domain

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_domain                            string      ecp.priv
SQL> alter system set db_domain='aei.com' scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 4731170856 bytes
Fixed Size                  8667176 bytes
Variable Size             989855744 bytes
Database Buffers         3724541952 bytes
Redo Buffers                8105984 bytes
Database mounted.
Database opened.
SQL> show parameters db_domain

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_domain                            string      aei.com
SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
EPCHRS.ECP.PRIV

SQL> ALTER DATABASE RENAME GLOBAL_NAME TO EPCHRS.AEI.COM;

Database altered.

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
EPCHRS.AEI.COM

SQL>


FINALLY UPDATE CLIENT tnsnames.ora

Based on my observations the db_domain and global_name are not required to be changed when changing the domain of the server.

I tried to connect to the db by only updating the host in the tnsnames.ora to aei and left the the service_name=ecpchris.ecp.priv.

It was still working!!

C:\Users\DexterVelasco>tnsping hkepwind2

TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 26-MAY-2021 19:54:11

Copyright (c) 1997 Oracle Corporation.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = sandrock.aei.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = wind.ecp.priv)))
OK (140 msec)

C:\Users\DexterVelasco>sqlplus dav4635@hkepwind2

SQL*Plus: Release 12.2.0.1.0 Production on Wed May 26 19:54:36 2021

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Wed May 26 2021 19:37:12 +08:00

Connected to:
Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 - Production

SQL> select * from tab;

TNAME
--------------------------------------------------------------------------------
TABTYPE        CLUSTERID
------------- ----------
IRONWIND
TABLE


SQL>



Posted by at No comments:

Sunday, May 16, 2021

Grant select on SQL server

 use [CAROL]

GO

GRANT SELECT ON [dbo].[INV013_MPIS_Daily] TO [ro_dwhProduction]

GO

Posted by at No comments:

Wednesday, May 12, 2021

How to get transaction per second in SQL Server

 


DECLARE @Counter bigint

SELECT @Counter = cntr_value FROM sys.dm_os_performance_counters

WHERE counter_name = 'Transactions/sec'

AND object_name='SQLServer:Databases'

AND instance_name ='eTrace'--Your DB Name


-- Wait for 1 second


WAITFOR DELAY '00:00:01'


SELECT cntr_value - @Counter FROM sys.dm_os_performance_counters

WHERE counter_name ='Transactions/sec'

AND object_name ='SQLServer:Databases'

AND instance_name ='eTrace'--Your DB Name

Posted by at No comments:

Thursday, April 29, 2021

SQL Server List Jobs and Owners

 USE msdb


EXEC dbo.sp_help_job

Posted by at No comments:

Tuesday, April 13, 2021

How to change domain for Linux

 [root@artemis ~]# vi /etc/hostname

[root@artemis ~]# vi /etc/hostname

[root@artemis ~]# hostname artemis

[root@artemis ~]# vi /etc/hosts

[root@artemis ~]# vi /etc/resolv.conf

[root@artemis ~]# vi /etc/sysconfig/network

[root@artemis ~]# vi /etc/sysconfig/network-scripts/ifcfg-eth0

[root@artemis ~]# service network restart  





Posted by at No comments:

Friday, March 26, 2021

Oracle archivelog full

-- Measure DB_RECOVERY_USAGE

SELECT name

, ceil( space_limit / 1024 / 1024) SIZE_M

, ceil( space_used  / 1024 / 1024) USED_M

, decode( nvl( space_used, 0),

 0, 0

 , ceil ( ( space_used / space_limit) * 100) ) PCT_USED

FROM v$recovery_file_dest

ORDER BY name

/


SELECT * FROM V$RECOVERY_FILE_DEST;


SELECT * FROM V$RECOVERY_AREA_USAGE;


 ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 150G SCOPE=BOTH

  ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 50G SCOPE=BOTH

  ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 100G SCOPE=BOTH


--$ rman target=/

RMAN> CrossCheck backup ;

RMAN> Delete Obsolete ;

RMAN>delete expired archivelog all;

RMAN> crosscheck archivelog all; 

Posted by at No comments:

SQL Quick count of tables

select a.name tablename , b.rows 

from sysobjects as a inner join sysindexes as b on a.id = b.id

where (a.xtype = 'u') and (b.indid in (0,1))

  and a.name in ('drop_T_Shippment',

                             'drop_T_TDHeader',

                             'drop_T_TDItem',

                             'drop_T_SFCompTrace')

order by b.rows desc;

--

SELECT

      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]

      , SUM(sPTN.Rows) AS [RowCount]

FROM 

      sys.objects AS sOBJ

      INNER JOIN sys.partitions AS sPTN

            ON sOBJ.object_id = sPTN.object_id

WHERE

      sOBJ.type = 'U'

      AND sOBJ.is_ms_shipped = 0x0

      AND index_id < 2 -- 0:Heap, 1:Clustered

GROUP BY 

      sOBJ.schema_id

      , sOBJ.name

ORDER BY [TableName]

GO

Posted by at No comments:

Tuesday, March 16, 2021

SQL SERVER LIST FOREIGN KEYS

 SELECT

    f.name constraint_name
   ,OBJECT_NAME(f.parent_object_id) referencing_table_name
   ,COL_NAME(fc.parent_object_id, fc.parent_column_id) referencing_column_name
   ,OBJECT_NAME (f.referenced_object_id) referenced_table_name
   ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) referenced_column_name
   ,delete_referential_action_desc
   ,update_referential_action_desc
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
   ON f.object_id = fc.constraint_object_id
ORDER BY f.name
Posted by at No comments:

Friday, March 12, 2021

SQL SERVER RESTORE ERROR Invalid object name 'MSreplication_subscriptions'.

 --Fix


Go back to the original and then disable the subscription

right click and create script

--DISABLE

/****** Begin: Script to be run at Subscriber ******/

use [Worklog]

exec sp_dropmergepullsubscription @publisher = N'PHAILORTSQL05', @publisher_db = N'Worklog', @publication = N'Worklog'

GO


/****** End: Script to be run at Subscriber ******/


/****** Begin: Script to be run at Publisher ******/

/*use [Worklog]

exec sp_dropmergesubscription @subscription_type = N'pull', @publication = N'Worklog', @subscriber = N'HKACPKWUSQL01', @subscriber_db = N'Worklog'

*/

/****** End: Script to be run at Publisher ******/


--ENABLE

-- Adding the merge pull subscription


/****** Begin: Script to be run at Subscriber ******/

use [Worklog]

exec sp_addmergepullsubscription @publisher = N'PHAILORTSQL05', @publication = N'Worklog', @publisher_db = N'Worklog', @subscriber_type = N'Local', @subscription_priority = 0, @description = N'', @sync_type = N'Automatic'

exec sp_addmergepullsubscription_agent @publisher = N'PHAILORTSQL05', @publisher_db = N'Worklog', @publication = N'Worklog', @distributor = N'PHAILORTSQL05', @distributor_security_mode = 1, @distributor_login = N'', @distributor_password = N'', @enabled_for_syncmgr = N'False', @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'False', @job_login = N'ecp\reynaldoglennangeles', @job_password = null, @publisher_security_mode = 1, @publisher_login = N'', @publisher_password = N'', @use_interactive_resolver = N'False', @dynamic_snapshot_location = N'', @use_web_sync = 0

GO

/****** End: Script to be run at Subscriber ******/


/****** Begin: Script to be run at Publisher ******/

/*use [Worklog]

exec sp_addmergesubscription @publication = N'Worklog', @subscriber = N'HKACPKWUSQL01', @subscriber_db = N'Worklog', @subscription_type = N'pull', @subscriber_type = N'local', @subscription_priority = 0, @sync_type = N'Automatic'

*/

/****** End: Script to be run at Publisher ******/

Posted by at No comments:

Thursday, March 11, 2021

sql server display all database

 sp_databases

Posted by at No comments:

Monday, March 8, 2021

SQL server list database backup

 -Database Backups for all databases For Previous Week 

--------------------------------------------------------------------------------- 
SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
msdb.dbo.backupset.expiration_date, 
CASE msdb..backupset.type 
WHEN 'D' THEN 'Database' 
WHEN 'L' THEN 'Log' 
END AS backup_type, 
msdb.dbo.backupset.backup_size, 
msdb.dbo.backupmediafamily.logical_device_name, 
msdb.dbo.backupmediafamily.physical_device_name, 
msdb.dbo.backupset.name AS backupset_name, 
msdb.dbo.backupset.description 
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
ORDER BY 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_finish_date
Posted by at No comments:

Friday, March 5, 2021

SQL SERVER EMAIL

 EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'Kosmos Mailer'

   ,@recipients = 'dexter.velasco@aei.com'

   ,@subject = 'Email from SQL Server'

   ,@body = 'This is my First Email sent from SQL Server :)'

   ,@importance ='HIGH'

GO


SELECT * FROM msdb.dbo.sysmail_unsentitems


SELECT * FROM msdb.dbo.sysmail_sentitems


SELECT * FROM msdb.dbo.sysmail_faileditems

Posted by at No comments:

Thursday, March 4, 2021

SQL SERVER - list all tables

 SELECT

  *
FROM
  INFORMATION_SCHEMA.TABLES;
GO
Posted by at No comments:

Wednesday, March 3, 2021

ITAMS refresh

 EXEC sp_change_users_login 'Report'


EXEC sp_change_users_login 'Auto_Fix', 'itamsuser';


EXEC sp_change_users_login 'Auto_Fix', 'sa_web';

Posted by at No comments:

Tuesday, February 16, 2021

SQL Developer returns 0 rows

 

SYMPTOMS

When running a query [SELECT count(*) from <table_name>] on a table in SQL Developer, a count of 0 is returned.

If the same SELECT is run as the same user in SQL*Plus, then a positive row count is returned.


CAUSE

The query is dependent on the results of USERENV('LANG').

This will give different results for SQL Developer and SQL*Plus.

In order for USERENV('LANG') - and the query using this - to give the same results, the LANGUAGE part of NLS_LANG needs to be modified.


SOLUTION

Change the LANGUAGE part of NLS_LANG such that USERENV('LANG') returns the same for SQL Developer and SQL*Plus.

Example:
Tools->Preferences->database ->NLS Parameters from "English" to American"
set NLS_LANG=AMERICAN_AMERICA.UTF8 gives:
SELECT USERENV ('LANG') FROM dual; => US
And
set NLS_LANG=DUTCH_AMERICA.UTF8 gives:
SELECT USERENV ('LANG') FROM dual; => NL


Posted by at No comments:

Thursday, February 11, 2021

Cannot create an instance of OLE DB provider "OraOLEDB.Oracle"

 https://stackoverflow.com/questions/14499977/cannot-create-an-instance-of-ole-db-provider-error-as-windows-authentication-u

Posted by at No comments:

Saturday, February 6, 2021

SQL SERVER get execution plan based on SPID

 SELECT CONVERT(XML, c.query_plan) AS ExecutionPlan

FROM sys.dm_exec_requests a with (nolock)
OUTER APPLY sys.dm_exec_sql_text(a.sql_handle) b
OUTER APPLY sys.dm_exec_text_query_plan (a.plan_handle, a.statement_start_offset, a.statement_end_offset) c
LEFT JOIN sys.dm_exec_query_memory_grants m (nolock)
ON m.session_id = a.session_id
AND m.request_id = a.request_id
JOIN sys.databases d
ON d.database_id = a.database_id
WHERE  a.session_id = @@SPID --replace @@SPID with the SPID number for which you want to capture query plan
ORDER BY a.Start_Time
Posted by at No comments:
Subscribe to: Posts (Atom)