Cannot see TFS2010 alerts in the Alerts Explorer

PROBLEM:
You cannot see your TFS2010 alerts in the Alerts Explorer

SOLUTION:
This problem can occur after you move your TFS server from one hardware to another.
The following solution removes the old alerts that not configured correctly.
After removing the old alerts, you should create new alerts using the Alerts Explorer

1. Connect to your TFS database and run the following query to get the list of alerts and their ID

select *
from Tfs_DefaultCollection.dbo.tbl_EventSubscription
order by id

2. Open the command prompt in your TFS server, change directory to TFS tools and run the bissubscribe.exe tool with IDs you found in the above select (in this example it 762)

CD "C:\Program Files\Microsoft Team Foundation Server 2010\Tools"
bissubscribe.exe /unsubscribe /collection http://localhost:8080/Tfs/DefaultCollection /id 762

3. Open Alerts Explorer and configure your new alerts

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Search box for Team Foundation Server 2010

PROBLEM:
You want to search TFS 2010 items

SOLUTION:
This extension creates a search box in a Visual Studio 2010 toolbar and Team menu which is used to pull up a work item directly by its ID or search through work items based on text entered.

The default search fields are Title, History, and Description. If you want to modify which work item fields are searchable, type in "--template" (no quotes) into the search box and hit enter. This will open the search template. Just add another "Or" statement, pick the field name, select an operator, type "[search]" (no quotes) in the value field, and hit ctrl + s to save. The next time you run a search it will use the modified search template. 

http://visualstudiogallery.msdn.microsoft.com/en-us/3f31bfff-5ecb-4e05-8356-04815851b8e7

 If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

What are Team Foundation Server alternatives?

PROBLEM:

What are Team Foundation Server alternatives?

SOLUTION:

The free alternative 

Component	Product	Price
Source Control	Subversion / Mercurial	Free
Project Management	Trac	Free
Build	CruiseControl.NET / TeamCity	Free (TeamCity is free up to 20 users)

The commercial alternative (improved project management and build)

Component	Product	Price
Source Control	Subversion / Mercurial	Free
Project Management	Jira (with GreenHopper)	Jira (50 Users) – $3300
Build	TeamCity (Enterprise) / Finalbuilder Server	TeamCity - $1999 FinalBuilder (50 users) $3500

 If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Create basic MAIN-DEV branches in Team Foundation Server 2010

PROBLEM:
You want to create basic MAIN-DEV branches in Team Foundation Server 2010

SOLUTION:

1. Open the source control in the new team project
2. Get latest of the new empty source control project (will also map)
3. Click on the new folder button and add a folder called “Main”
4. Check-in the “Main” folder
5. Right-click the “Main” folder and select “branching and merging” --> “convert folder to branch”
6. “Main” branch is created
7. Right click on the “Main” branch and from the menu select “branching and merging” --> “branch”
8. Change the name of the branch from “Main-branch” to “Dev”
9. “Dev” branch is created

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Basic Oracle storage principles in 1 hour

PROBLEM:

You want to learn some basic Oracle storage principles in 30 minutes:

1. Logical & Physical

• Database
• Datafiles
• Tablespace
• Segments
• Extents (and HWM)
• Oracle blocks
• OS blocks
• Schemas

2. Tablespace Types

• SYSTEM
• SYSAUX
• Temporary
• UNDO
• User Defined tablespaces

3. In which Tablespace a new table will be created?

• Explicitly specifing the Tablespace
• If the Tablespace not explicitly specified, new table will be created in user Default Tablespace
• If there is no User Defalt Tablespace, it will be created in the Database Default Tablespace (10g)
• If there is no Database Defalt Tablespace, the table will be created in the System Tablespace

4. Extent Management

• Locally managed VS. Dictionary managed
• Locally managed - Extent allocation: AUTOALLOCATE VS. UNIFORM

5. Resize Tablespace

• Add a new file
• Increase an existing datafile
• Manual Resize - RESIZE
• Automatic resize - AUTOEXTEND

6. Online/Offline, Read Only/Read Write

7. OMF Oracle-Managed Files

SOLUTION:

-- STORAGE - Logical & Physical
-- 1. Database
-- Database ---< Tablespace
-- Tablespace: Within a database the tablespace is the largest logical storage structure. Every database must consist of one or more tablespaces. Every tablespace must belong to one and only -- one database. Tablespace size = sum of data files size

-- Database ---< Datafiles
-- Every Database must consist of one or more datafiles. Each datafile must belong to one and only one Database.

