Author: sqldba

Posted on by — Leave a comment

Drop and Recreate PK Index

Disable PK constraint.
alter table TBL1 disable constraint PK_TBL1 ;

Delete PK index.
alter table TBL1 drop index PK_TBL1 ;

Create PK index.
create unique index “PK_TBL1” on “TBL1” (“INSPECTORID”, “DUTYID”, “INSPID”)
tablespace “TBLSPCINDX”
pctfree 10 initrans 2 maxtrans 255
storage
(
initial 64K
next 0K
minextents 1
maxextents 2147483645
pctincrease 0
freelists 1
freelist groups 1
)
nologging;

Enable PK constraint.
alter table “TBL1” enable constraint “PK_TBL1” ;

Posted on by — Leave a comment

Example Creating Common Role

1. Log into the oracle instance using SQL*Plus or other oracle scripting tool as SYSTEM user.

2. Create roles SCHEMA_DEVELOPER and SCHEMA_USER.

CREATE ROLE “SCHEMA_DEVELOPER” NOT IDENTIFIED;

GRANT CREATE SESSION TO “SCHEMA_DEVELOPER”;
GRANT SELECT ANY DICTIONARY TO “SCHEMA_DEVELOPER”;
GRANT ALTER SESSION TO “SCHEMA_DEVELOPER”;
GRANT CREATE CLUSTER TO “SCHEMA_DEVELOPER”;
GRANT CREATE DATABASE LINK TO “SCHEMA_DEVELOPER”;
GRANT CREATE PROCEDURE TO “SCHEMA_DEVELOPER”;
GRANT CREATE PUBLIC SYNONYM TO “SCHEMA_DEVELOPER”;
GRANT CREATE SEQUENCE TO “SCHEMA_DEVELOPER”;
GRANT CREATE TABLE TO “SCHEMA_DEVELOPER”;
GRANT CREATE TRIGGER TO “SCHEMA_DEVELOPER”;
GRANT CREATE VIEW TO “SCHEMA_DEVELOPER”;
GRANT DROP PUBLIC SYNONYM TO “SCHEMA_DEVELOPER”;

CREATE ROLE “SCHEMA_USER” NOT IDENTIFIED;

GRANT CREATE SESSION TO “SCHEMA_USER”;
GRANT SELECT ANY DICTIONARY TO “SCHEMA_USER”;

3. Create the tablespaces (make sure to update the filepath for the datafile according to the server you are working with). Variables are enclosed in curley braces {}. Use this script as a guide:

CREATE TABLESPACE “COMMONDATA”
LOGGING
DATAFILE ‘{PATH_TO_DATAFOLDER}COMMONDATA01.DBF’
SIZE 10M REUSE
AUTOEXTEND ON
NEXT 50M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;

CREATE TABLESPACE “COMMONINDX”
NOLOGGING
DATAFILE ‘{PATH_TO_DATAFOLDER}COMMONINDX01.DBF’
SIZE 5M REUSE
AUTOEXTEND ON
NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;

4. Create the user and role using this script as a guide. (Note: if you are using Oracle 10g or higher you will need to remove lines that set quota unlimited on temp).

CREATE USER “COMMON”
profile “DEFAULT”
identified by “{COMMON_PASSWORD}”
default tablespace “COMMONDATA”
temporary tablespace “TEMP”
quota unlimited on COMMONDATA
quota unlimited on COMMONINDX
account UNLOCK;

CREATE ROLE “COMMON_TABLE_USER” NOT IDENTIFIED;

5. Open a command prompt window and import a copy of the common schema from a .dmp file. If you dont have a .dmp file find another instance with the common schema and export it. Use these scripts as a guide:

For importing the schema:

imp common/{COMMON_PASSWORD}@{INSTANCE_NAME} file=common.dmp log=commonimp.log fromuser=common touser=common

For exporting the schema:

exp common/{COMMON_PASSWORD}@{INSTANCE_NAME} file=common.dmp log=commonexp.log

7. Run the following scripts one at a time to generate grant scripts for the roles needed.
Select the statements that were created by these scripts and run them in order to grant the correct priveleges:

SELECT ‘grant SELECT, REFERENCES on “COMMON”.’ || table_name “Grant Privileges”, ‘TO ‘ || role || ‘;’ “To Role”
FROM dba_tables
, dba_roles
WHERE owner = ‘COMMON’
AND table_name = any (select table_name from dba_tables where table_name like ‘CL_%’)
AND role = any (select role from dba_roles where role like ‘COMMON%’)
ORDER BY role, table_name;

col “Grant Privileges” format a50
SELECT ‘grant SELECT, INSERT, UPDATE on “COMMON”.’ || table_name “Grant Privileges”, ‘TO ‘ || role || ‘;’ “To Role”
FROM dba_tables
, dba_roles
WHERE owner = ‘COMMON’
AND table_name = any (select table_name from dba_tables where table_name like ‘CT_%’)
AND role = any (select role from dba_roles where role like ‘COMMON%’)
ORDER BY role, table_name;

8. Grant schema role permissions:

GRANT SCHEMA_DEVELOPER TO COMMON;
GRANT COMMON_TABLE_USER TO SCHEMA_DEVELOPER;
GRANT COMMON_TABLE_USER TO SCHEMA_USER;

Posted on by — Leave a comment