-- 2. Tablespace
-- Tablespace ---< Segments
-- Segment: Within a tablespace, space is allocated to segments. A segment is an object in the database that requires storage, such as a table or an index. A view, on the other hand, is not a -- segment, since it does not store data; it is just a prewritten query that allows easy access to data stored in tables.
-- Every tablespace may contain one or more segments. Every segment must exist in one and only one tablespace.

-- Tablespace ---< Datafiles
-- Every tablespace must consist of one or more datafiles. Each datafile must belong to one and only one tablespace.

-- 3. Segments
-- Segment ---<  Extents
-- Extent: When space is allocated for segments in a tablespace, it is allocated in extent units. Every segment must consist of one or more extents. Each extent must belong to one and only one segment.

-- Segments >---< Datafiles
-- Every datafile consists of one or more segments. Each segmets can belong to one or more datafiles

-- 4. Extents
-- Extents  ---< Oracle blocks (Default 8k)
-- Oracle block: Oracle block is the smallest logical storage unit for every Oracle I/O request at least one Oracle block is being read. Every extent must consist of one or more Oracle blocks. Each Oracle block may belong to one and only one extent.

-- Extents >--- Datafiles
-- Every extent must be located in one and only one datafile. The space in the datafile may be allocated as one or more extents. Delete operations (with / without commit) won't change table High Water Mark (HWM)

-- 5. Oracle blocks
-- Oracle block ---< OS blocks
-- Every Oracle block must consist of one or more operating system blocks. Every operating system block may be part of one and only one Oracle block. It is a good idea to keep the operating -- system block size the same as the Oracle block size. This way, for every Oracle I/O request the operating system needs to retrieve only one block.

-- Datafiles ---< OS Blocks
-- OS Block: Operating system blocks are the minimum allocation unit for the file system. Each file system has its own minimum and default size. Every datafile must consist of one or more operating system blocks. Each operating system block may belong to one and only one datafile.

-- Schemas: There is no relationship between a tablespace and a schema. Objects in the same schema can be in different tablespaces, and a tablespace can hold objects from different schemas.

-- Tablespace Types

-- The SYSTEM tablespace
-- * The SYSTEM tablespace contains the data dictionary—internal tables that describe the structure of the database itself, all of the objects in it, users, roles, and privileges.
-- * The SYSTEM tablespace also contains the SYSTEM undo or rollback segment (also called SYSTEM), which can be used only for operations on objects stored in the SYSTEM tablespace.
-- * Oracle Database creates The SYSTEM tablespace automatically when the database is created.
-- * User objects should be kept out of the SYSTEM tablespace in order to keep it operating efficiently.
-- * Cannot be taken offline

-- The SYSAUX tablespace (10g)
-- * The SYSAUX tablespace is also considered a SYSTEM tablespace and is used to store statistical and other Oracle tools information (AWR, OEM and many more)
-- * The SYSAUX tablespace is always created during database creation or database upgrade

-- The Temporary tablespaces (9i)
-- * Temporary tablespaces are used mainly to manage space for database sort operations (Server Process will try to sort data in the PGA by default, if it's not possible it will sort it in the Temporary tablespaces)
-- * When the SYSTEM tablespace is locally managed, you must define at least one default temporary tablespace when creating a database.
-- * A locally managed SYSTEM tablespace cannot be used for default temporary storage.
-- * If SYSTEM is dictionary managed and if you do not define a default temporary tablespace when creating the database, then SYSTEM is still used for default temporary storage. However, you will receive a warning in ALERT.LOG saying that a default temporary tablespace is recommended and will be necessary in future releases.
-- * Cannot be taken offline

-- The UNDO tablespaces
-- * Holds information that is used to roll back, or undo, changes to the database
-- * A database can contain more than one undo tablespace, but only one can be in use at any time.
-- * Cannot be taken Offline

-- User Defined tablespaces
-- * User defined tablespaces created by the user

-- Viewing logical & physical storage using System Tables

-- Redo Log Files:
SELECT * FROM v$log;
SELECT * FROM v$logfile;

-- Manual log switch:
-- ALTER SYSTEM SWITCH LOGFILE;

-- Tablespaces and Data files
DESC DBA_TABLESPACES

-- Information about all tablespaces in the DB:
SELECT TABLESPACE_NAME, CONTENTS, EXTENT_MANAGEMENT, ALLOCATION_TYPE
FROM DBA_TABLESPACES;

-- Information about all data files in the DB:
DESC DBA_DATA_FILES

col file_name for a50
SELECT FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 File_Size_MB
FROM DBA_DATA_FILES;

-- Tablespace Size
-- Allocated Size:
SELECT TABLESPACE_NAME, SUM(bytes)/1024/1024 size_mb, COUNT(*) files
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME;

-- Free Space:
SELECT TABLESPACE_NAME, SUM(bytes)/1024/1024 size_mb
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME;

-- Via OEM: Server => (Storage) => Tablespaces / Datafiles

-- In which Tablespace a new table will be created?

-- Explicitly specifing the Tablespace
CREATE TABLE newtab (id NUMBER) TABLESPACE users;

-- If the Tablespace not explicitly specified, new table will be created in user
-- Defalt Tablespace
SELECT username, DEFAULT_TABLESPACE
FROM dba_users;

-- If there is no User Defalt Tablespace, it will be created in the
-- Database Defalt Tablespace (10g)
SELECT * FROM  database_properties
WHERE PROPERTY_NAME LIKE '%TABLESPACE%'

ALTER DATABASE DEFAULT TABLESPACE example;

-- If there is no Database Defalt Tablespace, the table will be created in the System Tablespace

-- Creating a new Tablespace

CREATE TABLESPACE MY_TBS
DATAFILE 'c:\my_tbs_file1.dbf' SIZE 10M ,
'c:\my_tbs_file2.dbf' SIZE 10M

DROP TABLESPACE MY_TBS ;

DROP TABLESPACE MY_TBS INCLUDING CONTENTS AND DATAFILES ;

CREATE BIGFILE TABLESPACE BIGFILE_TBS
DATAFILE 'C:\BIGFILE.DBF'SIZE 100M ;

DROP TABLESPACE BIGFILE_TBS INCLUDING CONTENTS AND DATAFILES ;

-- Bigfile VS. Smallfile
-- * A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion (232) blocks. The maximum size of the single datafile or tempfile is 32TB (for a tablespace with 8K blocks).
-- * A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (222) blocks. (32 GB for 8k block)

-- Locally managed VS. Dictionary-managed:

-- Dictionary managed
-- * Dictionary-managed tablespaces are the historical way of managing extents requires any allocation or deallocation of an extent to update a table in the data dictionary.
-- * This means that any time an extent is allocated to a table, the data dictionary must be touched to record the change. When a table is dropped or truncated, the data dictionary must be changed. Because of the amount of work required on the data dictionary when using dictionary-managed extents, they are no longer recommended and exist primarily for backward compatibility.
-- * All tablespaces should be created withlocal extent management

CREATE TABLESPACE dict_tbs
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\dicttbs.dbf' SIZE 10m
EXTENT MANAGEMENT DICTIONARY

CREATE TABLE am05 (col1 number)
STORAGE (initial 100mb next 10mb pctincrease 50);

Dictionary managed Extent allocation
INITIAL - size of the first extent (100)
NEXT    - size for the next extent (10)
PCTINCREASE - (15)

-- If System TBS is Locally managed, It's not possible to create DM TBS

-- Locally managed
-- * Locally managed extents are more efficient and are recommended for all database data
-- * In a tablespace where extents are locally managed, free extents are stored in a bitmap in the tablespace
-- * As an extent is allocated to a segment or freed up because a segment was dropped, truncated, or resized, the bitmap is updated to reflect the change

CREATE TABLESPACE auto_tbs
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\auto_tbs.dbf' SIZE 25M
EXTENT MANAGEMENT LOCAL

-- Locally managed - Extent allocation: AUTOALLOCATE VS. UNIFORM
-- * AUTOALLOCATE, allocation method
-- * First 16  extents --> 64kb
-- * Next  63  extents --> 1MB
-- * Next  120 extents --> 8MB 
-- * Ramaining extents --> 64MB 

-- DROP TABLESPACE auto_tbs INCLUDING CONTENTS AND DATAFILES ;

CREATE TABLESPACE auto_tbs
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\auto_tbs.dbf' SIZE 25M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

SELECT TABLESPACE_NAME, CONTENTS, EXTENT_MANAGEMENT, ALLOCATION_TYPE
FROM DBA_TABLESPACES;

col FILE_NAME for a40
COL TABLESPACE_NAME for a15
SELECT FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 File_Size_MB
FROM DBA_DATA_FILES;

-- Create a table in the tablespace:
CREATE TABLE auto_tab (a VARCHAR2(3000))
   TABLESPACE auto_tbs;

SELECT TABLE_NAME, TABLESPACE_NAME
FROM DBA_TABLES
WHERE TABLESPACE_NAME = 'AUTO_TBS';

-- See how much space is allocated to the table:
col SEGMENT_NAME for a30
SELECT SEGMENT_NAME, BYTES/1024, EXTENTS
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = 'AUTO_TAB';

-- See how many extents:
DESC DBA_EXTENTS
SELECT SEGMENT_NAME, EXTENT_ID, BYTES/1024, BLOCKS
FROM DBA_EXTENTS
WHERE segment_name = 'AUTO_TAB';

-- Fill up the table with data:
BEGIN
    FOR i IN 1..5000 LOOP
        INSERT INTO AUTO_TAB VALUES (LPAD('*',3000,'*'));
    END LOOP;
END;

-- Check segment size and extents again...

-- How to drop Tablespace

DROP TABLESPACE auto_tbs
DROP TABLESPACE auto_tbs INCLUDING CONTENTS

-- but what about the datafiles ?
-- Creating the tablespace again with the [REUSE] option
CREATE TABLESPACE auto_tbs
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\auto_tbs.dbf' SIZE 25M
REUSE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

-- 1 [DROP TABLESPACE] and remove data files manually
-- 2 DROP TABLESPACE auto_tbs INCLUDING CONTENTS AND DATAFILES


CREATE TABLE [STORAGE]

CREATE TABLE mytab2 (id number)
STORAGE (INITIAL 100m);

SELECT SEGMENT_NAME, EXTENT_ID, BYTES/1024, BLOCKS
FROM DBA_EXTENTS
WHERE segment_name = 'MYTAB2';

DROP TABLE MYTAB2

-- Advantages
-- 1. reduce I/O when the table is being scanned
-- 2. Improve performance when Oracle allocates the extents

-- Local - UNIFORM SIZE

DROP TABLESPACE uniform_tbs INCLUDING CONTENTS AND DATAFILES ;

CREATE TABLESPACE uniform_tbs
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\uni_tbs.dbf' SIZE 25M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M;

SELECT TABLESPACE_NAME, CONTENTS, EXTENT_MANAGEMENT, ALLOCATION_TYPE, NEXT_EXTENT / 1024
FROM DBA_TABLESPACES;

-- Create a new table in it:
CREATE TABLE uniform_tab (a VARCHAR2(3000))
TABLESPACE uniform_tbs;

-- See how much space is allocated to the table:
SELECT SEGMENT_NAME, BYTES/1024, EXTENTS
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = 'UNIFORM_TAB';

-- See how many extents:
col segment_name for a15
SELECT SEGMENT_NAME, EXTENT_ID, BYTES/1024, BLOCKS
FROM DBA_EXTENTS
WHERE segment_name = 'UNIFORM_TAB';

-- Fill up the table with data:
BEGIN
    FOR i IN 1..5000 LOOP
        INSERT INTO UNIFORM_TAB VALUES (LPAD('*',3000,'*'));
    END LOOP;
END;

-- See how many extents:
SELECT SEGMENT_NAME, EXTENT_ID, BYTES/1024, BLOCKS
FROM DBA_EXTENTS
WHERE segment_name = 'UNIFORM_TAB';

-- Other tablespaces:
SELECT TABLESPACE_NAME, CONTENTS, EXTENT_MANAGEMENT, ALLOCATION_TYPE
FROM DBA_TABLESPACES;

-- Check free space:
SELECT TABLESPACE_NAME, BYTES/1024/1024
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME = 'UNIFORM_TBS';

RESIZE TABLESPACE
-- Fill up:
BEGIN
    FOR i IN 1..5000 LOOP
        INSERT INTO UNIFORM_TAB VALUES (LPAD('*',3000,'*'));
    END LOOP;
END;
/

[ORA-01653: unable to extend table SYS.UNIFORM_TAB by 256 in tablespace]

-- Add a new file

ALTER TABLESPACE UNIFORM_TBS
 ADD DATAFILE  'C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNIFORM_TBS02.DBF' size 25m;

ALTER TABLESPACE UNIFORM_TBS
 DROP DATAFILE  'C:\UNIFORM_TBS02.DBF' ;

-- Fill up:
BEGIN
    FOR i IN 1..5000 LOOP
        INSERT INTO UNIFORM_TAB VALUES (LPAD('*',3000,'*'));
    END LOOP;
END;

SELECT FILE_NAME, BYTES/1024/1024 File_Size, MAXBYTES/1024/1024 Max_Size,
AUTOEXTENSIBLE, INCREMENT_BY*8/1024 Growth
FROM dba_data_files
WHERE TABLESPACE_NAME = 'UNIFORM_TBS';

-- Fill up again:
BEGIN
    FOR i IN 1..5000 LOOP
        INSERT INTO UNIFORM_TAB VALUES (LPAD('*',3000,'*'));
    END LOOP;
END;

-- Increase an existing datafile, Manual Resize  - RESIZE

ALTER DATABASE DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNIFORM_TBS02.DBF' RESIZE 50M;

ALTER DATABASE DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNIFORM_TBS02.DBF' RESIZE 30M;

SELECT FILE_NAME, BYTES/1024/1024 File_Size, MAXBYTES/1024/1024 Max_Size,
AUTOEXTENSIBLE, INCREMENT_BY*8/1024 Growth
FROM dba_data_files
WHERE TABLESPACE_NAME = 'UNIFORM_TBS';

SELECT TABLESPACE_NAME, SUM(bytes)/1024/1024 size_mb, COUNT(*) files
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME;

BEGIN
    FOR i IN 1..5000 LOOP
        INSERT INTO UNIFORM_TAB VALUES (LPAD('*',3000,'*'));
    END LOOP;
END;
/

-- Automatic resize - AUTOEXTEND

ALTER DATABASE
 DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNIFORM_TBS02.DBF'
 AUTOEXTEND ON NEXT 10m MAXSIZE 150m;

DROP TABLESPACE uniform_tbs INCLUDING CONTENTS AND DATAFILES ;

CREATE TABLESPACE uniform_tbs
 DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\uni_tbs.dbf' SIZE 25M
 AUTOEXTEND ON NEXT 10M ;

CREATE TABLE uniform_tab (a VARCHAR2(3000))
 TABLESPACE uniform_tbs;

SELECT SEGMENT_NAME, BYTES/1024, EXTENTS
 FROM DBA_SEGMENTS
 WHERE SEGMENT_NAME = 'UNIFORM_TAB';

col segment_name for a15
SELECT SEGMENT_NAME, EXTENT_ID, BYTES/1024, BLOCKS
 FROM DBA_EXTENTS
 WHERE segment_name = 'UNIFORM_TAB';

BEGIN
    FOR i IN 1..7500 LOOP
        INSERT INTO UNIFORM_TAB VALUES (LPAD('*',3000,'*'));
    END LOOP;
END;
/

SELECT SEGMENT_NAME, EXTENT_ID, BYTES/1024, BLOCKS
  FROM DBA_EXTENTS
  WHERE segment_name = 'UNIFORM_TAB';

col FILE_NAME for a40
COL TABLESPACE_NAME for a15
SELECT FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 File_Size_MB
FROM DBA_DATA_FILES;


ONLINE/OFFLINE  READ ONLY/READ WRITE

SELECT tablespace_name, status
FROM dba_tablespaces;

-- ONLINE/OFFLINE
-- You may want to take a tablespace offline for any of the following reasons:
-- * To make a portion of the database unavailable while allowing normal access to the remainder of the database
-- * To perform an offline tablespace backup (even though a tablespace can be backed up while online and in use)
-- * To rename or relocate tablespace datafiles
-- * When a tablespace is taken offline, the database takes all the associated files offline.
-- * You cannot take the following tablespaces offline: SYSTEM, The undo tablespace, Temporary tablespaces

ALTER TABLESPACE example OFFLINE;
ALTER TABLESPACE example ONLINE;

READ ONLY/READ WRITE

ALTER TABLESPACE example READ ONLY;
SELECT last_name from hr.employees WHERE employee_id = 100;
UPDATE hr.employees SET salary=salary;
ALTER TABLESPACE example READ WRITE ;

-- OMF Oracle-Managed Files

-- Oracle-Managed Files
-- * Using Oracle-managed files simplifies the administration of an Oracle Database.
-- * Oracle-managed files eliminate the need for you, the DBA, to directly manage the operating system -- files comprising an Oracle Database.
-- * You specify operations in terms of database objects rather than filenames.
-- * The database internally uses standard file system interfaces to create and delete files as needed.

-- Enabling the Creation and Use of Oracle-Managed Files

-- DB_CREATE_FILE_DEST
-- This initialization parameter allow Oracle use the Oracle-managed files for Datafiles
-- and Temporary files

show parameter db_create_file

ALTER SYSTEM SET db_create_file_dest = 'E:\app\Owner\oradata\orcl';

CREATE TABLESPACE tbs_3 ;
-- By default, OMF Files are 100MB, Autoextentsible (unlimited)
DROP TABLESPACE tbs_3  ;
CREATE TABLESPACE tbs_3 DATAFILE SIZE 40M;
DROP TABLESPACE tbs_3  ;
CREATE TABLESPACE tbs_3 DATAFILE SIZE 40M, SIZE 20M ;
DROP TABLESPACE tbs_3  ;

CREATE TABLESPACE tbs_3 DATAFILE SIZE 40M, SIZE 20M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

ALTER TABLESPACE tbs_3 ADD DATAFILE;

DROP TABLESPACE tbs_3  ;

-- Is it still possible to use Non-OMF?
CREATE TABLESPACE mytbs
DATAFILE 'C:\oracle\mytbs.dbf' SIZE 10m

-- Advantages
-- * DBAs don't need to specify file names, locations and sizes when creating a tablespace or database
-- * Automatic removal of files when a tablespace or log file is dropped
-- * Simplified creation of test and development systems

-- Disadvantages
-- * Can only be used with file systems, not with RAW Volumes
-- * Generated file names and locations might not be according to the site's naming standards
-- * Limited scope for file placement and I/O tuning may impact performance (although locations can be altered dynamically)

-- DEMO - MIGRATION
SELECT tablespace_name, block_size
FROM dba_tablespaces;

-- Create table
CREATE TABLE longrec_table (a varchar2(3000),
                            b varchar2(3000),
                            c varchar2(3000))
   TABLESPACE USERS;

BEGIN
FOR i IN 1..100 LOOP
    INSERT INTO LONGREC_TABLE(a)
     VALUES (LPAD('*',3000,'*'));
END LOOP;
END;

-- Check stats - How many blocks is the table taking and are there any migrated rows? (CHAIN_CNT):
SELECT NUM_ROWS, CHAIN_CNT, AVG_ROW_LEN
FROM dba_tables
WHERE TABLE_NAME = 'LONGREC_TABLE';

-- Still no data in dba_tables - must analyze the table:
-- Analyze
ANALYZE TABLE longrec_table COMPUTE STATISTICS;

-- Increase each row size from 3K to 6K:
UPDATE longrec_table
SET b=LPAD('*',3000,'*');

ANALYZE TABLE longrec_table COMPUTE STATISTICS;

-- Since block size is 8K, Half the rows have migrated (The first row in each block that was updated):
SELECT NUM_ROWS, CHAIN_CNT, AVG_ROW_LEN
FROM dba_tables
WHERE TABLE_NAME = 'LONGREC_TABLE';

-- Rebuild the table to fix the problem:
ALTER TABLE longrec_table MOVE TABLESPACE users;

-- ASSM - Automatic Segment Space Management - From 9i, Default from 10g
-- Managing block available for insert in bitmaps instead of free-lists (Manual)

SELECT tablespace_name, segment_space_management
FROM dba_tablespaces;

-- If the tablespace is Locally managed,
-- we can simply add the SEGMENT SPACE MANAGEMENT clause:
CREATE TABLESPACE uniform_tbs
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\uni_tbs.dbf' SIZE 25M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M
SEGMENT SPACE MANAGEMENT AUTO;

DEMO - Chaining
-- Add the third column:
UPDATE longrec_table
SET c=LPAD('*',3000,'*');

-- Analyze
ANALYZE TABLE longrec_table COMPUTE STATISTICS;

-- Check stats, and see that ALL rows are now split:
SELECT NUM_ROWS, CHAIN_CNT, AVG_ROW_LEN
FROM dba_tables
WHERE TABLE_NAME = 'LONGREC_TABLE';

-- Rebuilding the table - does not help... :
ALTER TABLE longrec_table MOVE TABLESPACE users;
-- (Check stats...)

Multiple Block Size Support
-- See current tablespaces and their block size
SELECT tablespace_name, block_size
FROM dba_tablespaces;

-- Try to create one with a block size of 16K
CREATE TABLESPACE longrec_tbs
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\longrec_tbs01.dbf' SIZE 40M
BLOCKSIZE 16K;

-- See current configured Database Buffer Caches:
SHOW PARAMETER cache_size

ALTER SYSTEM SET db_16k_cache_size=12M [scope=spfile];

CREATE TABLESPACE longrec_tbs
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\longrec_tbs01.dbf' SIZE 40M
BLOCKSIZE 16K;

-- Solve with moving the table to a tablespace with a bigger block size:
ALTER TABLE longrec_table MOVE TABLESPACE longrec_tbs;

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Rebuild a full-text catalog

PROBLEM:
How do I rebuild a full-text catalog using SQL Server Management Studio?

SOLUTION:
To rebuild a full-text catalog:

1. In Object Explorer, expand the server, expand Databases, and then expand the database that contains the full-text catalog that you want to rebuild.
2. Expand Storage, and then expand Full Text Catalogs.
3. Right-click the name of the full-text catalog that you want to rebuild, and select Rebuild.
4. To the question Do you want to delete the full-text catalog and rebuild it?, click OK.
5. In the Rebuild Full-Text Catalog dialog box, click Close.

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Information about current users, sessions and processes

PROBLEM:
You need information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. You want to filtered it to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session

SOLUTION:
Listing all current processes

USE master;
GO
EXEC sp_who2
GO

Listing a specific user's process

USE master;
GO
EXEC sp_who2 'domain\joe'
GO

Displaying all active processes

USE master;
GO
EXEC sp_who2 'active'
GO

Displaying a specific process identified by a session ID

USE master;
GO
EXEC sp_who2 '10' --specifies the process_id
GO

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Basic Oracle networking principles in 10 minutes

PROBLEM:

You want to learn some basic Oracle networking principles in 10 minutes:

1. What is Connect-Identifier

2. What is Connect Descriptor (Machine, Port, Protocol, Actual instance/service name)

3. What is Naming methods

• Local Naming (Uses a local configuration file - TNSNAMES.ORA)
• EZCONNECT (Uses a TCP/IP connect string )
• conn user_name/password@host_name:[port_number]/service_name
• LDAP (Uses a Directory Server)
• External Naming (Uses a 3rd party naming service)

4. What is a LISTENER

• Static registration of all Instances in LISTENER.ORA
• Dynamic registration - PMON

5. What is TNSPING

SOLUTION:

First change the SQL prompt to see current login in the following file: C:\app\Administrator\product\11.2.0\dbhome_1\sqlplus\admin\glogin.sql

set sqlprompt "_USER>"

Open SQL*PLUS and connect locally as sysdba

conn / as sysdba

Edit $oracle_home\network\admin\sqlnet.ora

change

SQLNET.AUTHENTICATION_SERVICES=(NTS)

to

#SQLNET.AUTHENTICATION_SERVICES=(NTS)

conn / as sysdba

[ORA-01031: insufficient privileges]

conn sys/oracle as sysdba

Connect locally as any other user

conn hr/hr

change the registry entry for ORACLE_SID

host

set oracle_sid = orc123

sqlplus / nolog

conn hr/hr

[ORA-12560: TNS:protocol adapter error]

Conect remotely:

connect user_name/password@connect_identifier

connect hr/hr@orcl

Naming method - Connect-Identifier => Connect Descriptor

-------------------------------------------------------

Which actual database is ORCL pointing to, and where is it located?

Oracle uses a "Naming Method" to translate the the "Connect-Identifier" to a "Connect Decriptor", which includes:

   * Machine

   * Port

   * Protocol

   * Actual instance/service name

Client naming methods:

   * EZCONNECT (Uses a TCP/IP connect string)

   * Local Naming (Uses a local configuration file - TNSNAMES.ORA)

   * LDAP (Uses a Directory Server)

   * External Naming (Uses a 3rd party naming service)

All Networking files located in:

  %Oracle-Home%\Network\Admin\

  * Usually:

     * Client Side: TNSNAMES.ORA

     * Server Side: LISTENER.ORA

Can also use the environment variable "TNSADMIN" to specify shared network location

for networking files.

TNSNAMES (Local Naming)

-----------------------

Client side uses a TNSNAMES.ORA file to resolve connect identifier.

The file is located in the %Oracle-Home%\NETWORK\ADMIN\

Typical TNSNAMES Entry:

Connect identifier is ORCL11g (Logical name for the client only), which points to the ORCL db, located on the machine OraSRV3. The Listener is listening on its default port 1521, and accepting TCP requests:

ORCL11g =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = OraSRV3)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