Scripted Backups Example 2

USE master

GO


DECLARE @theday char(1)

      , @file varchar(128)


   SET @theday = datepart(dw, getdate())


   ALTER DATABASE dbname SET RECOVERY SIMPLE;


   SET @file = 'D:BACKUPSdbname_' + @theday + '.bak';


   BACKUP DATABASE dbname TO DISK = @file WITH INIT;


   BACKUP LOG dbname WITH TRUNCATE_ONLY;


 GO

This does a 7 day "rolling backup", overwriting the backup from last week. Set it up on a nightly job.

Posted on by — Leave a comment

Scripted Backups Example 1

USE master
GO
DECLARE @dbs AS TABLE ( id int identity(1,1), dbname sysname )

DECLARE @id int
, @dbname sysname
, @path varchar(128)
, @file nvarchar(255)
, @theday char(1)

SET @path = ‘D:DEV_BACKUPS’
SET @theday = datepart(dw, CURRENT_TIMESTAMP)

INSERT INTO @dbs ( dbname )
SELECT name
FROM sys.databases
WHERE database_id > 4 –not system dbs
AND state = 0 –online
ORDER BY name

SELECT @id = max(id) FROM @dbs

WHILE @id > 0
BEGIN
SELECT @dbname = dbname FROM @dbs WHERE id = @id
SET @file = @path + ” + @dbname + ‘_BAK’ + @theday + ‘.bak’
BACKUP DATABASE @dbname TO DISK = @file WITH INIT;
BACKUP LOG @dbname WITH TRUNCATE_ONLY;
SET @id = @id – 1
END
GO

Posted on by — Leave a comment

Default data file location

To prevent the C: drive from filling up, it is a good idea to set the database default location. To do this:

1. Open SQL Server Management Studio
2. Right click the server instance
3. Select “Properties”
4. In the Server Properties window, select “Database Settings”
5. Under “Database default locations”, specify path for “Data:” and “Log:”, for example: “D:SQLDATA”

Additionally, if space on the C: drive is limited, check the properties of the TEMPDB.

This can be found under the “Databases” –> “System Databases” branches in the server’s tree-view.

First, since the tempdb does not autoshrink, you can manually shrink it by right-clicking tempdb and selecting “Task”–>”Shrink”–>”Database”.

Next, right-click the tempdb database and select “Properties”. Then select “Files”. You can set the “tempdev.mdf” file to be restricted growth and add an additional database file that is unrestricted on another drive.

Posted on by — Leave a comment

Invalid Heap Size

Was getting the following error when trying to start the OAS server:

D:OASopmnbin>opmnctl startall
opmnctl: starting opmn and all managed processes…
====================================================================
opmn id=MYSERVER:6200
1 of 2 processes started.
ias-instance id=MYSERVER.XXXX.XXXXX ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ias-component/process-type/process-set:
default_group/MYAPP/default_group/
Error
–> Process (index=1,uid=809764963,pid=3936)
failed to start a managed process after the maximum retry limit
Log:
D:OASopmnlogsdefault_group~MYAPP~default_group~1.log

Here’s what the log said:

——–
09/02/17 12:37:26 Start process
——–
Error occurred during initialization of VM
Incompatible initial and maximum heap sizes specified

Cracked open the config file and found the following:

D:OASopmnconfopmn.xml

<data id=”java-options” value=”-server -mx512M -ms1024M -Xrs -XX:MaxPermSize=256M -XX:AppendRatio=3 -Djava.security.policy=$ORACLE_HOME/j2ee/MYAPP/config/java2.policy -Djava.awt.headless=true -Dhttp.webdir.enable=false -XX:+UseConcMarkSweepGC -XX:+CMSPermGenSweepingEnabled -XX:+CMSClassUnloadingEnabled”/>
Fixed it:

<data id=”java-options” value=”-server -mx1024M -ms512M -Xrs -XX:MaxPermSize=256M -XX:AppendRatio=3 -Djava.security.policy=$ORACLE_HOME/j2ee/MYAPP/config/java2.policy -Djava.awt.headless=true -Dhttp.webdir.enable=false -XX:+UseConcMarkSweepGC -XX:+CMSPermGenSweepingEnabled -XX:+CMSClassUnloadingEnabled”/>

Posted on by — Leave a comment

Tracing Calls to Stored Procedures from C

//where cmd is of type SqlCommand

Console.WriteLine(“”);
Console.WriteLine(cmd.CommandText);
String sep = ” “;
foreach (SqlParameter param in cmd.Parameters)
{
if (param.SqlValue.Equals(“Null”))
{
Console.WriteLine(sep + param.ParameterName + “=” + param.SqlValue);
}
else
{
switch (param.SqlDbType)
{
case SqlDbType.NChar:
case SqlDbType.NVarChar:
case SqlDbType.VarChar:
case SqlDbType.Char:
case SqlDbType.Date:
case SqlDbType.DateTime:
case SqlDbType.DateTime2:
case SqlDbType.SmallDateTime:
case SqlDbType.Text:
Console.WriteLine(sep + param.ParameterName + “='” + param.SqlValue + “‘”);
break;
default:
Console.WriteLine(sep + param.ParameterName + “=” + param.SqlValue);
break;
}
}
sep = “, “;
}
Console.WriteLine(“”);