Change the connect identifier

[ORA-12154: TNS:could not resolve the connect identifier specified] 

get  ?\NETWORK\ADMIN\TNSNAMES.ora

Change orcl to test11g

edit ?\NETWORK\ADMIN\TNSNAMES.ora

conn hr/hr@orcl

[ORA-12154: TNS:could not resolve the connect identifier specified] 

conn hr/hr@test11g

SELECT instance_name FROM v$instance;

Change the host in thsnames.ora

[ORA-12545: Connect failed because target host or object does not exist]

conn hr/hr@orcl

[ORA-12545: Connect failed because target host or object does not exist]

LISTENER

--------

Server side uses a LISTENER to handle incoming connection requests.

The Listener validates the Username and Password, and connect the client to the requested instance.

The Listener is responsible to create a server process for the user process

When connection between UP and SP established (Handoff), the communication pathway s directly between UP and SP.

The listener must know the DB Service requested by the client:

  * Until 8i - Static registration of all Instances in LISTENER.ORA

  * Since 8i - Dynamic registration - PMON registers the Instance on statup and every few minutes

Stop the listener [ORA-12541: TNS:no listener] 

----------------------------------------------

Manage the listener using the utility "LSNRCTL"

host lsnrctl help

host lsnrctl status

host lsnrctl stop

host lsnrctl start

host lsnrctl reload

host lsnrctl stop

conn hr/hr@orcl

[ORA-12541: TNS:no listener]

host lsnrctl start

Same error will occur when the port number is incorrect

Delete orcl service from the listener

[ORA-12514: TNS:listener does not currently know of service...]

edit ?\NETWORK\ADMIN\listener.ora

conn hr/hr@orcl

[ORA-12514: TNS:listener does not currently know of service requested in connect descriptor]

Dynamic registration will take place in a few minuters or can register manually from the DB:

ALTER SYSTEM REGISTER;

View the LISTENER.ORA file:

host type C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora

Trying to startup the database remotely without a static registration

[ORA-12514: TNS:listener does not currently know of service requested in connect descriptor]

edit ?\NETWORK\ADMIN\listener.ora

conn hr/hr@orcl

ALTER SYSTEM REGISTER;

conn hr/hr@orcl

conn sys/oracle@orcl as sysdba

shutdown immediate

startup

host lsnrctl reload

conn sys/oracle@orcl as sysdba

startup

TNSPING

-------

Tests the Oracle connectivity to a remote service's Listener:

host TNSPING orcl

host TNSPING no_such_service

Note: This utility only tests if the listener is available. It cannot tell if the databases behind the listener is up or not.

EZCONNECT - (10g)

-----------------

Specify the connect descriptor in the CONNECT command

No need for TNSNAMES.ORA in the clients. Uses regular host resolution in the network.

CONNECT username/password@[//][host][:port][/service_name]

Add EZCONNECT to sqlnet.ora

Only when onnecting to an Oracle database across a TCP/IP network

conn user_name/password@host_name:[port_number]/service_name

conn hr/hr@basdb_ins:1521/orcl

conn hr/hr@basdb_ins/orcl

conn hr/hr@basdb_ins:1521/orcl

Directory Naming

----------------

With the directory naming method, connect identifiers are mapped to connect descriptors contained in an LDAP-compliant directory server.

To use directory naming, you must first install and configure a directory server somewhere on your network.

Oracle provides an LDAP server (the Oracle Internet Directory) as part of the Oracle Application Server, but you do not have to use that.

If you already have a copy of Microsoft Active Directory, that will be perfectly adequate.

IBM and Novell also sell directory servers conforming to the LDAP standard.

LDAP - stands for Lightweight Directory Access Protocol.

External Naming

---------------

External naming is conceptually similar to directory naming.

The external naming method stores net service names in a supported non-Oracle naming service

it uses third-party naming services such as Sun’s Network Information Services (NIS+) or the Cell Directory Services

NIS -stands for Network Information Service.

Following steps need to be performed in order to configure your clients against the server (ldap).

In your Oracle Client configure the sqlnet.ora

  * NAMES.DIRECTORY_PATH = (LDAP)

  * NAMES.DEFAULT_DOMAIN = trivadis.com # adjust to your domain

In your Oracle Client configure the ldap.ora

  * DIRECTORY_SERVERS = (oassrv01.trivadis.com:389:636) # adjust to your servername and LDAP ports

  * DEFAULT_ADMIN_CONTEXT = "dc=trivadis,dc=com" # adjust to your Naming Context

  * DIRECTORY_SERVER_TYPE = OID

Specifying Naming Methods

-------------------------

Via NAMES.DIRECTORY_PATH in "sqlnet.ora"

For example: NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

host type C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\sqlnet.ora

Service names

-------------

For Local and Directory naming, we can specify a few network addresses for the same service.

ALTER SYSTEM SET service_names='orcl, reports,orders';

host lsnrctl status

Connecting using EZconnect

conn sys/oracle@RAM/orders

Connecting using TNSnames

reports =

 (DESCRIPTION =

   (ADDRESS_LIST =

     (ADDRESS = (PROTOCOL = TCP)(HOST = RAM)(PORT = 1521))

   )

 (CONNECT_DATA =

   (SERVICE_NAME = reports)

 )

)

conn hr/hr@reports

SELECT instance_name FROM v$instance;

col username for a15

SELECT username, service_name

FROM v$session

WHERE username IS NOT NULL;

DBLINK

------

A database link is a connection between two physical database servers that allows a client to access them as one logical database.

conn sh/sh@reports

SELECT count(*) FROM products;

conn hr/hr@orcl

--drop database link sales_db;

SELECT count(*) FROM products;

SELECT count(*) FROM sh.products;

CREATE DATABASE LINK sales_db

CONNECT TO sh IDENTIFIED BY sh

USING 'reports';

SELECT COUNT(*) FROM products@sales_db;

Disable win firewall

--------------------

Start => Control Panel => Windows Firewall => Exceptions => Add Port => Oracle, 1521 => OK

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!